I want to store several hundreds of gigabytes of geodata in a postgresql database. I want to query the data on position, time and/or a unique identifier for each object.
My table layout is similar to this:
CREATE TABLE objects( id int not null, at timestamp not null, pos geometry(Point) not null /* Other columns irrelevant to the question */ ) PARTITION BY RANGE (at)
BRIN indexes on
pos perfectly serve the needs, since they speed up the queries with relatively small indexes.
A B-TREE index on
id quickly becomes several gigabytes, so the server memory cannot hold many of them. BRIN is not suitable, as the id's are very spread within a page, so minimum and maximum is not a useful statistic.
Is there an alternate index type which can speed up queries like
SELECT ... FROM objects WHERE id = x with smaller index sizes?
The table is append-only, so only for the newest timestamps new rows will be added.