Any decent SSD has capacitor (enterprise) or battery backed (phones) DRAM. Therefore, a sync write is just “copy the data to an I/O buffer over PCIe”.
For databases, where you do lots of small scattered writes, and lots of small overwrites to the tail of the log, modern SSDs coalesce writes in that buffer, greatly reducing write wear, and allowing the effective write bandwidth to exceed the media write bandwidth.
These schemes are much less expensive than optane.
You are correct that writes are not guaranteed to be atomic.
PostgreSQL has a feature to handle torn pages: full_page_writes = on, which is enabled by default. This means you do double write.
For RocksDB I believe torn pages are not a problem as SST files are immutable. But correct me if I am wrong.
For databases, where you do lots of small scattered writes, and lots of small overwrites to the tail of the log, modern SSDs coalesce writes in that buffer, greatly reducing write wear, and allowing the effective write bandwidth to exceed the media write bandwidth.
These schemes are much less expensive than optane.