PostgreSQL Sharding Without Losing ACID: How Postgres Pro Shardman WorksNote · digest for November 19
PostgreSQL Sharding Without Losing ACID: How Postgres Pro Shardman Works
Note3 sections≈ 4 min

PostgreSQL Sharding Without Losing ACID: How Postgres Pro Shardman Works

Databases present a frustrating paradox. You want to spread tables across a dozen servers so traffic stops choking one box. At the same time, you want to talk to the cluster like plain PostgreSQL—writing standard SQL and knowing a transfer between two accounts either commits everywhere or rolls back cleanly. Usually, those two goals fight each other. In a Habr deep dive, Postgres Professional engineers showed how they combined horizontal sharding with strict ACID in Postgres Pro Shardman (a commercial cluster based on PostgreSQL 17 and 18).

Why DIY Sharding Breaks Transactions

Single-node PostgreSQL scales a long way with bigger hardware, until heavy OLTP workloads hit MVCC and VACUUM limits. One cleanup worker handles a table: a long-running report pins old row versions, storage bloats, and you risk transaction ID wraparound. Partitioning parallelizes VACUUM, yet memory, CPU, and disks stay shared on one machine.

Once hardware maxes out, teams split the database into independent servers (shards) inside application code. Capacity grows, while ACID—atomicity, consistency, isolation, and durability—crumbles. Transfer money between cards on different nodes and the network drops halfway: one debits, the other never credits. Run a report and one shard has committed new rows while another hasn't, giving you a skewed snapshot. Taking a cluster-wide point-in-time backup becomes nearly impossible.

How Shardman Builds Distributed ACID

Shardman builds sharding into a modified PostgreSQL core. All nodes are peers: your app connects to any primary, and that node acts as coordinator. Under the hood, it uses partitioning and a customized shardman_fdw (based on postgres_fdw, letting nodes query each other's tables). Tables come in three flavors: distributed (distributed_by a hash key), colocated (colocate_with so related tables live on the same shards for local joins), and global (global, replicated on every node for lookup tables).

-- Distributed table: the shard key must be part of PK/UNIQUE
CREATE TABLE transactions (
    transaction_id   serial8,
    card_id          bigint NOT NULL,
    transaction_time timestamp NOT NULL,
    amount           numeric(12, 2) NOT NULL,
    PRIMARY KEY (card_id, transaction_id)
) WITH (distributed_by = 'card_id', num_parts = 12);

-- Global lookup table: stored synchronously on every shard
CREATE TABLE currencies (
    code text PRIMARY KEY,
    name text NOT NULL
) WITH (global);

-- Transfer across cards on different shards via any cluster node
BEGIN ISOLATION LEVEL REPEATABLE READ;
INSERT INTO transactions (card_id, transaction_time, amount)
VALUES (1020444400000060, now(), -500.00);
INSERT INTO transactions (card_id, transaction_time, amount)
VALUES (1020444400000080, now(),  500.00);
COMMIT;

To your application, that's ordinary SQL. Internally, multi-node writes use two-phase commit (2PC), a global CSN counter, and a background monitor.

First, the coordinator sends PREPARE to participants: each shard writes changes to WAL, marks the transaction InDoubt, and returns its 64-bit CSN (Commit Sequence Number, tied to time). The coordinator picks the highest CSN and broadcasts COMMIT PREPARED. Every node uses that number to know exactly which row versions are visible to a transaction.

A background monitor on each segment resolves stuck InDoubt transactions after crashes, detects distributed deadlocks, and syncs the oldest active CSN for VACUUM. Inter-node connections run through the Silk multiplexer (silkroad and silkworm, shrinking connections from M × N to N × (N - 1) + M), per-shard high availability uses built-in Postgres Pro BiHA with a Referee arbiter instead of Patroni, and backups plus PITR align on (CSN, LSN) checkpoints.

The Cost of Distribution and When to Hold Off on Sharding

Transparent ACID costs latency and schema flexibility. 2PC adds a network round-trip, and avoiding stale reads under clock drift requires a csn_commit_delay pause (in nanoseconds)—making accurate NTP/PTP mandatory.

Every PRIMARY KEY and UNIQUE constraint must include the shard key, and FOREIGN KEY constraints only work between colocated or global tables. The authors also note current limits: Silk speeds up reads only (writes still go through FDW), cross-shard JOIN queries off the shard key run slowly, rebalancing via shardmanctl/shardmand is heavy, and upgrading from v14 to v18 requires migrating data. It's also a commercial enterprise product.

Until you've exhausted indexes, PgBouncer, partitioning, read replicas, caching, and queues, a single PostgreSQL cluster will stay simpler and faster. Shardman makes sense once one cluster physically can't keep up with writes, yet dropping strict SQL and ACID isn't an option.

Want a deep diveVotes help us choose what to break down in detail. This material has 0 votes.
Sources · 4