TL;DR: Except for a specific bug in PostgreSQL 9.3 prior to 9.3.4 which it's vital that you patch, index corruption after crash is never expected and hasn't been since PostgreSQL 7.1 was released in 2001.
The advice/folklore you got was wrong.
I am told that a long time ago in a galaxy far, far away (PostgreSQL 8.4 under Ubuntu 12.04), PostgreSQL frequently suffered from an issue where if the machine holding the server failed (e.g. kernel crash), when the machine was restarted, Postgres would start but the indexes on the database might be corrupted.
That way predates 8.4 unless you're using hash indexes, which are documented not to be crash safe and are not recommended.
WAL logging of indexes was introduced in the deep dark distant past of mid-2001. PostgreSQL has been crash safe for an exceedingly long time. That advice was wrong.
MySQL switched to crash-safe by default (by defaulting to InnoDB tables) in MySQL 5.5, in 2009. Prior to that its default storage engine, MyISAM, isn't crash-safe and will happily corrupt indexes and tables. So your folklore isn't only wrong, it's backwards.
If you had corruption after a crash under 8.4, then either:
- you hit a bug (not impossible, but not likely either) in PostgreSQL, the kernel, the filesystem, etc;
- You were using LVM before write-barrier support was fixed, and had barriers enabled on your file system;
- your storage subsystem lied to the kernel about fsync(); or
- you disabled crash safety by setting
fsync=off
.
There have been a couple of index-related bugs over time, but all the others I know of have been minor and/or hard to trigger, and few have been related to crash recovery.
The issue with 9.3 is different. That's a very specific bug that we're all rather embarrassed about. It occurred when a replica PostgreSQL server was promoted to a master after certain other operations had happened in a particular order. It's a really bad bug because it affects the table contents, not just indexes, so it's vital that you patch to 9.3.5 or higher. To learn more about it than you ever wanted to search for "multixact bug".
So:
Is manual rebuilding of indices an expected task after a system crash?
Not unless you are using hash indexes, which the manual specifically wants you about.
If not, has it ever been, and when was that changed?
PostgreSQL 7.1, back in 2001.
If you read the release notes you'll find the occasional bug and bug fix, but other than the aforementioned multixact bugs in 9.3, nothing too serious.
Is it possible to avoid the need for manual reindexing?
Do nothing. Congratulations, you don't need manual reindexing.
Simply:
- Leave
fsync
set to on
, the default
- Leave
full_page_writes
set to on
, the default
- Don't
kill -9
the postmaster but no other processes then delete postmaster.pid
and start PostgreSQL again. The latest releases even detect it if you do this and try to prevent damage.
- Don't explicitly specify hash indexes when creating an index. PostgreSQL will never use a hash index unless you ask it to.
Bonus points if you:
- Keep PostgreSQL reasonably up to date, i.e. apply point release patches
- Use decent quality SSDs if you're going to use SSDs, otherwise any database will be prone to corruption. Make sure they have a supercapacitor or other power-loss protection.
Best Answer
You could just create new index with different name by
Then drop corrupted index with
Then you could rename new index to old name:
Latter will require lock, but for few milliseconds of runtime after acquire the lock. So you do not need downtime due to write lock.
The definition of the index can be obtained from the command
pg_dump -s -t tablename --no-acl
This is exactly the same procedure that does
reindex concurrently
under the hood. Butreindex concurrently
is a bit cheaper since do not need lock for index rename phase.Also widely known
pg_repack
has feature to reindex table with option--only-indexes
. This option is implemented as create + drop index concurrently.Well, any index creation without
concurrently
will read the entire table sequentially (concurrently
will read the table twice). Something else depends on access method. Btree will sort all live tuples. This is the most time-consumption part of create index, for large indexes the work will be done in temporary files (remember increasemaintenance_work_mem
). This part also depends on datatypes and values. Text with small selectivity (e.g. somestatus
field) will be noticeable slower to build than integer sequences.I have no way to estimate, except for one: to measure the creation time of an index on some data sample:
Reindex is just a special form of index creation. Hmm, and an important point:
reindex table
has no difference with severalreindex index
in terms of resourse usage.reindex table
is implemented by callingreindex_index
for each individual index on table. So, table with 5 indexes will be scanned 5 times.