2

I'm attempting to speed up the performance of postgresql on ec2.

An ec2 node is structured as follows - you have slow, durable network attached storage (EBS), and you also have a fast, volatile storage (ephemeral storage). I.e., in a system crash, ephemeral storage will be lost.

In order to speed up db performance I'm considering setting my postgres temp_tablespaces to a directory living in ephemeral storage. However, ephemeral storage has no durability guarantees - in a system crash it will all be completely and permanently destroyed.

Does this run the risk of any data loss? In principle, it seems to me it should not, since the temp_tablespace is used for temporary objects. But I'm not intimately familiar with the postgres data model - are there dangers here that I'm missing?

Chris Stucchio
  • 871
  • 1
  • 8
  • 4

1 Answers1

1

Yes, that should be safe, if you crash before the operation that requires the temporary table is fully committed, you should recover to the point before the operation. Though, I don't know if Postgresql clears that area on a restart, I would check for yourself.

Now a proper geek would try to implement a file system over Amazon's memcache equivalent and use that...

Frodo Baggins
  • 8,290
  • 6
  • 45
  • 55
  • 1
    PostgreSQL doesn't use transaction log for content of temporary date, but it is not problem for temp tables. But issue is a tablespace dictionary. When is destroyed on file system, then should be dropped manually in database and manually recreated - only in few cases will be recreated in recovery process. – Pavel Stehule Dec 09 '11 at 19:47