5 ms·
One of the gotchas of columnar storage (coming from Redshift) is that you lose all of the compression benefits if you have just one column that’s fat or hard to
by teej 6y ago
One of the gotchas of columnar storage (coming from Redshift) is that you lose all of the compression benefits if you have just one column that’s fat or hard to compress.
In Redshift columns are stored in blocks. You want to fit roughly the same number of column values per block across all your columns. But if you have one column where a small number of values can fit in a block, the rest of the columns end up leaving most of the block space unused. The result is wasted disk space and poor query performance.
This Postgres extension has similar-sounding storage ideas with stripes, but it’s not clear to me if it suffers from the same issue.
My first test to vet this would be a table with 50 columns of ints and one column of md5 hashes stored as varchar.
- jeff-davis 6y agoIn citus columnar, stripes/chunks are variable in size. If you have one large column that doesn't bloat the other columns.
- teej 6y agoCool! So if I select two columns with a predicate in the first, does it scan all stripes for the second column?
- jeff-davis 6y agoStripes aren't really "scanned". They are more of a logical concept that tracks where the physical data for each column is, and only fetches what it needs. If I understand what you are asking, let me restate: "can you apply a predicate first on column A before reading column B, so that you can avoid reading column B if the predicate on A doesn't match?". The answer is: "sometimes". Predicates match some rows and not others, so matching rows may be mixed in with non-matching rows, so it's not always possible to avoid the IO to read column B. However, if the matching and non-matching rows happen to be separated (e.g. if your table is naturally in time order and you have a time-based predicate), then it's able to do this optimization. Please see the section on "Chunk Group Filtering".
- teej 6y agoThanks for the in depth explanation! I look forward to exploring it more.
- georgewfraser 6y agoDo you have a source for this, or a code sample that can demonstrate it? This would be an extremely naive implementation of columnar storage. There are some truly hard cases around long variable-length strings, but any halfway decent columnar storage engine should be able to handle columns with different widths.
- teej 6y agoThey call this issue out in the docs specifically when the fat column is a sort/distkey. rsbb in the Intermix slack has studied this in the most detail I know of outside AWS but they haven’t published their book yet.
- zten 6y agohttps://github.com/awslabs/amazon-redshift-utils/blob/master/src/Investigations/EarlyMaterialization.md https://github.com/awslabs/amazon-redshift-utils/blob/master... The compression actually works as advertised, but that document outlines why you don't want to have the sortkey skew from other columns. The disk I/O balloons as it goes and fetches a larger number of blocks from the other columns that match what you're filtering in the sortkey.
- mrits 6y agoSeveral different compression algorithms would do very well for this. E.g dict compression for low cardinality wide columns. I'd recommend familiarizing yourself with a handful of different encodings for columnar data stores before you give up on the idea.
- tticvs 6y agoThis caveat would apply for columns queried, not columns stored right?