1

We are running a system using Postgres 11 and we implemented partitioning with ID which is most convenient to us but which produces many partitions (currently over 1000 with expected total of 10000 within next 5 years). We generate 30 million rows per month with heavy indexing. Partition rows are never updated and our queries always target single partition.

Now we have run into a problem that we exhausted maximum allowed locks (cca. 25000). Is increasing max_locks_per_transaction and max_connections a viable solution with expected rise in partition number? Or should we switch to hash or date-range partitioning? And if so, what would be the optimal partition size and count?

Improve this question
5
  • And this ^^^^ should be posted as an answer.
    – McNets
    Commented Oct 7, 2019 at 9:20
  • Thank you for your answer. Just to clarify, for this particular scenario, you don't find 10,000 partitions to be too much for Postgres to handle?
    – user192502
    Commented Oct 7, 2019 at 10:10
  • It's definitely a very high value. If you use the partitioned table in a query, the optimizer will have to consider all these partitions (even if it ends up not using them all), so planning time will grow significantly. If these are long running queries, it might not matter so much. With short queries it could kill you.
    – Laurenz Albe
    Commented Oct 7, 2019 at 10:32
  • @a_horse_with_no_name "max_connections has nothing to do with that" That isn't correct. The amount of memory set aside for locks is proportional to max_connections * max_locks_per_transaction. So changing max_connections is effective, but you shouldn't do it because it is unintuitive.
    – jjanes
    Commented Oct 7, 2019 at 16:54
  • @a_horse_with_no_name the lock space is scaled per connection in max_connections, but it isn't segregated per connection it is just a giant pool. So increasing max_connectons will increase the number of available locks to anyone.
    – jjanes
    Commented Oct 7, 2019 at 17:02

1 Answer 1

Reset to default
0

we implemented partitioning with ID

What does that mean specifically? List partitioning, where each list has only one member? And what is ID? Is that the primary key from a parent table, and you are partitioning a child table?

Did you do before-and-after studies, or testing in a good test environment, to show that you actually get a benefit from this setup in the first place?

Partition rows are never updated and our queries always target single partition. Now we have run into a problem that we exhausted maximum allowed locks (cca. 25000).

If you don't update, and queries always target a single partition, then I don't see how you would be running out of locks. What operation is happening which locks all the partitions at once? Is 25000 the current setting of max_locks_per_transaction * max_connections, which is inadequate, or is that what you want to increase it to? If 25,000 is already inadequate and you still expect your data to grow 10 times, I'd be a bit worried there that just increasing it may not be good long term solution (although probably fine as a short term solution). Or at least, I'd do some simulations in a test environment.

Or should we switch to hash or date-range partitioning?

Partitioning is not fungible. If partitioning by ID gave you a substantial benefit, there is no reason to think changing it to date-range would give the same benefit. Without knowing the source of the benefit, we can't predict what other methods would retain (or better) that benefit.

Improve this answer
1
  • We are running out of locks because, no matter that the planner successfully pinpoints single partition, initially it allocates NUMBER_OF_PARTITIONS * INDICES access share locks for the SELECT query. That number became higher than MAX_LOCKS_PER_TRANSACTION * MAX_CONNECTIONS.
    – user192502
    Commented Oct 8, 2019 at 6:33

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.