4 ms·

I've found that Postgres consumes (by default) more disk than, for example, MySQL. And the difference is quite significant. That means more money that I have to

I've found that Postgres consumes (by default) more disk than, for example, MySQL. And the difference is quite significant. That means more money that I have to pay every month. But, sure Postgres seems like I system that integrates a lot of subsystems, that adds a lot of complexity too. I'm just marking the bad points because you mention the good points in the post. You're also trying to sell you service, which is good too.

EvanAnderson 8mo ago
Some people do Postgres on compressed ZFS volumes to great success.
- ddtaylor 8mo ago
  I am curious if you know anyone using Btrfs for this too. I like ZFS, but it Btrfs can do this it would be easier to use with some distros, etc. as it's supported in kernel.
  - riku_iki 8mo ago
    I do it. The big problem for me from running DB on Btrfs is that when I delete large dirs or files (100GB+), it locks disk system, and Db basically stop responding on any queries. I am very surprised that FS which is considered prod grade having this issue..
    - jlundberg 8mo ago
      Try XFS if you havn’t yet. Very solid and no such issues.
      - ddtaylor 8mo ago
        I haven't used XFS in almost two decades, does it have compression support in the same way? Also, does it do JBOD stuff? I know it's a bit of a different thing, but I really enjoy the pool many disks together part of Btrfs, although it has its limitations.
        EvanAnderson 8mo ago
        XFS doesn't have inline compression, nor does it have volume management functionality. It's a nice filesystem (and it's very fast) but it's just a filesystem.
      - riku_iki 8mo ago
        No compression.
- olavgg 8mo ago
  On average I get around 4x compression on PostgreSQL data with zstd-1
- icefo 8mo ago
  That's something I did for small medium size data analysis (300Gb of csv). The reported size was around 800gb with indexes iirc and it fitted on a 512gb SSD. It compresses the indexes pretty well but don't forget to turn off postgres' own copy on write mechanism
PunchyHamster 8mo ago
On flipside, restore from plain postgresql dump is much, much faster than plain mysql backup. There are alternative strategies for mysql but that's extra work
benjiro 8mo ago
The problem is that Postgres uses something like 24B overhead per row. That is not a issue with small Tables, but when your having a few billion around, each byte starts to add up fast. Then you a need link tables that explode that number even more, etc ... It really eats a ton of data. At some point you end up with binary columns and custom encoded values, to save space by reducing row count. Kind of doing away with the benefits of a DB.
- direwolf20 8mo ago
  Yeah postgres and mariadb have some different design choices. I'd say use either one until it doesn't work for you. One of the differences is the large row header in postgres.

EvanAnderson 8mo ago

Some people do Postgres on compressed ZFS volumes to great success.

ddtaylor 8mo ago
I am curious if you know anyone using Btrfs for this too. I like ZFS, but it Btrfs can do this it would be easier to use with some distros, etc. as it's supported in kernel.
- riku_iki 8mo ago
  I do it. The big problem for me from running DB on Btrfs is that when I delete large dirs or files (100GB+), it locks disk system, and Db basically stop responding on any queries. I am very surprised that FS which is considered prod grade having this issue..
  - jlundberg 8mo ago
    Try XFS if you havn’t yet. Very solid and no such issues.
    - ddtaylor 8mo ago
      I haven't used XFS in almost two decades, does it have compression support in the same way? Also, does it do JBOD stuff? I know it's a bit of a different thing, but I really enjoy the pool many disks together part of Btrfs, although it has its limitations.
      - EvanAnderson 8mo ago
        XFS doesn't have inline compression, nor does it have volume management functionality. It's a nice filesystem (and it's very fast) but it's just a filesystem.
    - riku_iki 8mo ago
      No compression.
olavgg 8mo ago
On average I get around 4x compression on PostgreSQL data with zstd-1
icefo 8mo ago
That's something I did for small medium size data analysis (300Gb of csv). The reported size was around 800gb with indexes iirc and it fitted on a 512gb SSD. It compresses the indexes pretty well but don't forget to turn off postgres' own copy on write mechanism

PunchyHamster 8mo ago

On flipside, restore from plain postgresql dump is much, much faster than plain mysql backup. There are alternative strategies for mysql but that's extra work

benjiro 8mo ago

The problem is that Postgres uses something like 24B overhead per row. That is not a issue with small Tables, but when your having a few billion around, each byte starts to add up fast. Then you a need link tables that explode that number even more, etc ... It really eats a ton of data. At some point you end up with binary columns and custom encoded values, to save space by reducing row count. Kind of doing away with the benefits of a DB.

direwolf20 8mo ago
Yeah postgres and mariadb have some different design choices. I'd say use either one until it doesn't work for you. One of the differences is the large row header in postgres.