Postgres 11 declarative partitioning – using large number of partitions

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?

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.