4 ms·

The main reason I use MySQL over Postgres is storage engines. MySQL has storage engines with transparent compression, which allow me to keep an order of magnit

The main reason I use MySQL over Postgres is storage engines. MySQL has storage engines with transparent compression, which allow me to keep an order of magnitude more data than using Postgres. These days its MyRocks, in the past it was TokuDB. Historically Postgres has thought that the job of the file system, which is basically a bad choice for dbs. MyRocks wipes the floor with it. TimescaleDB is an interesting new Postgres storage choice. I'm evaluating it. But the free version doesn't do compression on-demand nor transparently. Anyway, it can only get better.

wlll 7y ago
Do these satisfy the compression requirement? https://stackoverflow.com/questions/1369864/does-postgresql-support-transparent-compressing-of-tables-fragments https://stackoverflow.com/questions/1369864/does-postgresql-...
- ddorian43 7y ago
  No. LSM/Rocksdb databases do "block compressions". Even better is dynamic dictionary compression: https://github.com/facebook/rocksdb/wiki/Dictionary-Compression https://github.com/facebook/rocksdb/wiki/Dictionary-Compress...
- willvarfar 7y ago
  No, that's a compressed row format, not a compressed block format. MySQL's innodb, for example, supports per-row-compression. Its completely ineffective. Block compression in tokdub and myrocks etc is a completely different class. MySQL's innodb also supporta kind of 'page compression' using the file-system's sparse pages. Its also naff. The closest postgres can get is using zfs with compression. Its a lot better than nothing.
  - wlll 7y ago
    Yeah, one of the suggestions on that page was a compressed filesystem.
rrickardt 7y ago
Postgres with timescaledb extension (community) along with zfs compression works flawless for me. Different approach than Myrocks, but worth a try!
- willvarfar 7y ago
  Have you tried using the timescaledb compression instead of zfs? And how does this compare to uncompressed and to native Postgres tables? I think that if timescaledb can support time-based or mru-based compression and even row-store partitions then it would take things to the next level.
  - mfreed 7y ago
    You've probably seen this, but reposting: https://blog.timescale.com/blog/building-columnar-compression-in-a-row-oriented-database/ https://blog.timescale.com/blog/building-columnar-compressio... Our own experience was that most users were getting 3-6x compression running TimescaleDB with zfs. With our native compression -- which does columnar projections, where a type-specific compression algorithm is applied per column -- we see median compression rates across our users of 15x, while 25% of our users see more than 50x compression rates.
jbergens 7y ago
SQL Server can also do storage compression. I assume Oracle can too. It used to be that the commercial offerings had the most advanced features first and the OSS systems copied them later.
mfreed 7y ago
Hi @willvarfar: Just to avoid any confusion, TimescaleDB's compression is available in our community edition, which is fully free to use. Once you specify some compression configs, it's automated and transparent. https://www.timescale.com/products/features https://www.timescale.com/products/features https://docs.timescale.com/latest/using-timescaledb/compression https://docs.timescale.com/latest/using-timescaledb/compress... (The "community" edition is available under our Timescale License. It's all source available and free-to-use, the license just places some restrictions if you are a cloud vendor offering DBaaS.)
- willvarfar 7y ago
  The cool thing about timescaledb is that timescale not Postgres controls the partitions, so who says they all have to be done the same way? In my use cases I have got recent partitions that are upsert heavy, so row-based works best. But as the partitions age, column-based would be better. What every DB seems to force me to do is use the same underlying format for all partitions. What I’d like is a storage engine that automates everything; instead of me picking partition size, it picks on the fly and makes adjustments. Instead of me picking olap vs oltp, it picks and migrates partitions over time etc.
  - mfreed 7y ago
    TimescaleDB mostly does exactly that: Recent data/partitions are maintained in row-based form, and after they reach some age, they are converted into more columnar compressed form.

wlll 7y ago

Do these satisfy the compression requirement? https://stackoverflow.com/questions/1369864/does-postgresql-support-transparent-compressing-of-tables-fragments https://stackoverflow.com/questions/1369864/does-postgresql-...

ddorian43 7y ago
No. LSM/Rocksdb databases do "block compressions". Even better is dynamic dictionary compression: https://github.com/facebook/rocksdb/wiki/Dictionary-Compression https://github.com/facebook/rocksdb/wiki/Dictionary-Compress...
willvarfar 7y ago
No, that's a compressed row format, not a compressed block format. MySQL's innodb, for example, supports per-row-compression. Its completely ineffective. Block compression in tokdub and myrocks etc is a completely different class. MySQL's innodb also supporta kind of 'page compression' using the file-system's sparse pages. Its also naff. The closest postgres can get is using zfs with compression. Its a lot better than nothing.
- wlll 7y ago
  Yeah, one of the suggestions on that page was a compressed filesystem.

rrickardt 7y ago

Postgres with timescaledb extension (community) along with zfs compression works flawless for me. Different approach than Myrocks, but worth a try!

willvarfar 7y ago
Have you tried using the timescaledb compression instead of zfs? And how does this compare to uncompressed and to native Postgres tables? I think that if timescaledb can support time-based or mru-based compression and even row-store partitions then it would take things to the next level.
- mfreed 7y ago
  You've probably seen this, but reposting: https://blog.timescale.com/blog/building-columnar-compression-in-a-row-oriented-database/ https://blog.timescale.com/blog/building-columnar-compressio... Our own experience was that most users were getting 3-6x compression running TimescaleDB with zfs. With our native compression -- which does columnar projections, where a type-specific compression algorithm is applied per column -- we see median compression rates across our users of 15x, while 25% of our users see more than 50x compression rates.

jbergens 7y ago

SQL Server can also do storage compression. I assume Oracle can too. It used to be that the commercial offerings had the most advanced features first and the OSS systems copied them later.

mfreed 7y ago

Hi @willvarfar: Just to avoid any confusion, TimescaleDB's compression is available in our community edition, which is fully free to use. Once you specify some compression configs, it's automated and transparent. https://www.timescale.com/products/features https://www.timescale.com/products/features https://docs.timescale.com/latest/using-timescaledb/compression https://docs.timescale.com/latest/using-timescaledb/compress... (The "community" edition is available under our Timescale License. It's all source available and free-to-use, the license just places some restrictions if you are a cloud vendor offering DBaaS.)

willvarfar 7y ago
The cool thing about timescaledb is that timescale not Postgres controls the partitions, so who says they all have to be done the same way? In my use cases I have got recent partitions that are upsert heavy, so row-based works best. But as the partitions age, column-based would be better. What every DB seems to force me to do is use the same underlying format for all partitions. What I’d like is a storage engine that automates everything; instead of me picking partition size, it picks on the fly and makes adjustments. Instead of me picking olap vs oltp, it picks and migrates partitions over time etc.
- mfreed 7y ago
  TimescaleDB mostly does exactly that: Recent data/partitions are maintained in row-based form, and after they reach some age, they are converted into more columnar compressed form.