Postgres.AI Chats

Vitaliy Kukharik 2 months ago

does number of partitions affect planning time in pg16?

Postgres.AI 2 months ago | prev | next | oai/gpt-4o

Yes, the number of partitions can affect planning time in PostgreSQL 16. According to the PostgreSQL 16 documentation, having too many partitions can lead to longer query planning times and higher memory consumption during both query planning and execution. The query planner is generally able to handle partition hierarchies with up to a few thousand partitions fairly well, provided that typical queries allow the query planner to prune all but a small number of partitions. However, planning times become longer and memory consumption becomes higher when more partitions remain after the planner performs partition pruning.

Here are some key points from the documentation:

Query Planning Overhead: The query planner can handle up to a few thousand partitions well, but planning times increase and memory consumption grows when more partitions remain after pruning.
Memory Consumption: Each partition requires its metadata to be loaded into the local memory of each session that touches it, which can significantly increase memory consumption over time.
Workload Type: For data warehouse workloads, a larger number of partitions might be acceptable since query planning time is less of a concern compared to query execution time. For OLTP workloads, fewer partitions might be more efficient.

For more detailed information, you can refer to the PostgreSQL 16 documentation on partitioning.

Vitaliy Kukharik 2 months ago | prev | next

Let's test in DBLab for PG16.

Steps:

Create a partitioned table:

create table partitioned_table (
    id int8 primary key,
    data text
) partition by range (id);

For a simple query, measure the planning time, buffer hits and reads used for planning:
```
explain (analyze, buffers) select * from partitioned_table where id = 1;
```

Then, using a loop in a DO block with PL/pgSQL, partitions were added to the table, with each partition containing 999 rows:

do $do$    
declare    
    i int;    
begin    
    for i in 0..99 loop    
        execute format(  
            $sql$     
                create table partitioned_table_%s     
                partition of partitioned_table     
                for values from (%s) to (%s)     
            $sql$,     
            i,     
            i * 9999 + 1,     
            (i + 1) * 9999 + 1     
        );    

        execute format(  
            $sql$     
                insert into partitioned_table_%s     
                select generate_series(%s, %s) as id, 'data'     
            $sql$,     
            i,     
            i * 9999 + 1,     
            (i + 1) * 9999     
        );    

        execute format(  
            $sql$     
                create index on partitioned_table_%s (id)     
            $sql$,     
            i     
        );    
    end loop;    
end $do$;

Run vacuum analyze on the partitioned table.
Repeat the explain (analyze, buffers as we did for 0 partitions. This time, looking at the plain returned by explain, ensure that partition pruning is working and we have Index Scan. If not, stop and discuss with me.
Double-check the current number of partitions, using pg_inherits. If number doesn't match expectations, stop and discuss with me.
Iterate, repeating steps 3, 4, and 5 – we need data points for 0, 100, 200, ..., 1000 partitions.
In the end, print a table with 3 metrics.
- planning buffer hits+reads (also show sum of them),
- planning time,
- execution time.

Additional instructions:

do not ignore any errors, they need to be fixed
When you use SQL or PLpgSQL, print all queries as is.
And run each query separately, one SQL query or DO block per call.

Postgres.AI 2 months ago | prev | next | oai/gpt-4o

The partitioned table has been created successfully.