5 ms·

I’m not a DBA, but this seems like it would be huge if I were: > Additionally, PostgreSQL 12 adds the ability to rebuild indexes concurrently, which lets you p

I’m not a DBA, but this seems like it would be huge if I were: > Additionally, PostgreSQL 12 adds the ability to rebuild indexes concurrently, which lets you perform a REINDEX operation without blocking any writes to the index. This feature should help with lengthy index rebuilds that could cause downtime when managing a PostgreSQL database in a production environment.

pilif 7y ago
there's an extension called `pg_repack` (https://github.com/reorg/pg_repack https://github.com/reorg/pg_repack) that already did this as a third-party solution. It even goes a bit farther in that it doesn't just rebuild indexes but can also rewrite a table completely in any order you want - all while keeping the original table fully usable [1]. Compared to what's now coming as a built-in, there's one restriction though in that in order for repack to work, the table needs a unique key of some sort, but usually you have a synthesised primary key anyways. It also once did a bad thing to a non-logged table with relation to having it be synced to the replica with no way of getting rid of the data again, though that might have been fixed since. I have used pg_repack many times and it's both very useful and extremely reliable. [1]: it will need to acquire two very short-lived exclusive locks (sub 0.1s) and you can tell it after how much time it should get impatient and just kill whatever's preventing it from acquiring that lock.
vbezhenar 7y ago
Can't you just create second index online and remove first when second index is built?
- anarazel 7y ago
  You can. But sometimes you can't drop indexes without further consequences. It might be e.g. a primary key, or a unique index used to enforce uniqueness on the referred-to side of a foreign key. You can work around those, but that requires holding an exclusive lock for at least a short amount of time (problematic in itself, but also can increase deadlock risk unless you're really careful).
- masklinn 7y ago
  Yes, though it can be less than easy for constraint indexes or FK indexes. REINDEX CONCURRENTLY is basically a shortcut for hand-rolling that reliably with less exclusive locking: https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=5dc92b844e680c54a7ecd68de0ba53c949c3d605 https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit...
nickpeterson 7y ago
Yep, although other commercial databases have already had it, definitely useful.
- why-el 7y ago
  Which ones? I know you can in SQL Server but not as cleanly as this. It's done transparently by PG.
  - tomnipotent 7y ago
    > but not as cleanly as this. SQL Server looks pretty clean to me. ALTER INDEX IX_name ON schema.table REBUILD WITH (ONLINE=ON);
  - ken 7y ago
    I've not used it myself, but it was my understanding that Oracle did this. Phil Greenspun, in the mid-1990's [1]: > "In Oracle, readers never wait for writers or vice versa. I can serve my 18,000 daily users. I can EXPORT a consistent backup dump. I can update a 20,000 row table. I can rebuild an index. All simultaneously." [1]: http://philip.greenspun.com/wtr/oracle-tips.html http://philip.greenspun.com/wtr/oracle-tips.html
    - nickpeterson 7y ago
      In that context, I think he was referencing multi version concurrency control, but your point remains.

pilif 7y ago

there's an extension called `pg_repack` (https://github.com/reorg/pg_repack https://github.com/reorg/pg_repack) that already did this as a third-party solution. It even goes a bit farther in that it doesn't just rebuild indexes but can also rewrite a table completely in any order you want - all while keeping the original table fully usable [1]. Compared to what's now coming as a built-in, there's one restriction though in that in order for repack to work, the table needs a unique key of some sort, but usually you have a synthesised primary key anyways. It also once did a bad thing to a non-logged table with relation to having it be synced to the replica with no way of getting rid of the data again, though that might have been fixed since. I have used pg_repack many times and it's both very useful and extremely reliable. [1]: it will need to acquire two very short-lived exclusive locks (sub 0.1s) and you can tell it after how much time it should get impatient and just kill whatever's preventing it from acquiring that lock.

vbezhenar 7y ago

Can't you just create second index online and remove first when second index is built?

anarazel 7y ago
You can. But sometimes you can't drop indexes without further consequences. It might be e.g. a primary key, or a unique index used to enforce uniqueness on the referred-to side of a foreign key. You can work around those, but that requires holding an exclusive lock for at least a short amount of time (problematic in itself, but also can increase deadlock risk unless you're really careful).
masklinn 7y ago
Yes, though it can be less than easy for constraint indexes or FK indexes. REINDEX CONCURRENTLY is basically a shortcut for hand-rolling that reliably with less exclusive locking: https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=5dc92b844e680c54a7ecd68de0ba53c949c3d605 https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit...

nickpeterson 7y ago

Yep, although other commercial databases have already had it, definitely useful.

why-el 7y ago
Which ones? I know you can in SQL Server but not as cleanly as this. It's done transparently by PG.
- tomnipotent 7y ago
  > but not as cleanly as this. SQL Server looks pretty clean to me. ALTER INDEX IX_name ON schema.table REBUILD WITH (ONLINE=ON);
- ken 7y ago
  I've not used it myself, but it was my understanding that Oracle did this. Phil Greenspun, in the mid-1990's [1]: > "In Oracle, readers never wait for writers or vice versa. I can serve my 18,000 daily users. I can EXPORT a consistent backup dump. I can update a 20,000 row table. I can rebuild an index. All simultaneously." [1]: http://philip.greenspun.com/wtr/oracle-tips.html http://philip.greenspun.com/wtr/oracle-tips.html
  - nickpeterson 7y ago
    In that context, I think he was referencing multi version concurrency control, but your point remains.