4 ms·

Please, please add transparent compression like most of the other RDBMSs have for many-many years already..

by crypto5 10y ago

dijit 10y ago
Anything large enough to get into TOAST tables is compressed, what are you hoping to achieve with compression? It neuters your ability to do a scan on disk and doesn't help in memory anyway. Not saying PostgreSQL shouldn't do it, just curious what benefits there are.
- crypto5 10y ago
  TOAST means that individual cell/row needs to be extra-large to trigger compression. Compression can obviously save a lot of disk space, and also reduce IO traffic which benefits performance. > It neuters your ability to do a scan on disk Sorry, I didn't understand why is this..
  - Jweb_Guru 10y ago
    Compression is not really that useful except on large values or large numbers of values. Other than column stores, I don't think most storage engines actually compress the data (as opposed to indices) across rows (because it would slow down reads and writes dramatically) so there's really no point in compressing tiny strings. Page-level compression might be useful, I guess, depending on your use case.
    - crypto5 10y ago
      > I don't think most storage engines actually compress the data Most major RDBMs (Oracle, MS SQL Server and even MySql) support page level compression. > because it would slow down reads and writes dramatically Not really, modern compression algorithms (e.g. zippy) have much faster decompression/compression speed than IO speed even for SSD drives, and having smaller IO traffic due to data compression can make actual performance higher with compression. Also you can fit more compressed data into FS cache, which again reduces IO traffic. > Page-level compression might be useful, I guess, depending on your use case. Sure, here are some examples: http://sqlblog.com/blogs/linchi_shea/archive/2008/05/11/sql-server-2008-page-compression-compression-ratios-from-real-world-databases.aspx http://sqlblog.com/blogs/linchi_shea/archive/2008/05/11/sql-... and http://sqlblog.com/blogs/linchi_shea/archive/2008/05/16/sql-server-2008-page-compression-performance-impact-on-table-scans.aspx http://sqlblog.com/blogs/linchi_shea/archive/2008/05/16/sql-...
anarazel 10y ago
You're looking for compression in row or column style storage?
- crypto5 10y ago
  I am not very familiar with db internals, but I think RDBMs usually do page level compression.
  - anarazel 10y ago
    That's more an orthogonal angle. Row stores (what you usually want for oltp workloads) usually achieves a lot lower compression ratios than column stores (more useful for write once analytics use cases). If you think about it, that's not too surprising. Columns in a row will usually have largely independent values (cf normalization), thus a sequence is rows is harder to compress; in column stores all the values for a column follow each other, and quite often that allows for far higher compression ratios.
    - pgaddict 10y ago
      There are row stores (e.g. Oracle does that) that reorganize the page structure from row to column, usually achieving significantly better compression ratio without significant impact on OLTP performance.
    - crypto5 10y ago
      I think it is your company who published good results of using compression with PostgreSQL on ZFS. Also column stores contain not just actual column values, e.g. if you look at cassandra, each SSTable value has key, timestamp, column name, and actual cell value, or the same data layout as 4 columns in row store. Still they strongly recommend to use compression.

Please, please add transparent compression like most of the other RDBMSs have for many-many years already..

by crypto5 10y ago

Please, please add transparent compression like most of the other RDBMSs have for many-many years already..

dijit 10y ago

Anything large enough to get into TOAST tables is compressed, what are you hoping to achieve with compression? It neuters your ability to do a scan on disk and doesn't help in memory anyway. Not saying PostgreSQL shouldn't do it, just curious what benefits there are.

crypto5 10y ago
TOAST means that individual cell/row needs to be extra-large to trigger compression. Compression can obviously save a lot of disk space, and also reduce IO traffic which benefits performance. > It neuters your ability to do a scan on disk Sorry, I didn't understand why is this..
- Jweb_Guru 10y ago
  Compression is not really that useful except on large values or large numbers of values. Other than column stores, I don't think most storage engines actually compress the data (as opposed to indices) across rows (because it would slow down reads and writes dramatically) so there's really no point in compressing tiny strings. Page-level compression might be useful, I guess, depending on your use case.
  - crypto5 10y ago
    > I don't think most storage engines actually compress the data Most major RDBMs (Oracle, MS SQL Server and even MySql) support page level compression. > because it would slow down reads and writes dramatically Not really, modern compression algorithms (e.g. zippy) have much faster decompression/compression speed than IO speed even for SSD drives, and having smaller IO traffic due to data compression can make actual performance higher with compression. Also you can fit more compressed data into FS cache, which again reduces IO traffic. > Page-level compression might be useful, I guess, depending on your use case. Sure, here are some examples: http://sqlblog.com/blogs/linchi_shea/archive/2008/05/11/sql-server-2008-page-compression-compression-ratios-from-real-world-databases.aspx http://sqlblog.com/blogs/linchi_shea/archive/2008/05/11/sql-... and http://sqlblog.com/blogs/linchi_shea/archive/2008/05/16/sql-server-2008-page-compression-performance-impact-on-table-scans.aspx http://sqlblog.com/blogs/linchi_shea/archive/2008/05/16/sql-...

anarazel 10y ago

You're looking for compression in row or column style storage?

crypto5 10y ago
I am not very familiar with db internals, but I think RDBMs usually do page level compression.
- anarazel 10y ago
  That's more an orthogonal angle. Row stores (what you usually want for oltp workloads) usually achieves a lot lower compression ratios than column stores (more useful for write once analytics use cases). If you think about it, that's not too surprising. Columns in a row will usually have largely independent values (cf normalization), thus a sequence is rows is harder to compress; in column stores all the values for a column follow each other, and quite often that allows for far higher compression ratios.
  - pgaddict 10y ago
    There are row stores (e.g. Oracle does that) that reorganize the page structure from row to column, usually achieving significantly better compression ratio without significant impact on OLTP performance.
  - crypto5 10y ago
    I think it is your company who published good results of using compression with PostgreSQL on ZFS. Also column stores contain not just actual column values, e.g. if you look at cassandra, each SSTable value has key, timestamp, column name, and actual cell value, or the same data layout as 4 columns in row store. Still they strongly recommend to use compression.