"Leonardo Francalanci" <lfrancalanci@simtel.ie> writes:
> we have a system that stores 200,000,000 rows per month
> (other tables store 10,000,000 rows per month)
> Every row has 400 columns of integers + 2 columns (date+integer) as index.
> Our system compresses rows before writing them to a binary file on disk.
> Data don't usually need to be updated/removed.
> We usually access all columns of a row (hence compression on a per-row basis
> makes sense).
> Is there any way to compress data on a per-row basis? Maybe with
> a User-Defined type?
If you just stuck all the integers into a single integer-array column,
it would be 1600 bytes wide, which is ... hmm ... not quite wide enough
to trigger the toast logic. Perhaps it would be worthwhile for you to
run a custom build with TOAST_TUPLE_THRESHOLD/TOAST_TUPLE_TARGET set
to half their standard values (see src/include/access/tuptoaster.h).
You'd not need to write any specialized code that way.
Note that if you sometimes search on the values of one of the non-index
columns, this might be a bad idea.
regards, tom lane