Skip to content
Yassir Halaoui
FR
Lire en français

Competence

PostgreSQL and the data layer: WAL, JSONB, replication

Most of the production incidents I've chased ended in the data layer, so I learned it properly: how PostgreSQL's write-ahead log behaves under CDC, when JSONB is the right model, and why OFFSET pagination stops working.

In short

Yassir Halaoui works with PostgreSQL in depth, including JSONB, the write-ahead log, the logical replication slots used by Debezium, and keyset pagination, alongside Oracle, MySQL, Redis, Elasticsearch and Firebase. For a payments platform he built a generic audit trail that stores before-and-after snapshots as JSONB behind a keyset-paginated API.

Where I've used it

  1. Pluxee · 2025 to now

    Runs Debezium change data capture on PostgreSQL's pgoutput logical decoding, and built a generic audit trail with before-and-after JSONB snapshots served by a keyset-paginated REST API.

  2. Société Générale and Docaposte

    PostgreSQL as the system of record for a fee-declaration platform and an insurer data-exchange platform.

  3. CIH Bank · 2017 to 2022

    Oracle databases behind production banking services.

  4. RevealTogether

    Redis for real-time state, and a hard lesson in document-database read quotas that became one of these field notes.

Everything in this area

Relational
  • PostgreSQL
  • Oracle
  • MySQL
PostgreSQL in depth
  • JSONB
  • Write-ahead log
  • Logical replication slots
  • pgoutput
  • Keyset pagination
Other stores
  • Redis
  • Elasticsearch
  • Firebase / Firestore
Tooling
  • Flyway
  • Spring Data JPA
  • Hibernate
  • Testcontainers

In depth

The write-ahead log is part of your architecture

The moment you add change data capture, the WAL stops being an implementation detail. A replication slot that stops advancing keeps every segment until the disk is full, and on a shared database that's someone else's pager going off. Retained WAL per slot is the first alert I set up.

JSONB where the shape changes

An audit trail has to stay readable after four refactors of the entities it records. Storing before-and-after snapshots as JSONB means an entry from two years ago still describes what the record looked like then. For data with a stable shape that you join on, plain columns stay the right answer.

Pagination that doesn't slow down every month

OFFSET makes the database read and throw away every skipped row, so page four hundred times out on a table that only grows. Keyset pagination carries the last id instead, and every page costs the same.

Questions

Why does PostgreSQL's WAL keep growing with a replication slot?

Because the slot hasn't been advanced: the consumer is down, lagging or deleted, or its captured tables are quiet on a busy database. Monitor retained WAL per slot, drop orphaned slots, and add a heartbeat for quiet tables.

When should you use JSONB in PostgreSQL?

When the shape of the data changes over time or varies between records, such as audit snapshots, events or settings. Not for data you constrain, join and query by field every day.

Keyset or offset pagination?

Keyset for anything that grows: audit trails, feeds, transaction lists. Offset only for small, bounded result sets where users really need to jump to page N.

Field notes

Shipped work

Related competences