I'm struggling with a problem of folk memory re Postgres.
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. For instance
SELECT foo FROM bar WHERE baz = 123
would fail because the index on
bar.baz would be corrupted. Reindexing the table fixed the issue but as there was no way to detect this issue, and this was in an appliance environment (no DBA on hand), in essence to guard against this it would be necessary to reindex all tables on a reboot, which would be prohibitively expensive. Thus (the reasoning goes) without a DBA on hand it would not be sensible to use Postgres particularly in an environment where tables were large and computing indices expensive.
Because of this folklore, MySQL is preferred in such circumstances.
I dimly recall seeing this issue happening under 8.4, so I think there's some truth in it.
I am assured this also has happened on Postgres 9.3 under Ubuntu 14.0.4. I don't know whether it happens 'frequently'. The system it happened on is running with (admittedly old) enterprise grade SAS drives which claim write caching is turned off, so I don't think this is a write caching problem.
Were this still the case, I think I'd be able to find more evidence of it on the web than I can find.
Thus I suspect it may be fixed, or may be a result of something bad in
postgresql.conf(though inspection suggests only the autovacuum settings are changed from the defaults).
In the system I know did this recently (using 9.3) no LVM is in use. The storage system is ext4 on a physical drive mounted with default attributes. This drive is an (admittedly old) enterprise SAS drive plus SAS1068 PCI-X Fusion-MPT SAS controller which
sdparmclaims it doesn't have a write cache switched on, but this has (allegedly) been seen on multiple drives with different vendors etc., though always on ext4 on Ubuntu.
fsyncis in the default state (
No master/slave configuration - just a single master - (so it's not the
Tables are created with default indexing (no fancy hash indexes).
Postgres regularly updated to whatever the relevant Ubuntu LTS release has. The latest problem was on 14.04.
Is manual rebuilding of indices an expected task after a system crash?
If not, has it ever been, and when was that changed?
Is it possible to avoid the need for manual reindexing?