Hi all! I would like to use postgres for time-series data and need geometric index. For this reason I am using timescale extension and GiST index on box type. Overall ingest and query performance is fantastic! But I would like to optimize disk usage a bit. More technically my table schema:
CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;
CREATE TABLE tracks (
tstamp timestamp NOT NULL,
objectId integer NOT NULL,
rect box NOT NULL,
-- five more smallint fields
);
SELECT create_hypertable('tracks', 'tstamp', chunk_time_interval => interval '30 day');
At this point I am ingesting my test data ~85M rows. An checking table size
>SELECT pg_size_pretty( pg_database_size('db_test') );
>14 GB
Then created index like this:
CREATE INDEX rect_indx ON tracks USING gist(rect);
After index is created reported table size is - 16 GB.
Then I started to optimize disk usage. Builtin box type is 32 bytes (4x8 bytes), for my case i need only 2 bytes for coordinate precision, I changed table schema like this:
CREATE TABLE tracks2 (
tstamp timestamp NOT NULL,
objectId integer NOT NULL,
-- replaced box type with 4 explicit coordinates
rleft smallint NOT NULL,
rtop smallint NOT NULL,
rright smallint NOT NULL,
rbottom smallint NOT NULL,
-- five more smallint fields
);
Ingesting the same test data, database size is only 7.2 GB!
After that I have created index like this:
CREATE INDEX rect_indx ON tracks2 USING gist( box( point(rleft,rtop), point(rright,rbottom))) ;
With this index created my table size is 14 GB, which is disappointing. I can't explain why db became so bloated.
Any help on this is much appreciated.
Thank you for reading to this point.
--
ГукIи псэкIи фыфей / Sincerely yours
Iэпщэ Анзор / Anzor Apshev
