Re: Question on overall design

On Mon, Dec 11, 2023 at 4:41 AM Dominique Devienne <ddevienne(at)gmail(dot)com>
wrote:

> On Sun, Dec 10, 2023 at 5:56 PM Ron Johnson <ronljohnsonjr(at)gmail(dot)com>
> wrote:
>
>> * We departitioned because SELECT statements were *slow*. All
>> partitions were scanned, even when the partition key was specified in the
>> WHERE clause.
>>
>
> Surely that's no the case on newer PostgreSQL, is it? Otherwise what's the
> point of partitioning?
> Also, I remember reading something about recent improvements with a large
> number of partitions, no?
>
> As someone who's interested on partitioning, I'd appreciate details.
> Thanks, --DD
>

This was on 12.5. v13 was just released, and we weren't confident about
running a mission-critical system on a .1 version.

All "transaction" tables were partitioned by month on partion_date, while
the PK was table_name_id, partition_date.

Queries were _slow_, even when the application knew the partion_date range
(since queries might span months). PG just wouldn't prune.

When I departitioned the tables, performance became acceptable.