PostgreSQL – Can queries that require all partitions be blocked or rejected?

Question

We are planning to partition a dataset of ~500million rows using list/range partitioning in PostgreSQL 13.4 on RDS. Our read access patterns should only require a very small number of partitions to be accessed for each query. We would like to prevent read queries that require accessing every partition, so that this strategy can serve well as the volume of data grows. Is it possible to enforce any checks or guards at the database layer to reject/block/fail on queries that require a query plan that includes every partition?

Essentially, I would expect this to manifiest as ensuring there is sufficient filtering in the WHERE clause for the query planner to know it does not need to route to every partition. We can examine the query plans for a sample of the queries coming from the business logic, but it would be nice to be able to enforce it automatically. Ideally we can enforce in production if the overhead is not prohibitive, but just doing it in lower environments would be a positive step. Are there any options or suggestions on how that might be possible? Many thanks in advance.

• Vérace: Hi, and welcome to dba.se! The most important thing is to ensure that your queres use an Index Scan rather than a Seq Scan - doing so improved a query's performance from 25mins to 5 seconds - see here. You can try setting enable_seqscan to off - for the transaction! This will not guarantee that they are not done but may help? YMMV!
• user1822: "ensuring there is sufficient filtering in the WHERE clause" - there is nothing built into Postgres to do that.

Answer 1

There is no way to do that.

The best I can think of is to set the parameter statement_timeout in your database session so that statements that run too long fail with an error.

• Barry Hurley: Thank you @larenz-albe for the suggestion, that could be a good compromise.