3 ms·

The PostgreSQL mailing list is having a conversation right now about using SSDs. This seems like a very important comment for anyone considering them: http

The PostgreSQL mailing list is having a conversation right now about using SSDs. This seems like a very important comment for anyone considering them: http://archives.postgresql.org/pgsql-general/2012-12/msg00202.php Basically, you need to make sure that you buy SSDs with a capacitor that allows the drive to flush what it needs in event of abrupt power loss. EDIT: Looks like the list archives didn't preserve the thread very well, so here is the original question for anyone interested: http://archives.postgresql.org/pgsql-general/2012-11/msg00427.php

bbulkow 14y ago
Is this because PostgreSQL's file format will become hopelessly confused and unable to restart if flush doesn't work? A lot of applications can lose the last 100ms of writes, especially if its rare because of a k-safe cluster design, as long as you don't have a corrupted file format. A good transaction log based system will recover - as the author's should.
- hosay123 14y ago
  It's not only a case of file formats.. a few years ago one of the Linux kernel developers (Theodore Tso IIRC) made a post regarding drive behaviour under power loss, and the results were pretty insane. For example when a rotating drive fails, you might lose +/- 4kb around the previous sector under write, whereas with particular SSDs, he witnessed 1mb chunks zeroed out every Nmb across the entire drive. That kind of thing, you simply can't work around in software
- jacques_chester 14y ago
  > A good transaction log based system will recover - as the author's should. Unless the drive lies. Tells filthy dirty ugly lies. And lots of them do. OS: "Did you persist that data I flushed?" Disk controller (hiding single copy in RAM cache behind its back): "Oh absolutely, bro. It's tight. Solid. You got nothing to worr-"BZZZT
dclusin 14y ago
Disk drives with battery backed write caches are a requirement for any system where data corruption is considered a system failure. We use these at my job for things other than just our data bases. It doesn't need to be just SSD's either; traditional platter-based hard drives have this feature as well.
- pjungwir 14y ago
  Thanks; I wondered why this would be unique to SSDs.
  - baruch 14y ago
    Normally you use an HDD without any write cache and if you do use a write cache along the way (on the HDD or on the server) you make sure you are battery backed. SSD is a bit different since even if you do not use write caching on the SSD there is a non-trivial amount of meta data that is kept in RAM and needs to be written safely to the media. You need quite a bit of juice to do all this work which is entails keeping most of the hardware working. Even the HDD has enough capacitor power to park the head back and lock it safely. But I believe that you don't really need all hardware operating at full capacity, only enough spin to rotate and to pull the head back and the spin continues even if you don't power it for the parking time. Both HDD and SSD do not guarantee much about an IO that was started writing but an acknowledgement wasn't sent about it. SCSI standard from which all disks derive requirements doesn't require anything in such a case and leaves it as undefined.

bbulkow 14y ago

Is this because PostgreSQL's file format will become hopelessly confused and unable to restart if flush doesn't work? A lot of applications can lose the last 100ms of writes, especially if its rare because of a k-safe cluster design, as long as you don't have a corrupted file format. A good transaction log based system will recover - as the author's should.

hosay123 14y ago
It's not only a case of file formats.. a few years ago one of the Linux kernel developers (Theodore Tso IIRC) made a post regarding drive behaviour under power loss, and the results were pretty insane. For example when a rotating drive fails, you might lose +/- 4kb around the previous sector under write, whereas with particular SSDs, he witnessed 1mb chunks zeroed out every Nmb across the entire drive. That kind of thing, you simply can't work around in software
jacques_chester 14y ago
> A good transaction log based system will recover - as the author's should. Unless the drive lies. Tells filthy dirty ugly lies. And lots of them do. OS: "Did you persist that data I flushed?" Disk controller (hiding single copy in RAM cache behind its back): "Oh absolutely, bro. It's tight. Solid. You got nothing to worr-"BZZZT

dclusin 14y ago

Disk drives with battery backed write caches are a requirement for any system where data corruption is considered a system failure. We use these at my job for things other than just our data bases. It doesn't need to be just SSD's either; traditional platter-based hard drives have this feature as well.

pjungwir 14y ago
Thanks; I wondered why this would be unique to SSDs.
- baruch 14y ago
  Normally you use an HDD without any write cache and if you do use a write cache along the way (on the HDD or on the server) you make sure you are battery backed. SSD is a bit different since even if you do not use write caching on the SSD there is a non-trivial amount of meta data that is kept in RAM and needs to be written safely to the media. You need quite a bit of juice to do all this work which is entails keeping most of the hardware working. Even the HDD has enough capacitor power to park the head back and lock it safely. But I believe that you don't really need all hardware operating at full capacity, only enough spin to rotate and to pull the head back and the spin continues even if you don't power it for the parking time. Both HDD and SSD do not guarantee much about an IO that was started writing but an acknowledgement wasn't sent about it. SCSI standard from which all disks derive requirements doesn't require anything in such a case and leaves it as undefined.