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 at
and 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.
CLUSTER
on id, and useBRIN
, especially if you're not insert/update heavyid
coming from? Who assigns them, and where does the querent obtain them to incorporate into the query at issue? Are you free to reorganize them? It seems strange thatat
andpos
are naturally correlated so as to be mutually useful for BRIN. One would naively expectat
andid
to be in that situation.id
is an unique identifier for the physical object, think car license plate. It's a number but non-sequental so for this use case more or less random. If you think about it in terms of car/license-plates is easy to see why(license_plate, time)
andpos
are correlated, if 2 records are for the same license plate 1 second apart, thepos
of the two records will also be very close together.time
andpos
can be highly correlated conditioned on the sameid
, but I don't see how such conditioned correlation gets you compatibility for effective BRIN indexes. I think you would need unconditional correlation for that.