Skip to content
pg_crdt

Quickstart: a counter column

Add a like counter to a table, let two writers count under their own names, merge a count that arrives from somewhere else, and read the value. It takes a few minutes and needs no role setup.

Before you start

Section titled “Before you start”

Install the extension in an empty test database, as the database owner. Run the SQL examples in one psql session, or select JavaScript and run them in one Node.js module (for example quickstart.mjs).

For JavaScript, set DATABASE_URL to your test database and install the driver. The examples use plain node-postgres. Every JavaScript example on this page and in the other guides assumes this connected client:

Terminal window
npm install pg
import pg from 'pg'


const client = new pg.Client({
  connectionString: process.env.DATABASE_URL,
})
await client.connect()

Create a table with a counter

Section titled “Create a table with a counter”

crdt.pn is the column type of the PN-Counter, a counter that goes up and down. A new counter is crdt.pn_empty(): no entries, value 0.

create table posts (
  id bigint primary key,
  title text not null,
  likes crdt.pn not null default crdt.pn_empty()
);
insert into posts (id, title) values (1, 'Hello');

Count as two writers

Section titled “Count as two writers”

Each writer counts under its own replica id: any name that identifies it, such as a user id or a device. pn_increment adds to that writer’s running total; pn_decrement adds to its total of subtractions. The update reads the current state and writes the new one in a single statement, which is what makes it safe when several writers do it at once.

await client.query(
  `update posts
   set likes = crdt.pn_increment(likes, $1)
   where id = $2`,
  ['alice', 1],
)
await client.query(
  `update posts
   set likes = crdt.pn_increment(likes, $1, $2)
   where id = $3`,
  ['bob', 2, 1],
)
await client.query(
  `update posts
   set likes = crdt.pn_decrement(likes, $1)
   where id = $2`,
  ['alice', 1],
)

Alice liked and then took her like back. Bob liked twice, for example from two buttons. The counter holds the sum of what each writer added, minus the sum of what each subtracted.

Read the value

Section titled “Read the value”

crdt.pn_value returns the number as a bigint. node-postgres returns a bigint as a string.

const { rows: [post] } = await client.query(
  `select crdt.pn_value(likes) as likes
   from posts
   where id = $1`,
  [1],
)
console.log(post.likes) // '2'

The state itself is JSON. Each replica id has its total of additions (p) and of subtractions (n):

const { rows: [row] } = await client.query(
  'select likes from posts where id = $1',
  [1],
)
console.log(row.likes)
// { n: { alice: 1 }, p: { bob: 2, alice: 1 }, t: 'pn', v: 1 }

The value is sum(p) - sum(n), which is 3 - 1 = 2. t and v are the type and the format version. Postgres prints the keys of a jsonb value in its own order, not the order in which they were written.

Merge a count from another place

Section titled “Merge a count from another place”

A phone that was offline counted 5 likes under its own id, carol. When it syncs, it sends its state. You merge it with pn_merge in the set clause of an update:

const fromCarol = { t: 'pn', v: 1, p: { carol: 5 }, n: {} }
await client.query(
  `update posts
   set likes = crdt.pn_merge(likes, $1)
   where id = $2`,
  [fromCarol, 1],
)

Merge takes the larger total for every replica id, so it is safe to repeat. If the phone’s message arrives twice, or the app retries after a timeout, nothing is counted twice:

const again = { t: 'pn', v: 1, p: { carol: 5 }, n: {} }
await client.query(
  `update posts
   set likes = crdt.pn_merge(likes, $1)
   where id = $2`,
  [again, 1],
)
const { rows: [merged] } = await client.query(
  'select crdt.pn_value(likes) as likes from posts where id = $1',
  [1],
)
console.log(merged.likes) // '7'

The value is 7: Alice and Bob’s 2, plus Carol’s 5, counted once.

Rules to keep from the start

Section titled “Rules to keep from the start”
  • Merge inside the update. Never read the state, merge it in your application and write the result back: another writer’s change in between is lost. See Update safely.
  • One writer per replica id. If two writers count under the same id, they overwrite each other. See Replicas and identity.
  • Here the server chose the ids. alice, bob and carol above came from trusted code. If clients send their own counts, a client can send an entry that claims to be someone else’s, and merge keeps it forever. Give such clients a wrapper function instead of update access: see Accept writes from untrusted clients.

Next steps

Section titled “Next steps”