Customer Success

Understanding Locking in PostgreSQL

Locking is like the security guard of databases, ensuring data integrity and preventing conflicts when multiple users access the database at the same time. In PostgreSQL, “AccessShareLock” locks were acquired on partitions and indexes before PostgreSQL 12. These locks kept the table structure consistent during concurrent operations, preventing unwanted changes.

Introducing Fastpath Locks

Enter “fastpath locks” – lightweight locks designed for efficiency in low-contention scenarios. These locks are managed directly by individual database connections, optimized for speed and performance. But there’s a catch – they’re capped at 16 locks per connection. When that limit is crossed, the central lock manager takes over, providing more comprehensive control.

The real magic lies in how fastpath locks streamline common locking situations, offering impressive performance benefits. But remember, their behavior can change depending on transaction duration. To fully understand their impact, tailored benchmark tests are essential.

To begin, let’s examine the structure of the pgbench_accounts table. This table is partitioned based on the aid column using the RANGE strategy, distributing rows across various child tables like pgbench_accounts_1, pgbench_accounts_2, and so forth. Each child table contains a range of aid values.

-- Table structure
CREATE TABLE public.pgbench_accounts (
aid int4 NOT NULL,
bid int4 NULL,
abalance int4 NULL,
filler bpchar(84) NULL,
CONSTRAINT pgbench_accounts_part_pkey PRIMARY KEY (aid)
)
PARTITION BY RANGE (aid);

CREATE TABLE pgbench_accounts_1 PARTITION OF pgbench_accounts FOR VALUES FROM (100001) TO (110001);
CREATE TABLE pgbench_accounts_2 PARTITION OF pgbench_accounts FOR VALUES FROM (110001) TO (120001);
CREATE TABLE pgbench_accounts_3 PARTITION OF pgbench_accounts FOR VALUES FROM (120001) TO (130001);
CREATE TABLE pgbench_accounts_4 PARTITION OF pgbench_accounts FOR VALUES FROM (130001) TO (140001);
CREATE TABLE pgbench_accounts_5 PARTITION OF pgbench_accounts FOR VALUES FROM (140001) TO (150001);
CREATE TABLE pgbench_accounts_6 PARTITION OF pgbench_accounts FOR VALUES FROM (150001) TO (160001);
CREATE TABLE pgbench_accounts_7 PARTITION OF pgbench_accounts FOR VALUES FROM (160001) TO (170001);
CREATE TABLE pgbench_accounts_8 PARTITION OF pgbench_accounts FOR VALUES FROM (170001) TO (180001);
CREATE TABLE pgbench_accounts_9 PARTITION OF pgbench_accounts FOR VALUES FROM (180001) TO (190001);
CREATE TABLE pgbench_accounts_10 PARTITION OF pgbench_accounts FOR VALUES FROM (190001) TO (200001);
CREATE TABLE pgbench_accounts_11 PARTITION OF pgbench_accounts FOR VALUES FROM (200001) TO (210001);
CREATE TABLE pgbench_accounts_12 PARTITION OF pgbench_accounts FOR VALUES FROM (210001) TO (220001);
CREATE TABLE pgbench_accounts_13 PARTITION OF pgbench_accounts FOR VALUES FROM (220001) TO (230001);
CREATE TABLE pgbench_accounts_14 PARTITION OF pgbench_accounts FOR VALUES FROM (230001) TO (240001);
CREATE TABLE pgbench_accounts_15 PARTITION OF pgbench_accounts FOR VALUES FROM (240001) TO (250001);
CREATE TABLE pgbench_accounts_16 PARTITION OF pgbench_accounts FOR VALUES FROM (250001) TO (260001);
CREATE TABLE pgbench_accounts_17 PARTITION OF pgbench_accounts FOR VALUES FROM (260001) TO (270001);
CREATE TABLE pgbench_accounts_18 PARTITION OF pgbench_accounts FOR VALUES FROM (270001) TO (280001);
CREATE TABLE pgbench_accounts_19 PARTITION OF pgbench_accounts FOR VALUES FROM (280001) TO (290001);
CREATE TABLE pgbench_accounts_20 PARTITION OF pgbench_accounts FOR VALUES FROM (290001) TO (300001);

Investigating Locking Behavior Changes

Let’s dive into the main focus of this post: the change in locking behavior from PostgreSQL 11 to PostgreSQL 14. We’ll compare the locking behavior when querying for the aid value of 100002 across all partitions in both versions.

PostgreSQL 11: Locking Behavior

In PostgreSQL 11, when querying for aid = 100002, the following locks are acquired:

postgres@ubuntu:~$ psql
psql (11.20 (Ubuntu 11.20-1.pgdg20.04+1))
Type "help" for help.

part_test=# begin;
BEGIN
part_test=*# select * from pgbench_accounts where aid = 100002;
    aid    |    bid    | abalance  |                                        filler
-----------+-----------+-----------+--------------------------------------------------------------------------------------
 100002 | 100002 | 100002 | filler
(1 row)

part_test=# select count(*) from (select nspname, relname,l.locktype, l.pid,l.mode, l.granted, l.fastpath from pg_locks l join pg_class c on (relation = c.oid) join pg_namespace nsp on (c.relnamespace = nsp.oid)
where nspname not in ('pg_catalog','information_schema') and  pid in (select pid  from pg_stat_activity
 where datname = current_database()
 and query != current_query())
  order by l.fastpath
) as lock_test;
 count
-------
    41
(1 row)

PostgreSQL 14: Locking Behavior

After upgrading to PostgreSQL 14, querying for the same aid = 100002 reveals a distinct locking behavior:

postgres@ubuntu:~$ psql -d part_test
psql (14.8 (Ubuntu 14.8-1.pgdg20.04+1))
Type "help" for help.

part_test=# begin;
BEGIN
part_test=*# select * from pgbench_accounts where aid = 100002;
    aid    |    bid    | abalance  |                                        filler
-----------+-----------+-----------+--------------------------------------------------------------------------------------
 100002 | 100002 | 100002 | filler
(1 row)

part_test=# select nspname, relname,l.locktype, l.pid,l.mode, l.granted, l.fastpath from pg_locks l
    join pg_class c on (relation = c.oid)
    join pg_namespace nsp on (c.relnamespace = nsp.oid)
where nspname not in ('pg_catalog','information_schema') and  pid in (select pid
              from pg_stat_activity
              where datname = current_database()
                and query != current_query())
order by l.fastpath;
 nspname |         relname         | locktype |  pid  |      mode       | granted | fastpath
---------+-------------------------+----------+-------+-----------------+---------+----------
 public  | pgbench_accounts_1_pkey | relation | 14485 | AccessShareLock | t       | t
 public  | pgbench_accounts_1      | relation | 14485 | AccessShareLock | t       | t
 public  | pgbench_accounts        | relation | 14485 | AccessShareLock | t       | t
(3 rows)

Workload Variability: Locking behavior can differ based on individual workload characteristics. Thoroughly test the impact of these changes on your application’s performance.

Informed Decision-Making: Prior to upgrading PostgreSQL, assess the locking behavior changes, and ensure that your application’s performance won’t be negatively affected.

Comparing All Partitions: PostgreSQL 11 vs. PostgreSQL 14
Here’s a comprehensive comparison of locking behavior across all partitions for the pgbench_accounts table between PostgreSQL 11 and PostgreSQL 14.

(Note: Due to space constraints, we’re showcasing a subset of partitions.)

The AccessShareLock in PostgreSQL is a double-edged sword, offering concurrency while posing potential hurdles. Let’s look into its disadvantages:

Contention at the Table Level: While AccessShareLock promotes read concurrency, it locks entire tables. This can lead to contention when concurrent transactions need different levels of access, stalling progress.

Blocking Writes: Though AccessShareLock supports reads, write-oriented transactions requiring exclusive locks can get blocked by those holding AccessShareLocks, hampering data modification.

Deadlock Danger: Poorly coordinated lock acquisitions can trigger deadlocks. When transactions chase incompatible locks, they could deadlock, stalling the system.

Performance Overhead: Locks incur overhead. Frequent lock acquisitions and releases could impact performance, particularly in highly concurrent scenarios.

Read-Only Overkill: For read-only tasks, AccessShareLock might be overkill. Alternative strategies, like READ COMMITTED isolation or snapshot isolation, could offer read consistency without excessive locking.

In conclusion, while AccessShareLock has merits, its drawbacks warrant careful consideration. Tailor your lock strategy to your application’s needs, optimizing concurrency without sacrificing performance.

Summary:

The understanding that PostgreSQL versions prior to 12 acquire locks on all partitions and indexes may not be universally applicable. It is essential to conduct specific testing to confirm the impact of such locks. In our own testing, we observed no discernible impact before and after upgrading to PostgreSQL version 13. Therefore, it is crucial to evaluate the locking behavior based on individual workload characteristics and conduct thorough testing to ascertain the actual impact.