This is not a big database usecase. It just needs one to not do silly things like opening a new database session for every query when it's well documented that this is expensive.
Over here we use a PKI cert for that. A smartcard providing the root of that trust is provided by the government after verifying your identity using the typical stuff used for identity documents (any biometric data on file, birth certificate, etc.). That still doesn't mean that it's impossible to steal an identity, or acquire a made up one, but it does make it a whole lot harder.
What you are describing here does not match how postgres works. A read on the replica does not generate temporary tables, nor can anything on the replica create locks on the primary. The only two things a replica can do is hold back transcation log removal and vacuum cleanup horizon. I think you may have misdiagnosed your problem.
There is no backpressure from replication and streaming replication is asynchronous by default. Replicas can ask the primary to hold back garbage collection (off by default), which will eventually cause a slow down, but not blocking. Lagging replicas can also ask the primary to hold onto WAL needed to catch up (again, off by default), which will eventually cause disk to fill up, which I guess is blocking if you squint hard enough. Both will take considerable amount of time and are easily averted by monitoring and kicking out unhealthy replicas.
These are limitations in the current PostgreSQL implementation. It's quite possible to have consistent commits and snapshots across sharded databases. Hopefully some day in PostgreSQL too.
Postgres setups are typically based on physical replication, which is not an option on MySQL. My testing shows the limit to be about 177k tps with each transaction consisting of 3 updates and 1 insert.
For updating a single resource where the order of updates matters the best throughput one can hope for is the inverse of locking duration. Typical postgres using applications follow the pattern where a transaction involves multiple round trips between the application and the database to make decisions in the code running on the application server.
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.
I'm wondering if it would make sense to integrate the rim, motor and wheel bearing into a single assembly to save weight and cost. That combined with the weight and packaging benefits of not having half shafts and differentials might make it worth it. Plus there can be additional benefits, like the extra maneuverability that ZF Easy Turn and Hyundai's e-Corner have demonstrated.
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.
In that snippet are links to Postgres docs and two blog posts, one being the blog post under discussion. None of those contain the information needed to make the presented claims about throughput.
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.
Triggers are not even particularly slow. They just hide the extra work that is being done and thus sometimes come back to bite programmers by adding a ton of work to statements that look like they should be quick.
That thread is indeed about the same issue. I don't think anyone has done a more concise writeup on it.
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.
Interesting why this magic would be needed. Vanilla Postgres does support quorum commit which can do this. You can also set up the equivalent multi-AZ cluster with Patroni, and (modulo bugs) it does the necessary coordination to make sure to promote primaries in a way that does not lose transactions or makes visible a transaction that is not durable.
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.
Yes and no. On the primary durability order and visibility order are different. So an async transaction that starts committing later can become visible to readers before a sync transaction that comes before it.
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.
Consumers already have the choice of not buying the most powerful card.
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.
LLMs are not a great source for this type of information. This mess in no way articulated in what way savepoints and transactions are different.
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.
Not disagreeing, but for more context. These blog posts also have to be taken with a grain of salt as the authors obviously have their own project to promote, coloring their point of view.
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.