Обсуждение: Large pkey index on insert-only table
Hi all,
I have a suspiciously large index, and I could use a hand finding a root cause for its size. This index supports the primary key for a closure table that models threaded comments with columns `(id, ancestor_id, depth)`. The primary key is composite: `(id, ancestor_id)`. The id columns are varchars which are a bit long for identifiers, around 70 bytes. This table is insert-only: the application never performs updates or deletes.
The table has grown to 200GB, and the unique index supporting the primary key is nearly double that at around 360GB, which stood out to me as rather large compared to the table itself. The index uses the default fillfactor of 90. I would not anticipate very much bloat since updates and deletes never occur on this table, and according to pg_stat_all_tables autovacuum has been running regularly. I've used the btree bloat estimator from https://github.com/ioguix/pgsql-bloat-estimation, and it estimates the bloat percentage at 47%.
Any thoughts on why this may be, or where to go next to continue tracking this down? Also, could the primary key column order `(id, ancestor_id)` vs `(ancestor_id, id)` significantly affect the index size depending on the column cardinalities? I appreciate your time and input, thanks!
--
Devin Ivy
On Mon, Jun 26, 2023 at 8:50 AM Devin Ivy <devinivy@gmail.com> wrote: > Any thoughts on why this may be, or where to go next to continue tracking this down? Also, could the primary key columnorder `(id, ancestor_id)` vs `(ancestor_id, id)` significantly affect the index size depending on the column cardinalities? That is certainly possible, yes. I describe one particular pathology that causes composite B-Tree indexes to only have about 50% space utilization here: https://youtu.be/p5RaATILoiE?t=2079 Theoretically this shouldn't be a problem anymore, because (as the talk describes) Postgres 12 added heuristics that avoid the problem. But those heuristics are kind of conservative; they only kick in when it's fairly clearly the right thing to do. I don't think that they'll work very reliably for varchar columns. Note that 90% space utilization isn't really the standard case for B-Trees in general. Even absent updates and deletes, an index with completely random insertions (e.g., a UUID index) is expected to have about 70% space utilization. You can only really expect ~90% space utilization with monotonically increasing insertions. On the other hand having less than 50% space utilization is pretty poor, so (assuming that that's what "bloat percentage 47%" means) then I'd say that you're right to suspect that something is a bit off here. This isn't necessarily a big deal, but I tend to agree that what you're seeing is something that theoretically can be avoided by the implementation (if there were deletes involved then that wouldn't apply, but there aren't). -- Peter Geoghegan