Hello Darafei,
> After my talk at pgconf.ru Alexander Korotkov encouraged me to share my
> story and thoughts in -hackers.
> [...]
> - An append-only table of shape (id uuid, ts timestamp, geom geometry,
> heading speed accuracy float, source text).
> A btree index on (id, ts).
> [...]
> select * from positions where id = :id and ts between :start_ts and :end_ts;
Thank you for sharing the detailed description of the problem!
Frankly, considering your requirements, including one regarding data
durability and consistency, I would advice to write a microservice that
stores data in non-transactional append-only one-file-per-driver-id
fashion. For serializing the data I would advice to use something like
Thrift or Protobuf. Also it would be a good idea to store CRC of every
record. It will take like one workday of any Go/Java developer and will
solve your problem entirely.
Granted, PostgreSQL is a powerful database. But knowing it development
process and current resources limitations (for instance, amount of people
willing to do code review) objectively it will take a few years before
this problem will be solved in the vanilla code, plus some time before
AWS deploys it. (Assuming there is a PostgreSQL core developer
interested in this particular task and he or she has time to spare.)
It is my understanding that waiting a few years is not an option in your
case. This is why I'm proposing an alternative solution.
--
Best regards,
Aleksander Alekseev
