How do I recover my database after losing a tablespace with indexes?

Question

I'm thinking of moving all the indexes in my PostgreSQL 9.3 database to a tablespace on an SSD for improved performance. This SSD is not in a RAID array and I need to consider a scenario where it fails and the tablespace is lost.

The docs (and various blogs) warn that losing any tablespace probably leaves you with a database broken beyond recovery. Is this really the case, if I'm only losing my indexes?

I'm assuming that after failure I'll have a slow but functioning database which I can then safely reindex, perhaps using my default tablespace (or a new one on another SSD). Am I wrong? What would actually happen to my database, and how easy would it be to restore it to its pre-fail state?

Answer 1

How much improvement are you expecting from the SSD? It is surprising how little physical I/O actually happens on busy indexes and I don't think I've ever solved an actual performance problem with physical I/O devices.

I don't know enough about PostgresSQL to comment on buffering or recoverability and the like but I will say that choosing performance over recoverability is probably not a good practice.

I never want to find out that I have found a previously unknown recovery bug when my data is sitting in a tape on a shelf. I learned the DBA craft in the days before RAID - every night was a sleepless night. Always, always, always use RAID's.

If a user is complaining about performance, tell them you need a dozen SSD's to put in a 10 disk RAID5 array with hot failover. He'll leave with his head spinning but he might come back with a purchase order.

Good luck.