PostgreSQL table with ts+key as primary key:\u00a0 ~43GB<\/p>\n
PostgreSQL wide column table with ts as primary key : 247GB
\nCassandra wide column table with ts as primary\u00a0 key: 4.5GB<\/p>\n
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). )<\/p>\n
It seems that the Apache Cassandra Column Store can compress the columns pretty good – factor 10 less disk space!<\/p>\n
The source table in PostgreSQL (TimescaleDB) with a timestamp and a key column and 8 data columns had about 170 Mio rows.<\/p>\n
CREATE TABLE candles\r\n(\r\n instrument character varying(10) NOT NULL,\r\n ts timestamp(3) without time zone NOT NULL,\r\n o numeric,\r\n h numeric,\r\n l numeric,\r\n c numeric,\r\n primary key (instrument, ts)\r\n)<\/pre>\nI 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.<\/p>\n
CREATE TYPE price AS (\r\n o float,\r\n c float,\r\n h float,\r\n l float,\r\n volume float\r\n);\r\n\r\nCREATE TABLE candles_wide\r\n(\r\n ts timestamp(3) without time zone NOT NULL,\r\n AU200_AUD price,\r\n AUD_CAD price,\r\n AUD_CHF price,\r\n AUD_HKD price,\r\n AUD_JPY price,\r\n AUD_NZD price,\r\n ... 124 columns<\/pre>\nApache Cassandra wide column store table with ts as primary key and many columns.<\/p>\n
CREATE TABLE candles (ts timestamp,\r\n AU200_AUD tuple<float,float,float,float,float>, \r\n AUD_CAD tuple<float,float,float,float,float>, \r\n AUD_CHF tuple<float,float,float,float,float>, \r\n ... 124 tuples<\/pre>\n<\/p>\n
<\/p>\n","protected":false},"excerpt":{"rendered":"
PostgreSQL table with ts+key as primary key:\u00a0 ~43GB PostgreSQL wide column table with ts as primary key : 247GB Cassandra wide column table with ts as primary\u00a0 key: 4.5GB Strange that in PostgreSQL a table with much less rows (but … Continue reading