ants_a

I guess we agree on them being an API that gets deployed on the database, but I disagree that it needs to be crude. It's exactly as crude as you make it. If you don't have authentication, testing, versioning, observability and source control for your database you are doing it wrong.

Stored procedures in this context are just a clumsy workaround to control the planner so your comment about API layer is irrelevant. But if you like, you can write stored procedures in a ton of different languages. And if you do not have source control for your database artifacts, you are doing it wrong. A common reason for stored procedures is to not have a bunch network roundtrips in the middle of your transaction logic while you are holding onto locks / have an open conflict window.

But this pattern is not required by PostgreSQL, it's possible to run arbitrarily complex transactions all on server side using more complex query patterns and/or stored procedures. In this case the locking time will be mainly determined by time-to-durability. Which, depending on infrastructure specifics, might be one or two orders of magnitude faster. Or in case of fast networks and slow disks, it might not have a huge effect.

One can also use batching in PostgreSQL to update the resource multiple times for each durability cycle. This will require some extra care from application writer to avoid getting totally bogged down by deadlocks/serializability conflicts.

What will absolutely kill you on PostgreSQL is high contention and repeatable read and higher isolation levels. PostgreSQL handles update conflicts with optimistic concurrency control, and high contention totally invalidates all of that optimism. So you need to be clever enough to achieve necessary correctness guarantees with read committed and the funky semantics it has for update visibility. Or use some external locking to get rid of contention in the database. The option for pessimistic locking would be very helpful for these workloads.

What would also help is a different kind of optimism, that would remove durability requirement from lock hold time, which would then result in readers having to wait for durability. Postgres can do tens of thousands of contended updates per second with this model. See the Eventual Durability paper for details.

30kW sustained/60 kW per wheel peak power is easily enough even for large passenger vehicles. Sustained could take 3 ton vehicle up a 10% grade at 120 km/h.

To make those claims it's necessary to know what work is being done while the lock is held. This includes a bunch of various resource cleanup, which should be cheap, and RecordTransactionCommit() which will grab a lock to insert a WAL record, wait for it to get flushed to disk and potentially also for it to get acknowledged by a synchronous replica. So the expected throughput is somewhere between hundreds and tens of thousands of notifies per second. But as far as I can tell this conclusion is only available from PostgreSQL source code and some assumptions about typical storage and network performance.

Core of the issue is that on the primary, commit inserts a WAL record, waits for durability, local and/or replicated, and then grabs a lock (ProcArrayLock) to mark itself as no longer running. Taking a snapshot takes that same lock and builds a list of running transactions. WAL insert and marking itself as visible can happen in different order. This causes an issue on the secondary where there is no idea of the apparent visibility order, so visibility order on secondary is strictly based on order of commit records in the WAL.

The obvious fix would be to make visibility happen in WAL order on the primary too. However there is one feature that makes that complicated. Clients can change the desired durability on a transaction-by-transaction basis. The settings range from confirm transaction immediately after it is inserted in WAL stream, through wait for local durability, all the way up to wait for it to be visible on synchronous replicas. If visibility happens in WAL order, then an async transaction either has to wait on every higher durability transaction that comes before it in the WAL stream, or give up on read-your-writes. That's basically where the discussion got stuck without achieving a consensus on which breakage to accept. This same problem is also the main blocker for adopting a logical (or physical) clock based snapshot mechanism.

By now I'm partial to the option of giving up on read-your-writes, with an opt-in option to see non-durable transactions as an escape hatch for backwards compatibility. Re-purposing SQL read uncommitted isolation level for this sounds appealing, but I haven't checked if there is some language in the standard that would make that a bad idea.

A somewhat elated idea is Eventual Durability, where write transactions become visible before they are durable, but read transactions wait for all observed transactions to be durable before committing.

There still is a Postgres deficiency that makes something similar to this pattern possible. Non-replicated transactions where the client goes away mid-commit become visible immediately. So in the example, if T1 happens on a partitioned leader, disconnects during commit, T2 also happens on a partitioned node, and T3 and T4 happen later on a new leader, you would also see the same result. However, this does not jive with the statement that fault injection was not done in this test.

Edit: did not notice the post that this pattern can be explained by inconsistent commit order on replica and primary. Kind of embarrassing given I've done a talk proposing how to fix that.

A read-write sync transaction that reads the result of a non-durable transaction cannot commit ahead of the async transaction. But there is no such guarantee for read-only transactions. So a transaction could read a non-durable state, decide that no further action is needed, and have that decision be invalidated by a crash/failover.

To make things even worse, similar things can happen with synchronously replicated transactions. If a committing transaction waiting on replication is cancelled it will become visible immediately. A reasonably written application would run a retry of the transaction, see that the result is there and conclude that no action is necessary, even though the data is not replicated and would be lost if a failover then happens.

UI wise it does not make sense to have this distinction, as the window to get durability is a small fraction of a second. But for concurrent modifications the reduction in lock duration can mean an order of magnitude throughput.

Increasing die size to run cores in a more power efficient regime is not going to work, because a) the chips are already as big as can be made, and b) competition will still push companies to run the 250 cores uber fast and figure out some way to push enough power to it.

As long as there is customer demand for this, these things will get built. Given the amount of bad press these melted connectors create, possibly with better engineered power delivery systems.

Savepoints are fully equivalent to nested transactions with the constraint that only one is concurrently active. This limitation is just from the SQL standard programming model. At least in PostgreSQL implementation it would be reasonably simple to support multiple concurrent subtransactions. They are even called subtransactions internally.

The algorithms detailed in the graph DB post can be run on top of PostgreSQL, though one has to do some of the work of the DBMS themselves (maintaining indexes, transforming query to correct form). It's extra work and requires some knowhow, but up to some extent it works "well enough".

Similarly, the CedarDB comments, that could be boiled down to the lack of column major batched executor model, are mostly applicable to analytical queries. Point query and traversal type workloads are not as different. There are various projects that work around this to some extent by sticking DuckDB into PostgreSQL . This gets vectorized execution on mass data scans, which is usually enough to get into the right ballpark.

The point being that the amount of use cases where Postgres is sufficient is highly dependent on the skill and time put into it. And at some point the extra organizational cost of going heterogeneous is going to be worth the payoff. The hard part is figuring that out ahead of time. Which is why my recommendations for starting developers is to just use Postgres until it starts to hurt, then optimize it to work well again and use the insight gained to see what functionality needs to be peeled off into a specialized system.

    SELECT date_bin('5 minutes', log_time, '2000-01-01') log_time,
           AVG(value) avg_value
    FROM logs GROUP BY 1