Core Concepts

HTAP — Hybrid Transactional/Analytical Processing

ParticleDB is an HTAP database. It runs OLTP transactions (INSERT, UPDATE, DELETE with full ACID guarantees) and OLAP analytics (complex GROUP BY, aggregations over billions of rows) on the same data, in the same engine.

A conventional stack splits these across separate systems — a transactional store, an analytical store, a vector store, a cache — joined by ETL pipelines. ParticleDB removes the split:

Conventional stack	ParticleDB
Separate engines for OLTP, OLAP, vectors, and cache	One database
ETL pipeline with hours of lag	Real-time analytics on live data
4 connection strings, 4 schemas	1 connection string

Connecting

The native wire protocol

ParticleDB’s own binary wire protocol is the fast path, and it is what the engine is built around. It listens on port 5440. It avoids the per-statement translation the compatibility layer performs, and it exposes capabilities that have no equivalent elsewhere — batched multi-statement transactions in a single round trip, native key-value operations, and server-side compiled procedures.

Reach for it when throughput and latency are what you are optimizing for. The first-party SDKs speak it directly — see SDKs.

Bring your existing client

You do not have to adopt a new driver to get started. ParticleDB also speaks the PostgreSQL v3 wire protocol on port 5432, so the client and driver you already use will connect and run SQL without a ParticleDB-specific package.

• psql, pgAdmin, DBeaver
• JDBC, Npgsql, psycopg2, node-postgres, sqlx
• Diesel, SQLAlchemy, Prisma, GORM

This is a compatibility layer, not an emulation: ParticleDB is a different engine, and some SQL features, system catalogs, and extensions differ. Treat it as the convenient way in, and move the paths that matter for performance to the native wire.

Transactions

Isolation modes

The server runs in one of three transaction modes, set at startup with --txn-mode.

Mode	Isolation level	Permits	Choose it when
fast	None — no isolation guarantee	Concurrent writers silently overwriting each other	Bulk load and single-writer ingest only, never with concurrent writers
occ (default)	Snapshot Isolation	Predicate anti-dependency cycles (G2)	Normal application workloads
serializable	Strict Serializable	Nothing	An invariant spans rows, or services coordinate outside the database

These are the only three values --txn-mode accepts. occ and serializable are optimistic: ordinary MVCC reads do not block concurrent writes, and ordinary writes do not block readers. A transaction that cannot be ordered safely fails at COMMIT and you retry it — see Handling conflicts. fast does no conflict detection at all, so it never asks you to retry — it loses one of the writes instead.

The default, Snapshot Isolation, is the guarantee the SQL standard describes as REPEATABLE READ. Every statement in your transaction sees one consistent snapshot, and you never see another transaction’s uncommitted work.

What Snapshot Isolation permits

Two patterns are worth checking your application against before staying on the default.

Write skew. Two transactions read the same rows, then update different rows based on what they read. Neither conflicts with the other, so both commit — and a rule that spanned both rows is broken.

-- Rule: at least one engineer must stay on call.

-- Session A                            -- Session B
BEGIN;                                  BEGIN;
SELECT count(*) FROM oncall             SELECT count(*) FROM oncall
  WHERE on_duty;         -- 2             WHERE on_duty;         -- 2
UPDATE oncall SET on_duty = false       UPDATE oncall SET on_duty = false
  WHERE name = 'alice';                   WHERE name = 'bob';
COMMIT;                                 COMMIT;

-- Both succeed. Nobody is on call.

Write skew is the anomaly to check for. If your application has an invariant that spans rows — a balance that must stay non-negative, a minimum staffing level, a uniqueness rule the database does not enforce — occ will not protect it. serializable will.

A transaction that begins after another session’s COMMIT is acknowledged does observe that commit, on a single node in the default configuration. You do not need serializable for that alone.

Verified

Measured at 12,000 probes across both transaction modes and both durable WAL modes, on both wire protocols, with a control that detects the anomaly 100% of the time when a snapshot is genuinely pinned early. Single node; we have not yet measured this on a replicated cluster.

SQL isolation levels

You do not have to change the server default. Standard SQL works per transaction or per session.

You write	You get	Compared to the default
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED	Read Committed — promoted; dirty reads are never permitted	Weaker
SET TRANSACTION ISOLATION LEVEL READ COMMITTED	Read Committed — a fresh snapshot for each statement	Weaker
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ	Snapshot Isolation — this is the default	Same
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE	Serializable	Stronger

BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- ... your statements ...
COMMIT;

Two rules:

• SET TRANSACTION ISOLATION LEVEL must run inside a transaction, before its first query or data-modification statement. Outside a transaction it emits warning 25P01 and does nothing. After the transaction has run a query, it returns 25001 and marks the transaction failed.
• To change the default for subsequent transactions on the connection, use SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL …. It does not change a transaction that is already open. BEGIN TRANSACTION ISOLATION LEVEL … sets it inline.

READ COMMITTED weakens your session

READ COMMITTED is the default in many SQL databases, so ORMs and connection pools often set it explicitly, expecting nothing to change. Here something does change — ParticleDB already defaults to Snapshot Isolation, so asking for READ COMMITTED moves you down to per-statement snapshots and allows non-repeatable reads. To keep the default, ask for REPEATABLE READ, or ask for nothing.

Handling conflicts

Under either mode, a conflicting transaction can fail with SQLSTATE 40001 (serialization_failure), most often at COMMIT. This is normal and expected — it is how an optimistic engine tells you two transactions could not both be ordered safely. Retry the whole transaction from BEGIN.

for attempt in range(5):
    try:
        with conn:                     # BEGIN … COMMIT
            cur.execute("UPDATE accounts SET balance = balance - 100 WHERE id = 1")
            cur.execute("UPDATE accounts SET balance = balance + 100 WHERE id = 2")
        break
    except psycopg2.errors.SerializationFailure:
        conn.rollback()                # try again

Keep transactions short. The longer one runs, the more likely it is to conflict.

Durability

Every committed transaction is written to a write-ahead log. --wal-sync-mode controls when that log is flushed to disk.

Mode	What it does	On disk when COMMIT returns?
sync	Flushes on every commit	Yes
groupsync (default)	Concurrent commits share a flush	Yes
nosync	Disables WAL logging entirely	No — acknowledged commits are lost on any restart

Use groupsync for normal workloads. It keeps durable-before-acknowledgement semantics while letting concurrent commits share flush work, which is why it is the default. sync gives the same guarantee with more frequent flushes.

nosync does not merely relax the flush — it turns the write-ahead log off. Nothing is logged, so there is no crash recovery at all and any acknowledged commit is gone after a restart, clean or otherwise. Use it only for benchmarks and data you can reload. The server refuses to start with nosync when replication is enabled.

Older mode names

Earlier releases exposed additional --txn-mode values. They have been removed, not deprecated: the server exits non-zero and names the replacement — see Transaction Engine for the full mapping.

Further reading

• Transaction Engine — the full anomaly matrix, commit path, and how each guarantee is verified
• Configuration — every server flag and its default