PostgreSQL table with ts+key as primary key: ~43GB
PostgreSQL wide column table with ts as primary key : 247GB
Cassandra wide column table with ts as primary key: 4.5GB
Strange that in PostgreSQL a table with much less rows (but much more columns) needs a lot of more space (both tables store the same amount of data). )
It seems that the Apache Cassandra Column Store can compress the columns pretty good – factor 10 less disk space!
The source table in PostgreSQL (TimescaleDB) with a timestamp and a key column and 8 data columns had about 170 Mio rows.
CREATE TABLE candles ( instrument character varying(10) NOT NULL, ts timestamp(3) without time zone NOT NULL, o numeric, h numeric, l numeric, c numeric, primary key (instrument, ts) )
I needed to flatten the table so that i have just the timestamp as primary key and many columns and each column is of a type. It ends up in a table with about 1.6 Mio rows and many columns.
CREATE TYPE price AS ( o float, c float, h float, l float, volume float ); CREATE TABLE candles_wide ( ts timestamp(3) without time zone NOT NULL, AU200_AUD price, AUD_CAD price, AUD_CHF price, AUD_HKD price, AUD_JPY price, AUD_NZD price, ... 124 columns
Apache Cassandra wide column store table with ts as primary key and many columns.
CREATE TABLE candles (ts timestamp, AU200_AUD tuple<float,float,float,float,float>, AUD_CAD tuple<float,float,float,float,float>, AUD_CHF tuple<float,float,float,float,float>, ... 124 tuples