Re: All Taxi Services need Index Clustered Heap Append

From: Aleksander Alekseev <a(dot)alekseev(at)postgrespro(dot)ru>
To: Darafei "Komяpa" Praliaskouski <me(at)komzpa(dot)net>
Cc: PostgreSQL Developers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Re: All Taxi Services need Index Clustered Heap Append
Date: 2018-03-05 12:10:06
Message-ID: 20180305121006.GB26193@e733.localdomain
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Darafei Komяpa Praliaskouski 2018-03-05 12:11:25 Re: All Taxi Services need Index Clustered Heap Append
Previous Message Fabien COELHO 2018-03-05 12:08:44 Re: [HACKERS] Re: [COMMITTERS] pgsql: Remove pgbench "progress" test pending solution of its timing is (fwd)