Hi
I have been wondering what the minimum useful heap table compression
system would be for Postgres, in order to reduce disk footprint of
large mostly static datasets. Do you think an approach similar to the
static row-level compression of DB2 could make sense? I imagine
something like this:
1. You have a table which already has data in it.
2. You run a COMPRESS operation, which builds a static dictionary,
and rewrites the whole table with compressed frozen tuples. Frozen
tuples have CTIDs just like regular tuples, and can be pointed to by
indexes. They are decompressed on the fly when needed.
Clearly things get tricky once you need to update rows. Assume for
simplicity that future UPDATEs and INSERTs produce normal,
non-compressed tuples that would only be compressed by a subsequent
COMPRESS operation. The question is how to deal with the existing
compressed rows when UPDATEd or DELETEd. Some approaches:
1. Just don't allow updates of compressed rows (!).
2. Exclusively lock the whole page when trying to update any
compressed row, while you explode it into regular uncompressed tuples
on new pages which you can work on (!).
3. Pull the minimum header fields out of the compressed tuples so
that the MVCC machinery can work, to support updates of compressed
tuples. Perhaps just the t_xmax, t_ctid values (t_xmin == frozen is
implied), so that a writer can update them. This means an overhead of
at least 10 bytes per tuple over the compressed size (plus the item
offsets in the page header).
4. Something far cleverer.
Well, these are straw-man suggestions really and I probably don't
understand enough about PG internals (MVCC and implications for
VACUUM) to be making them. But I'm curious to know if anyone has
researched something like this.
For example, I have a system that occupies a couple of TB on disk, but
only a few to a few hundred MB per day change, mostly adding data to
an active partition. I periodically run CLUSTER on any partition that
has pg_stat.correlation < 0.9 (this effectively just re-CLUSTERs the
active one). At the same times I would COMPRESS, and the DB could
potentially fit on much smaller SSDs.
Most commercial database systems I encounter these days are using
compression of some sort (more sophisticated than the above,
with dynamic dictionaries, and sometimes column based storage etc).
Thanks
Thomas