Continuously Adding SSDs to an existing PostgreSQL Server

An Ubuntu 18.04 server dedicated to running a PostgreSQL 11.2 server (with TimescaleDB extension) will be running out of disk space soon so new SSD disks will need to be added to the machine to support the growing database size.

The data is expected to continue increasing at the same/higher rate, so the storage hardware will need to be increased continuously until the machine runs out of 2.5" drive bays. Only then will distributing the database over multiple machines be considered, due to the increased complexity involved.

Thoughts

-
Union file systems like mergerfs can pool the drives together, solving the storage expansion problem easily. But this will increase the latency of database operations and are thus not recommended. Redundancy can be added by having an underlying RAID-1/5/6/10 or using SnapRAID.

-
RAID-0 and RAID-10 allows expansion of the RAID array into the newly added drive, with the bonus of increased performance from striping. However every drive being added is an additional point of failure. Furthermore, numerous people claims that mirroring SSDs is of limited use as both SSDs in RAID-0 will likely fail at the same time. So maybe this means that RAID-10 is no better than RAID-0. Furthermore, failure rates increase linearly with each additional SSD added.

-
RAID-5/6 reduce performance due to parity calculations and having write to 2 drives, reducing the effective IOPS by 75%. Seems to be a poor choice for databases.

-
PostgreSQL TABLESPACES can be used to assign every table to a specific drive. However, using tablespaces will make recovery very complicated. Furthermore, is it possible to create new tablespaces on the new drives and let Postgres automatically decide where to write new record to?

-
ZFS, BTRFS? Not familiar with them, willing to explore if they are suitable.

Question: What is the year-2020 recommended method of expanding the storage of a PostgreSQL machine, if expansion has to be done frequently (1-2X a year), performa

Have you turned on TimescaleDB's native compression, available since v1.5? I ask because you mention ZFS/BTRFS above, which suggests you aren't employing its compression already.

Typically see 90-98% storage savings in the wild...

https://docs.timescale.com/latest/using-timescaledb/compression

Additionally, you can use TimescaleDB's attach_tablespace command to add more disks, and then new chunks are load balanced across the available tablespaces.

https://docs.timescale.com/latest/api#attach_tablespace

StackExchange Database Administrators Q#259738, answer score: 3