Re: All Taxi Services need Index Clustered Heap Append

On 3 March 2018 at 05:30, Darafei "Komяpa" Praliaskouski <me(at)komzpa(dot)net> wrote:
> Our options were:
>
> - partitioning. Not entirely trivial when your id is uuid. To get visible
> gains, we need to make sure each driver gets their own partition. That would
> leave us with 50 000(+) tables, and rumors say that in that's what is done
> in some bigger taxi service, and relcache then eats up all the RAM and
> system OOMs.

It's a good job someone invented HASH partitioning then.

It would be interesting to hear how your benchmarks go using current
master + the faster partition pruning patchset [1]. Currently, HASH
partitioning does exist in master, just there's no partition pruning
for the non-matching partitions, which is why you need [1].

I think trying with something like 500-1000 partitions might be a good
place to start.

[1] https://www.postgresql.org/message-id/0f96dd16-f5d5-7301-4ddf-858d41a6cbe3@lab.ntt.co.jp

--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services