> The section on multi-column indexes mirrors how I was taught and how I’ve generally handled such indexes in the past. But is it still true for more recent PG versions?
No, it isn't. PostgreSQL 18 added support for index skip scan:
It's actually possible to use a multicolumn index with a query that only has operators on its lower-order columns in earlier versions. But that requires a full index scan, which is usually very inefficient.
> I may not be remembering fully, maybe the indexes never shrunk but the tables did in size.
That only happens when it is possible to give back space to the OS filesystem using relation truncation in the first place -- which isn't all that common (it's generally only seen when there are bulk range deletions that leave lots of contiguous empty space at the end of a table/heap structure). But you said that this is an append-only workload.
This behavior can be disabled by setting the vacuum_truncate table storage parameter to "off". This is useful with workloads where relation truncation is disruptive (truncation needs to acquire a very heavyweight table lock).
> Is there no way to automatically clean up indexes then?
What I meant was that indexes do not support relation truncation. It follows that the amount of space used for an index (from the point of view of the OS) cannot ever go down, barring a REINDEX or a VACUUM FULL.
This does not mean that we cannot reuse space for previously freed/deleted pages (as long as we're reusing that space for the same index). Nor does it mean that "clean up" isn't possible in any general sense.
> In a previous use case, when using postgres as a WAL-like append only store, I noticed that indexes would get massive. Then, after a while, they'd magically shrink.
It's possible to recycle pages within indexes that have some churn (e.g., with workloads that use bulk range deletions). But it's not possible for indexes to shrink on their own, in a way that can be observed by monitoring the output of psql's "\di+" command. For that you'd need to REINDEX or run VACUUM FULL.
I've done extensive work on improving the Postgres B-Tree code, over quite a number of releases. I'm not aware of any problems with high-insert workloads in particular. I have personally fixed a number of subtle issues that could lead to lower space utilization with such workloads [1][2] in the past, though.
if there's a remaining problem in this area, then I'd very much like to know about it.
CONCURRENTLY needs to wait for old transactions to go away, including those that haven't (and won't ever) touch the table that you're building an index on. So it's not waiting for a lock as such - it's waiting for older transactions to go away without conflicting in a way that can cause these "traffic jams". This can be a problem for the obvious reason, though generally only for the index build itself.
Tricky problems with relation-level locks tend to come from a combination of one lock request that is "generally non-disruptive but long-lived", and another lock request that is "generally disruptive/blocking but short-lived". I have heard of quite a few problem scenarios where these locks conflict with each other (usually by chance), leading to "generally disruptive/blocking and long-lived" -- which can be very dangerous. But that's fundamental to how lock managers work in general.
The Postgres implementation tries to make it as unlikely as reasonably possible. For example, autovacuum usually notices when something like this happens, and cancels itself.
> In Postgres, by contrast, any schema change, even adding an index with the CONCURRENTLY option, needs to at least momentarily hold a totally exclusive lock that blocks all writes and reads on the table.
It's true that every DDL statement requires a relation-level lock. Though the same is true for even a simple SELECT statement. The important detail is the lock strength for each variety of DDL, and how that affects and interacts with other queries (including other DDL):
CREATE INDEX (even without CONCURRENTLY) will not block reads, even for a moment (it just blocks write DML). CREATE INDEX (without CONCURRENTLY) won't even block other CREATE INDEX statements that run against the same table.
My guess is that your application appeared to exhibit this behavior, but the true problem was actually how several conflicting locks accumulated, which had the effect of blocking SELECTs for an unreasonably long time. A combination of CREATE INDEX and some other conflicting DDL that really does block reads (e.g., certain kinds of ALTER TABLE) can create the false impression that CREATE INDEX blocks reads in general. But that's not really the case at all. At worst, CREATE INDEX is only one part of the "traffic jam" that caused SELECTs to block in this scenario.
I suggest testing the same workload with your existing hash indexes replaced with equivalent B-Trees.
[1] https://github.com/postgres/postgres/blob/master/src/backend...