CYBERTEC, a PostgreSQL consultancy, publishes technical posts on Postgres performance, indexing, and features, many by Laurenz Albe.
Commenters recommend Laurenz Albe's posts as great reads, and cite its articles on UUID keys, benchmarks, and partitioned table statistics.
In most cases[1], when you update a tuple in Postgres, a new tuple is put somewhere else in the same heap, with different visibility information, "xmin", "xmax". The old tuple remains where it is. Index pointers to it likewise remain unchanged, but a new entry is added for the new tuple. The old version gains an updated "xmax" field indicating that version was deleted at a certain logical point.
Later on, VACUUM has to plow through everything and check the oldest running transaction to see whether the tuple can be "frozen" (old enough to be seen by every transaction, and not yet deleted) or the space reclaimed as usable (deleted and visible to nothing). Index tuples likewise must be pruned at this time.
In systems with an UNDO log, the tuple is mutated in place and the contents of the old version placed into a sequential structure. In the case where the transaction commits, and no existing concurrent repeatable read level transactions exist, the old version in the sequential structure can be freed, rather than forcing the system to fish around doing garbage collection at some later time to obsolete the data. This could be considered "optimized for commit" instead of the memorable "optimized for rollback."
On the read side, however, you need special code to fish around in UNDO (since the copy in the heap is uncommitted data at least momentarily) and ROLLBACK needs to apply the UNDO material back to the heap. Postgres gets to avoid all that, at the cost of VACUUM.
[1] The…
It's not 1992 anymore.
https://youtu.be/8Fb5Qgpr03g?si=Jc7dpuVgws3POiXA
It may upset someone's sense of "purity", but the SQL standard and thoughts about the relational model have long since moved on from this dogmatic view of atoms, and no, foreign keys can't perfectly model what folks need. Eventually the rubber hits the road, theory and practice diverge, and different approaches are clearly needed.
There is a place for a document atom in a database (JSON, XML, etc. that don't map well or efficiently to a traditional relational model). For arrays. (Putting in a separate table with its own PK to join is somehow more flexible? Just use unnest(…) for that.) For label trees. For geometries (GIS). For references to tables on other servers (SQL/MED). For tuples. For UUIDs. For ranges. For intervals.
You may call Date and Darwen's work fan fiction, but I have seen tremendous efficiency gains over the last few decades from engines drawing upon their insights without compromising data integrity. In fact, exclusion constraints with timestamp ranges is one area where the old strict relational model would be wholly ineffective for data integrity and avoiding race conditions cascading out to the application level.
https://www.cybertec-postgresql.com/en/postgresql-exclusion-constraints-beyond-unique/
We've migrated from Oracle to PG and are some differences in behavior have caused us headaches. Off the top of my head:
* if TX1 holds a lock acquired with SELECT FOR UPDATE on a row of table A, TX2 cannot INSERT into table B with with foreign key to A -> you need SELECT FOR NO KEY UPDATE
* WHERE A.X = FOO or B.X = FOO performs poorly[0]
* adding a column to a table blocks/is blocked by SELECTs, this can lead to a deadlock in the following scenario:
** application thread: start TX1
** application thread: SELECT FROM TENANT
** DB migration thread: attempt add column to TENANT table, blocked by AccessShareLock held by TX1
** application thread: start TX2 (we need it so that TX1 does not get rolled back if what we do here fails)
** application thread: attempt SELECT FROM TENANT (INSERT INTO A with A having an FK to TENANT has the same effect), blocked by DB migration
** application thread now waits for TX2 to complete, migration waits for TX1 to complete, TX2 waits for migration to complete
* so far we have found no way to see the query that has acquired the lock, only the last query in that TX
[0] https://www.cybertec-postgresql.com/en/rewrite-or-to-union-in-postgresql-queries/
Hmm.. some bits of this don't ring true to my understanding... for example:
"There are a lot of reasons that you don't want to coopt your database's role management to manage your app's users as well.
It doesn't scale. Postgres isn't designed to have thousands of roles"
While the general assertion that you don't want the database role management system to be conflated with your app's is certainly the common practice, the assertion that the database not scaling with many roles doesn't seem to be well supported. As a counterpoint to this part of the arguement:
https://www.cybertec-postgresql.com/en/creating-1-million-users-in-postgresql/
"The bottom line is really that PostgreSQL can easily handle a million users without any problems."
The Cybertec conclusion is more in line with what I'd expect. Database roles are really just data and if PostgreSQL can't handle a few tables with thousands of records (or millions).... you've got more scalability problems than role scalability to be worried about.
Maybe the author is conflating the well known scalability issues with the number of concurrent connections to PostgreSQL with the number of defined roles.
> databases are complicated beasts and tens of thousands if not more working years of the smartest people have been working on them since the 70s
Yes, but they've also set themselves a much harder problem. Instead of "find the proper execution for this particular query, armed with all the knowledge of the database you have" they have the problem of "find the proper execution for all possible queries, using only information that's encoded in DB statistics."
So it's no surprise that it's quite easy to find places the optimizer does silly things. To take one instance that bit me recently: or clauses representing different tables cannot be optimized well at all in postgres (https://www.cybertec-postgresql.com/en/avoid-or-for-better-performance/).
I'd still rather have optimizers than not--the optimizer does well most of the time, and my current work has far too many queries to hand-optimize. But you do end up seeing a lot of poorly optimized queries.
I mentioned this towards the bottom of the post, but to reiterate: we're extremely grateful to Laurenz for helping us out here, and his post on this is more than worth checking out: https://www.cybertec-postgresql.com/en/partitioned-table-statistics/
(plus an interesting discussion in the comments of that post on how the query planner chose a certain row estimate in the specific case that Laurenz shared!)
The other thing I'll add is that we still haven't figured out:
1. An optimal ANALYZE schedule here on parent partitions; we're opting to over-analyze than under-analyze at the moment, because it seems like our query distribution might change quite often.
2. Whether double-partitioned tables (we have some tables partitioned by time series first, and an enum value second) need analyze on the intermediate tables, or whether the top-level parent and bottom-level child tables are enough. So far just the top-level and leaf tables seem good enough.