0

I'm running Postgres 14 on Ubuntu with 64 cores/ 128 threads and 128GB of memory. I need to run large aggregation jobs daily. The one table that I need to aggregate on is current 1.4 billion rows.

Basically doing anything with this DB will not use more than one process per query. Once SELECT count(*) ran 8 parallel workers, but even this isn't reliable.

Here are my settings:

cpu_index_tuple_cost 0 
cpu_operator_cost 0
cpu_tuple_cost 0
max_logical_replication_workers        | 4                 
 max_parallel_maintenance_workers       | 32               
 max_parallel_workers                   | 32               
 max_parallel_workers_per_gather        | 32               
 max_pred_locks_per_page                | 2                
 max_pred_locks_per_relation            | -2               
 max_pred_locks_per_transaction         | 64               
 max_prepared_transactions              | 50
maintenance_io_concurrency             | 10   
 maintenance_work_mem                   | 64000MB                                 
 max_connections                        | 1000                                    
 max_files_per_process                  | 1000               

I have tried setting every other setting to be very permissive so I can use more of this powerful machine. I would really like to stick with pure Postgres to run these aggregations instead of using code, which I thought would be easy. But I'm a few hours into getting this to work now.

The user is postgres and because it did, one time, spin up parallel workers for the count, I believe it is not an OS permissions issue. There are 12 postgres user processes currently running.

My read table has no foreign keys, and 3 indexes.

Here is an example query (that has been obfuscated but does run) that runs one process only (all of the jobs do except sometimes SELECT count(*) runs 8):

WITH std_devs AS (
    SELECT
        a,
        b,
        c,
        STDDEV(d) AS std_dev
    FROM
        table_name
    GROUP BY
        a, b, c
)
INSERT INTO event_records_avg
    (a, b, c, std_dev)
SELECT
    std_devs.a,
    std_devs.b,
    std_devs.c,
    std_devs.std_dev
FROM
    std_devs
ON CONFLICT (a, b, c)
DO UPDATE SET
    std_dev = excluded.std_dev;

Appreciate any help getting this to run more parallel.

I tried EXPLAIN ANALYZE ... LIMIT 1000 and adding a timeout but it just times out prior to completion so I couldn't see the query plan.

1 Answer 1

3

Data modifying statements are never parallelized, and there is nothing you can do about that. See the documentation for the current restrictions on parallel query.


The rest of my answer are tips how you can use parallel query to your advantage with statements where it is supported.

If you really have only a single query running, you should set max_parallel_workers and max_parallel_workers_per_gather to 64, so that a single query can use all resources.

The number of parallel processes actually used depends on min_parallel_table_scan_size and min_parallel_index_scan_size and a heuristics:

  • if the limit is exceeded, PostgreSQL plans a single parallel worker

  • if the table or index exceeds three times the limit, a second worker is planned

  • if the table exceeds nine times the limit, a third worker is planned, and so on

So the number of parallel workers grows logarithmically. The idea is that even scanning a large table won't use up all resources. However, you can override these heuristics by setting the storage parameter parallel_workers on the table. If you want a scan of a table to use more parallel workers, adjust parallel_workers on that table.

1
  • Complaining is easy, coming up with a concept how data modifying statements can be parallelized not so much. Anyway, you got your question answered. Oct 2, 2023 at 19:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.