PostgreSQL Event Sourcing: Append-Only Patterns

Tip: Event Table Design

CREATE TABLE events (
    id BIGSERIAL PRIMARY KEY,
    aggregate_id UUID NOT NULL,
    event_type TEXT NOT NULL,
    payload JSONB NOT NULL,
    created_at TIMESTAMPTZ DEFAULT now()
);

All state changes are recorded as events.

Gotcha: Never Update or Delete Events

Event sourcing requires an immutable event log. Use soft deletes if you must "remove" events.

Tip: Reconstruct State

SELECT payload FROM events
WHERE aggregate_id = 'uuid'
ORDER BY created_at;

Apply events in order to rebuild current state.

Gotcha: Performance for Long Aggregates

Reconstructing state from thousands of events is slow. Use snapshots to cache intermediate state.

Tip: Projections with Triggers

CREATE OR REPLACE FUNCTION project_order() RETURNS trigger AS $$
BEGIN
    -- Update read model
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Maintain a denormalized read model alongside events.

Gotcha: Event Versioning

Include a version field in events to handle schema evolution over time.

Tip: EXPLAIN (ANALYZE, BUFFERS) Is Your Best Friend

For query debugging, always use EXPLAIN (ANALYZE, BUFFERS) instead of plain EXPLAIN. The BUFFERS option shows hit/miss rates for every node, revealing whether your indexes are actually in memory.

Tip: Partial Indexes Are Underutilized

CREATE INDEX ON orders (status) WHERE status = 'pending' creates a tiny index that covers only the rows your query needs. It's faster to scan and cheaper to maintain than a full-column index.

Gotcha: NULL Sorting Is Non-Obvious

By default, NULLs sort AFTER non-null values in ascending order. ORDER BY col DESC puts NULLs FIRST. Use NULLS LAST or NULLS FIRST to be explicit.

Senior Insight

PostgreSQL is surprisingly well-suited for event sourcing. Using JSONB for event payload, an identity column for event ordering, and LISTEN/NOTIFY for real-time projections, you can implement event sourcing without additional infrastructure. I've built production event-sourced systems that handle millions of events on a single PostgreSQL instance. The key: partition the events table by month and create covering indexes for replay queries.

Source: pganalyze Blog (https://pganalyze.com/blog), PostgreSQL Docs (https://www.postgresql.org/docs/current/), Crunchy Data Blog (https://www.crunchydata.com/blog)