4 ms·

Honest question here, slightly of topic. How do you handle indexing a compressed column? Or is this considered an anti-pattern?

• roenxi 5y ago
  An index is typically a mapping of values to database pages. Nothing about that cares whether the data has been compressed or not. It may or may not be compressed, but it is still on the page somewhere.
  • herehere1x 5y ago
    To be clear, an index is compression agnostic, it's the uncompressed value that will appear in the index, or the value selected e.g. bigJsonbDoc->>'email' is that right?
    • GrayShade 5y ago
      Indexes are generally a mapping from the key to a row location (or sometimes to the primary key of the row). You can compress the index itself, but it's usually accessed randomly, so it might be detrimental. Depending on the database, you might have compressed pages (sets of consecutive rows), rows, or columns in a row. Postgres does the latter (it's called TOAST) where the large columns are compressed and/or stored in a hidden table. So you first search the index for the key, figure out where your row is, read the row from the table, then, if needed, you read the columns you need from the TOAST table and uncompress them.