Need for box type with 1/4 precision and gist indexes

[Anzor Apshev <anzor.apshev@xxxxxxxxx>]

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