In Azure there are VMs that have local storage either as spinning disk (A-series) or SSD (D-series). Considering this space is wiped on reboot...

  • Is there a benefit to choosing the SSD option for PostgreSQL?
  • Can it be configured to use for temp/swap data even though it's wiped on reboot?
  • How would one need to condition this disk space before PostgreSQL's start?

I think it's worth noting (based on comment) that the install and application databases won't be installed on the local storage (which are wiped)... I'm only curious if there are bits that could take advantage of it (temporary databases/tables, swap space, etc) and what would need to be configured to initialize/re-initialize any directories that would/could be used as such.

The same questions that would apply if one were to have say a RAM-Drive (that is not permanent).

  • I don't have the reputation required to add a plain "azure" tag, not to be confused with "azure-sql" which is different. – Tracker1 Dec 17 '14 at 20:16
  • Depends a lot on workload, and on the magnetic drives vs the SSDs. Not all drives are equal, not all workloads are equal. If you're running PostgreSQL on local storage that's not durable and crash safe you'll want to turn off PostgreSQL's data durability/crash safety features and rely on replication + really good backups. Use streaming replication + WAL shipping PITR (PgBarman or similar) + periodic logical dumps. Yes, "and". – Craig Ringer Dec 17 '14 at 21:39
  • PostgreSQL itself won't be installed/running on the local storage, it will be using Azure storage... I was mainly curious if there was anything in PostgreSQL (temp dbs, etc) that could be configured to take advantage of the temp/swap space on SSD. – Tracker1 Dec 17 '14 at 23:52
  • @CraigRinger ... and in this case, faster disks (which we don't know here if those are the SSDs or not) give an advantage, especially when much WAL is written, right? – dezso Dec 18 '14 at 9:00
  • Well, if you're doing lots of big sorts etc that spill out of work_mem and to disk then it might help - but the on-disk sort algorithm used by PostgreSQL is optimised for disks with slow seeking anyway. So. Maybe if you're doing lots with temporary tables, and/or doing big sorts and joins over huge data sets that must be materialized. You'd create a tablespace and set temp_tablespaces to point to the storage. – Craig Ringer Dec 18 '14 at 9:25

Your Answer

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

Browse other questions tagged or ask your own question.