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
signature.asc
Description: PGP signature