SV: design partioning scheme for selecting from latest partition

>Fra: Francisco Olarte <folarte(at)peoplecall(dot)com>
>Sendt: 22. marts 2021 20:04
>Til: Niels Jespersen <NJN(at)dst(dot)dk>
>Cc: pgsql-general(at)lists(dot)postgresql(dot)org
>Emne: Re: design partioning scheme for selecting from latest partition
>
>Niels:
>
>On Mon, Mar 22, 2021 at 3:40 PM Niels Jespersen <NJN(at)dst(dot)dk> wrote:
>...
>> -- Then I want to be able to do this wothout scanning all partitions for the highest version number.
>>
>> select s.* from s where s.version = (select max(version) from s);
>
>> I could add an index on the version column. But the only use would be to the newest partition, so that seems a bit like overkill, indexing 100 of milliomns of rows.
>
>Without an index, or some caching, you would need to scan partitions.
>...
>Even if you can do something like that, without an index you will need a full scan, or do some trigger magic and keep a cache ( just keep versio, count(*) on a table and maintain it ). If your partitions are ordered, you can always keep the last one indexed, or if you know versions do not decrease, you may keep things cached. This seems to be the kind of problem where the generic solution is hard but a little insider knowledge can accelerate it a lot.
>
>Regards.
> Francisco Olarte.

Thank you Francisco

I think I will revisit the whole design. Better do it right.

Niels