Will tables partitioned using inheritance always lock all partitions with AccessShareLocks?

Question

Will tables partitioned with inheritance based partitioning always take AccessShareLock locks on all child tables, even when constraints would exclude those partitions?

I'm currently trying to reduce the number of AccessShareLocks taken when querying an inheritance based partitioned table. We've been seeing a large amount of waits on LWLock:LockManager on this table, so we are attempting to reduce the number of AccessShareLocks taken by the most frequent queries on this table. The table uses inheritance based partitioning and CHECK constraints. The parent table items is where rows are inserted, and later archived to finished_items when in an appropriate state.

The table structure looks like:

CREATE TABLE items (
    id bigint NOT NULL,
    state character varying(255) DEFAULT 'pending'::character varying NOT NULL,
    deleted_at boolean,
    CONSTRAINT state_valid CHECK (((state)::text = ANY (ARRAY[('accepted'::character varying)::text, ('assigned'::character varying)::text, ('blocked'::character varying)::text, ('broken'::character varying)::text, ('canceled'::character varying)::text, ('canceling'::character varying)::text, ('finished'::character varying)::text, ('limited'::character varying)::text, ('expired'::character varying)::text]))) 
);

CREATE INDEX ON items(state);

CREATE TABLE finished_items (
    id bigint NOT NULL,
    state character varying(255) DEFAULT 'pending'::character varying NOT NULL,
    deleted_at boolean,
    CONSTRAINT state_is_finished CHECK (((state)::text = ANY (ARRAY[('finished'::character varying)::text, ('broken'::character varying)::text, ('expired'::character varying)::text, ('canceled'::character varying)::text, ('timed_out'::character varying)::text])))
) INHERITS (items);

CREATE TABLE deleted_items (
    id bigint NOT NULL,
    state character varying(255) DEFAULT 'pending'::character varying NOT NULL,
    deleted_at boolean,
    CONSTRAINT is_deleted CHECK ((deleted_at IS NOT NULL)) 
) INHERITS (items);

Notably finished_items only contains states: finished, broken, expired and timed_out.

70% of rows are held within finished_items and many queries explicitly filter queries on items to avoid scanning finished_items by using state = assigned. Query plans reflect this and exclude finished_items. However, we've noticed that AccessShareLocks are being taken out on all child tables, even when CHECK constraints on the finished_items and deleted_items would prevent rows matching the filter condition on state existing in the finished_items table, and the filter condition on deleted_at should exclude rows from deleted_items. We currently set constraint_exclusion to partition.

A query on this table would look like:

SELECT * FROM items WHERE state IN ('accepted', 'assigned') AND deleted_at IS NULL;

The query plan looks like:

[items_db] # EXPLAIN SELECT * FROM items WHERE state IN ('accepted', 'assigned') AND deleted_at IS NULL;
                                         QUERY PLAN
--------------------------------------------------------------------------------------------
 Seq Scan on items  (cost=0.00..0.00 rows=1 width=525)
   Filter: ((deleted_at IS NULL) AND ((state)::text = ANY ('{accepted,assigned}'::text[])))
(2 rows)

Time: 4.879 ms

The resulting locks acquired by the query look like:

[items_db] # SELECT relation::regclass, mode  FROM pg_locks WHERE pid = pg_backend_pid();
    relation     |      mode
-----------------+-----------------
 pg_locks        | AccessShareLock
 items_state_idx | AccessShareLock
 deleted_items   | AccessShareLock
 finished_items  | AccessShareLock
 items           | AccessShareLock
 ¤               | ExclusiveLock
(6 rows)

I've discovered that changing the query to use the ONLY keyword would reduce the AccessShareLock locks taken to only the parent table:

SELECT * FROM ONLY items WHERE state IN ('accepted', 'assigned') AND deleted_at IS NULL;

[items_db] # SELECT relation::regclass, mode  FROM pg_locks WHERE pid = pg_backend_pid();
    relation     |      mode
-----------------+-----------------
 pg_locks        | AccessShareLock
 items_state_idx | AccessShareLock
 items           | AccessShareLock
 ¤               | ExclusiveLock
(4 rows)

However, is there any additional configuration (CHECK constraints, configuration options) on the table we can add to prevent it from scanning all child tables when WHERE conditions would prevent rows from matching the query filters in those tables?

Answer 1

Yes, it will lock all the tables. The system can't know the constraint rules out the partition until the constraint is inspected, and with inheritance-based partitioning a lock is needed to do that inspection.

Answer 2

I wonder if this is a "constraint exclusion" vs. "partition pruning" thing.

It sounds like a rapid migration to declarative partitioning isn't viable in your real world schema, but for interests sake I adjusted the schema in your question to declarative partitioning:

CREATE TABLE items (
    id bigint NOT NULL,
    state character varying(255) DEFAULT 'pending'::character varying NOT NULL,
    deleted_at boolean
)
PARTITION BY LIST (state);

CREATE INDEX ON items(state);


CREATE TABLE items_finished PARTITION OF items FOR VALUES IN ('finished','broken', 'expired', 'canceled');
CREATE TABLE items_running PARTITION OF items FOR VALUES IN ('accepted','assigned', 'blocked', 'canceling','limited');

The query plan for selecting rows from a specific state shows that partition pruning is in effect:

# explain select * from items where state='assigned';
                                             QUERY PLAN                                              
-----------------------------------------------------------------------------------------------------
 Index Scan using items_running_state_idx on items_running items  (cost=0.14..8.16 rows=1 width=525)
   Index Cond: ((state)::text = 'assigned'::text)

However unlike with table inheritance and constraint exclusion, it seems locks aren't taken on the partitions that are pruned out:

# begin;
BEGIN

# select * from items where state='assigned';
 id | state | deleted_at 
----+-------+------------
(0 rows)

# SELECT relation::regclass, mode  FROM pg_locks WHERE pid = pg_backend_pid();
        relation         |      mode       
-------------------------+-----------------
 pg_locks                | AccessShareLock
 items_running_state_idx | AccessShareLock
 items_running           | AccessShareLock
 items                   | AccessShareLock
                         | ExclusiveLock

I'm not a postgres expert, so this is more a guess than an answer. I wonder if constraint exclusion requires the planner to actively check the constraints of each partition (requiring an AccessShareLock on each), but partition pruning can exclude partitions based on the partitioning metadata and the AccessShareLock on the parent is enough?