The Practical Developer

CQRS with Postgres: Separate Read Models Without Adding a Second Database

June 14, 2026 by The Practical Developer

Your normalized write schema makes every list page join five tables. CQRS within a single Postgres database builds a read-optimized copy that serves queries in milliseconds, kept fresh by triggers and a lightweight queue. No event store, no message broker, no second system.

postgres

architecture

performance

node.js

Two diverging paths in a data visualization, representing the separation of read and write models

Your team built a clean normalized schema. Orders, line items, products, customers, and payments each live in their own table with proper foreign keys, CHECK constraints, and third-normal-form purity. The write path is fast and correct. Every INSERT, UPDATE, and DELETE is a few microseconds on indexed primary keys.

Then you open the order dashboard. The query joins five tables, aggregates over line items, computes running totals, and filters by customer segments. It takes 800 milliseconds. The product manager wants a real-time dashboard that refreshes every 10 seconds. The 800 ms query would lock those tables under a read workload that pushes 200 requests per second. Something has to give.

Most teams reach for one of three solutions: add more indexes (which slow writes), cache the result in Redis (which adds staleness and cache-invalidation logic), or throw hardware at the database. None of these fix the fundamental tension. Your write model is optimized for consistency and your read model is optimized for query speed, and both live in the same schema.

CQRS (Command Query Responsibility Segregation) says: stop asking one model to do both. Maintain a separate read-optimized data structure that is built from events emitted by your write model. This post shows a pragmatic, in-production CQRS pattern that keeps everything in a single Postgres database, uses triggers and a lightweight queue to keep the read model fresh, and costs less operational complexity than adding Redis to the stack.

When CQRS makes sense (and when it does not)

CQRS is not a default. It adds eventual consistency, a synchronization mechanism, and more database objects to maintain. Apply it only when you have a measurable read-write conflict.

Good signs you need it:

  • A single list or dashboard query joins 4+ tables and takes over 200 ms.
  • Your write throughput suffers because read-heavy queries are competing for shared indexes.
  • You compute aggregates (counts, sums, running balances) on every read, and those aggregates do not change on every write.
  • Different client types (mobile, web, admin dashboard) need completely different shapes of the same data.

Bad signs to skip it:

  • Your schema is small, reads are simple, and you just want to micro-optimize.
  • Your read and write shapes are nearly identical.
  • Your team has not yet instrumented query performance. Fix that first.
  • You need strong read-after-write consistency for every user-facing feature. CQRS is eventually consistent by nature.

In this post I assume you have checked those boxes and your order-dashboard query is genuinely slow. Here is the pattern that fixes it.

The write model: stay normalized

Your write model does not change. Orders go into the orders table, line items into order_items, customers into customers. Writes stay fast because they target narrow, indexed rows with no join overhead.

CREATE TABLE orders (
  id          uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  customer_id uuid NOT NULL REFERENCES customers(id),
  status      text NOT NULL DEFAULT 'pending'
                CHECK (status IN ('pending', 'confirmed', 'shipped', 'cancelled')),
  total_cents integer NOT NULL DEFAULT 0,
  created_at  timestamptz NOT NULL DEFAULT now(),
  updated_at  timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE order_items (
  id         uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  order_id   uuid NOT NULL REFERENCES orders(id),
  product_id uuid NOT NULL REFERENCES products(id),
  quantity   integer NOT NULL CHECK (quantity > 0),
  unit_cents integer NOT NULL CHECK (unit_cents >= 0)
);

Your application inserts and updates these tables through the normal path. No changes to the service layer needed.

The read model: denormalize for speed

The read model is a separate table that stores exactly the shape your dashboard needs. No JOINs, no aggregations at query time. Every row is pre-computed.

CREATE TABLE order_read_model (
  id                uuid PRIMARY KEY,
  customer_name     text NOT NULL,
  customer_email    text NOT NULL,
  status            text NOT NULL,
  item_count        integer NOT NULL,
  total_cents       integer NOT NULL,
  first_item_name   text,
  created_at        timestamptz NOT NULL,
  updated_at        timestamptz NOT NULL
);

CREATE INDEX idx_order_read_model_status ON order_read_model (status);
CREATE INDEX idx_order_read_model_created ON order_read_model (created_at DESC);

The dashboard query becomes a single table scan with a WHERE clause:

SELECT id, customer_name, status, item_count, total_cents, created_at
FROM order_read_model
WHERE status = 'pending'
ORDER BY created_at DESC
LIMIT 50;

No JOINs. No aggregations. Under 5 milliseconds on a table with millions of rows.

The sync mechanism: event table + triggers

Here is the key question: how does data flow from the normalized write model to the denormalized read model?

The naive answer is application-level dual-writes. Every time the service creates an order, it also inserts into order_read_model. This leaks. A new code path for cancellations forgets to update the read model. A bulk-import script inserts directly into orders and the read model stays empty. The two models drift silently.

The production answer is: let the database drive synchronization. Every write to the normalized tables enqueues a change event into a dedicated event table, and a background process applies those events to the read model.

CREATE TABLE cqrs_events (
  id          bigserial PRIMARY KEY,
  aggregate   text NOT NULL,        -- 'order', 'order_item', etc.
  aggregate_id uuid NOT NULL,
  event_type  text NOT NULL,        -- 'created', 'updated', 'deleted'
  payload     jsonb NOT NULL,
  created_at  timestamptz NOT NULL DEFAULT now(),
  processed   boolean NOT NULL DEFAULT false
);

CREATE INDEX idx_cqrs_events_unprocessed
  ON cqrs_events (created_at)
  WHERE processed = false;

Now add a trigger on every write-model table. When an order row changes, the trigger inserts an event.

CREATE OR REPLACE FUNCTION notify_order_change()
RETURNS trigger AS $$
BEGIN
  INSERT INTO cqrs_events (aggregate, aggregate_id, event_type, payload)
  VALUES (
    'order',
    COALESCE(NEW.id, OLD.id),
    CASE
      WHEN TG_OP = 'INSERT' THEN 'created'
      WHEN TG_OP = 'UPDATE' THEN 'updated'
      WHEN TG_OP = 'DELETE' THEN 'deleted'
    END,
    row_to_json(COALESCE(NEW, OLD))::jsonb
  );
  RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_order_cqrs_event
  AFTER INSERT OR UPDATE OR DELETE ON orders
  FOR EACH ROW EXECUTE FUNCTION notify_order_change();

Same pattern for order_items. When a line item changes, enqueue an event with the parent order_id so the read model can rebuild that order’s aggregate.

CREATE OR REPLACE FUNCTION notify_order_item_change()
RETURNS trigger AS $$
DECLARE
  target_order_id uuid;
BEGIN
  target_order_id := COALESCE(NEW.order_id, OLD.order_id);

  INSERT INTO cqrs_events (aggregate, aggregate_id, event_type, payload)
  VALUES (
    'order_item',
    target_order_id,
    CASE
      WHEN TG_OP = 'INSERT' THEN 'item_added'
      WHEN TG_OP = 'UPDATE' THEN 'item_updated'
      WHEN TG_OP = 'DELETE' THEN 'item_removed'
    END,
    row_to_json(COALESCE(NEW, OLD))::jsonb
  );

  RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_order_item_cqrs_event
  AFTER INSERT OR UPDATE OR DELETE ON order_items
  FOR EACH ROW EXECUTE FUNCTION notify_order_item_change();

Triggers cannot fail the transaction. If the event insert fails (unique violation, disk full), the original write rolls back too. This is a feature, not a bug. It guarantees that every committed write produces exactly one event. There is no window where the order exists but the event does not.

The consumer: applying events to the read model

A background worker polls the cqrs_events table, processes events in order, and updates the read model. In Node.js, this is a long-running process that runs alongside your API server.

import { Pool } from 'pg';

const pool = new Pool({ connectionString: process.env.DATABASE_URL });

async function processEvents(batchSize = 50) {
  const client = await pool.connect();

  try {
    await client.query('BEGIN');

    const { rows: events } = await client.query(
      `SELECT id, aggregate, aggregate_id, event_type, payload
       FROM cqrs_events
       WHERE processed = false
       ORDER BY created_at
       LIMIT $1
       FOR UPDATE SKIP LOCKED`,
      [batchSize]
    );

    for (const event of events) {
      await applyEvent(client, event);
    }

    if (events.length > 0) {
      const ids = events.map((e) => e.id);
      await client.query(
        `UPDATE cqrs_events SET processed = true WHERE id = ANY($1)`,
        [ids]
      );
    }

    await client.query('COMMIT');
    return events.length;
  } catch (err) {
    await client.query('ROLLBACK');
    throw err;
  } finally {
    client.release();
  }
}

async function applyEvent(
  client: any,
  event: { aggregate: string; aggregate_id: string; event_type: string; payload: any }
) {
  if (event.aggregate === 'order') {
    await applyOrderEvent(client, event);
  } else if (event.aggregate === 'order_item') {
    await applyOrderItemEvent(client, event);
  }
}

async function applyOrderEvent(
  client: any,
  event: { aggregate_id: string; event_type: string; payload: any }
) {
  if (event.event_type === 'created' || event.event_type === 'updated') {
    await rebuildOrderReadModel(client, event.aggregate_id);
  } else if (event.event_type === 'deleted') {
    await client.query(
      'DELETE FROM order_read_model WHERE id = $1',
      [event.aggregate_id]
    );
  }
}

async function applyOrderItemEvent(
  client: any,
  event: { aggregate_id: string; event_type: string; payload: any }
) {
  await rebuildOrderReadModel(client, event.aggregate_id);
}

async function rebuildOrderReadModel(client: any, orderId: string) {
  await client.query(
    `INSERT INTO order_read_model (id, customer_name, customer_email, status,
                                   item_count, total_cents, first_item_name,
                                   created_at, updated_at)
     SELECT
       o.id,
       c.name,
       c.email,
       o.status,
       COALESCE(SUM(oi.quantity), 0),
       COALESCE(SUM(oi.quantity * oi.unit_cents), 0),
       (SELECT p.name
        FROM order_items oi2
        JOIN products p ON p.id = oi2.product_id
        WHERE oi2.order_id = o.id
        ORDER BY oi2.id
        LIMIT 1),
       o.created_at,
       o.updated_at
     FROM orders o
     JOIN customers c ON c.id = o.customer_id
     LEFT JOIN order_items oi ON oi.order_id = o.id
     WHERE o.id = $1
     GROUP BY o.id, c.name, c.email, o.status, o.created_at, o.updated_at
     ON CONFLICT (id) DO UPDATE SET
       customer_name  = EXCLUDED.customer_name,
       customer_email = EXCLUDED.customer_email,
       status         = EXCLUDED.status,
       item_count     = EXCLUDED.item_count,
       total_cents    = EXCLUDED.total_cents,
       first_item_name = EXCLUDED.first_item_name,
       updated_at     = EXCLUDED.updated_at`,
    [orderId]
  );
}

Key design decisions in this code:

  • FOR UPDATE SKIP LOCKED lets multiple consumer workers process different events concurrently without fighting over rows. Scale horizontally to handle more throughput.
  • The full rebuild on every event keeps the read-model query simple. For an order with 50 line items, SUM(quantity) is recomputed on every event. This is fine for hundreds of events per second. If you need higher throughput, switch to incremental updates that add or subtract deltas instead of recalculating everything.
  • ON CONFLICT DO UPDATE is an upsert. The first event for a new order inserts the row; subsequent events update it.

Running the consumer

The consumer runs as a standalone process, separate from your HTTP server. A simple polling loop with backpressure keeps the database from being overwhelmed.

import { Pool } from 'pg';

const pool = new Pool({ connectionString: process.env.DATABASE_URL });

async function processEvents(batchSize: number): Promise<number> {
  // ... (the function defined above)
}

async function startConsumer() {
  let consecutiveEmptyPolls = 0;

  while (true) {
    try {
      const processed = await processEvents(50);

      if (processed === 0) {
        consecutiveEmptyPolls++;
      } else {
        consecutiveEmptyPolls = 0;
      }

      if (consecutiveEmptyPolls >= 3) {
        await sleep(1000); // No events, back off
      }
    } catch (err) {
      console.error('CQRS consumer error:', err);
      await sleep(5000); // Error backoff
    }
  }
}

function sleep(ms: number) {
  return new Promise((resolve) => setTimeout(resolve, ms));
}

startConsumer();

The polling interval adapts automatically. Under load, the consumer runs tight loops processing events as fast as they arrive. When the queue is empty, it backs off to a 1-second poll. This keeps CPU near zero during idle periods.

Measuring and verifying the read model

You need to confirm two things in production: that the read model is being updated, and that it is being updated within an acceptable delay.

Add a staleness metric to the read model itself:

ALTER TABLE order_read_model ADD COLUMN last_synced_at timestamptz NOT NULL DEFAULT now();

Then track the lag between updated_at (when the order last changed) and last_synced_at (when the read model was last rebuilt). Export this as a Prometheus gauge.

import { Counter, Gauge } from 'prom-client';

const cqrsLagGauge = new Gauge({
  name: 'cqrs_read_model_lag_seconds',
  help: 'Lag between write model update and read model sync',
  labelNames: ['aggregate']
});

Most teams see sub-second lag on a single consumer with normal traffic. Under sustained load, lag stays under 3-5 seconds with two consumer workers. If lag exceeds 30 seconds, you need more throughput (more workers, incremental updates, or a dedicated sync service).

When this pattern breaks

The single-database CQRS pattern has three failure modes worth knowing.

High write throughput overwhelms the consumer. If you insert 5,000 orders per second, the consumer has to process 5,000 events per second. Each event triggers a full aggregate rebuild that involves JOINs and aggregates. At some point, the consumer falls behind. The fix is incremental updates (apply deltas instead of rebuilds) or horizontal scaling of the consumer with SKIP LOCKED.

Long-running transactions delay visibility. If a write transaction takes 30 seconds to commit (due to a lock, a slow index build, or a bad query), the trigger fires on commit, but the read model is not updated until the consumer polls. For most business domains, a delay of seconds is acceptable. If it is not, use Postgres LISTEN/NOTIFY to push notifications from the trigger to the consumer, eliminating the polling delay.

Schema changes drift the read model. When you add a column to orders, you must also update the read-model table, the rebuild query, and the trigger function. It is easy to forget one of these steps. Mitigate with integration tests that insert through the write path and assert on the read model shape.

When to use the single-database CQRS pattern

This pattern fits best when your read model lives in the same database as your write model, your throughput is under a few thousand transactional writes per second, and you need sub-second to low-seconds read-model freshness. It is the simplest CQRS implementation that does not introduce a second system, and it is the right starting point for 80% of teams that need to separate reads from writes.

If you outgrow it, the migration path is clean: replace the Postgres cqrs_events table with a Kafka topic or a dedicated event store, and move the consumer to a separate service. The application code does not change because the application never talks to the read model directly. It writes to the normalized schema, and the triggers generate events. Swap the plumbing underneath, and the write path stays untouched.

Takeaway

CQRS does not require Kafka, Event Store, or a second database. Within a single Postgres instance, triggers and a lightweight event table give you a denormalized read model that serves dashboard queries in single-digit milliseconds while your write model stays fast and normalized. Start with triggers and a polling consumer. Add LISTEN/NOTIFY and incremental updates only when the load profile demands them. The architecture scales with your needs, not against them.

A note from Yojji

Building systems that handle real production traffic means designing for the asymmetry between reads and writes from the start. Yojji’s engineering teams routinely apply patterns like CQRS to keep applications fast as data grows, separating the write path that needs correctness from the read path that needs speed. It is the kind of architecture-level thinking that keeps a product stable through 10x growth.

Keep Reading