Compression of table - what are my options? (postgres15)

Question

I have the below table which I need to compress on postgres15

What compression options do I have available given the following:

The data is queried like this

SELECT lat, lon FROM staging_common_cellid_data
WHERE mcc = 1, mnc =2, lac=3, cellid=4

this is an internal database and not customer facing so we can play around with no impact, there will be 10 billion records in this table, and would prefer an alter rather than drop and recreate

-- Table: public.staging_common_cellid_data

-- DROP TABLE IF EXISTS public.staging_common_cellid_data;

CREATE TABLE IF NOT EXISTS public.staging_common_cellid_data
(
    id bigint NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 ),
    mcc smallint NOT NULL,
    mnc smallint NOT NULL,
    lac integer NOT NULL,
    cellid integer NOT NULL,
    lon_lat point,
    lat real NOT NULL,
    lon real NOT NULL,
    gps_date date NOT NULL,
    rssi smallint NOT NULL,
    common_table_id bigint,
    CONSTRAINT staging_common_cellid_data_pkey PRIMARY KEY (id)
)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS public.staging_common_cellid_data
    OWNER to doadmin;
-- Index: idx_commontrackingdata_ipkcommontrackingid

-- DROP INDEX IF EXISTS public.idx_commontrackingdata_ipkcommontrackingid;

CREATE UNIQUE INDEX IF NOT EXISTS idx_commontrackingdata_ipkcommontrackingid
    ON public.staging_common_cellid_data USING btree
    (common_table_id ASC NULLS LAST)
    TABLESPACE pg_default;
-- Index: idx_unique_record

-- DROP INDEX IF EXISTS public.idx_unique_record;

CREATE UNIQUE INDEX IF NOT EXISTS idx_unique_record
    ON public.staging_common_cellid_data USING btree
    (mcc ASC NULLS LAST, mnc ASC NULLS LAST, lac ASC NULLS LAST, cellid ASC NULLS LAST, lat ASC NULLS LAST, lon ASC NULLS LAST, rssi ASC NULLS LAST)
    TABLESPACE pg_default;

Answer 1

There are no options for table compression in PostgreSQL. Large attributes are compressed automatically, but you don't have any of these. In addition, PostgreSQL is more wasteful with storage than many other database systems, as it has a row header of 23 bytes. You could look for a file system that transparently compresses files, but that would mean a severe performance hit.

You can save a bit of storage space by defining the columns in a different order: first the 8-byte data types (bigint), then the 4-byte data types (real, date and integer), then the 2-byte data types (smallint). This is because (for example) a bigint has to start at an address that is a multiple of 8, so if such a column comes right after a smallint one, PostgreSQL might have to introduce 6 “padding bytes” to ascertain the correct alignment.

You can see the alignment requirements for each type with

SELECT typname, typalign FROM pg_type;

A typalign of c means “can start anywhere”, s means that the address has to be a multiple of 2, i means a multiple of 4, and d means a multiple of 8.

Answer 2

In plain Postgres, your only option is TOAST for large attributes. You can, however, use Timescale to compress a table like your example above (more below). TOAST only allows compressing large attributes - your current table has not such attributes currently. You can, however try to put a bunch of the measurement columns into a JSONB column instead of breaking them out. Then you can toast the JSONB column. However, I am skeptical that this would give you good compression.

A much better alternative is to try this on TimescaleDB. You can read about our compression here. For the table above, I'd suggest adding a timestamp "time" column to replace the id, then the setup would be something like:

SELECT create_hypertable('public.staging_common_cellid_data', by_range('time', INTERVAL '1 day'));

ALTER TABLE public.staging_common_cellid_data SET (
   timescaledb.compress,
   timescaledb.compress_segmentby = 'common_table_id',
   timescaledb.compress_orderby = 'time desc' 
);

-- Compress data after 7 days
SELECT add_compression_policy(public.staging_common_cellid_data, interval '7 day');

Disclaimer: I work for Timescale and helped develop this feature.