Postgres: How Many Connections to Open? Benchmark

The question

I’ve heard many people said that, in postgres, each connection is a process, so it consume more resource than thread, context switch between processes is also costlier. When there are too many processes, the context switch overhead will cause the performance go down.

Those statements are intuitive. But i want to observe the system from when number of connection small to large.

The setup

General idea

The idea is simple. I created two VPSs, one to run pgBench, another to run Postgres, both in the same region to reduce network latency. I created few tables on postgres, then run pgBench to apply some workloads to that tables. Each run I use a different number of connection, from small to large, during the run, I will collect metrics to later comparing.

Table setup

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39

CREATE TABLE users (
    id         BIGSERIAL   PRIMARY KEY,
    name       TEXT        NOT NULL,
    email      TEXT        UNIQUE NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE wallets (
    id         BIGSERIAL      PRIMARY KEY,
    user_id    BIGINT         NOT NULL REFERENCES users(id),
    currency   CHAR(3)        NOT NULL DEFAULT 'USD',
    balance    NUMERIC(18, 2) NOT NULL DEFAULT 0,
    status     TEXT           NOT NULL DEFAULT 'active'
                   CHECK (status IN ('active', 'frozen', 'closed')),
    updated_at TIMESTAMPTZ    NOT NULL DEFAULT NOW(),
    created_at TIMESTAMPTZ    NOT NULL DEFAULT NOW()
);

CREATE TABLE transactions (
    id              BIGSERIAL      PRIMARY KEY,
    idempotency_key UUID           NOT NULL UNIQUE,
    type            TEXT           NOT NULL
                        CHECK (type IN ('transfer', 'topup', 'withdrawal')),
    amount          NUMERIC(18, 2) NOT NULL CHECK (amount > 0),
    currency        CHAR(3)        NOT NULL,
    status          TEXT           NOT NULL DEFAULT 'pending'
                        CHECK (status IN ('pending', 'completed', 'failed', 'reversed')),
    description     TEXT,
    created_at      TIMESTAMPTZ    NOT NULL DEFAULT NOW()
);

CREATE TABLE ledger_entries (
    id             BIGSERIAL      PRIMARY KEY,
    transaction_id BIGINT         NOT NULL REFERENCES transactions(id),
    wallet_id      BIGINT         NOT NULL REFERENCES wallets(id),
    amount         NUMERIC(18, 2) NOT NULL CHECK (amount > 0),
    direction      TEXT           NOT NULL CHECK (direction IN ('debit', 'credit')),
    created_at     TIMESTAMPTZ    NOT NULL DEFAULT NOW()
);

Workload

Full detail of the sql script can be found at: https://github.com/khanh1998/pg-connection-bench

Write heavy

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

\set sender_id   random(1, {{NUM_USERS}})
\set receiver_id random(1, {{NUM_USERS}})
\set amount      random(1, 50)

BEGIN;

SELECT id, balance
FROM wallets
WHERE id IN (:sender_id, :receiver_id)
ORDER BY id
FOR UPDATE;

SELECT floor(balance)::int AS sender_balance
FROM wallets WHERE id = :sender_id
\gset

\if :sender_balance < 1
ROLLBACK;
\else
UPDATE wallets SET balance = balance - :amount, updated_at = NOW() WHERE id = :sender_id;
UPDATE wallets SET balance = balance + :amount, updated_at = NOW() WHERE id = :receiver_id;

INSERT INTO transactions (idempotency_key, type, amount, currency, status)
VALUES (gen_random_uuid(), 'transfer', :amount, 'USD', 'completed')
RETURNING id \gset txn_

INSERT INTO ledger_entries (transaction_id, wallet_id, amount, direction)
VALUES
    (:txn_id, :sender_id,   :amount::numeric, 'debit'),
    (:txn_id, :receiver_id, :amount::numeric, 'credit');

COMMIT;
\endif

Read-Write balance

Collected metrics

pgbench: tps, latency, latency stddev postgres:- pg_stat_activity,

• pg_stat_statements os: /proc/vmstat kernel: perf {cpu-cycles,instructions,ref-cycles,bus-cycles},{uops_issued.any,uops_issued.stall_cycles,uops_retired.slots,uops_retired.stall_cycles},{cycle_activity.stalls_total,cycle_activity.stalls_mem_any,cycle_activity.stalls_l1d_miss,cycle_activity.stalls_l3_miss},{resource_stalls.sb,mem_inst_retired.lock_loads,cache-references,cache-misses},msr/aperf/,msr/mperf/,cpu-clock,task-clock,context-switches,cpu-migrations,major-faults,minor-faults

Hardware

CherryServers

• pgBench run on VPS: 8 vCPU (Intel Xeon Processor (Icelake)), 31 GB RAM, 200 GB NVMe
• Postgres run on bare metal: 8 cores 16 threads (Intel Xeon Gold 5315Y @ 3.20GHz), 31 GB RAM, 250 GB NVMe (Raid 1)

Postgres was running on bare metal for a stable performance Both instances was on the same region to reduce network delay

Software

pgbench

Postgres 18 running with config from PgTune:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21

max_connections = 500
shared_buffers = 8GB
effective_cache_size = 24GB
maintenance_work_mem = 2GB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 1.1
effective_io_concurrency = 1000
work_mem = 21466kB
huge_pages = try
jit = off
wal_compression = lz4
autovacuum_work_mem = 2GB
io_method = io_uring
min_wal_size = 2GB
max_wal_size = 8GB
max_worker_processes = 8
max_parallel_workers_per_gather = 4
max_parallel_workers = 8
max_parallel_maintenance_workers = 4

Benchmark scenarios

The same setup will be executed against these scenarios: 16, 32, 64, 128, 256, 512 and 1024 connections.

The observation

we gonna going from top to the bottom

Write heavy workload

pgBench metrics

Latency is increasing in exponentially. The latency growth rate is always increase, from 512 connections, double connection also double latency. That’s understandable, maybe because when we double connections (more processes) in next scenario, they queue up for the resource. The latency is made up from the time processing and the time it waiting on the the queue.

TPS drop is increasing until c256, then drop. The growth rate of tps is always drop, from c16 to c32, double connection leads to double in tps, but after that, the growth rate reduce. TPS drops and TPS growth rate also drops. It make me think that the resources like CPU has been doing something not useful when we increase the connection.

It make me think so, because, for example, there is a coffee shop with two bartenders, each can make one cup of coffee per minute, together they can make 2 cups per minute. If one people is waiting (no queue) they make 1 cup per minute (50% utilization). If two people request coffee (100% utilization) they make two cup per minute. if four people request (saturation) two people get serve and two people waiting in the queue, but they still making 2 cup per minute. No matter how many people are waiting, they consistently making two cups per minute. If they output drop, that mean the bartenders can’t focus on their job and must doing something else like cleaning table.

Postgres metrics

pg_stat_activity raw wait event count

let’s look at the stat activity to see those processes (connections) waiting on what

pg_stat_activity wait event percents

pg_stat_statement

LWLock:BufferContent

LWLock:WALWrite

Both insert queries spend most of their time waiting on LWLock:BufferContent and LWLock:WALWrite.

After pg_stat_activity and pg_stat_statements, we can see that when we increase more connection, most of connection time now are waiting for. These waiting in theory should cost very little CPU, but how is it behave under high load? let’s see some lower level metrics

OS Metrics

PSI

relative low

The conclusion

afs