2

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?

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.

| improve this answer | |
  • Good advice, in any case. I'm hoping keeping the indexes on an SSD will speed up queries on my 400+ GB of data, but how much disk I/O actually happens during the index lookups is a good question. The indexes are way too large to be cached. – kontextify Feb 12 '16 at 11:03
  • Edit problem cause commentd comment to be posted before ready. Sorry. – Terry Feb 13 '16 at 1:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.