Re: All Taxi Services need Index Clustered Heap Append

On Sat, Mar 3, 2018 at 4:53 PM, David Rowley
<david(dot)rowley(at)2ndquadrant(dot)com> wrote:
> 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.

I don't think that will actually help much. 1000 partitions means each
partition gets data from ~50 vehicles. A 60 tuples per page each page
in the partitioned able will contain on average 1.2 interesting
tuples. So you still have almost one page read per row.

Regards,
Ants Aasma
--
+43-670-6056265
Cybertec Schönig & Schönig GmbH
Gröhrmühlgasse 26, A-2700 Wiener Neustadt
Web: https://www.cybertec-postgresql.com