CYBERTEC Blog

I don’t generally blog about it, but I may at some point. For specific discussions on what I wrote about, I recommend (other than reading Postgres’ docs, of course) this [0] and this [1], and anything else on those sites. If you like technical podcasts, postgres.fm [2] is pretty good. [0]: https://www.cybertec-postgresql.com/en/hot-updates-in-postgresql-for-b… [1]: https://www.enterprisedb.com/blog/impact-full-page-writes?lang=en [2]: https://postgres.fm/

Also, here's a list of blog posts from Laurenz Albe (the author of the OP post): https://www.cybertec-postgresql.com/en/author/cybertec_albe/ His blog posts are a great read, I'd recommend checking them out!

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.

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-postgres…

The author doesn't reference any tests that they themselves ran, but they did link a cybertec article [0] with some benchmarks. [0] https://www.cybertec-postgresql.com/en/unexpected-downsides-of-uuid-ke…

Direct I/O is being worked on, but is not yet available. See e.g. here: https://www.cybertec-postgresql.com/en/postgresql-18-and-beyond-from-a…

Using UUIDv4 as primary key has unexpected downsides because data locality matters in surprising places [1]. A UUIDv7 primary key seems to reduce / eliminate those problems. If there is also an indexed UUIDv4 column for external id, I suspect it would not be used as often as the primary key index so would not cancel out the performance improvements of UUIDv7. [1] https://www.cybertec-postgresql.com/en/unexpected-downsides-of-uuid-ke…

It is true that DB compute is relatively expensive, but doing a join on the DB itself is obscenely more efficient vs doing it manually in a nested loop on an app server. Also, the DB is generally a lot smarter than doing quadratic operations. The usual worst case is a Merge Join which involves sorting both tables first, to do an efficient join in nlogn time. It’s just not feasible to compete with the DB for efficiency without reimplementing a DB, which is pretty yikes. See https://www.cybertec-postgresql.com/en/join-strategies-and-performance…

Work expands to fill the time of the team allocated to it. This whole article reminds me of the tech blog from Uber[1] proudly describing how they had made their own geofencing system that was worse than what comes out of the box with postgis[2]. [1] https://www.uber.com/en-GB/blog/go-geofence-highest-query-per-second-s… [2] https://www.cybertec-postgresql.com/en/beating-uber-with-a-postgresql-…

> I think because Postgres writes the entire row even if a single column has been updated. You might want to have a look at HOT [0] tuples if you haven't already. [0] - https://www.cybertec-postgresql.com/en/hot-updates-in-postgresql-for-b…

As can Postgres: https://www.cybertec-postgresql.com/en/upgrading-postgres-major-versio…

Looks like it's still being actively worked on. https://www.cybertec-postgresql.com/en/zheap-undo-logs-discarding-in-p… https://github.com/cybertec-postgresql/postgres/tree/zheap_undo_record…

https://www.cybertec-postgresql.com/en/postgresql-zheap-current-status… The last status update is from July, so seems like things are progressing.

Where did Robert Haas say this? Quick search didn't surface much, except for a blog post from July by Hans-Jürgen Schönig: https://www.cybertec-postgresql.com/en/postgresql-zheap-current-status… Doesn't sound like they discontinued the project.

This is already being developed. zheap [1] is based on the undo/redo log system that databases such as Oracle use, and will be one of the options once Postgres supports pluggable storage backends. [1] https://www.cybertec-postgresql.com/en/postgresql-zheap-current-status…

Blog post: https://www.cybertec-postgresql.com/en/pg_timetable-advanced-postgresq…

Meta: this company wrote an impressive number of articles about PostgreSQL since 2013. List at https://www.cybertec-postgresql.com/en/tag/postgresql/

This advice applies IMHO. https://www.cybertec-postgresql.com/en/postgres-scaling-advice-for-202… "So, you’re building the next unicorn startup and are thinking feverishly about a future-proof PostgreSQL architecture to house your bytes? My advice here, having seen dozens of hopelessly over-engineered / oversized solutions as a database consultant over the last 5 years, is short and blunt: Don’t overthink, and keep it simple on the database side! Instead of getting fancy with the database, focus on your application. Turn your microscope to the database only when the need actually arises, m’kay! When that day comes, first of all, try all the common vertical scale-up approaches and tricks. Try to avoid using derivative Postgres products, or employing distributed approaches, or home-brewed sharding at all costs – until you have, say, less than 1 year of breathing room available."

There is a super informative blog post from last week at https://www.cybertec-postgresql.com/en/zheap-reinvented-postgresql-sto… - discussed at https://news.ycombinator.com/item?id=24710765

also check out ora_migrator https://github.com/cybertec-postgresql/ora_migrator (another extension from Cybertec) which uses oracle_fdw under the hood. intro blog post: https://www.cybertec-postgresql.com/en/ora_migrator-moving-from-oracle…

> The big difference is that duplicate index entries can still occur in B-tree indexes. GIN indexes have to integrate new entries into the posting list every time, which makes data modifications less efficient (this is mitigated by the pending list). In contrast, PostgreSQL deduplicates B-tree entries only when it would otherwise have to split the index page. So it has to do the extra work only occasionally, and only when it would have to do extra work anyway. This extra work is balanced by the reduced need for page splits and the resulting smaller index size. https://www.cybertec-postgresql.com/en/b-tree-index-deduplication/

It's very true today. The problem discussed here is performance on complex queries (e.g. subqueries), and the query planner plays a huge role in that. The Postgres query planner has various issues. Here are two recent posts talking about planner issues: https://medium.com/@rbranson/10-things-i-hate-about-postgresql-20dbab8… https://www.cybertec-postgresql.com/en/things-could-be-improved-postgr… Also JIT compilation, while very nice and a step in the right direction, is very barebones at the moment, hardly achieving its potential. Here's a long todo list of what and how to make efficient use of JIT in postgres, by the main author of the feature. https://twitter.com/AndresFreundTec/status/1002589969616199680 Postgres release 12 did not add any JIT related improvements, and as far as I know no work has been done on it on 13 either.

Another rebuttal which is less through due to the benchmark being very synthetic but still points out that Uber's numbers are nothing impressive. https://www.cybertec-postgresql.com/en/beating-uber-with-a-postgresql-…

THe process is similar, you would convert to csv and then use the \copy command to bulk insert the data. In pg 12, the copy command has grown a lot of very useful features like WHERE filtering, so if you get the chance go for 12: https://www.cybertec-postgresql.com/en/tech-preview-improving-copy-and…

Part 1 of the blog post here: https://www.cybertec-postgresql.com/en/are-triggers-really-that-slow-i…

> 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.

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-constraint…

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…

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-pos… "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.