From one minutes to 400ms

In this post, I’ll walk through a set of common misconceptions that drive teams to introduce new infrastructure when they don’t need to. All of these can be solved with vanilla PostgreSQL 18 using standard extensions available on RDS, with no special infrastructure and no distributed-systems cosplay.

The database can be used for many things people often miss:

• user roles (finance) for modularity. In particular, a proper role can be used for defining very precise interfaces on top of database objects
• domains can be defined as an application of the abtract data type to database management
• a table for transfers can be stored with a system-time temporal table
• account audits (store old versions for logs)
• transaction state machine with functions
• record size estimation
• Heap-Only Tuple to avoid write amplification when primary keys are aligned with immutable columns. It enables faster updates. See https://ebellani.github.io/blog/2026/all-you-need-is-postgresql/#introduction:~:text=Enabling%20HOT%20Updates%20for%20Transfers,-By
• excellent OLTP (quick read and write individual rows) with a primary key reflecting the access pattern
• view to reuse queries from multiple tables
• excellent OLAP: large volumes of data while allowing quick query response times: a finance.balance_ledger table stores a snapshot of both balances after every transaction.
• incremental maintenance via triggers
• correct isolation with alter role finance set default_transaction_isolation = 'serializable';. If a cyclic transaction is detected, then one of the transaction fails. The application must be prepared to retry aborted transactions, but the invariants are never violated.

Key takeaways:

• 766 TPS overall with 0 failed transactions. The startup target of 10,000 transfers/day is ~0.12 TPS. We exceed that by over 6,000x, confirming massive headroom on modest hardware.
• 0 serialization failures: Despite 10 concurrent clients writing to 100 accounts under SERIALIZABLE isolation, no transactions were aborted. The working set is distributed across enough accounts that write contention is negligible at this scale.
• 8ms average read latency: The UNION ALL view over 4 tables plus the balance ledger lookup completes well within interactive response time, even as the tables grow throughout the 60-second run.
• 33ms average write latency: Each write transaction exercises the full constraint set that implements complex business logic (audit trails, time constraints, balances, etc) all within 33ms. This is the true cost of enforcing every business rule at the data level, and it is more than acceptable.

The full SQL is provided at the end.

Instead of doing a single join, an innocent looking for loop can end up doing tons and tons of separate queries, resulting in much slower execution.

The 1+N problem occurs if a database request is made for every item returned by the first query.

The programming language Arcadia doesn't have recursion or loops, so the problem can not exist.

Attribute creep and excessive use of foreign keys shows me is that in order to use ORMs effectively, you still need to know SQL. My contention with ORMs is that, if you need to know SQL, just use SQL since it prevents the need to know how non-SQL gets translated to SQL.

I’ve found myself thinking about the database as just another data type that has an API: the queries. The queries return values of some type, which are represented as some object in the program. By moving away from thinking of the objects in my application as something to be stored in a database (the raison d’être for ORMs) and instead thinking of the database as a (large and complex) data type, I’ve found working with a database from an application to be much simpler. And wondering why I didn’t see it earlier.

Note: In SQLite rowid tables are implemented as B+-Trees where all content is stored in the leaves of the tree, whereas WITHOUT ROWID tables are implemented using ordinary B-Trees with content stored on both leaves and intermediate nodes.

UUIDs take 5 to 16x times slower to insert. UUID without row id in SQLite are only 2 to 3 times slower.

Not random insertion is also useful to write in a database efficiently.

Ein Text-Adventure-Lernspiel für die Datenbanksprache SQL

Step into a smoky 80s detective agency, question suspects with SQL queries, and crack the case one statement at a time.

I didn't read it but look after the strategies: sequences with serial IDs, for update skip locked

So, it's a matter of your requirements. If you need massive scale and throughput (> 100k messages/sec), Kafka will be hard to beat. But if you're not operating at that scale, Postgres is hard to beat.

So, it's a matter of your requirements. If you need massive scale and throughput (> 100k messages/sec), Kafka will be hard to beat. But if you're not operating at that scale, Postgres is hard to beat.

Here's the thing: 99% of companies don't need them. The top 1% have tens of millions of users and a large engineering team to match.

The fun thing about Postgres is there is already an extension for that: PostGIS, Full-text search, JSONB, TimescaleDB, pgvectorm, and many for AI

Each database add hidden costs: backup strategy, monitoring dashboards, seceurity patches, on-call runbooks, failover testing.

SLA math: Three systems at 99.9% uptime each = 99.7% combined

The permission system should handle folders and files.

Strategies:

1. (naive) read-time permission queries
2. A simple table (RBAC role based access control).

-- RBAC: Pre-computed permissions
-- access_type: 'owner' (full control), 'shared' (read only), 'path_only' (visible but no access)
CREATE TABLE permissions (
  user_id INTEGER NOT NULL,
  resource_id INTEGER NOT NULL,
  access_type TEXT NOT NULL,
  PRIMARY KEY (user_id, resource_id),
  FOREIGN KEY (user_id) REFERENCES users(id),
  FOREIGN KEY (resource_id) REFERENCES resources(id)
);

3. Attribute-Based Access Control

This approach is very clear and composable. It works great for single-resource access checks: "can user X access resource Y?" It struggles when listing resources, as we would need to execute the policies for each resource and can't directly query the resources table with simple filters.

4. Zanzibar and ReBAC

Nice!

A case in favor of materialized views: it avoids caches; off-by-one error and so on.

There is currently a support for go, kotlin, python and typescript. It is very interesting, because it takes over one reason why ORMs are used (with migration handling and security).

The documentation is available at https://docs.sqlc.dev/en/latest/

and how to batch to optimize DELETEs