But a lot of teams still treat these numbers like something you glance at once. A screenshot in a deck. A one-off notebook cell. A quick check in a UI before a meeting.

That works until you need to answer basic questions that show up in real workflows:

What did TSLA's surface look like at 10:32? When did skew start steepening? Did the change come from the wings moving or the ATM shifting?

If you don't store the data as it arrives, you can't replay it, compare it, or audit it. You're stuck with whatever you happened to look at in the moment.

In this walkthrough, we'll build something small but practical: an internal database that continuously captures SpiderRock MLink's LiveImpliedQuote analytics for TSLA, stores each snapshot as queryable history, and also maintains a "latest view" table so you can pull the current surface state without scanning the full history.

The goal is not to build a trading system. It's to build a reliable internal dataset that you can monitor and query.

Note: SpiderRock MLink's LiveImpliedQuote analytics is a product offered for a fee, which includes exchange charges for the underlying market data used in its creation.

Table of Contents

• Prerequisites

• What Data We're Using

• Setup: Importing Packages

• Database Design

• Pulling LiveImpliedQuote

• Normalizing the Response Into Rows

• Writing To The Database

• Running a Short Polling Capture

• Analysis: Smile Reconstruction From the Database

  • Pick an Expiry with Good Coverage

  • Rebuild the Smile Across Snapshots

  • Zoom-In Around Spot

• Analysis: ATM IV and Skew Over Time

• Alert-Style Thresholds

• Wrapping Up

Prerequisites

Before running any of the code in this walkthrough, there are a few things you need to have in place.

On the API side, you need a SpiderRock MLink account with access to the LiveImpliedQuote feed. The examples use the REST interface, so no websocket setup is required, but you do need a valid API key. If you don't have one yet, you can reach out to SpiderRock directly to get access.

On the Python side, the environment is minimal. You need Python 3.10 or later for the tuple type hint syntax used in one of the function signatures. The external packages are requests, pandas, numpy, and matplotlib. Everything else – sqlite3, time, datetime – is part of the standard library. You can install the external dependencies with:

pip install requests pandas numpy matplotlib

No database setup is required beyond a writable local path. SQLite creates the file automatically on first run, so there's nothing to install or configure separately.

Finally, the walkthrough uses TSLA as the target symbol because it has a liquid and active options chain. If you want to swap in a different underlying, the only thing you need to change is the symbol variable in the config block.

What Data We're Using

This build is driven by one OptAnalytics message type from SpiderRock MLink: LiveImpliedQuote.

Each message represents an option contract and comes with the analytics you actually need for monitoring:

• the option identifier (symbol, expiry, strike, call or put)

• surface IV (sVol) and related surface fields

• Greeks (delta, gamma, theta, vega)

• context fields like underlying price (uPrc), time to expiry (years), and rate (rate)

• timestamps and calc source markers, which matter when you're turning a live feed into a database

We'll treat sVol as the main volatility field for the article and refer to it as surface IV. That keeps the workflow consistent when we rebuild smiles or compute skew proxies from stored history.

The demo uses TSLA because it has a rich and active options chain, which makes the database and queries more interesting even in a short capture window. The same pipeline works for any other underlying – the only thing you change is the symbol filter.

Setup: Importing Packages

Before touching the database or the API, we set up a small, repeatable environment. This section is intentionally minimal. We only import what we need for three things: making REST calls, storing data in SQLite, and doing basic analysis and plots.

import requests
import sqlite3
import pandas as pd
import numpy as np
import time
from datetime import datetime, timezone
import matplotlib.pyplot as plt
plt.style.use('ggplot')

• requests is used for calling MLink REST endpoints.

• sqlite3 gives us a lightweight database we can write to locally without extra setup.

• pandas and numpy are only for shaping and filtering the data once it comes back.

• time and datetime help us run a polling loop and timestamp each snapshot so the database becomes a real-time series.

Database Design

If the goal is to make live analytics queryable, the database design has to support two different needs.

First, you want an audit trail. Every snapshot should be preserved so you can reconstruct what the surface looked like at a specific time.

Second, you also want a fast way to answer "what does it look like right now" without scanning everything you've ever stored.

So we use two tables:

• implied_quote_history: Append-only. Every poll inserts a full snapshot.

• implied_quote_latest: One row per option contract. Each poll upserts into this table so it always reflects the most recent snapshot.

The core of both tables is a stable option identifier. In the feed, the option key is nested, so we normalize it into a single option_key string that includes symbol, expiry, strike, call or put, and venue fields. This becomes the primary key for the latest table and the main join key for queries.

#config
api_key = "YOUR SPIDERROCK API KEY"
mlink_url = "https://mlink-live.nms.saturn.spiderrockconnect.com/rest/json"

msg_type = "LiveImpliedQuote"

symbol = "TSLA"
poll_interval_s = 10
poll_duration_s = 120
limit = 2000

#create db connection
db_path = "/mnt/data/optanalytics_iv_greeks.db"

def get_conn(path: str = db_path):
    conn = sqlite3.connect(path)
    conn.execute("PRAGMA journal_mode=WAL;")
    conn.execute("PRAGMA synchronous=NORMAL;")
    return conn

#create db schema
def setup_db(path: str = db_path):
    conn = get_conn(path)
    cur = conn.cursor()

    cur.execute("""
    create table if not exists implied_quote_history (
        id integer primary key autoincrement,
        asof_ts text not null,

        option_key text not null,
        symbol text not null,
        expiry text not null,
        strike real not null,
        cp text not null,

        calc_source text,
        u_prc real,
        years real,
        rate real,

        s_vol real,
        atm_vol real,
        s_mark real,

        o_bid real,
        o_ask real,
        o_bid_iv real,
        o_ask_iv real,

        delta real,
        gamma real,
        theta real,
        vega real,

        src_ts text
    );
    """)

    cur.execute("""
    create index if not exists idx_hist_symbol_expiry_asof
    on implied_quote_history(symbol, expiry, asof_ts);
    """)

    cur.execute("""
    create index if not exists idx_hist_option_asof
    on implied_quote_history(option_key, asof_ts);
    """)

    cur.execute("""
    create table if not exists implied_quote_latest (
        option_key text primary key,

        last_asof_ts text not null,
        symbol text not null,
        expiry text not null,
        strike real not null,
        cp text not null,

        calc_source text,
        u_prc real,
        years real,
        rate real,

        s_vol real,
        atm_vol real,
        s_mark real,

        o_bid real,
        o_ask real,
        o_bid_iv real,
        o_ask_iv real,

        delta real,
        gamma real,
        theta real,
        vega real,

        src_ts text
    );
    """)

    cur.execute("""
    create index if not exists idx_latest_symbol_expiry
    on implied_quote_latest(symbol, expiry);
    """)

    conn.commit()
    conn.close()

setup_db()

This creates the SQLite database file and both tables. The history table is append-only and indexed for the two queries we'll run later: pulling snapshots by expiry and time, and pulling a specific option's timeline by option_key. The latest table is keyed by option_key, which lets us upsert and maintain a consistent "current view."

The columns we store are intentionally opinionated. We keep surface IV (s_vol), surface mark (s_mark), Greeks, and a few context fields. We also store timestamps so later we can reason about when a value was produced.

Pulling LiveImpliedQuote

Now we do the first live pull. The goal here is not to build a perfect filter. It's to confirm that we can retrieve a meaningful slice of TSLA option analytics and that the response structure is what we expect.

We request LiveImpliedQuote and filter by symbol using the where clause. The response is a list where most rows are actual LiveImpliedQuote messages, and one row at the end is a QueryResult summary.

def fetch_live_implied_quote(symbol: str, limit: int = 2000):
    where = f"okey.tk:eq:{symbol}"

    params = {
        "apiKey": api_key,
        "cmd": "getmsgs",
        "msgType": msg_type,
        "where": where,
        "limit": limit
    }

    r = requests.get(mlink_url, params=params)
    r.raise_for_status()
    return r.json()

raw = fetch_live_implied_quote(symbol, limit=limit)
print("raw messages:", len(raw))
print("first type:", raw[0].get("header", {}).get("mTyp") if raw else None)

This is a straight REST getmsgs call. We pass the API key, message type, and a simple symbol filter. The limit is important. It caps how many messages we get back in one poll, so for active underlyings, the returned set of strikes and expiries can vary between polls. That's fine for this tutorial, because the goal is to show the database pattern and the types of monitoring queries it enables.

This is the output you should see:

Normalizing the Response Into Rows

Right now, raw is a list of nested message objects. That format is fine for transport, but it's not something you can store or query directly. So now, we turn each LiveImpliedQuote message into one flat row with a consistent schema.

def make_option_key(okey: dict) -> str:
    return "|".join([
        str(okey.get("tk")),
        str(okey.get("dt")),
        str(okey.get("xx")),
        str(okey.get("cp")),
        str(okey.get("at")),
        str(okey.get("ts")),
    ])

def normalize_liq(raw: list, asof_ts: str, keep_calc_source: str = "Loop") -> pd.DataFrame:
    rows = []

    for row in raw:
        if row.get("header", {}).get("mTyp") != "LiveImpliedQuote":
            continue

        m = row.get("message", {})
        if keep_calc_source and m.get("calcSource") != keep_calc_source:
            continue

        pkey = m.get("pkey", {})
        okey = pkey.get("okey", {})
        if not okey:
            continue

        s_vol = m.get("sVol")
        if s_vol is None or s_vol == 0:
            continue

        o_bid = m.get("oBid", 0) or 0
        o_ask = m.get("oAsk", 0) or 0

        quote_ok = int(not (o_bid == 0 and o_ask == 0))

        rows.append({
            "asof_ts": asof_ts,
            "option_key": make_option_key(okey),

            "symbol": okey.get("tk"),
            "expiry": okey.get("dt"),
            "strike": okey.get("xx"),
            "cp": okey.get("cp"),

            "calc_source": m.get("calcSource"),
            "u_prc": m.get("uPrc"),
            "years": m.get("years"),
            "rate": m.get("rate"),

            "s_vol": s_vol,
            "atm_vol": m.get("atmVol"),
            "s_mark": m.get("sMark"),

            "o_bid": o_bid,
            "o_ask": o_ask,
            "o_bid_iv": m.get("oBidIv"),
            "o_ask_iv": m.get("oAskIv"),
            "quote_ok": quote_ok,

            "delta": m.get("de"),
            "gamma": m.get("ga"),
            "theta": m.get("th"),
            "vega": m.get("ve"),

            "src_ts": m.get("timestamp"),
        })

    df = pd.DataFrame(rows)
    if df.empty:
        return df

    df = (
        df.sort_values("src_ts")
          .drop_duplicates(subset=["option_key"], keep="last")
          .reset_index(drop=True)
    )
    return df

asof_ts = datetime.now(timezone.utc).isoformat(timespec="seconds").replace("+00:00", "Z")
snapshot_df = normalize_liq(raw, asof_ts)

print("snapshot rows:", len(snapshot_df))
print("quote_ok distribution:", snapshot_df["quote_ok"].value_counts().to_dict() if not snapshot_df.empty else {})
snapshot_df.head()

There are three practical decisions baked into this normalization step:

• First, we build a stable option_key from the option identifier so we have a consistent primary key for the latest table.

• Second, we keep only calcSource="Loop". LiveImpliedQuote can include both Tick and Loop records. Loop records tend to be more consistent for snapshot-style analysis because the underlying reference price is stable across the surface.

• Third, we avoid aggressive filtering. In this dataset, the top-of-book bid and ask fields can be zero even when the analytics fields are populated. So instead of dropping those rows, we store a quote_ok flag and keep the record. That keeps the pipeline usable while still making it obvious later which rows had live quotes.

This is the output:

At this point, one row represents one option contract snapshot. The fact that quote_ok is 0 across the board simply means bid and ask are not populated in this slice, even though surface IV, Greeks, and other analytics fields are present. That's still useful for building a monitoring database, because the core idea here is tracking the evolution of analytics over time, not reconstructing executable markets.

Writing to the Database

Now that we have a clean snapshot DataFrame, the job is to persist it in two places.

History table: Append everything. This is the audit log. Latest table: Upsert by option_key. This is the fast "current view."

This separation is what makes the database useful. History lets you reconstruct any past snapshot. Latest lets you answer "what does the surface look like right now" without scanning time series.

def safe_add_column(table: str, col: str, col_type: str, path: str = db_path):
    conn = get_conn(path)
    cur = conn.cursor()
    existing = [r[1] for r in cur.execute(f"PRAGMA table_info({table});").fetchall()]
    if col not in existing:
        cur.execute(f"ALTER TABLE {table} ADD COLUMN {col} {col_type};")
    conn.commit()
    conn.close()

safe_add_column("implied_quote_history", "quote_ok", "INTEGER")
safe_add_column("implied_quote_latest", "quote_ok", "INTEGER")

def write_snapshot_to_db(df: pd.DataFrame, path: str = db_path) -> tuple[int, int]:
    if df.empty:
        return 0, 0

    conn = get_conn(path)
    cur = conn.cursor()

    cols = [
        "asof_ts",
        "option_key","symbol","expiry","strike","cp",
        "calc_source","u_prc","years","rate",
        "s_vol","atm_vol","s_mark",
        "o_bid","o_ask","o_bid_iv","o_ask_iv",
        "delta","gamma","theta","vega",
        "quote_ok","src_ts"
    ]

    for c in cols:
        if c not in df.columns:
            df[c] = None

    insert_df = df[cols].copy()

    cur.executemany(
        """
        insert into implied_quote_history (
            asof_ts,
            option_key, symbol, expiry, strike, cp,
            calc_source, u_prc, years, rate,
            s_vol, atm_vol, s_mark,
            o_bid, o_ask, o_bid_iv, o_ask_iv,
            delta, gamma, theta, vega,
            quote_ok, src_ts
        ) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        """,
        insert_df.itertuples(index=False, name=None)
    )
    history_inserted = cur.rowcount

    cur.executemany(
        """
        insert into implied_quote_latest (
            option_key,
            last_asof_ts, symbol, expiry, strike, cp,
            calc_source, u_prc, years, rate,
            s_vol, atm_vol, s_mark,
            o_bid, o_ask, o_bid_iv, o_ask_iv,
            delta, gamma, theta, vega,
            quote_ok, src_ts
        ) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        on conflict(option_key) do update set
            last_asof_ts=excluded.last_asof_ts,
            symbol=excluded.symbol,
            expiry=excluded.expiry,
            strike=excluded.strike,
            cp=excluded.cp,
            calc_source=excluded.calc_source,
            u_prc=excluded.u_prc,
            years=excluded.years,
            rate=excluded.rate,
            s_vol=excluded.s_vol,
            atm_vol=excluded.atm_vol,
            s_mark=excluded.s_mark,
            o_bid=excluded.o_bid,
            o_ask=excluded.o_ask,
            o_bid_iv=excluded.o_bid_iv,
            o_ask_iv=excluded.o_ask_iv,
            delta=excluded.delta,
            gamma=excluded.gamma,
            theta=excluded.theta,
            vega=excluded.vega,
            quote_ok=excluded.quote_ok,
            src_ts=excluded.src_ts
        """,
        insert_df[[
            "option_key","asof_ts","symbol","expiry","strike","cp",
            "calc_source","u_prc","years","rate",
            "s_vol","atm_vol","s_mark",
            "o_bid","o_ask","o_bid_iv","o_ask_iv",
            "delta","gamma","theta","vega",
            "quote_ok","src_ts"
        ]].itertuples(index=False, name=None)
    )
    latest_upserted = cur.rowcount

    conn.commit()
    conn.close()
    return history_inserted, latest_upserted

hist_n, latest_n = write_snapshot_to_db(snapshot_df)
print("history inserted:", hist_n)
print("latest upserted:", latest_n)

We batch write using executemany so inserts are fast even with thousands of option rows. The history insert is straightforward. The latest write uses a SQLite upsert keyed on option_key, which means if the contract already exists in the latest table, its fields are overwritten with the newest snapshot.

You should see:

After the first write, both tables have the same number of rows. That's expected, because there is only one snapshot in history so far. Once we start polling multiple snapshots, the history table will grow every cycle, while the latest table will stay roughly flat and continue updating in place.

Running a Short Polling Capture

At this point, the pipeline works end-to-end for a single snapshot. The whole point of the database, though, is to turn live analytics into a time series. So we run a short capture window and store multiple snapshots back-to-back.

This isn't meant to be a production scheduler. It's just a simple loop that runs for a couple of minutes, polls every few seconds, timestamps the snapshot, and writes it to both tables.

def poll_and_write(symbol: str, duration_s: int = poll_duration_s, interval_s: int = poll_interval_s):
    start = time.time()
    polls = 0
    total_hist = 0

    while time.time() - start < duration_s:
        asof_ts = datetime.now(timezone.utc).isoformat(timespec="seconds").replace("+00:00", "Z")

        raw = fetch_live_implied_quote(symbol, limit=limit)
        df = normalize_liq(raw, asof_ts)

        hist_n, latest_n = write_snapshot_to_db(df)
        polls += 1
        total_hist += hist_n

        print(f"[{polls}] {asof_ts} snapshot_rows={len(df)} history+={hist_n} latest_upsert={latest_n}")
        time.sleep(interval_s)

    print(f"done. polls={polls}, total_history_added={total_hist}")

poll_and_write(symbol, duration_s=120, interval_s=10)

Each loop iteration represents one snapshot. We generate a UTC timestamp (asof_ts), pull the latest batch from LiveImpliedQuote, normalize it into rows, then write it into the database. The history table accumulates every snapshot. The latest table overwrites by option_key, so it always represents the most recent view.

One practical detail is worth calling out. The API call is capped by limit, so you're not guaranteed to receive an identical set of strikes and expiries every poll. That's why snapshot_rows can vary between iterations.

In production, you usually stabilize the slice by pinning specific expiries and a strike band or by interpolating IV to fixed moneyness points. For this tutorial, we're keeping ingestion simple and focusing on the database pattern and the monitoring queries it enables.

You should see per-poll telemetry like this:

[1] 2026-04-14T18:09:29Z snapshot_rows=1454 history+=1454 latest_upsert=1454
...
done. polls=9, total_history_added=12806

This confirms the database is building a time series. Over nine polls, you stored 12,806 option rows in history. The latest table is updated each time, but it doesn't grow in the same way as history because it overwrites per contract key.

From the next section, we'll stop writing and start querying.

Analysis: Smile Reconstruction From the Database

Once the data is in implied_quote_history, the workflow flips. We stop thinking in terms of "API responses" and start thinking in terms of "queries." This section does two things. First, it picks an expiry that has enough rows to be representative. Then it reconstructs the call-side volatility smile for that expiry across a few timestamps.

Pick an Expiry with Good Coverage

If you pick an expiry that only appears sporadically in the captured snapshots, the smile plot will be misleading. So we start by looking at which expiries have the most rows in the history table.

conn = get_conn()

expiry_counts = pd.read_sql_query(
    """
    select expiry, count(*) as n
    from implied_quote_history
    where symbol = ?
    group by expiry
    order by n desc
    limit 10
    """,
    conn,
    params=(symbol,)
)

conn.close()
expiry_counts

This query scans only the history table, filters to TSLA, and counts how many option rows exist per expiry across the capture window. We keep the top 10 and pick the first one as the expiry we'll reconstruct.

The expiry date 2026-11-20 has the highest count.

Here, the count doesn't mean this expiry is "best" in any trading sense. It just means it showed up most consistently in the captured data. That makes it a practical choice for a clean smile comparison.

Rebuild the Smile Across Snapshots

Now we query the stored history for one expiry, keep only calls, and plot surface IV (s_vol) against strike for multiple snapshot timestamps.

chosen_expiry = "2026-11-20" 

conn = get_conn()
smile = pd.read_sql_query(
    """
    select asof_ts, strike, cp, s_vol, u_prc
    from implied_quote_history
    where symbol = ? and expiry = ?
    """,
    conn,
    params=(symbol, chosen_expiry)
)
conn.close()

smile_calls = smile[smile["cp"] == "Call"].copy()

ts_list = sorted(smile_calls["asof_ts"].unique())
pick = [ts_list[0], ts_list[len(ts_list)//2], ts_list[-1]]

plt.figure(figsize=(9,5))
for ts in pick:
    g = smile_calls[smile_calls["asof_ts"] == ts].sort_values("strike")
    plt.plot(g["strike"], g["s_vol"], label=ts)

plt.title(f"{symbol} Vol Smile (Calls) | Expiry {chosen_expiry} | 3 snapshots")
plt.xlabel("Strike")
plt.ylabel("Implied Vol (s_vol)")
plt.grid(True)
plt.legend()
plt.show()

We pull all rows for the chosen expiry from history, then filter to calls so we don't mix put and call shapes. To keep the plot readable, we only plot three snapshots. First, middle, and last.

Over a short capture window, the smiles often overlap heavily. That doesn't mean the system isn't working. It usually means the surface didn't move much in those two minutes. The important part is that we can reconstruct and compare it purely from stored history.

Zoom-In Around Spot

The full-range plot is useful for shape, but it can hide small shifts near the region people actually care about. So we zoom to a band around the underlying price.

s0 = float(smile_calls["u_prc"].dropna().median())
low, high = s0 * 0.6, s0 * 1.4

for ts in pick:
    g = smile_calls[smile_calls["asof_ts"] == ts].sort_values("strike")
    g = g[(g["strike"] >= low) & (g["strike"] <= high)]
    plt.plot(g["strike"], g["s_vol"], label=ts)

plt.title(f"{symbol} Vol Smile (Calls) | Expiry {chosen_expiry} | zoomed")
plt.xlabel("Strike")
plt.ylabel("Implied Vol (s_vol)")
plt.grid(True)
plt.legend(fontsize=8)
plt.show()

We take a robust spot proxy from the stored u_prc values and then keep strikes within a range around it. The goal is not precision. It's to make the chart readable and show whether the near-ATM region is drifting.

Here, even small changes become visible. This is also why storing history matters. If you only looked at one snapshot in isolation, these shifts would be easy to miss or dismiss.

Analysis: ATM IV and Skew Over Time

A full smile plot is useful, but it's not always the fastest way to monitor a surface. In practice, teams usually track a few summary numbers per expiry so they can spot changes quickly, then drill down only when something looks off.

Here we reduce each stored snapshot into two metrics for a single expiry.

• ATM IV: Surface IV at the strike closest to spot.

• Skew proxy: Surface IV at 0.9 times spot minus surface IV at 1.1 times spot, using the closest available strikes.

chosen_expiry = "2026-11-20"

conn = get_conn()
df = pd.read_sql_query(
    """
    select asof_ts, strike, s_vol, u_prc
    from implied_quote_history
    where symbol = ? and expiry = ? and cp = 'Call'
    """,
    conn,
    params=(symbol, chosen_expiry)
)
conn.close()

df["strike"] = df["strike"].astype(float)
df["s_vol"] = df["s_vol"].astype(float)

def closest_iv(grp: pd.DataFrame, target_strike: float):
    g = grp.iloc[(grp["strike"] - target_strike).abs().argsort()[:1]]
    return float(g["s_vol"].iloc[0]), float(g["strike"].iloc[0])

rows = []
for ts, grp in df.groupby("asof_ts"):
    spot = float(grp["u_prc"].dropna().median())
    atm_target = spot
    down_target = spot * 0.9
    up_target = spot * 1.1

    atm_iv, atm_k = closest_iv(grp, atm_target)
    down_iv, down_k = closest_iv(grp, down_target)
    up_iv, up_k = closest_iv(grp, up_target)

    rows.append({
        "asof_ts": ts,
        "spot": spot,
        "atm_strike": atm_k,
        "atm_iv": atm_iv,
        "k90": down_k,
        "iv_90": down_iv,
        "k110": up_k,
        "iv_110": up_iv,
        "skew_90_110": down_iv - up_iv
    })

metrics = pd.DataFrame(rows).sort_values("asof_ts").reset_index(drop=True)
metrics

We query the history table for one expiry and keep only calls, then group by snapshot timestamp. For each snapshot, we use the median u_prc as a spot proxy and pick the closest available strike to spot. That gives ATM IV. We repeat the same approach for 0.9 times spot and 1.1 times spot and compute a skew proxy as the difference.

The table also stores the actual strikes used (atm_strike, k90, k110). Options strikes are discrete, so the nearest strike can change between snapshots. Keeping the chosen strikes visible makes the metric explainable when it moves.

The output is a table with one row per snapshot timestamp and the computed metrics.

Now that we have a clean time series table, we can visualize the two metrics. First, ATM IV. Then, the skew proxy.

plt.plot(metrics["asof_ts"], metrics["atm_iv"])
plt.title(f"{symbol} ATM IV over time | Expiry {chosen_expiry}")
plt.xticks(rotation=30, ha="right")
plt.ylabel("ATM IV (s_vol)")
plt.grid(True)
plt.show()

plt.plot(metrics["asof_ts"], metrics["skew_90_110"])
plt.title(f"{symbol} Skew proxy (IV@0.9S - IV@1.1S) | Expiry {chosen_expiry}")
plt.xticks(rotation=30, ha="right")
plt.ylabel("Skew proxy")
plt.grid(True)
plt.show()

Here is the first chart, ATM IV over time.

ATM IV tends to move slowly over short windows unless there is a sharp repricing event. In this run, it stays fairly stable, which is a realistic outcome for a short capture. The value here is that the database turns "fairly stable" into something you can quantify and compare later, rather than a vague impression.

Here is the second chart, Skew proxy over time.

The skew proxy is more sensitive because it's based on wing points. If it changes, it usually means the downside is being repriced differently from the upside for that expiry. One nuance is that the nearest available strike can change between snapshots, which can create step-like moves even when the surface isn't moving dramatically. That's why we keep k90 and k110 in the metrics table. It keeps the skew plot explainable.

Alert-Style Thresholds

Once you have a metrics table per snapshot, adding a monitoring layer is straightforward. The idea isn't to generate trades. It's to flag when the surface moves enough that someone should look closer.

Here we do two checks:

• ATM IV change alert: Flag if ATM IV changes more than a small threshold between snapshots.

• Skew change alert: Flag if the skew proxy changes more than a threshold between snapshots.

alerts = metrics.copy()

alerts["atm_iv_change"] = alerts["atm_iv"].diff()
alerts["skew_change"] = alerts["skew_90_110"].diff()

atm_thresh = 0.002    
skew_thresh = 0.003   

alerts["atm_alert"] = alerts["atm_iv_change"].abs() >= atm_thresh
alerts["skew_alert"] = alerts["skew_change"].abs() >= skew_thresh

alerts[[
    "asof_ts",
    "atm_iv", "atm_iv_change", "atm_alert",
    "skew_90_110", "skew_change", "skew_alert",
    "atm_strike", "k90", "k110"
]]

We take the per-snapshot metrics table and compute first differences. Then we compare those changes to thresholds and store boolean flags. The output table keeps both the metrics and the strikes used for the calculations, so any alert is explainable rather than a black box.

In this run, the ATM IV alerts are all false, while the skew alert triggers once.

The skew alert fires because the skew proxy jumps by more than the threshold between two snapshots. This is explainable. If you see the table, you can see the strikes used for the proxy changed around the same time (k90 shifts from 340 to 315). Because strikes are discrete, nearest-strike metrics can step even when the surface is not moving dramatically.

To make this easier to read, we also plot the two series and mark alert points.

plt.plot(alerts["asof_ts"], alerts["atm_iv"])
for i, r in alerts[alerts["atm_alert"]].iterrows():
    plt.scatter(r["asof_ts"], r["atm_iv"],  s=30, edgecolors="r", alpha=0.6, linewidth=2)
plt.title(f"{symbol} ATM IV with alerts | Expiry {chosen_expiry}")
plt.xticks(rotation=30, ha="right")
plt.grid(True)
plt.show()

plt.plot(alerts["asof_ts"], alerts["skew_90_110"])
for i, r in alerts[alerts["skew_alert"]].iterrows():
    plt.scatter(r["asof_ts"], r["skew_90_110"], s=30, edgecolors="r", alpha=0.6, linewidth=2)
plt.title(f"{symbol} Skew proxy with alerts | Expiry {chosen_expiry}")
plt.xticks(rotation=30, ha="right")
plt.grid(True)
plt.show()

Both plots use the same pattern. Plot the metric as a line, then overlay a marker on any timestamp where the corresponding alert flag is true. This makes it obvious when something crossed the threshold.

This chart represents skew proxy with alerts.

This chart shows one alert marker, which matches what we saw in the table.

The ATM IV plot isn't featured since there are no alert points.

Wrapping Up

In this walkthrough, we used SpiderRock MLink's LiveImpliedQuote feed for TSLA and turned it into a small internal database you can query. We stored every snapshot in an append-only history table, maintained a latest view keyed by a stable option identifier, then used that stored data to rebuild a smile, track ATM surface IV and a simple skew proxy, and add a basic alert rule on top.

This fits well in B2B workflows because it turns live analytics into something operational: a dataset you can audit, replay, and monitor. The same pattern works whether you're building an internal dashboard, running routine surface checks for a desk, or doing a quick post-event review without relying on screenshots and one-off notebook runs.

If you want to extend it, the most practical next steps are longer capture windows, tracking multiple symbols, and moving from SQLite to Postgres once the data volume grows. If metric stability becomes important, you can also standardize the slice you track per poll or interpolate IV to fixed moneyness points so skew measures don't step when nearest strikes change.

With that being said, you've reached the end of the article. Hope you learned something new and useful.

So I built a benchmark suite that pits vanilla PostgreSQL against a feature-optimized PostgreSQL instance — measuring caching, message queues, full-text search, and pub/sub under controlled conditions.

In this article, you'll learn how to use PostgreSQL's built-in features for caching, job queues, full-text search, and pub/sub. You'll see actual benchmark results (latency percentiles, throughput, and error rates) comparing naive PostgreSQL patterns against optimized ones, and understand where PostgreSQL's limits are so you can decide whether you really need that extra service in your stack.

Table of Contents

• Prerequisites

• The Setup

• Benchmark 1: Caching with UNLOGGED Tables

• Benchmark 2: Job Queues with SKIP LOCKED

• Benchmark 3: Full-Text Search with tsvector

• Benchmark 4: Pub/Sub with LISTEN/NOTIFY

• The Combined Workload: The Honest Test

• What I Learned

Prerequisites

To follow along or reproduce the benchmarks, you'll need:

• Docker and Docker Compose

• Node.js 20+ (for the Express TypeScript API layer)

• k6 for load testing

• Basic familiarity with SQL and PostgreSQL

The full benchmark project is open source on GitHub — you can clone it and run every test yourself.

The Setup

The benchmark uses two identical PostgreSQL 17 instances running in Docker containers, each with fixed resource constraints (2 CPUs, 2 GB RAM). Both share the same Express TypeScript API layer — the only difference is which PostgreSQL features are enabled.

┌─────────┐     ┌──────────────────┐     ┌─────────────────┐
│   k6    │────>│  Express API     │────>│  PG Baseline    │
│  (load  │     │  (TypeScript)    │     │  (vanilla PG17) │
│  test)  │────>│  Port 3001/3002  │────>│  PG Modded      │
└─────────┘     └──────────────────┘     │  (features on)  │
                                         └─────────────────┘

The baseline instance uses naïve approaches (regular tables, ILIKE search, polling). The modded instance uses PostgreSQL's built-in features (UNLOGGED tables, tsvector with GIN indexes, LISTEN/NOTIFY, partial indexes). Same hardware, same API code, same data. Only the database features differ.

Both instances share this tuned postgresql.conf:

# Memory allocation
shared_buffers = 512MB           # 25% of available RAM
effective_cache_size = 1536MB    # 75% of RAM — helps the query planner
work_mem = 16MB                  # per-sort/hash operation memory

# SSD-optimized planner settings
random_page_cost = 1.1           # default 4.0 assumes spinning disks
effective_io_concurrency = 200   # allow parallel I/O on SSDs

These settings matter. The defaults assume spinning disks from the early 2000s. Setting random_page_cost = 1.1 tells the query planner that random reads are nearly as fast as sequential reads on SSDs, which encourages index usage over sequential scans.

Benchmark 1: Caching with UNLOGGED Tables

The idea: Use an UNLOGGED table as an in-database cache. UNLOGGED tables skip PostgreSQL's Write-Ahead Log (WAL) — the mechanism that guarantees durability. Since cache data is ephemeral by nature, losing it on a crash is acceptable, and skipping WAL removes the biggest write bottleneck.

-- Modded: UNLOGGED table for cache entries
CREATE UNLOGGED TABLE cache_entries (
    key TEXT PRIMARY KEY,
    value JSONB NOT NULL,
    expires_at TIMESTAMPTZ
);

-- Baseline: same schema, but a regular (logged) table
CREATE TABLE cache_entries (
    key TEXT PRIMARY KEY,
    value JSONB NOT NULL,
    expires_at TIMESTAMPTZ
);

Results (200 Virtual Users)

A consistent 13% improvement across all percentiles. Not dramatic, but free — you change one keyword in your CREATE TABLE statement.

Under Stress (1,000 Virtual Users, No Sleep)

The relative improvement stays locked at 12-13% regardless of load level. The UNLOGGED advantage is a per-write optimization — it saves the same amount of I/O whether you are doing 100 or 10,000 writes per second. The modded instance served 37,000 more requests in the same time window.

The Verdict

UNLOGGED tables won't match Redis for sub-millisecond hot-path caching (real-time bidding, gaming leaderboards). But for web applications where the difference between 2ms and 5ms is invisible to users, they eliminate an entire infrastructure dependency for zero additional complexity.

You do give up Redis data structures (sorted sets, HyperLogLog, streams). If you need those, a dedicated cache is still the right call.

Benchmark 2: Job Queues with SKIP LOCKED

The idea: Use PostgreSQL as a job queue with SELECT ... FOR UPDATE SKIP LOCKED. Multiple workers poll the same table, and SKIP LOCKED ensures each worker gets a different row — no duplicates, no contention.

-- Queue table with a partial index on pending jobs only
CREATE TABLE job_queue (
    id SERIAL PRIMARY KEY,
    payload JSONB NOT NULL,
    status TEXT NOT NULL DEFAULT 'pending',
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Partial index: only indexes pending jobs
-- As jobs complete, they leave the index — it stays small forever
CREATE INDEX idx_pending_jobs ON job_queue (created_at)
    WHERE status = 'pending';

The dequeue pattern:

-- Atomic dequeue: select + update in one statement
UPDATE job_queue SET status = 'processing'
WHERE id = (
    SELECT id FROM job_queue
    WHERE status = 'pending'
    ORDER BY created_at
    LIMIT 1
    FOR UPDATE SKIP LOCKED  -- skip rows locked by other workers
) RETURNING *;

How SKIP LOCKED works: Worker A locks row 1. Worker B tries row 1, sees the lock, skips it, and takes row 2 instead. No blocking, no duplicates. If a worker crashes, the transaction rolls back and the row becomes available again.

Results (100 Producers + 50 Consumers)

They're virtually identical. The partial index doesn't show its value in a 60-second benchmark because the table doesn't accumulate enough completed rows for the index size difference to matter. In a production system with millions of completed jobs, the partial index keeps the index at kilobytes while a full index grows to gigabytes.

The Verdict

SKIP LOCKED is production-ready for job queues. Libraries like pg-boss (Node.js) and river (Go) build on this exact pattern.

You do give up exchange/routing patterns (fan-out, topic-based routing) and consumer groups with message replay. If you need those, a dedicated message broker is still the right tool. For simple "process this job once" workloads, PostgreSQL handles it.

Benchmark 3: Full-Text Search with tsvector

The idea: Use PostgreSQL's built-in full-text search instead of a separate search service. A tsvector column stores pre-processed search tokens, and a GIN (Generalized Inverted Index) enables fast lookups using the same inverted index concept that powers Elasticsearch.

-- Search-optimized article table
CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    body TEXT NOT NULL,
    search_vector tsvector  -- pre-computed search tokens
);

-- GIN index for full-text search
CREATE INDEX idx_search ON articles USING GIN (search_vector);

-- Auto-update search_vector on insert/update
CREATE OR REPLACE FUNCTION update_search_vector() RETURNS trigger AS $$
BEGIN
    NEW.search_vector := to_tsvector('english',
        COALESCE(NEW.title, '') || ' ' || COALESCE(NEW.body, ''));
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_search
    BEFORE INSERT OR UPDATE ON articles
    FOR EACH ROW EXECUTE FUNCTION update_search_vector();

The baseline uses ILIKE with a leading wildcard — the approach most developers reach for first:

-- Baseline: sequential scan on every query
SELECT * FROM articles
WHERE title ILIKE '%postgresql%' OR body ILIKE '%postgresql%';

-- Modded: GIN index lookup with relevance ranking
SELECT id, title,
    ts_rank(search_vector, plainto_tsquery('english', 'postgresql')) AS rank
FROM articles
WHERE search_vector @@ plainto_tsquery('english', 'postgresql')
ORDER BY rank DESC LIMIT 20;

Results (500 Virtual Users)

This is the standout result. The baseline's p95 of 101ms versus the modded's 10ms is a 10x improvement.

Why the baseline's p50 (1.96ms) is slightly better than the modded's (2.76ms): simple ILIKE queries on small result sets can be fast when the data fits in shared_buffers. But as load increases and the buffer cache is contested, sequential scans degrade dramatically. The GIN index stays stable.

Under Stress (500 Virtual Users, No Sleep)

ILIKE collapses to 1-second p95 latencies. Each query forces a sequential scan of all 10,000 articles, blocking shared buffers and starving concurrent queries. The tsvector approach serves 2.6x more requests in the same time window because the GIN index lookup is O(log n) regardless of concurrency.

The Verdict

This is the strongest argument in the entire benchmark. The fix requires zero extensions — to_tsvector(), plainto_tsquery(), and CREATE INDEX USING GIN are all built into core PostgreSQL. If you're doing WHERE column ILIKE '%term%' on any table with more than a few thousand rows, you're leaving massive performance on the table.

You do give up distributed search across shards, complex analyzers for CJK languages, and aggregation/faceted search pipelines. For a product search bar, blog search, or internal tool — PostgreSQL is enough.

Benchmark 4: Pub/Sub with LISTEN/NOTIFY

The idea: Use PostgreSQL's native LISTEN/NOTIFY for pub/sub messaging, triggered automatically on INSERT via a database trigger.

-- Trigger that fires pg_notify on every new message
CREATE OR REPLACE FUNCTION notify_message() RETURNS trigger AS $$
BEGIN
    PERFORM pg_notify(NEW.channel, NEW.payload::text);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_notify
    AFTER INSERT ON messages
    FOR EACH ROW EXECUTE FUNCTION notify_message();

Results (200 Virtual Users)

Here we have a 20% improvement at p95. The trigger-based approach does more work per INSERT (INSERT + NOTIFY), but the reduced round trips and better connection reuse patterns offset the overhead.

The Verdict

LISTEN/NOTIFY works for real-time features where you would otherwise reach for Redis pub/sub. The main limitation is payload size (8,000 bytes maximum) and the requirement for dedicated connections (incompatible with PgBouncer in transaction mode).

The Combined Workload: The Honest Test

Individual benchmarks are flattering. The real question: can one PostgreSQL instance handle caching, queues, search, and pub/sub simultaneously without degrading?

Results (All Four Workloads Running Together)

Under combined load, the baseline marginally outperforms the modded setup. The modded PostgreSQL does more work per operation — maintaining GIN indexes, firing triggers, running pg_cron in the background. When all these features are active simultaneously, the overhead is measurable: about 15% higher p95 latency.

But both setups stay comfortably under 10ms at p95. For most web applications, that's more than good enough.

What I Learned

After running all these benchmarks, here's what I would tell a team evaluating whether to "just use Postgres":

1. Do it for full-text search: Switching from ILIKE to tsvector with a GIN index is a 10x improvement that requires zero extensions. This is the single highest-ROI change in the entire PostgreSQL ecosystem, and most developers don't know it exists.

2. Do it for job queues: SKIP LOCKED is production-ready and eliminates RabbitMQ for simple "process this job" workloads. Use a library like pg-boss or river rather than rolling your own.

3. Consider it for caching: UNLOGGED tables give a steady 13% improvement over regular tables. If sub-millisecond latency is not a hard requirement (and for most web apps, it is not), you can drop Redis entirely.

4. Be honest about the overhead: Running all four roles simultaneously adds about 15% latency compared to running any single role. Whether that matters depends on your latency budget.

5. Know where to stop: PostgreSQL won't match Redis for sub-millisecond caching, Kafka for millions of messages per second, or Elasticsearch for distributed multi-node search with complex analyzers. The line is at extreme throughput or extreme specialization.

The honest conclusion is not "PostgreSQL does everything." It is: for most applications, a single well-configured PostgreSQL instance handles 80% of what you would otherwise need three to five additional services for. That is less infrastructure to deploy, monitor, and maintain — and fewer things to break at 3 AM.

Enterprise-scale applications processing millions of messages per second, serving sub-millisecond cache hits to millions of concurrent users, or running distributed search across terabytes of documents will still need specialized tools. Those tools exist for a reason, and at that scale the operational cost of running them is justified by the performance you get back.

But most of us aren't building at that scale — and may never need to. Starting with PostgreSQL for these roles means you ship faster with fewer moving parts. If and when you outgrow what PostgreSQL can handle, your benchmarks will tell you exactly which role needs to be extracted into a dedicated service. That is a much better position than starting with five services on day one because you assumed you would need them.

The benchmark project is open source if you want to reproduce these results or adapt the tests for your own workload.

You can find more of my writing at site.aaronhsyong.com.

The fix, more often than not, is an index.

A database index is a data structure that helps the database find rows faster without scanning the entire table. It works a lot like the index at the back of a textbook: instead of reading every page to find a topic, you look it up in the index, get the page number, and go straight there.

In this tutorial, you'll learn how indexes work under the hood, how to create and use them effectively in PostgreSQL, and how to avoid the common mistakes that make indexes useless or even harmful.

Table of Contents

• Prerequisites

• Why Do You Need Indexes?

• How Indexes Work Under the Hood

• How to Create Your First Index

• How to Use EXPLAIN ANALYZE to Measure Performance

• Types of Indexes in PostgreSQL

• How to Create a Composite Index

• How to Create a Partial Index

• How to Create an Expression Index

• How to Create a Unique Index

• How to Manage Indexes

• When Indexes Hurt Instead of Help

• Common Mistakes That Prevent Index Usage

• Best Practices for Indexing

• Conclusion

Prerequisites

To follow along with the examples, you'll need:

• Basic knowledge of SQL (SELECT, INSERT, UPDATE, DELETE, WHERE, JOIN)

• A running PostgreSQL instance (version 12 or later)

• A SQL client like psql, pgAdmin, or DBeaver

If you don't have PostgreSQL installed locally, you can use a free cloud-hosted instance from services like Neon or Supabase.

Why Do You Need Indexes?

When you run a query like SELECT * FROM users WHERE email = 'jane@example.com', the database needs to find the matching row. Without an index, PostgreSQL performs a sequential scan — it reads every single row in the table and checks whether the email column matches.

For a table with 100 rows, this is fine. For a table with 10 million rows, it's painfully slow.

An index solves this by creating a separate, sorted data structure that maps column values to their row locations. Instead of scanning 10 million rows, PostgreSQL can look up the value in the index and jump directly to the matching row. This can reduce query time from seconds to milliseconds.

But indexes aren't free. They come with trade-offs you need to understand before adding them everywhere. You'll learn about those trade-offs throughout this tutorial.

How Indexes Work Under the Hood

PostgreSQL's default index type is the B-tree (balanced tree). Understanding how a B-tree works will help you make smarter decisions about when and how to index.

A B-tree organizes data into a sorted, hierarchical structure with three levels:

1. Root node — the top of the tree. It holds a few values that divide the data into broad ranges.

2. Internal nodes — each one further narrows down the range.

3. Leaf nodes — the bottom level. These hold the actual indexed values along with pointers to the corresponding rows in the table.

When PostgreSQL uses a B-tree index to find a value, it starts at the root and follows the path that matches the target value, moving through internal nodes until it reaches the correct leaf node. This path is called a tree traversal, and it typically requires only 3–4 steps even for tables with millions of rows.

Think of it like a phone book. You don't start at page one and read every name. You open to roughly the right section (root), narrow it down to the right page (internal nodes), and scan the entries on that page (leaf node).

This sorted structure is also why B-tree indexes work well for range queries like WHERE price > 50 AND price < 100. The database finds the starting point in the tree and then scans forward through the leaf nodes, which are already in order.

How to Create Your First Index

Let's build a practical example. You'll create a table, load it with data, and see the difference an index makes.

Step 1 – Create the Table and Insert Sample Data

CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    city VARCHAR(50),
    created_at TIMESTAMP DEFAULT NOW()
);

Now insert a large number of rows so the performance difference is visible. This generates 500,000 rows of sample data:

INSERT INTO customers (first_name, last_name, email, city)
SELECT
    'User' || gs,
    'Last' || gs,
    'user' || gs || '@example.com',
    (ARRAY['Lagos', 'London', 'New York', 'Berlin', 'Tokyo'])[1 + (gs % 5)]
FROM generate_series(1, 500000) AS gs;

Step 2 – Query Without an Index

EXPLAIN ANALYZE
SELECT * FROM customers WHERE email = 'user250000@example.com';

You'll see output similar to this:

Seq Scan on customers  (cost=0.00..11374.00 rows=1 width=52) (actual time=45.123..91.456 rows=1 loops=1)
  Filter: ((email)::text = 'user250000@example.com'::text)
  Rows Removed by Filter: 499999
Planning Time: 0.085 ms
Execution Time: 91.502 ms

The key detail here is Seq Scan — PostgreSQL scanned all 500,000 rows to find a single match. It filtered out 499,999 rows. That's a lot of wasted work.

Step 3 – Create an Index

CREATE INDEX idx_customers_email ON customers (email);

This creates a B-tree index on the email column. The name idx_customers_email follows a common naming convention: idx_ prefix, then the table name, then the column name.

Step 4 – Query With the Index

Run the same query again:

EXPLAIN ANALYZE
SELECT * FROM customers WHERE email = 'user250000@example.com';

Now you'll see something like this:

Index Scan using idx_customers_email on customers  (cost=0.42..8.44 rows=1 width=52) (actual time=0.034..0.036 rows=1 loops=1)
  Index Cond: ((email)::text = 'user250000@example.com'::text)
Planning Time: 0.112 ms
Execution Time: 0.058 ms

The scan type changed from Seq Scan to Index Scan. The execution time dropped from ~91ms to ~0.06ms. That's roughly a 1,500x improvement — from one line of SQL.

How to Use EXPLAIN ANALYZE to Measure Performance

EXPLAIN ANALYZE is your most important tool for understanding how PostgreSQL executes a query. You already saw it in the previous section, but let's break down what the output means.

EXPLAIN ANALYZE SELECT * FROM customers WHERE city = 'Lagos';

The output will tell you several things:

• Scan type — whether PostgreSQL used a sequential scan, index scan, bitmap index scan, or another access method

• Cost — the estimated cost in arbitrary units. The first number is the startup cost, the second is the total cost

• Rows — how many rows PostgreSQL estimated it would find versus how many it actually found

• Actual time — the real time in milliseconds to execute the query

• Rows Removed by Filter — how many rows were scanned but didn't match the condition

If you see Seq Scan on a large table with a selective WHERE clause, that's usually a sign you need an index. If you see Index Scan or Index Only Scan, your index is working.

One thing to keep in mind: EXPLAIN without ANALYZE shows the plan without actually running the query. EXPLAIN ANALYZE runs the query and shows real timing data. Always use EXPLAIN ANALYZE when you're investigating performance, but be careful with it on destructive queries — EXPLAIN ANALYZE DELETE FROM ... will actually delete the rows. Wrap those in a transaction and roll back:

BEGIN;
EXPLAIN ANALYZE DELETE FROM customers WHERE city = 'Berlin';
ROLLBACK;

Types of Indexes in PostgreSQL

PostgreSQL supports several index types, each optimized for different query patterns.

B-tree (Default)

B-tree is the default index type and covers the vast majority of use cases. It supports equality checks (=), range queries (<, >, <=, >=, BETWEEN), sorting (ORDER BY), and IS NULL / IS NOT NULL checks.

-- These are equivalent – B-tree is the default
CREATE INDEX idx_name ON customers (last_name);
CREATE INDEX idx_name ON customers USING btree (last_name);

Use B-tree when you don't have a specific reason to use something else.

Hash

Hash indexes are optimized purely for equality comparisons (=). They don't support range queries or sorting. In practice, B-tree handles equality checks almost as fast, so hash indexes are rarely necessary.

CREATE INDEX idx_email_hash ON customers USING hash (email);

Consider a hash index only if you have a very large table with frequent equality-only lookups and want to save a small amount of index space.

GIN (Generalized Inverted Index)

GIN indexes are designed for values that contain multiple elements — like arrays, JSONB documents, or full-text search vectors. Instead of indexing a single value per row, GIN indexes every element within the value.

-- Add a JSONB column
ALTER TABLE customers ADD COLUMN preferences JSONB DEFAULT '{}';

-- Index the JSONB column
CREATE INDEX idx_preferences ON customers USING gin (preferences);

-- Now this query uses the GIN index
SELECT * FROM customers WHERE preferences @> '{"newsletter": true}';

Use GIN when you're querying inside JSONB data, searching arrays with @> or &&, or doing full-text search with tsvector.

GiST (Generalized Search Tree)

GiST indexes support geometric data, ranges, and full-text search. They're commonly used with PostGIS for geospatial queries.

-- Range type example
CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    duration TSRANGE
);

CREATE INDEX idx_event_duration ON events USING gist (duration);

-- Find overlapping events
SELECT * FROM events WHERE duration && '[2025-01-01, 2025-01-31]'::tsrange;

Use GiST when you're working with spatial data, range types, or need overlap/containment operators.

BRIN (Block Range Index)

BRIN indexes are extremely small and work well on large tables where the physical row order correlates with the indexed column's value. A common example is a timestamp column on an append-only table where new rows always have later timestamps.

CREATE INDEX idx_created_at_brin ON customers USING brin (created_at);

BRIN stores summary information (min/max values) for each block of rows rather than indexing every row individually. This makes the index much smaller than a B-tree, but it only works well when the data is naturally ordered.

Use BRIN for very large, append-only tables with naturally ordered data — like logs, events, or time-series data.

How to Create a Composite Index

A composite index (also called a multi-column index) covers more than one column. It's useful when your queries frequently filter or sort by multiple columns together.

CREATE INDEX idx_city_lastname ON customers (city, last_name);

The order of columns in a composite index matters. PostgreSQL can use this index for queries that filter on city alone, or on both city and last_name. But it can't efficiently use this index for queries that filter only on last_name.

Think of it like a phone book sorted by city first, then by last name within each city. You can easily look up everyone in Lagos. You can also look up everyone named "Adeyemi" in Lagos. But finding all people named "Adeyemi" across all cities requires scanning the whole book.

This principle is called the leftmost prefix rule: PostgreSQL can use a composite index for queries that include the leftmost column(s) of the index, but not for queries that skip them.

-- ✅ Uses the index (matches leftmost column)
SELECT * FROM customers WHERE city = 'Lagos';

-- ✅ Uses the index (matches both columns, left to right)
SELECT * FROM customers WHERE city = 'Lagos' AND last_name = 'Adeyemi';

-- ❌ Cannot use this index efficiently (skips the leftmost column)
SELECT * FROM customers WHERE last_name = 'Adeyemi';

When deciding column order, place the most selective column first — the one that narrows down the results the most.

How to Create a Partial Index

A partial index covers only a subset of rows in a table. You define the subset with a WHERE clause in the index definition.

This is useful when you only query a specific portion of the data. For example, if you have an orders table and you frequently query for pending orders but rarely look at completed ones:

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INT NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'pending',
    total NUMERIC(10, 2),
    created_at TIMESTAMP DEFAULT NOW()
);

-- Only index rows where status is 'pending'
CREATE INDEX idx_orders_pending ON orders (customer_id)
WHERE status = 'pending';

This index is smaller than a full index because it skips all rows that don't match the WHERE condition. Smaller indexes use less disk space, consume less memory, and are faster to maintain during writes.

For the index to be used, your query's WHERE clause must match the index's condition:

-- ✅ Uses the partial index
SELECT * FROM orders WHERE status = 'pending' AND customer_id = 42;

-- ❌ Cannot use the partial index (different status)
SELECT * FROM orders WHERE status = 'shipped' AND customer_id = 42;

How to Create an Expression Index

Sometimes you need to index the result of a function or expression rather than a raw column value. Expression indexes (also called functional indexes) handle this.

A common scenario is case-insensitive email lookups. If your queries use LOWER(email), a regular index on email won't help — PostgreSQL sees the function call as a different expression.

-- Regular index on email – won't help with LOWER() queries
CREATE INDEX idx_email ON customers (email);

-- This query does NOT use the index above
SELECT * FROM customers WHERE LOWER(email) = 'user100@example.com';

To fix this, create an index on the expression itself:

CREATE INDEX idx_email_lower ON customers (LOWER(email));

Now queries that use LOWER(email) in their WHERE clause will use this index:

-- ✅ Uses the expression index
SELECT * FROM customers WHERE LOWER(email) = 'user100@example.com';

The rule is straightforward: the expression in your query must match the expression in the index exactly. If the index is on LOWER(email), your query must also use LOWER(email).

How to Create a Unique Index

A unique index guarantees that no two rows have the same value (or combination of values) in the indexed columns. It serves a dual purpose: it enforces data integrity and provides fast lookups.

CREATE UNIQUE INDEX idx_customers_email_unique ON customers (email);

If you try to insert a duplicate value, PostgreSQL will reject the operation:

INSERT INTO customers (first_name, last_name, email, city)
VALUES ('Test', 'User', 'user1@example.com', 'Lagos');
-- ERROR: duplicate key value violates unique constraint "idx_customers_email_unique"

You might wonder how this differs from a UNIQUE constraint. Under the hood, PostgreSQL implements UNIQUE constraints by creating a unique index. The two are functionally identical.

The difference is intent — a UNIQUE constraint expresses a data integrity rule, while a unique index explicitly focuses on query performance with uniqueness as a bonus.

How to Manage Indexes

As your database grows, you'll need to inspect, monitor, and maintain your indexes.

How to List All Indexes on a Table

SELECT
    indexname,
    indexdef
FROM pg_indexes
WHERE tablename = 'customers';

This shows the name and full definition of every index on the table.

How to Check Index Size

SELECT
    pg_size_pretty(pg_relation_size('idx_customers_email')) AS index_size;

For a broader view of all indexes and their sizes:

SELECT
    indexrelname AS index_name,
    pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE relname = 'customers'
ORDER BY pg_relation_size(indexrelid) DESC;

How to Find Unused Indexes

Indexes that are never used waste disk space and slow down writes. You can find them by checking pg_stat_user_indexes:

SELECT
    indexrelname AS index_name,
    idx_scan AS times_used,
    pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE relname = 'customers'
AND idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

If an index has idx_scan = 0 after a reasonable period of normal usage, it's a candidate for removal. Just make sure to check across a full business cycle — some indexes are only used during monthly reports or seasonal operations.

How to Drop an Index

DROP INDEX IF EXISTS idx_customers_email;

If you're dropping an index on a production table and want to avoid locking writes, use CONCURRENTLY:

DROP INDEX CONCURRENTLY IF EXISTS idx_customers_email;

How to Rebuild an Index

Over time, indexes can become bloated as rows are inserted, updated, and deleted. You can rebuild an index to reclaim space:

REINDEX INDEX idx_customers_email;

Or rebuild all indexes on a table:

REINDEX TABLE customers;

On production systems, use REINDEX CONCURRENTLY (PostgreSQL 12+) to avoid locking the table:

REINDEX INDEX CONCURRENTLY idx_customers_email;

When Indexes Hurt Instead of Help

Indexes aren't free. Every index you add comes with costs:

1. Write overhead — every INSERT, UPDATE, or DELETE must also update every index on the table. If a table has 10 indexes and you insert a row, PostgreSQL performs 11 write operations (one for the table and one for each index). On write-heavy tables, excessive indexes can significantly slow down data modification.

2. Storage cost — indexes consume disk space. On large tables, indexes can take up as much space as the table itself, sometimes more. You can check this with pg_relation_size.

3. Memory consumption — PostgreSQL caches frequently used indexes in memory. More indexes means more memory pressure, which can push useful data out of the cache and slow down other queries.

4. Maintenance burden — indexes need periodic maintenance (vacuuming, reindexing) and add complexity to schema migrations.

The question to ask is not "should I add an index?" but rather "does the read performance gain justify the write performance cost for this table's workload?"

Common Mistakes That Prevent Index Usage

You can have the perfect index and PostgreSQL might still ignore it. Here are the most common reasons.

Wrapping the Indexed Column in a Function

-- Index on email
CREATE INDEX idx_email ON customers (email);

-- ❌ PostgreSQL cannot use the index because of LOWER()
SELECT * FROM customers WHERE LOWER(email) = 'user1@example.com';

-- ✅ Fix: create an expression index on LOWER(email)
CREATE INDEX idx_email_lower ON customers (LOWER(email));

Any function applied to the indexed column in a WHERE clause prevents the standard index from being used. You need an expression index that matches the function.

Implicit Type Casting

-- id is an INTEGER column with an index
-- ❌ Passing a string forces a type cast, which may prevent index usage
SELECT * FROM customers WHERE id = '42';

-- ✅ Use the correct type
SELECT * FROM customers WHERE id = 42;

When the query's value type doesn't match the column type, PostgreSQL may cast the column to match, which prevents index usage.

Using OR Conditions Across Different Columns

-- ❌ OR across different columns can prevent index usage
SELECT * FROM customers WHERE email = 'user1@example.com' OR city = 'Lagos';

-- ✅ Rewrite as UNION for better index utilization
SELECT * FROM customers WHERE email = 'user1@example.com'
UNION
SELECT * FROM customers WHERE city = 'Lagos';

Leading Wildcards in LIKE Queries

-- ❌ Leading wildcard cannot use a B-tree index
SELECT * FROM customers WHERE email LIKE '%@example.com';

-- ✅ Trailing wildcard CAN use a B-tree index
SELECT * FROM customers WHERE email LIKE 'user1%';

A B-tree index is sorted from left to right. A leading wildcard (%something) means the database can't use the sorted structure and falls back to a sequential scan. If you need to search by suffix or substring, consider a GIN index with the pg_trgm extension.

Low Selectivity

If a column has very few distinct values relative to the number of rows (low selectivity), PostgreSQL may decide a sequential scan is faster than using the index.

For example, if a status column has only three possible values ('pending', 'shipped', 'delivered') and each value covers roughly a third of the table, an index on status alone provides little benefit. PostgreSQL would still need to read a large portion of the table, and the extra index lookup adds overhead.

A partial index is often the better solution in these cases.

Best Practices for Indexing

Here's a summary of the key principles to follow:

1. Index columns that appear in WHERE, JOIN, and ORDER BY clauses. These are the columns the database needs to search, match, or sort by. Start with the queries that run most frequently or take the longest.

2. Measure before and after with EXPLAIN ANALYZE. Never add an index based on guesswork. Run your query with EXPLAIN ANALYZE, add the index, and run it again. If the execution time doesn't improve meaningfully, the index isn't helping.

3. Don't index every column. Each index slows down writes and consumes storage. Be deliberate about which columns you index based on actual query patterns.

4. Use composite indexes for multi-column filters. If your queries commonly filter on city and last_name together, a composite index on (city, last_name) is more efficient than two separate single-column indexes.

5. Put the most selective column first in composite indexes. The column that narrows the results the most should come first.

6. Use partial indexes when you only query a subset of data. If 90% of your queries target rows where status = 'active', a partial index on that subset is smaller and faster than a full index.

7. Monitor index usage regularly. Query pg_stat_user_indexes to find unused indexes and remove them.

8. Rebuild bloated indexes periodically. On tables with heavy update/delete activity, indexes can become bloated. Use REINDEX CONCURRENTLY on production systems.

Conclusion

In this tutorial, you learned what database indexes are and why they matter for query performance. You explored how B-tree indexes work under the hood, created several types of indexes (single-column, composite, partial, expression, and unique), and used EXPLAIN ANALYZE to measure the impact.

You also learned about the trade-offs indexes introduce — write overhead, storage cost, and memory pressure — and the common mistakes that silently prevent PostgreSQL from using your indexes.

The core principle is simple: index deliberately based on your actual query patterns, measure the results, and remove anything that isn't pulling its weight.

If you found this tutorial helpful, you can find more of my writing on freeCodeCamp and connect with me on LinkedIn and X.

A database trigger is a function that the database executes automatically when a specific event occurs on a table. You don't call it manually. Instead, you define the conditions, and the database handles the rest.

In this tutorial, you'll learn what triggers are, how they work, when to use them, and when to avoid them. You'll work through practical examples using PostgreSQL, but the core concepts apply to most relational databases.

Table of Contents

• Prerequisites

• How Triggers Work

• How to Create Your First Trigger

• BEFORE vs AFTER Triggers

• How to Build an Audit Log with an AFTER Trigger

• How to Use a BEFORE Trigger for Validation

• Row-Level vs Statement-Level Triggers

• The NEW and OLD Variables Reference

• How to Manage Triggers

• When to Use Triggers

• When to Avoid Triggers

• Conclusion

Prerequisites

To follow along with the examples, you'll need:

• Basic knowledge of SQL (SELECT, INSERT, UPDATE, DELETE)

• A running PostgreSQL instance (version 12 or later)

• A SQL client like psql, pgAdmin, or DBeaver

If you don't have PostgreSQL installed, you can use a free cloud-hosted instance from services like Neon or Supabase to follow along.

How Triggers Work

At a high level, a trigger has three parts:

1. The event: what action activates the trigger (INSERT, UPDATE, DELETE, or TRUNCATE)

2. The timing: when the trigger fires relative to the event (BEFORE or AFTER)

3. The function: what logic runs when the trigger fires

Here's the general flow: a user or application performs an operation on a table, the database checks if any triggers are associated with that operation, and if a match is found, the database executes the trigger function automatically.

You can think of triggers as event listeners for your database. Just like a JavaScript addEventListener watches for a click or keypress, a database trigger watches for row-level changes on a table.

How to Create Your First Trigger

In PostgreSQL, creating a trigger is a two-step process. You first create a trigger function, then you attach that function to a table with a CREATE TRIGGER statement.

Let's build a concrete example. Say you have a products table and you want to automatically set the updated_at timestamp every time a row is modified.

Step 1 – Create the Table

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price NUMERIC(10, 2) NOT NULL,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

Step 2 – Create the Trigger Function

A trigger function in PostgreSQL is a special function that returns the TRIGGER type. Inside the function body, you have access to two important variables: NEW (the row after the operation) and OLD (the row before the operation).

CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

This function sets the updated_at column to the current timestamp every time it runs. It then returns NEW, which tells PostgreSQL to proceed with the modified row.

Step 3 – Attach the Trigger to the Table

CREATE TRIGGER trigger_set_updated_at
BEFORE UPDATE ON products
FOR EACH ROW
EXECUTE FUNCTION set_updated_at();

Let's break down each part of this statement:

• BEFORE UPDATE – the trigger fires before the update is applied to the table

• ON products – the trigger is associated with the products table

• FOR EACH ROW – the function runs once for every row affected by the update

• EXECUTE FUNCTION set_updated_at() – the function to call

Step 4 – Test It

INSERT INTO products (name, price) VALUES ('Wireless Keyboard', 49.99);

-- Wait a moment, then update the row
UPDATE products SET price = 44.99 WHERE name = 'Wireless Keyboard';

SELECT name, price, created_at, updated_at FROM products;

You'll see that updated_at has been automatically updated to the time of the UPDATE operation, even though you didn't explicitly set it in your query. That's the trigger doing its job.

BEFORE vs AFTER Triggers

The timing of a trigger determines when the function executes relative to the actual data change.

BEFORE triggers run before the row is inserted, updated, or deleted. They are useful when you want to modify or validate the incoming data. Since the change hasn't been applied yet, you can alter the NEW row or even cancel the operation entirely by returning NULL.

AFTER triggers run after the row change has been committed to the table. They are useful for side effects like logging, sending notifications, or updating related tables. At this point, the change is already done, so you can't modify the row – but you can read both OLD and NEW to see what changed.

Here's a rule of thumb: use BEFORE triggers when you need to change or reject data, and use AFTER triggers when you need to react to a completed change.

How to Build an Audit Log with an AFTER Trigger

One of the most common uses for triggers is audit logging – keeping a record of every change made to an important table. Let's build one.

Step 1 – Create an Audit Table

CREATE TABLE product_audit (
    audit_id SERIAL PRIMARY KEY,
    product_id INT NOT NULL,
    action VARCHAR(10) NOT NULL,
    old_price NUMERIC(10, 2),
    new_price NUMERIC(10, 2),
    changed_by TEXT DEFAULT current_user,
    changed_at TIMESTAMP DEFAULT NOW()
);

Step 2 – Create the Audit Trigger Function

CREATE OR REPLACE FUNCTION log_product_changes()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'UPDATE' THEN
        INSERT INTO product_audit (product_id, action, old_price, new_price)
        VALUES (OLD.id, 'UPDATE', OLD.price, NEW.price);
    ELSIF TG_OP = 'DELETE' THEN
        INSERT INTO product_audit (product_id, action, old_price)
        VALUES (OLD.id, 'DELETE', OLD.price);
    ELSIF TG_OP = 'INSERT' THEN
        INSERT INTO product_audit (product_id, action, new_price)
        VALUES (NEW.id, 'INSERT', NEW.price);
    END IF;

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

There are a few important things happening here. The TG_OP variable is a special string that PostgreSQL provides inside trigger functions. It tells you which operation activated the trigger: 'INSERT', 'UPDATE', or 'DELETE'. This lets you handle different operations with a single function.

The RETURN COALESCE(NEW, OLD) at the end ensures the function returns the correct row. For INSERT and UPDATE operations, NEW exists and is returned. For DELETE operations, NEW is null, so OLD is returned instead.

Step 3 – Attach the Trigger

CREATE TRIGGER trigger_product_audit
AFTER INSERT OR UPDATE OR DELETE ON products
FOR EACH ROW
EXECUTE FUNCTION log_product_changes();

Notice the AFTER INSERT OR UPDATE OR DELETE syntax. You can bind a single trigger to multiple events, which keeps your setup clean.

Step 4 – Test It

-- Insert a new product
INSERT INTO products (name, price) VALUES ('USB-C Hub', 29.99);

-- Update the price
UPDATE products SET price = 24.99 WHERE name = 'USB-C Hub';

-- Delete the product
DELETE FROM products WHERE name = 'USB-C Hub';

-- Check the audit log
SELECT * FROM product_audit ORDER BY changed_at;

You'll see three rows in product_audit (one for each operation) with the old and new prices recorded automatically. No application code needed.

How to Use a BEFORE Trigger for Validation

Triggers can also enforce business rules at the database level. Let's say you want to prevent any product from having a negative price.

CREATE OR REPLACE FUNCTION prevent_negative_price()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.price < 0 THEN
        RAISE EXCEPTION 'Product price cannot be negative. Got: %', NEW.price;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_check_price
BEFORE INSERT OR UPDATE ON products
FOR EACH ROW
EXECUTE FUNCTION prevent_negative_price();

Now test it:

INSERT INTO products (name, price) VALUES ('Faulty Item', -10.00);
-- ERROR: Product price cannot be negative. Got: -10.00

The insert is rejected entirely. The row never makes it into the table. This is powerful because the rule is enforced at the database level regardless of which application or script sends the query.

Row-Level vs Statement-Level Triggers

All the triggers you've seen so far use FOR EACH ROW, which means the function runs once per affected row. If you update 100 rows in a single query, the trigger function runs 100 times.

PostgreSQL also supports FOR EACH STATEMENT triggers, which run once per SQL statement regardless of how many rows are affected.

CREATE OR REPLACE FUNCTION log_bulk_update()
RETURNS TRIGGER AS $$
BEGIN
    RAISE NOTICE 'A bulk operation was performed on the products table';
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_bulk_update_notice
AFTER UPDATE ON products
FOR EACH STATEMENT
EXECUTE FUNCTION log_bulk_update();

Statement-level triggers are less common, but they're useful for operations like refreshing a materialized view or sending a single notification after a batch update instead of one notification per row.

Important: in statement-level triggers, the NEW and OLD variables are not available because the trigger isn't tied to any specific row.

The NEW and OLD Variables Reference

Here's a quick reference for when NEW and OLD are available in row-level triggers:

Understanding when each variable is available will save you from runtime errors in your trigger functions.

How to Manage Triggers

As you add more triggers to your database, you'll need to know how to inspect, disable, and remove them.

How to List All Triggers on a Table

SELECT trigger_name, event_manipulation, action_timing
FROM information_schema.triggers
WHERE event_object_table = 'products';

How to Disable a Trigger Temporarily

-- Disable a specific trigger
ALTER TABLE products DISABLE TRIGGER trigger_product_audit;

-- Disable all triggers on a table
ALTER TABLE products DISABLE TRIGGER ALL;

This is useful during bulk data migrations where you want to skip trigger execution for performance reasons.

How to Re-Enable a Trigger

ALTER TABLE products ENABLE TRIGGER trigger_product_audit;

How to Drop a Trigger

DROP TRIGGER IF EXISTS trigger_product_audit ON products;

Note that dropping a trigger does not drop the associated function. You'll need to drop the function separately if you no longer need it:

DROP FUNCTION IF EXISTS log_product_changes();

When to Use Triggers

Triggers work well for specific use cases. Here are the scenarios where they're a strong choice:

• Audit logging: automatically recording who changed what and when, as you saw earlier in this tutorial.

• Derived data maintenance: keeping computed columns, counters, or summary tables in sync with the source data.

• Data validation: enforcing business rules that go beyond what CHECK constraints can express, like cross-table validations.

• Automatic timestamping: setting created_at and updated_at fields without relying on the application layer.

When to Avoid Triggers

Triggers are powerful, but they come with trade-offs. Here are cases where you should think twice before using them:

• Complex business logic: if the logic involves calling external APIs, sending emails, or orchestrating multi-step workflows, it belongs in your application layer. Triggers should stay lightweight.

• Performance-sensitive bulk operations: row-level triggers on tables that frequently receive bulk inserts or updates can create significant overhead. If you're inserting millions of rows, those triggers fire millions of times.

• Cascading triggers: when one trigger's action fires another trigger, which fires another, debugging becomes extremely difficult. If you find yourself building a chain of triggers, reconsider the design.

• Logic that developers need to discover easily: triggers are sometimes called "hidden logic" because they execute automatically without appearing in application code. If your team frequently asks "why did this column change?" and the answer is always "there's a trigger," that's a sign the logic might be more discoverable if placed in your application layer or a stored procedure that's called explicitly.

A good rule of thumb: if the logic is tightly coupled to the data and should always execute regardless of which client or service touches the table, a trigger is appropriate. If the logic depends on application context (like the current user's session, feature flags, or external state), it belongs in the application.

Conclusion

In this tutorial, you learned what database triggers are and how they work in PostgreSQL. You built three practical triggers: an automatic timestamp updater, a full audit logging system, and a data validation guard. You also learned the difference between BEFORE and AFTER triggers, row-level and statement-level triggers, and when NEW and OLD variables are available.

Triggers are a powerful tool for keeping your data consistent and your business rules enforced at the database level. Use them for focused, data-centric operations, and keep the logic simple.

If you found this tutorial helpful, you can connect with me on LinkedIn and X.

In this article, we'll talk about what it takes to design a highly-functional database using some key best practices.

This article is aimed at developers and those looking to start a career in managing Databases. We'll discuss what a database actually is, the components of a Database System, what we mean by database design, the stages of database design, and what's involved in Database System Design.

Table of Contents

1. Prerequisites and Setup

   • 1. Foundational Knowledge

   • 2. Software and Installation

   • 3. Verifying Your Setup

2. What is a Database?

3. Components of a Database System

4. Types of Database Systems

5. Database System vs. DBMS

6. Characteristics of a Good Database

7. Stages of Database Design

8. The Role of Normalisation

9. Practical: Designing a Library System

   • Step 1: Requirements & ER Diagram

   • Step 2: Normalization in Action

   • Step 3: Implementation (SQL)

10. Conclusion

Prerequisites and Setup

To get the most out of this guide, you should have the following foundational skills and tools ready. This will help ensure that you aren't just reading theory, but that you're actually building a functional system.

1. Foundational Knowledge

• Data Types: You should be able to distinguish between basic data formats. In database design, choosing the wrong type can lead to storage waste or application errors.

  • Strings/Varchars: Textual data (for example, "John Doe", "123 Main St").

  • Integers: Whole numbers used for math or unique IDs (for example, 10, 500).

  • Floats/Decimals: Numbers with decimal points, usually for currency (for example, 19.99).

  • Booleans: Simple True/False toggles (for example, is_available).

• Logical Thinking: You should be comfortable identifying "entities." If you're building an app for a school, you'll need to recognize that "Students," "Teachers," and "Classrooms" are separate objects that must be linked via relationships.

• Terminal/CLI Basics: While we'll use visual tools, you should know how to open your Command Prompt (Windows) or Terminal (Mac/Linux) and understand that commands are often case-sensitive.

2. Software and Installation

We'll use PostgreSQL (the database engine) and pgAdmin 4 (the visual management tool).

1. Download: Visit the official PostgreSQL Downloads page and select the installer for your operating system.

2. Installation Wizard: Run the installer. When asked which components to include, make sure that PostgreSQL Server, pgAdmin 4, and Command Line Tools are all checked.

3. The "Postgres" User: During setup, you will be prompted to create a password for the default "postgres" superuser. Note: Write this password down. You can't easily reset it, and you'll need it to access your data.

4. Port Selection: The default port is 5432. Keep this as the default unless you're an advanced user with a specific reason to change it.

3. Verifying Your Setup

Before moving to the practical section, let's verify that everything is installed correctly:

1. Open pgAdmin 4 from your applications menu.

2. In the left-hand sidebar, click on Servers.

3. Enter the master password you created during installation.

4. If you see "PostgreSQL [Version Number]" appear with a green icon, your database environment is successfully configured.

What is a Database?

A Database is a collection of structured data usually stored electronically in a computer. Databases are controlled and managed using a Database Management System (DBMS). Database Management Software is an application that constructs and maintain (and sometimes expands) Databases. Examples of DBMS are IBM's DB2, Oracle Corporation's Oracle, Microsoft Access, and Microsoft's SQL Server.

We use databases everyday, whether knowingly or unknowingly. And as a developer, you'll likely need to at least understand Database basics so you can effectively work with them.

It's also important for you to how to know how to design a scalable database, as well as be familiar with the environment in which the database will be housed (called, not surprisingly, a Database Environment). The hardware and operating system that house the database makes up this Database Environment.

Components of a Database System

A Database System is a computerized record-keeping system designed to store, manage, and retrieve data efficiently. It acts as a centralized repository that allows multiple users to access and manipulate data simultaneously while ensuring the integrity, security, and persistence of that information over time.

A Database System consists of four basic components:

1. Hardware

This includes the secondary storage where the database resides alongside other necessary components. Examples are Hard disks, Processors, RAM, and so on. Since a database can span from a single workstation to a global mainframe, hardware selection is a priority. Proper investment in processing power and storage is essential to handling the projected user load and data volume.

2. Software

In this case, Database Management Software (DBMS) is in charge of the maintenance and management of databases. It's robust software that acts as an intermediary, restricting users from the complex hardware-level details of data storage. The Software layer (DBMS) handles data storage, retrieval, and processing. Examples of DBMS are Microsoft's SQL Server, IBM's DB2, and Oracle.

3. Data

Data serves as the bridge connecting the machine components (hardware and software) to the human users. In a database system, data is organized into two main types:

• User Data: The actual structured information stored in tables, made up of columns (attributes) and rows (records).

• Metadata: Often defined as "data about data," metadata is stored in system tables and describes the actual structure of the database, such as the number of tables, field names, and defined primary keys

4. Users

These are the people who interact with the database to carry out their business responsibilities. Users generally fall into three distinct categories:

• Database Administrators (DBAs): Technical experts who hold central responsibility for the database. They monitor performance, define security and integrity checks, and establish backup and recovery strategies.

• Database Designers/Programmers: The engineers who actually write the code and use the DBMS to create the database's logical structure.

• End Users: The everyday people who access the database using query languages or simple menu-driven application interfaces

Types of Database Systems

It's important to know that not all databases store data in the same way. The choice of database depends on the specific needs of the application. The primary types include:

Hierarchical and Network Databases

These are older, legacy models. Hierarchical databases structure data in a tree-like, parent-child format where a child can only have one parent. Network databases improved on this by allowing a graph-like structure where records can have multiple parent and child relationships, making it easier to model complex associations.

Relational Databases (RDBMS)

The most widely used type today. They organize data into structured tables consisting of rows and columns. These tables are linked using primary and foreign keys, and they use Structured Query Language (SQL) for operations. They are ideal for applications requiring strong consistency, like banking systems.

Object-Oriented Databases (OODBMS)

These combine database capabilities with object-oriented programming principles (like Java or C++). Data is stored as "objects" that contain both the data and the methods (functions) that operate on it, making them great for complex data like multimedia or engineering designs.

NoSQL Databases

Designed to handle large volumes of unstructured or semi-structured data. Unlike relational databases, they don't rely on rigid table structures and are highly scalable. Types of NoSQL include document stores (for example, MongoDB), key-value stores (for example, Redis), column-family stores, and graph databases.

Cloud and Distributed Databases

Cloud databases are hosted on cloud platforms (like AWS or Microsoft Azure) and offer elasticity, scalability, and cost-efficiency (pay-as-you-go).

Distributed databases store data across multiple physical locations but function as a single unified system to the user, providing high availability and fault tolerance.

Database System vs. Database Management System (DBMS)

People often use "Database" and "DBMS" interchangeably, but there is a distinct difference:

• Database Management System (DBMS): This is purely the software that helps users interact with the database. It handles data storage, retrieval, security, and concurrency control. Examples include MySQL, PostgreSQL, and Oracle DB.

• Database System: This is the broader concept that encompasses the entire setup. It includes the actual database (where data is stored), the DBMS software, the physical hardware, the network, and the users interacting with it.

Characteristics of a Good Database

To make sure that your database design is successful, it should exhibit several core characteristics:

• Data integrity and consistency: Ensuring data is accurate, reliable, and uniform across the entire system.

• Data security: Protecting sensitive information from unauthorized access and potential breaches.

• Scalability and performance: The ability to handle increasing amounts of data and users efficiently while providing fast query processing.

• Redundancy management (normalization): Avoiding unnecessary duplication of data to save storage space and prevent errors during updates.

• Concurrency control: Allowing multiple users to access and modify data simultaneously without causing conflicts or data corruption.

• Backup and recovery: Supporting robust mechanisms to recover data in the event of hardware or system failures.

Stages of Database Design

Database design is a structured process consisting of several stages to ensure that data is efficiently stored, accessed, and managed. There are four key stages in this process:

Requirements Analysis

This is the foundational stage where designers gather and analyse the specific needs of users and the business. It involves identifying the overall purpose of the database, understanding data requirements, defining key entities and attributes, and establishing both functional and non-functional requirements.

Conceptual Design

In this phase, a high-level visual blueprint of the database is created, which is independent of any specific software implementation. This makes it easy for non-technical stakeholders to understand.

Designers typically use Entity-Relationship (ER) models or UML diagrams to identify entities, map out relationships, and define constraints like primary keys.

Logical Design

This stage involves translating the conceptual blueprint into a logical model that aligns with a specific type of Database Management System (DBMS), such as a relational or NoSQL system.

Key steps include converting the ER diagram into relational schemas (tables and columns), defining foreign and primary keys, and normalising the database to remove anomalies and reduce data redundancy.

Physical Design

The final stage translates the logical model into an actual physical structure optimized for high performance and efficient storage. Activities here include selecting the specific DBMS, establishing indexing strategies to speed up data retrieval, defining access paths, and configuring essential security policies and backup mechanisms.

The Role of Normalisation in Database Design

When building a relational database, one of the most critical steps in the logical design phase is a process called Normalisation.

Normalisation is a systematic approach to organising data to minimise redundancy (duplicate data) and improve overall data integrity. Essentially, it involves taking large, clunky tables and decomposing them into smaller, more focused tables, then linking them together using defined relationships.

Why is Normalisation Important?

A poorly designed database often suffers from errors that happen when you try to insert, update, or delete data. For example, if a teacher's phone number is stored in multiple places, updating it in one row but forgetting another creates an update anomaly. Normalisation solves this by ensuring each piece of information is stored in only one place.

The main objectives of normalisation are:

• Eliminating redundancy: By reducing duplicate data, you save valuable storage space and keep your data consistent.

• Avoiding anomalies: It prevents data corruption that arises during insertion, updating, or deletion.

• Ensuring data integrity: It maintains the accuracy and reliability of the data across the entire database.

• Enhancing query performance: Organising the data efficiently helps optimise how data is retrieved and updated.

Stages of Normalisation

Normalisation happens in sequential stages known as Normal Forms (NFs), where each stage builds upon the rules of the previous one to further refine the database structure.

For beginners, the first three forms are the most important to understand:

• First Normal Form (1NF): This stage ensures "atomicity". This means that every column in a table should hold a single, indivisible value, and duplicate columns are eliminated. For instance, you would not store two different phone numbers in a single "Phone" cell; you would separate them.

• Second Normal Form (2NF): To achieve 2NF, the table must first be in 1NF. Then, it ensures that all non-key attributes (the regular data columns) are fully dependent on the entire primary key. This often involves creating separate tables for distinct entities, like putting "Courses" into their own table rather than mixing them with "Student" details.

• Third Normal Form (3NF): A table in 3NF is already in 2NF and has removed all "transitive dependencies". This means that a non-key column shouldn't depend on another non-key column. For example, if a table has an "Instructor Name" and "Instructor Phone," those details should live in a dedicated "Instructor" table, not inside a "Course" table.

• Boyce-Codd Normal Form (BCNF): This is a stricter version of 3NF used to resolve any remaining, complex anomalies.

Finding the Right Balance

While normalisation is crucial for maintaining data consistency, it's important to strike a balance. Minimising redundancy is great, but excessive normalisation creates dozens of tiny tables. When you need to retrieve a complete record, the database system has to piece all those tables back together (using complex queries called "joins"), which can slow down performance.

So the ultimate goal of a good database designer is to find the sweet spot between a highly normalised structure and efficient query performance.

Practical: Designing a Library System

To move from theory to practice, let’s build a database for a small local library. We'll go through the design stages to ensure the data is structured efficiently.

Step 1: Requirements & ER Diagram

First, we'll identify what our library needs to track. We have three main entities:

• Authors: The writers of the books.

• Books: The actual items available for loan.

• Members: The people who borrow the books.

The Relationships:

• One Author can write many Books (One-to-Many).

• One Member can borrow many Books (One-to-Many).

Here's the ER diagram I've created for this example:

Step 2: Normalization in Action

To ensure our database is "well-designed" and free of redundancy, we'll apply the normalization rules discussed earlier. Instead of one giant spreadsheet, we split the data into three distinct tables:

1. Authors Table: * author_id (Primary Key)

   • author_name

2. Books Table: * book_id (Primary Key)

   • title

   • isbn

   • author_id (Foreign Key linking to the Authors table)

3. Members Table: * member_id (Primary Key)

   • first_name

   • last_name

   • email (Unique constraint)

Step 3: Implementation (SQL)

Now, let’s use the PostgreSQL Query Tool in pgAdmin 4 to actually create these tables and insert some dummy data.

-- 1. Create the Authors table
CREATE TABLE Authors (
    author_id SERIAL PRIMARY KEY,
    author_name VARCHAR(100) NOT NULL
);

-- 2. Create the Books table with a relationship to Authors
CREATE TABLE Books (
    book_id SERIAL PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    isbn VARCHAR(20) UNIQUE,
    author_id INT REFERENCES Authors(author_id)
);

-- 3. Create the Members table
CREATE TABLE Members (
    member_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100) UNIQUE NOT NULL
);

-- 4. Insert dummy data to test the design
INSERT INTO Authors (author_name) 
VALUES ('J.R.R. Tolkien'), ('George R.R. Martin');

INSERT INTO Books (title, isbn, author_id) 
VALUES ('The Hobbit', '978-0261102217', 1), 
       ('A Game of Thrones', '978-0553103540', 2);

Understanding the Schema Design:

By running the SQL script above, you’ve successfully transitioned from a logical design to a physical database. Here is a breakdown of the key concepts we applied:

• Primary Keys (PK): Using SERIAL PRIMARY KEY automatically creates a unique, incrementing ID for every new entry. This ensures no two authors or books are ever confused by the system.

• Foreign Keys (FK): The REFERENCES Authors(author_id) command is where the "Relational" part of a Relational Database happens. It tells the Books table that it must point to a valid ID in the Authors table, preventing "orphan" books without creators.

• Constraints: By using UNIQUE on the isbn and email columns, we've programmed the database to reject any duplicate data, ensuring high data integrity.

How to Fetch Your Data

Now that the data is stored, you need to know how to get it back out. In SQL, we can do this using the SELECT statement.

1. See Everything in a Table

To see all books currently in the library:

SELECT * FROM Books;

2. Filtering Results

Often, you don't want every single row. You can use the WHERE clause to filter for specific data. For example, to find an author by their exact name:

SELECT * FROM Authors 
WHERE author_name = 'J.R.R. Tolkien';

3. Joining Tables

In a normalized database, information is spread across tables. To see a list of book titles alongside their actual author names (instead of just an ID number), you use a JOIN.

SELECT Books.title, Authors.author_name
FROM Books
JOIN Authors ON Books.author_id = Authors.author_id;

The database is instructed by this query to "give the titles from the Books table and the names from the Authors table where the author_id matches in both." This enables you to effectively store data in distinct tables while viewing it as a single, comprehensive report.

This ability to link information across tables is what makes Relational Databases the industry standard for most business applications. But while the relational model is powerful, it isn't the only way to store data. Depending on whether you're handling social media connections, real-time sensor data, or simple document storage, you might need a different architectural approach.

Conclusion

Designing a database is much more than simply throwing information into a computer. It's the process of building a robust, efficient, and secure foundation for decision-making and business operations.

As we have explored here, a successful database relies on a carefully orchestrated ecosystem of hardware, software (the DBMS), data, and users.

By following the four stages of design – Requirements Analysis, Conceptual Design, Logical Design, and Physical Design – you can avoid costly structural mistakes and ensure that your system perfectly aligns with user needs.

Applying critical techniques like normalisation during this process guarantees that your data remains consistent, accurate, and free from frustrating anomalies.

Also, as the digital landscape continues to evolve, mastering these foundational concepts is your stepping stone into the future. Traditional relational databases remain incredibly powerful, but modern data demands are rapidly driving the adoption of cloud-based, AI-powered, and serverless database systems.

A well-designed system today must not only focus on data integrity and query performance but also prioritise scalability and stringent data security to protect against modern cyber threats.

Whether you are building a simple address book or architecting a backend for the next big application, keeping these core principles of database system design in mind will empower you to create resilient, high-performing, and future-proof data solutions.

These two operations look simple, but they hide a dangerous reliability problem. What if the database write succeeds but the message broker is temporarily unreachable? Or your service crashes between the two steps? You end up in an inconsistent state: your database has the new data, but the rest of the system never heard about it.

The Outbox Pattern is a well-established solution to this problem. In this tutorial, you'll learn what the pattern is, why it works, and how to implement it in Go with PostgreSQL and Google Cloud Pub/Sub.

Prerequisites

Before reading this tutorial, you should be familiar with:

• The basics of the Go programming language

• SQL and PostgreSQL

• The concept of database transactions

• Basic familiarity with event-driven or distributed systems (helpful but not required)

Table of Contents

1. The Problem: Two Operations, No Atomicity

2. How the Outbox Pattern Works

3. The Outbox Table Schema

4. The Message Relay

5. Go and PostgreSQL Implementation

   • The Orders Service

   • The Relay Service

6. Why Messages Can Be Delivered More Than Once

7. Alternative: PostgreSQL Logical Replication

8. Conclusion

The Problem: Two Operations, No Atomicity

To understand why the Outbox Pattern exists, you need to understand a core challenge in distributed systems: atomicity across different systems.

In a relational database, a transaction lets you group multiple operations so they either all succeed or all fail together. If you insert a row and update another row in the same transaction, you're guaranteed that both happen – or neither does.

The problem arises when you try to extend this guarantee across two different systems: for example, your database and your message broker (like Kafka, RabbitMQ, or Pub/Sub). These systems don't share a transaction boundary.

Here's a typical event-driven flow that breaks without the Outbox Pattern:

1. A user places an order.

2. Your service saves the order to the database ✅

3. Your service publishes an order.created event to the message broker ❌ (broker is down)

4. The order exists in the database, but downstream services never learned about it.

Or the reverse failure:

1. Your service publishes the event first ✅

2. Your service tries to save the order to the database ❌ (database times out)

3. Downstream services received a notification for an order that doesn't exist.

Either scenario leaves your system in an inconsistent state. This is the core problem the Outbox Pattern solves.

Here's what the process looks like when not using the Outbox Pattern:

How the Outbox Pattern Works

The Outbox Pattern solves the atomicity problem by keeping both operations inside the database:

1. Saves your business data (for example, a new order) to your database.

2. Writes the event message to a special table called the outbox table in the same database transaction.

3. A separate background process called the Message Relay polls the outbox table and publishes pending messages to the broker.

4. Once the broker confirms receipt, the relay marks the message as processed.

Because steps 1 and 2 happen in the same database transaction, they are atomic. Either both succeed or neither does. You can never end up with saved data but no corresponding event queued – or an event queued for data that was never saved.

The message is never published directly to the broker in your main application code. Instead, the database acts as a reliable staging area.

The Outbox Table Schema

The outbox table stores pending messages until the relay picks them up. Here's a typical PostgreSQL schema:

CREATE TABLE outbox (
    id          uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    topic       varchar(255)  NOT NULL,
    message     jsonb         NOT NULL,
    state       varchar(50)   NOT NULL DEFAULT 'pending',
    created_at  timestamptz   NOT NULL DEFAULT now(),
    processed_at timestamptz
);

Let's walk through each column:

• id: A unique identifier for each message. Using UUIDs makes it easy to reference specific messages.

• topic: The destination topic or queue name in your message broker (for example, orders.created).

• message: The event payload, stored as JSON. This is the data your consumers will receive.

• state: Tracks whether the message has been sent. The two main values are pending (waiting to be published) and processed (successfully published).

• created_at: When the message was inserted. The relay uses this to process messages in order.

• processed_at: When the relay successfully published the message.

You may want to add additional columns depending on your needs: for example, a retry_count column to track how many times the relay has attempted to send a message, or an error column to log failure reasons.

The Message Relay

The Message Relay is a background process (often a goroutine, a sidecar, or a separate service) that bridges the outbox table and the message broker.

Its responsibilities are:

1. Periodically query the outbox table for messages with state = 'pending'.

2. Publish each message to the appropriate topic in the broker.

3. Once the broker confirms delivery, update the row's state to 'processed'.

4. Handle failures gracefully: if publishing fails, leave the message as 'pending' so it will be retried.

This design gives you at-least-once delivery: a message will always be sent, even if the relay crashes and restarts. The trade-off is that a message might occasionally be sent more than once (more on this below), so your consumers should handle duplicates.

Go and PostgreSQL Implementation

Let's build a concrete example. Imagine you have an orders service. When a new order is created, you want to:

1. Save the order to a PostgreSQL orders table.

2. Publish an order.created event to Google Cloud Pub/Sub.

You'll use pgx for the PostgreSQL driver.

The Orders Service

The key insight is that the order insert and the outbox insert happen inside the same transaction. If anything goes wrong, both are rolled back.

// orders/main.go

package main

import (
	"context"
	"encoding/json"
	"log"
	"os"

	"github.com/google/uuid"
	"github.com/jackc/pgx/v5"
	"github.com/jackc/pgx/v5/pgxpool"
)

// Order represents a customer order in our system.
type Order struct {
	ID       uuid.UUID `json:"id"`
	Product  string    `json:"product"`
	Quantity int       `json:"quantity"`
}

// OrderCreatedEvent is the payload published to the message broker.
// It contains only the fields that downstream services need to know about.
type OrderCreatedEvent struct {
	OrderID uuid.UUID `json:"order_id"`
	Product string    `json:"product"`
}

// createOrderInTx saves a new order and its outbox event atomically.
// Both operations share the same transaction (tx), so either both succeed
// or both are rolled back — ensuring consistency.
func createOrderInTx(ctx context.Context, tx pgx.Tx, order Order) error {
	// Step 1: Insert the business data (the actual order).
	_, err := tx.Exec(ctx,
		"INSERT INTO orders (id, product, quantity) VALUES (\(1, \)2, $3)",
		order.ID, order.Product, order.Quantity,
	)
	if err != nil {
		return err
	}
	log.Printf("Inserted order %s into database", order.ID)

	// Step 2: Serialize the event payload that consumers will receive.
	event := OrderCreatedEvent{
		OrderID: order.ID,
		Product: order.Product,
	}
	msg, err := json.Marshal(event)
	if err != nil {
		return err
	}

	// Step 3: Write the event to the outbox table.
	// This does NOT publish to Pub/Sub — it just queues it for the relay.
	_, err = tx.Exec(ctx,
		"INSERT INTO outbox (topic, message) VALUES (\(1, \)2)",
		"orders.created", msg,
	)
	if err != nil {
		return err
	}
	log.Printf("Inserted outbox event for order %s", order.ID)

	return nil
}

func main() {
	ctx := context.Background()

	pool, err := pgxpool.New(ctx, os.Getenv("DATABASE_URL"))
	if err != nil {
		log.Fatalf("Unable to connect to database: %v", err)
	}
	defer pool.Close()

	// Begin a transaction that will cover both the order insert
	// and the outbox insert.
	tx, err := pool.Begin(ctx)
	if err != nil {
		log.Fatalf("Unable to begin transaction: %v", err)
	}
	// If anything fails, the deferred Rollback is a no-op after a successful Commit.
	defer tx.Rollback(ctx)

	newOrder := Order{
		ID:       uuid.New(),
		Product:  "Super Widget",
		Quantity: 10,
	}

	if err := createOrderInTx(ctx, tx, newOrder); err != nil {
		log.Fatalf("Failed to create order: %v", err)
	}

	// Committing the transaction makes both writes permanent simultaneously.
	if err := tx.Commit(ctx); err != nil {
		log.Fatalf("Failed to commit transaction: %v", err)
	}

	log.Println("Successfully created order and queued outbox event.")
}

Notice that createOrderInTx receives a pgx.Tx (a transaction) rather than a pool connection. This is intentional: it enforces that the caller is responsible for managing the transaction boundary, making the atomicity guarantee explicit.

The Relay Service

The relay runs as a separate background process. It polls the outbox table, publishes messages, and marks them as processed.

A critical detail here is the use of FOR UPDATE SKIP LOCKED in the SQL query. This PostgreSQL feature lets you run multiple relay instances concurrently without them stepping on each other. When one instance locks a row to process it, other instances skip that row and move on to the next one.

// relay/main.go

package main

import (
	"context"
	"log"
	"time"

	"cloud.google.com/go/pubsub"
	"github.com/google/uuid"
	"github.com/jackc/pgx/v5/pgxpool"
)

// OutboxMessage mirrors the columns we need from the outbox table.
type OutboxMessage struct {
	ID      uuid.UUID
	Topic   string
	Message []byte
}

// processOutboxMessages picks up one pending message, publishes it to Pub/Sub,
// and marks it as processed — all within a single database transaction.
func processOutboxMessages(ctx context.Context, pool *pgxpool.Pool, pubsubClient *pubsub.Client) error {
	tx, err := pool.Begin(ctx)
	if err != nil {
		return err
	}
	defer tx.Rollback(ctx)

	// Query for the next pending message.
	// FOR UPDATE SKIP LOCKED ensures that if multiple relay instances are
	// running, they won't try to process the same message simultaneously.
	rows, err := tx.Query(ctx, `
		SELECT id, topic, message
		FROM outbox
		WHERE state = 'pending'
		ORDER BY created_at
		LIMIT 1
		FOR UPDATE SKIP LOCKED
	`)
	if err != nil {
		return err
	}
	defer rows.Close()

	var msg OutboxMessage
	if rows.Next() {
		if err := rows.Scan(&msg.ID, &msg.Topic, &msg.Message); err != nil {
			return err
		}
	} else {
		// No pending messages — nothing to do.
		return nil
	}

	log.Printf("Publishing message %s to topic %s", msg.ID, msg.Topic)

	// Publish the message to the Pub/Sub topic and wait for confirmation.
	result := pubsubClient.Topic(msg.Topic).Publish(ctx, &pubsub.Message{
		Data: msg.Message,
	})
	if _, err = result.Get(ctx); err != nil {
		// Publishing failed. We return the error here without committing,
		// so the transaction rolls back and the message stays 'pending'.
		// The relay will retry it on the next polling interval.
		return err
	}

	// Mark the message as processed now that the broker has confirmed receipt.
	_, err = tx.Exec(ctx,
		"UPDATE outbox SET state = 'processed', processed_at = now() WHERE id = $1",
		msg.ID,
	)
	if err != nil {
		return err
	}
	log.Printf("Marked message %s as processed", msg.ID)

	// Commit the transaction: the state update becomes permanent.
	return tx.Commit(ctx)
}

func main() {
	// In production, initialize real connections using environment variables
	// or a config file. These are left as placeholders for clarity.
	var (
		pool         *pgxpool.Pool
		pubsubClient *pubsub.Client
	)

	// Poll the outbox table every second.
	// Adjust the interval based on your latency requirements.
	ticker := time.NewTicker(1 * time.Second)
	defer ticker.Stop()

	for range ticker.C {
		if err := processOutboxMessages(context.Background(), pool, pubsubClient); err != nil {
			log.Printf("Error processing outbox: %v", err)
		}
	}
}

The polling interval (1 second in this example) controls the maximum latency between an event being written to the outbox and it being published to the broker. For most use cases, 1–5 seconds is perfectly acceptable. If you need lower latency, you can reduce the interval, or consider using PostgreSQL's LISTEN/NOTIFY feature to wake up the relay immediately when a new row is inserted.

Why Messages Can Be Delivered More Than Once

You might wonder: isn't the Outbox Pattern supposed to guarantee exactly once delivery?

It does not. It guarantees at-least-once delivery. Here's the edge case:

1. The relay publishes the message to Pub/Sub successfully.

2. Before it can update the outbox row to 'processed', the relay process crashes.

3. On restart, the relay sees the message is still 'pending' and publishes it again.

This is a rare but possible scenario. The standard way to handle it is to design your message consumers to be idempotent. This means that they can safely receive and process the same message multiple times without causing incorrect behavior.

Common strategies for idempotency include:

• Using the message's id as a deduplication key, and checking if you've already processed it before acting.

• Making your operations naturally idempotent. For example, using INSERT ... ON CONFLICT DO NOTHING instead of a plain INSERT.

Alternative: PostgreSQL Logical Replication

The polling approach described above is simple and works well, but it has two drawbacks: it introduces some latency (up to one polling interval), and it issues database queries even when there's nothing to process.

For high-throughput systems where these trade-offs matter, PostgreSQL offers a more advanced alternative: logical replication via the Write-Ahead Log (WAL).

Every change made to a PostgreSQL database is first written to the WAL – an append-only log used for crash recovery and replication. With logical replication, you can subscribe to changes in specific tables and receive them as a stream in near real-time.

Instead of your relay asking "Are there any new messages?" on a schedule, PostgreSQL will proactively notify your relay the moment a new row is inserted into the outbox table.

This approach is lower latency and more resource-efficient for high-volume workloads. The trade-off is added implementation complexity: you need to manage a replication slot in PostgreSQL and handle the WAL stream correctly.

In Go, you can use the pglogrepl library to interact with PostgreSQL's logical replication protocol.

For more details on how WAL and change data capture work in PostgreSQL, see the official Write-Ahead Logging documentation.

Conclusion

The Outbox Pattern solves a fundamental problem in distributed systems: how do you reliably perform a database write and publish a message to a broker in a consistent way?

The key idea is to use your database as the source of truth for both the business data and the pending messages. By writing to the outbox table in the same transaction as your business data, you get atomic guarantees from the database itself: no distributed transaction protocol required.

Here's a quick summary of the key concepts:

• The outbox table stores pending events as part of your regular database schema.

• The transaction wraps both the business write and the outbox write, making them atomic.

• The Message Relay is a background process that reads from the outbox and publishes to the broker.

• At-least-once delivery means your consumers must be idempotent.

• FOR UPDATE SKIP LOCKED allows multiple relay instances to run safely in parallel.

• Logical replication is an advanced alternative that avoids polling for high-throughput systems.

The pattern is simple in concept, but there are several ways to implement it depending on your scale and infrastructure. The polling approach shown in this tutorial is a solid starting point for most applications.

Resources

• Source code on GitHub

• PostgreSQL Write-Ahead Logging (WAL)

• pglogrepl – Go library for PostgreSQL logical replication

• pgx – PostgreSQL driver and toolkit for Go

• Explore more Go tutorials on packagemain.tech

Disaster recovery testing is what separates assumed resilience from proven recovery, but it’s still skipped, rushed or treated as a checkbox exercise. For developers and technical teams, that gap can turn a manageable failure into a prolonged outage.

Table of Contents

• What is Disaster Recovery Testing?

• How Disaster Recovery Testing Works in Practice

• Disaster Recovery Testing Methods Developers Should Know

• What Technology Disaster Recovery Testing Evaluates

• How to Test a Disaster Recovery Plan

• Disaster Recovery Test Scenarios: Practical Examples

• Disaster Recovery Test Report: Turning Tests Into Improvements

• Disaster Recovery Audits and Continuous Validation

• Conclusion

What is Disaster Recovery Testing?

Disaster recovery (DR) testing is the process of validating that systems, data and applications can be restored after a disruptive event within defined recovery objectives. It generally evaluates:

• Recovery Time Objective (RTO): How quickly systems must be restored.

• Recovery Point Objective (RPO): How much data loss is acceptable.

• Operational readiness: Whether teams know what to do during an incident.

A disaster recovery test plan documents how these elements are tested, who is responsible and what success looks like. Without testing, DR plans are assumptions, not guarantees.

How Disaster Recovery Testing Works in Practice

In real environments, disaster recovery testing is used to check all elements of the disaster recovery plan and is rarely a single event. It’s a structured exercise that simulates failure, observes system behavior and measures outcomes against expectations.

A typical DR test involves:

1. Defining scope – Which applications, services, or data sets are included.

2. Selecting a scenario – Outage, corruption, ransomware, region failure, and so on.

3. Executing recovery actions – Restore data, fail over systems, reconfigure dependencies.

4. Measuring results – Time to recovery, data consistency, service availability.

5. Documenting findings – What worked, what failed, what needs improvement.

For developers, the key shift is recognizing that DR testing isn’t just an ops exercise. Application architecture, data handling and deployment patterns all influence recovery outcomes.

Importantly, regulatory pressure is also reshaping how organizations approach recovery validation. Frameworks such as the NIS2 Directive require essential and important entities in the EU to implement robust cybersecurity risk management measures, including incident response and business continuity capabilities.

Disaster Recovery Testing Methods Developers Should Know

Different testing methods provide different levels of confidence. Mature teams use more than one. Each method has a place, but relying only on low-impact testing creates blind spots that surface during real incidents.

Checklist Testing

The simplest method: Teams review documented recovery steps without executing them. This helps validate documentation completeness but does not confirm real-world recoverability.

Tabletop Exercises

Stakeholders walk through a simulated disaster scenario and discuss responses. Tabletop tests are useful for identifying communication gaps and unclear responsibilities, especially for cross-team coordination.

Partial or Component Testing

Specific systems, such as databases or backup restores, are tested in isolation. Developers often encounter this when validating recovery procedures for individual services or environments.

Full-scale Testing

This is the most comprehensive method. It involves actual failover or full recovery in production-like environments. While disruptive, full-scale tests provide the highest confidence.

What Technology Disaster Recovery Testing Evaluates

Modern environments are complex, and disaster recovery testing must validate more than just data restores.

DR testing evaluates:

• Backup integrity – Are backups usable, consistent and complete?

• Application dependencies – Do services come back in the correct order?

• Infrastructure recovery – Can compute, storage and networking be re-provisioned?

• Identity and access – Do credentials, secrets and permissions still function?

• Automation and scripts – Do recovery workflows still match current architectures?

For developers, this often reveals hidden coupling between services, outdated scripts or environment-specific assumptions that were never documented.

How to Test a Disaster Recovery Plan

Testing a disaster recovery plan doesn’t require shutting down production on day one. A practical, incremental approach works best.

1. Start with a single application: Pick a service with well-defined data and dependencies. Avoid starting with your most complex system.

2. Validate backup restores: Restore data into a non-production environment and confirm application functionality, not just file presence.

3. Measure RTO and RPO: Time the recovery process and compare results to stated objectives. At this stage, many teams can discover that their objectives were unrealistic.

4. Test failure assumptions: Simulate real-world issues like missing credentials, expired certificates or partial data loss.

5. Document gaps immediately: Update the disaster recovery test plan while findings are fresh. Untested fixes are just new assumptions.

This approach makes disaster recovery testing part of standard processes rather than a once-a-year compliance task.

Automating Restore Validation

One of the most common gaps in disaster recovery testing is stopping at “restore completed” instead of validating that the application actually works. A restored database that can’t serve queries or contains incomplete data doesn’t meet recovery objectives.

Teams can reduce this risk by automating post-restore validation. For example, after restoring a PostgreSQL database into a staging or isolated DR environment, a simple validation script can confirm connectivity and basic data integrity:

import psycopg2

import sys


def validate_restore():

    try:

        conn = psycopg2.connect(

            host="restored-db.internal",

            database="appdb",

            user="dr_test_user",

            password="securepassword"

        )

        cur = conn.cursor()

        cur.execute("SELECT COUNT(*) FROM users;")

        result = cur.fetchone()



        if result and result[0] > 0:

            print("Restore validation successful.")

        else:

            print("Restore validation failed: No data found.")

            sys.exit(1)


        conn.close()

    except Exception as e:

        print(f"Restore validation error: {e}")

        sys.exit(1)


validate_restore()

This script does three important things:

• Confirms the database is reachable

• Executes a real query, not just a connection check

• Fails explicitly if the expected data is missing

In practice, teams can integrate scripts like this into CI/CD pipelines or scheduled recovery drills. The goal isn’t to test every edge case, but to move from “backup exists” to “restore is functionally verified.” Over time, these automated checks become part of the disaster recovery test plan, helping teams measure RTO accurately and detect configuration drift before a real incident exposes it.

Disaster Recovery Test Scenarios: Practical Examples

Effective disaster recovery testing focuses on realistic failures, not idealized outages.

Accidental Deletion or Misconfiguration

A dropped database table, deleted storage bucket or bad configuration change tests how quickly teams can restore specific data without rolling back entire systems. These everyday incidents often reveal slow or overly manual recovery processes.

Data Corruption and Application Failure

Buggy releases can silently corrupt data while systems remain online. This scenario validates point-in-time recovery and whether teams can identify when corruption started, not just restore the latest backup.

Ransomware Simulation

Ransomware testing checks whether clean, uncompromised backups can be restored in isolation. It often exposes gaps in backup immutability, credential handling and realistic recovery times.

Infrastructure or Platform Outage

Simulating the loss of a cluster, availability zone or region tests automation and infrastructure-as-code maturity. In virtualized environments, most commonly VMware disaster recovery, testing involves restoring virtual machines at a secondary site and validating networking and application dependencies.

Credential and Access Failure

Recovery can stall if credentials, certificates or secret keys are unavailable. Testing this scenario validates identity systems and whether recovery procedures rely on fragile access assumptions.

Disaster Recovery Test Report: Turning Tests Into Improvements

Testing without documentation is wasted effort. A disaster recovery test report turns results into actionable improvements.

A valuable DR test report includes:

• Test scope and scenario

• Expected vs. actual RTO/RPO

• Recovery steps executed

• Failures, delays and root causes

• Recommended changes

For developers, this often results in concrete action items: refactoring startup dependencies, adding health checks, improving automation or adjusting data protection policies. The report should feed directly into backlog planning.

Disaster Recovery Audits and Continuous Validation

Audits often expose what teams already suspect: Disaster recovery plans exist, but haven’t been tested recently (or at all).

Rather than treating audits as one-time events, teams should adopt continuous validation:

• Regular restore tests integrated into CI/CD pipelines.

• Scheduled DR tests tied to major architecture changes.

• Automated alerts when recovery objectives drift.

This shifts disaster recovery testing from an annual obligation to an ongoing practice that evolves alongside the environment.

Conclusion

Disaster recovery testing is not about pessimism, it’s about realism. Systems and people change, and failure modes evolve faster than documentation. Without testing, even the best-designed recovery plan can become outdated.

For developers and technical teams, practicing disaster recovery testing builds confidence rooted in evidence, not assumptions. It exposes hidden dependencies, validates data protection strategies and ensures that when something goes wrong, recovery is predictable instead of chaotic.

A query that looks like a normal join can execute like a distributed system: rows move across the network, remote statements get executed repeatedly, and the local planner quietly becomes a coordinator. In that world, “fast SQL” is not mainly about CPU or indexes. It’s about data movement and round-trips.

This handbook covers the mechanism that determines whether a federated query behaves like a clean remote query or a chatty distributed workflow: pushdown.

Pushdown is not “moving compute”. Pushdown determines whether filtering, joining, ordering, and aggregation occur at the data source or after the data has already crossed the wire. When pushdown works, the local server receives a reduced result set. When it doesn’t, Postgres often has to fetch broad intermediate sets and finish the work locally.

The chapters ahead will help you build a practical mental model of what is “shippable” in postgres_fdw, why some expressions are blocked, and how to read EXPLAIN (ANALYZE, BUFFERS, VERBOSE) without getting tricked by familiar plan shapes.

After the core method, the handbook covers tuning knobs that matter in production, schema and indexing considerations, benchmarking methodology, monitoring and logging, and a case study that shows what a real pushdown win looks like end-to-end.

The later sections go deeper into advanced shippability edge cases, cost model calibration, and regression-proofing FDW workloads.

Table of Contents

• Prerequisites

• Executive Summary

• Motivation

• FDW Basics Without the Setup Tax

• Pushdown Mechanics

• Shippable Operations: a Deep Dive

• Pushdown Blockers and Why They Exist

• Reading EXPLAIN Like a Pro

• How to Tune postgres_fdw

• Schema and Index Recommendations

• Benchmarking Methodology

• Monitoring and Logging

• Case Study: Refactoring a Keycloak Coverage Query

• Checklist and Troubleshooting Guide

• Case Study Takeaways

• Advanced Operations: A Deeper Dive into Shippability

• Common Anti‑Patterns and How to Avoid Them

• Extending Tuning: Calibrating Cost Models

• Further Case Studies and Practical Examples

• Monitoring, Diagnostics, and Regression Testing

• Extended Guidelines for Advanced DBAs

• Bringing it All Together

• References

Prerequisites

This handbook assumes basic comfort with Postgres query plans. It builds on EXPLAIN (ANALYZE, BUFFERS) rather than reintroducing SQL fundamentals, indexing, or join algorithms.

The focus here is federated execution: how foreign queries behave, and how to reason about them with the same clarity as local plans.

Here’s what you should already be comfortable with:

• Reading EXPLAIN (ANALYZE, BUFFERS) output and spotting obvious plan smells (row explosions, bad join order, missed indexes).

• Basic join mechanics (nested loop, hash join, merge join) and why cardinality estimates matter.

• Postgres statistics at a practical level (ANALYZE, correlation, and what “estimated rows vs actual rows” implies).

And here’s what you need to follow along with the examples:

• A Postgres “local” instance that will run postgres_fdw and act as the coordinator.

• A Postgres “remote” instance that holds the foreign tables.

• Permission on the local side to:

  • CREATE EXTENSION postgres_fdw;

  • create a SERVER and USER MAPPING

  • create FOREIGN TABLE objects (or permission to use existing ones)

• A way to run queries and capture plans:

  • psql is enough, and so is any GUI, as long as you can run EXPLAIN (ANALYZE, BUFFERS, VERBOSE).

We won’t go through a long environment setup walkthrough. The examples assume the FDW objects exist and focus on plans and behavior.

We also won’t go into general distributed systems theory. Only the pieces that show up in an FDW plan are used.

Executive Summary

The single most important lesson of this handbook is that FDW pushdown reduces data movement. It’s tempting to think of pushdown as merely changing where a calculation happens (“move the work to the remote”). But what really matters is whether the remote server is asked for only the rows you need.

When pushdown is working, the remote server performs the selective join and filtering, and the local Postgres receives a small, already reduced result set. When pushdown fails, the local server becomes a distributed query coordinator: it pulls large intermediate sets over the network and then finishes the heavy lifting locally.

Why does this matter? Because a refactor that makes more of your query shippable to the remote server can slash end‑to‑end latency without changing a single row of output. In the case study we'll explore later, rewriting a query so that the FDW can ship a joined remote query instead of performing multiple foreign scans and local joins reduces runtime from approximately 166 ms to 25 ms. The business logic did not change – the shape of the work changed.

Below is a simple bar chart illustrating that dramatic drop. The chart uses actual timings from the case study. If you run the experiment yourself, the numbers may differ depending on your hardware and network, but the relative difference should be clear.

Motivation

Foreign data wrappers let you query remote data using the same SQL syntax you use locally. That convenience is exactly why they can be so deceptive.

A federated query may look like a normal join, but under the hood, it behaves like a distributed system: some part of the plan runs on the remote server, some on the local server, and every boundary between them is a network hop. The slow path is rarely “bad SQL” – it’s usually a combination of two things:

1. Too many rows are pulled over the network. Without pushdown, the FDW retrieves a large slice of the remote table and applies your filters and joins locally. This may lead to tens of thousands or millions of rows being shipped across the network when you only needed hundreds or fewer.

2. Too many round-trips. If the plan performs a nested loop that drives a foreign scan, it can end up executing the same remote query hundreds or thousands of times. Each call might be fast on its own, but latency adds up.

This isn't speculation. PostgreSQL's documentation makes clear that a foreign table has no local storage and that Postgres “asks the FDW to fetch data from the external source” [1]. There is no local buffer cache or heap storage to hide mistakes. Every row you retrieve must traverse the network at least once. If your plan fetches more rows than it needs, or repeatedly does so, performance can degrade quickly.

That’s why you should treat the Remote SQL shown in EXPLAIN (VERBOSE) as part of your query plan. It tells you exactly what the remote server is being asked to do. If it’s missing your filters or joins, you know the local server will have to finish the job. The rest of this handbook will teach you how to read that plan, how to force pushdown when possible, and how to recognize the signs that something has gone wrong.

FDW Basics Without the Setup Tax

You might be tempted to skip this section if you've already created foreign tables in your own databases. Don't. Understanding the architecture of foreign data wrappers is essential to understanding why pushdown matters.

SQL/MED in a nutshell

PostgreSQL implements the SQL/MED (Management of External Data) standard through its FDW framework. To access a remote Postgres server via postgres_fdw, you perform four steps:

1. Install the extension: CREATE EXTENSION postgres_fdw tells Postgres to load the FDW code.

2. Create a foreign server: CREATE SERVER foreign_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host '...', port '...', dbname '...')defines where the remote server resides and how to connect.

3. Create a user mapping: CREATE USER MAPPING FOR your_user SERVER foreign_server OPTIONS (user 'remote_user', password '...') tells Postgres how to authenticate on the remote side.

4. Create a foreign table: CREATE FOREIGN TABLE remote_table (...) SERVER foreign_server OPTIONS (schema_name '...', table_name '...'); defines the columns and references the remote table.

Once you've done that, you can run SELECT statements against the foreign table as if it were local. But the definition hides an important detail: there is no storage associated with that foreign table [1]. Every time you SELECT, INSERT, UPDATE, or DELETE, the FDW must connect to the remote server, build a remote query, send it, and read the results. This overhead is small for simple queries but becomes critical as queries get more complex.

What postgres_fdw does and does not do

postgres_fdw does two things for you:

1. It builds remote SQL from your query, including pushing down safe filters, joins, sorts, and aggregates when it can.

2. It fetches rows from the remote server and hands them to the local executor. If some part of your query cannot be executed remotely, the local executor performs that part.

The FDW tries hard to minimize data transfer by sending as much of your WHERE clause as possible to the remote server and by not retrieving unused columns [2]. It also has a number of tuning knobs that we'll explore later (such as fetch_size, use_remote_estimate, fdw_startup_cost, and fdw_tuple_cost[3]). But the real win often comes from structuring your query so that the FDW can push work down.

There's one last architectural point to keep in mind: the remote server runs with a restricted session environment. In remote sessions opened by postgres_fdw, the search_path is set to pg_catalog only, and TimeZone, DateStyle, and IntervalStyle are set to specific values [4]. This means that any functions you expect to run remotely must be schema‑qualified or packaged in a way that the FDW can find them. It also underscores why you should not override session settings for FDW connections unless you know exactly what you are doing [4].

Pushdown Mechanics

At a high level, “pushdown” means pushing as much of your SQL query as possible to the remote server. But the FDW cannot simply send arbitrary SQL. It must be safe and portable for remote evaluation. Postgres uses the term shippable to describe expressions and operations that can be evaluated on the foreign server.

What “shippable” means in practice

An expression is considered shippable if it meets several conditions:

1. It uses built‑in functions, operators, or data types, or functions/operators from extensions that have been explicitly allow‑listed via the extensions option on the foreign server [2]. If you use a custom function or an extension that has not been declared, the FDW assumes it cannot run remotely.

2. It’s marked IMMUTABLE. Postgres distinguishes between IMMUTABLE, STABLE, and VOLATILE functions. Only immutable functions – those that always return the same output for the same inputs and don’t depend on session state – are candidates for pushdown [5]. This rule prevents time‑dependent functions, such as now() or random() from being evaluated remotely, because the result might differ between the local and remote servers.

3. It doesn’t depend on local collations or type conversions. PostgreSQL’s docs warn that type or collation mismatches can lead to semantic anomalies [1]. If the FDW cannot guarantee that a comparison behaves identically on both servers, it will refuse to push it down. For example, comparing a citext column to a text constant could be unsafe if the remote server doesn’t have the citext extension installed.

From these rules, you can derive a mental checklist: avoid non‑immutable functions in your WHERE clause, keep your join conditions simple and typed correctly, and list any third‑party extensions you want to use in the foreign server’s extensions option so that they are considered shippable [2].

WHERE pushdown

If a WHERE clause consists entirely of shippable expressions, it will be included in the remote query. Otherwise, it will be evaluated locally. This matters because pushing a filter down reduces the number of rows returned to the local server.

Consider a predicate like this:

WHERE created_at >= now() - interval '30 days'

Because now() is volatile (it returns a different value each time it’s called), Postgres cannot assume the remote server will interpret now() the same way. The FDW therefore pulls the entire table and applies the filter locally.

A better approach is to pass a parameter into the query or compute the cutoff timestamp once in the application and embed it into the SQL.

Join pushdown conditions

Joins are the next big lever. When postgres_fdw encounters a join between foreign tables on the same foreign server, it will send the entire join to the remote server unless it believes it will be more efficient to fetch the tables individually or unless the tables use different user mappings [6].

It applies the same precautions described for WHERE clauses: the join condition must be shippable, and both tables must be on the same server. Cross‑server joins are never pushed down – the FDW will perform them locally.

Shippability decision tree

It can be helpful to visualize the shippability rules as a flowchart. Below is a simple decision tree that you can use when inspecting an expression or join clause.

It starts with the question of whether an expression is in a WHERE or JOIN clause. Further decisions are made based on factors like using volatile functions, built-in functions, type mismatches, or cross-server joins. The flowchart concludes with outcomes like "Not shippable, evaluated locally" or "Shippable, included in Remote SQL."

If you reach the left side of the tree, the expression will be evaluated locally. If you reach the right side, the FDW can ship it.

Shippable Operations: a Deep Dive

Postgres has been expanding what postgres_fdw can be pushed down over several versions. This section walks through each operation class and the conditions required for pushdown.

Filters (WHERE clauses)

As explained above, simple filters that use built‑in operators and immutable functions are generally pushed down. If you see a Filter: node above a Foreign Scan in your plan, it means some part of your predicate didn’t qualify. Common reasons include using now(), timezone() or other volatile functions, referencing a non‑allow‑listed extension, or comparing different collation settings.

When this happens, the entire table (or at least all rows matching other shippable conditions) is fetched, and the filter is applied locally.

Plan smell: Look for a Foreign Scan node with a Filter: line directly above it. That means filtering happened locally. Also look for broad Remote SQL such as:

SELECT * FROM remote_table WHERE (name = 'Hamdaan')

with no group constraints. That's a sign that the filter was not pushed down.

Joins

Simple inner joins between foreign tables on the same foreign server are usually pushable. The join condition must satisfy the same shippability rules as filters. If the join involves more than one foreign server, if the join condition uses an unshippable function, or if the foreign tables use different user mappings, the FDW will fetch each table separately and join them locally [6]. This can lead to large intermediate sets being transferred.

Plan smell: A Hash Join or Merge Join where both inputs are Foreign Scan nodes indicates that the join was performed locally. Conversely, a single Foreign Scan representing a join and containing the JOIN ... ON clause in Remote SQL indicates that the join was pushed down.

Aggregates (GROUP BY, COUNT, SUM, and so on)

Starting in PostgreSQL 10, aggregates can be pushed to the remote server when possible. The release notes state explicitly: “push aggregate functions to the remote server,” and explain that this reduces the amount of data that must be transferred from the remote server and offloads aggregate computation [7].

To qualify, both the grouping expressions and the aggregate functions themselves must be shippable. If the FDW cannot push an aggregate, it will fetch the raw rows and perform the aggregation locally.

Plan smell: Look for a GroupAggregate node above a Foreign Scan that returns many rows. When the aggregate is pushed down, there will be no local aggregate node. Instead, the Remote SQL will include a GROUP BY clause.

ORDER BY and LIMIT

Prior to PostgreSQL 12, sorting and limiting were rarely pushed down. In version 12, Etsuro Fujita’s patch allows ORDER BY sorts and LIMIT clauses to be pushed to postgres_fdw foreign servers in more cases [8]. For the sort or limit to be pushed, the underlying scan must be pushable, and the ordering expression must be shippable. Partitioned queries or complicated join trees may still cause the sort or limit to be applied locally.

Plan smell: A local Sort or Limit node above a Foreign Scan indicates the operation was not pushed down. Conversely, a Remote SQL statement containing ORDER BY and LIMIT indicates that pushdown succeeded.

DISTINCT

Distinct operations can be pushed down when the distinct expression list is shippable. But if the distinct is combined with unshippable expressions, or if the distinct is applied after a join that cannot be pushed down, the FDW will retrieve all rows and perform the distinct locally.

Window functions

In practice, window functions are rarely pushed down through postgres_fdw. They often require ordering or partitioning semantics that are difficult to represent portably. If you see a WindowAgg node in your plan, it’s almost always local. That doesn’t mean you can't use window functions with foreign tables, but you should expect them to incur network and CPU costs.

Version differences

Postgres developers continue to improve the FDW layer. Here are some notable changes by version:

1. PostgreSQL 9.6 introduced remote join pushdown and allowed UPDATE/DELETE pushdown. Before 9.6, all joins were local.

2. PostgreSQL 10 introduced aggregate pushdown, enabling remote GROUP BY and aggregate functions [7].

3. PostgreSQL 12 expanded ORDER BY and LIMIT pushdown [8].

4. PostgreSQL 15 added pushdown for certain CASE expressions and other improvements.

If you learned FDW behavior on an older version, revisit your assumptions.

Pushdown Blockers and Why They Exist

When pushdown fails, it’s not due to bad luck. There’s always a reason grounded in safety or correctness. Here are the most common blockers and how to diagnose them.

Non‑immutable functions

Functions marked VOLATILE or STABLE cannot be pushed down because their results may differ between the local and remote server. Examples include now(), random(), current_user, and user‑defined functions that look at session variables or query the database. Even functions you might think are harmless, like age() or clock_timestamp(), can cause pushdown to fail.

Fix: Compute volatile values in your application or in a CTE before referencing the foreign table. For example, compute timestamp 'now' - interval '30 days' as a constant and compare your created_at column against that constant. Alternatively, move the logic into a stored generated column on the remote table.

Type and collation mismatches

The documentation warns that when types or collations don’t match between the local and remote tables, the remote server may interpret conditions differently [1]. This is particularly insidious when text comparisons, case‑insensitive collations, or non‑default locale settings are used. If Postgres can't guarantee the same semantics, it will pull rows locally and evaluate the expression.

Fix: Make sure that your foreign table definition uses the same data types and collations as the remote table. When in doubt, explicitly cast values to a common type.

Cross‑server joins

Joins across different foreign servers cannot be pushed down. The FDW can only ship a join when both tables reside on the same remote server and use the same user mapping [6]. Otherwise, it will perform two separate scans and join the results locally.

Fix: If you frequently join tables across servers, consider consolidating the tables on a single server, materializing a view on one side, or pulling the smaller table into a temporary local table before joining.

Mixed local and foreign joins

A join between a local table and a foreign table will not be pushed down. Even though the foreign side might be pushdown‑eligible, the FDW cannot join it with local data on the remote server. A nested loop with a parameterized foreign scan is the typical pattern here, resulting in many remote calls.

Fix: Filter or aggregate as much as possible on the foreign side first (via a CTE or by materializing a subset) before joining to local tables.

Remote session settings and search paths

Because postgres_fdw sets a restricted search_path, TimeZone, DateStyle, and IntervalStyle in remote sessions [4], any functions you call must be schema‑qualified or otherwise compatible. If a function relies on the current search path or session settings, it may break or produce different results on the remote side.

Fix: Schema‑qualify remote functions and ensure that any environment‑dependent logic is safe to execute under the default FDW session settings. If necessary, attach SET search_path or other settings to your remote functions.

Troubleshooting matrix

The table below maps symptoms in your EXPLAIN plan to likely causes and fixes. Use it as a quick diagnostic tool when something looks off.

Reading EXPLAIN Like a Pro

Many developers skim EXPLAIN plans for local queries, looking at the top nodes and overall cost. For FDW queries, you must invert that habit: read the foreign parts first. The Remote SQL string tells you what the remote server is being asked to do, and the loops field tells you how many times that remote call is executed.

Inspect the Foreign Scan nodes

Start by finding the Foreign Scan node(s). In EXPLAIN (VERBOSE), each foreign scan includes a line like:

Remote SQL: SELECT ...

This line is not a trivial – it’s the actual SQL that will run on the remote server. Read it carefully. Does it include your WHERE predicates? Does it include your join conditions? If not, you know the local server will pick up the slack.

Look at the loops column. If the loops exceed 1, the same remote query is executed multiple times. For example:

Foreign Scan on public.user_entity  (rows=1 loops=416)
  Remote SQL: SELECT id, tenant_id FROM public.user_entity WHERE enabled AND service_account_client_link IS NULL AND id = $1

This is the “N+1” problem in disguise. The plan executes the foreign scan once per outer row. Multiply the per‑loop cost by the number of loops to understand why the query is slow. The fix is to rewrite the query so that the join and filters are applied in a single remote call.

Recognize InitPlan vs SubPlan

An InitPlan runs once and caches its result. A SubPlan can run per outer row. In FDW queries, subplans often drive parameterized remote scans. If you see a SubPlan attached to a nested loop that feeds a foreign scan, suspect a parameterized remote lookup and look for ways to turn it into an InitPlan or merge it into a single remote query.

Understand CTE materialization

Common table expressions (CTEs) behave differently depending on whether they are marked MATERIALIZED or NOT MATERIALIZED. A materialized CTE is computed once and stored in a temporary structure, then read by the rest of the query. A non‑materialized CTE is inlined into the parent query, allowing optimizations to span across the boundary.

In PostgreSQL 12 and later, CTEs are inlined by default unless they’re referenced multiple times or explicitly marked MATERIALIZED. Materializing a CTE that contains a foreign scan can freeze a broad remote fetch and prevent later clauses from being pushed down. On the other hand, materialization can prevent repeated remote scans if the CTE is referenced multiple times. Use this lever deliberately to control where remote work happens.

Annotated example

Let's annotate a simplified excerpt from a real plan. The goal is to show how to quickly read the relevant parts.

Nested Loop  (rows=414 loops=1)
  -> Hash Join  (rows=416 loops=1)
       -> Foreign Scan on public.user_entity (rows=1 loops=416)
            Remote SQL: SELECT id, tenant_id FROM public.user_entity WHERE enabled AND service_account_client_link IS NULL AND id = $1
  -> Foreign Scan on public.user_attribute (rows=671 loops=1)
       Remote SQL: SELECT ua.user_id, ua.value FROM user_attribute ua JOIN user_entity u ON ua.user_id = u.id JOIN tenant r ON u.tenant_id = r.id WHERE ua.name = 'attribute A' AND r.name = 'demo' AND u.enabled AND u.service_account_client_link IS NULL AND (g.name = 'keycloak-group-a' OR g.parent_group = $1)

In the old plan, the first Foreign Scan executed 416 times, each time retrieving a single row. The Remote SQL only applies the filter on enabled and service_account_client_link – it doesn’t include the tenant or group scoping. That scoping is applied by the nested loop outside the foreign scan.

In the refactored plan, the second Foreign Scan results from combining user_attribute, user_entity, user_group_membership, keycloak_group, and tenant into a single remote query. It retrieves 671 rows in a single query and includes all relevant filters. There is no repeated remote call. The timing difference is driven by the different loop values and the selectivity of the Remote SQL.

How to Tune postgres_fdw

Once you've structured your query for maximum pushdown, tuning knobs let you squeeze out further performance improvements and adjust planner decisions.

fetch_size

fetch_size controls how many rows postgres_fdw retrieves per network fetch. The default is 100 rows [9]. A small fetch size means more round-trips and lower memory usage. A larger fetch size reduces network overhead at the cost of buffering more rows in memory.

In practice, increasing fetch_size to a few thousand can reduce latency for large result sets. It’s specified either at the foreign server or foreign table level:

ALTER SERVER foreign_server OPTIONS (ADD fetch_size '1000');
ALTER FOREIGN TABLE remote_table OPTIONS (ADD fetch_size '1000');

use_remote_estimate

By default, the planner estimates the cost of foreign scans using local statistics. This can be wildly inaccurate if the foreign table has a different data distribution. Setting use_remote_estimate to true tells postgres_fdw to run EXPLAIN on the remote server to get row count and cost estimates. This can dramatically improve join order selection at the cost of an additional remote query during planning [3]. You can set this per table or per server:

ALTER SERVER foreign_server OPTIONS (SET use_remote_estimate 'true');

fdw_startup_cost and fdw_tuple_cost

These cost parameters model the overhead of starting a foreign scan and the cost per row fetched. Adjusting them can influence the planner’s choice of join strategy. A higher fdw_startup_cost discourages the planner from choosing plans with many small foreign scans (which might generate many remote calls). A higher fdw_tuple_cost discourages plans that fetch large numbers of rows [3]. Use these only after you have solid evidence from EXPLAIN and experiments.

ANALYZE and analyze_sampling

Running ANALYZE on a foreign table collects local statistics by sampling the remote table [3]. Accurate stats are essential for good estimates when use_remote_estimate is false.

But if the remote table changes frequently, these stats become stale quickly. The analyze_sampling option controls whether sampling happens on the remote side or locally. When analyze_sampling is set to random, system, bernoulli, or auto, ANALYZE will sample rows remotely instead of pulling all rows into the local server[3].

extensions

The extensions option lists extensions whose functions and operators can be shipped to the remote server [2]. If you rely on functions from citext, pg_trgm, or other extensions, add them to the server definition:

ALTER SERVER foreign_server OPTIONS (SET extensions 'citext,pg_trgm');

A quick knob impact table

Schema and Index Recommendations

Pushdown doesn’t eliminate the need for good indexes. In fact, effective pushdown depends on the remote server having indexes that support the filter and join predicates you’re shipping.

Below are some patterns to watch for in FDW queries and the indexes that support them. You can adapt these to your own schema.

In general, indexes should reflect the join order you expect the remote planner to use. If your Remote SQL starts with:

FROM user_attribute ua JOIN user_entity u ON ua.user_id = u.id JOIN user_group_membership ugm ON ...

ensure that indexes exist on user_attribute(user_id) and user_group_membership(user_id).

Benchmarking Methodology

It’s easy to claim a performance improvement without proper measurement. Here's a repeatable method you can use to benchmark FDW query changes.

1. Warm the caches. Run each query once to load data into the remote buffer cache and the local FDW connection. Discard the timings.

2. Measure latencies. Use EXPLAIN (ANALYZE, BUFFERS, VERBOSE) to capture execution times, buffer usage, and remote row counts. Be aware that EXPLAIN ANALYZE adds overhead, so record the raw execution time if possible by running the query directly.

3. Record remote metrics. On the remote server, enable pg_stat_statements and track the calls, total_time, and rows for each remote query. This gives you a per‑query breakdown and confirms what Remote SQL is executed.

4. Control for concurrency and network latency. Run benchmarks during a quiet period or isolate the test cluster. If your environment has high network latency, record the round‑trip time separately to attribute delays.

5. Compare apples to apples. Benchmark the old and new queries under identical conditions. Use the same sample data, same remote server, and same connection settings.

6. Look at row counts. The primary goal of pushdown is to reduce the number of rows shipped. Compare the rows column of each Foreign Scan node.

Here's a simple matrix you can use to record your experiments:

Monitoring and Logging

In production, you need to know when a query starts misbehaving. There are two places to look: the local server and the remote server.

Local metrics

1. pg_stat_statements. This extension tracks planning and execution times, row counts, and buffer hits for each query. Look for high total times relative to rows or calls.

2. Auto Explain or auto_explain. Turn on auto_explain.log_min_duration_statement to capture slow queries with plans. This will show you the Remote SQL executed and whether the plan changed.

3. Connection pool metrics. Monitor connection counts and wait events related to FDW operations (for example, PostgresFdwConnect, PostgresFdwGetResult) as described in the documentation [10].

Remote metrics

1. pg_stat_statements on the remote server. This lets you see which Remote SQL queries are being executed, how often, and how long they take. Compare these with the Remote SQL strings in your local EXPLAIN plans.

2. Server logs. Increase log_statement or log_min_duration_statement on the remote server to capture long-running remote queries.

Correlating local and remote metrics can reveal patterns such as a new code path causing a surge in remote queries or pushdown failures, leading to heavy remote scans.

Case Study: Refactoring a Keycloak Coverage Query

The theory above may seem abstract until you see it play out in practice. Let's walk through a real example inspired by a Keycloak integration.

The original query calculated coverage: given a list of category IDs, it returned the percentage of users who had attributes mapped to those categories and a JSON array of entity counts. The query used a CTE to build a list of scoped users, then joined it with user attributes, category mappings, and a few other tables.

Symptom

In a test environment with 100K user records, the query averaged 166 ms. This was slower than expected. Running EXPLAIN (ANALYZE, BUFFERS, VERBOSE) showed two foreign scans on the Keycloak database. The first scanned user_entity 416 times (loops = 416). The second pulled all rows from user_attribute where name = 'attributeA' before filtering by tenant and group locally.

Here's a simplified excerpt (numbers are approximate):

Foreign Scan on public.user_entity  (actual time=0.117..0.117 rows=1 loops=416)
  Remote SQL: SELECT id, tenant_id FROM public.user_entity WHERE (enabled AND service_account_client_link IS NULL AND id = $1)
Foreign Scan on public.user_attribute  (actual time=41.267..80.352 rows=80739 loops=1)
  Remote SQL: SELECT value, user_id FROM public.user_attribute WHERE (('attributeA' = name))

The first scan performed a single-row lookup 416 times. The second scan retrieved 80,739 rows because the only condition pushed down was name = 'attributeA'. Tenant and group scoping occurred locally. That meant 80k rows were transferred over the network and then filtered down to about 671 on the local side.

Diagnosis

There were two main issues.

First was the N+1 remote calls on user_entity. The join to user_entity was not pushed down, so the plan executed a remote lookup for each row from user_group_membership. This created 416 remote queries.

Second was the unscoped attribute fetch. Because the WHERE clause included user_entity.tenant_id = tenant.id and keycloak_group.name = 'groupA' in a higher CTE, the FDW could not see those predicates when scanning user_attribute. It therefore fetched all rows with name = 'attributeA' and left the tenant and group filters to the local side.

Refactor

The fix was to inline the tenant and group joins into the user_attribute scan to avoid the nested-loop pattern. The refactored selected_user_attributes CTE looked like this (simplified for readability):

WITH selected_user_attributes AS (
  SELECT DISTINCT ua.user_id, ua.value
  FROM public.user_attribute ua
  JOIN public.user_entity u ON u.id = ua.user_id
  JOIN public.user_group_membership ugm ON ugm.user_id = u.id
  JOIN public.keycloak_group g ON g.id = ugm.group_id
  JOIN public.tenant r ON r.id = u.tenant_id
  WHERE ua.name = 'attributeA'
    AND u.enabled
    AND u.service_account_client_link IS NULL
    AND r.name = 'tenantA'
    AND (g.name = 'groupA' OR g.parent_group = (
         SELECT id FROM public.keycloak_group WHERE name = 'groupA' AND tenant_id= r.id
    ))
)

This single query expresses the same scoping logic that previously lived in separate CTEs. Because all the join conditions are on the same foreign server and use built‑in operators, the FDW can push down the entire join. The new plan looked like this:

Foreign Scan  (actual time=7.840..7.856 rows=671 loops=1)
  Remote SQL: SELECT ua.user_id, ua.value FROM user_attribute ua JOIN user_entity u ON ua.user_id = u.id JOIN user_group_membership ugm ON ugm.user_id = u.id JOIN keycloak_group g ON g.id = ugm.group_id JOIN tenant r ON u.tenant_id= r.id WHERE ua.name = 'attributeA' AND u.enabled AND u.service_account_client_link IS NULL AND r.name = 'tenantA' AND (g.name = 'groupA' OR g.parent_group = $1)

Only one remote query is executed, and it returns 671 rows. Tenant and group scoping occur on the remote server. There is no nested loop or repeated remote scan. The final runtime dropped to about 25 ms.

Why it improved

1. Fewer rows crossing the network. The old plan fetched 80k attribute rows and filtered them locally. The new plan fetched only the 671 scoped rows.

2. No repeated remote calls. The old plan executed 416 remote scans of user_entity. The new plan performs one joined remote query.

3. Less local work. Because the join and filtering happen remotely, the local side no longer hashes or filters large sets.

Key takeaway

If you see a Foreign Scan with a high loops count or a Remote SQL that doesn’t contain your filters and joins, you’re leaving performance on the table. Merging filters and joins into a single remote query (subject to shippability rules) often yields orders-of-magnitude improvements.

Checklist and Troubleshooting Guide

The following steps summarize how to approach FDW performance tuning:

1. Inspect the Remote SQL. Always run EXPLAIN (VERBOSE) and look at what is being sent to the remote. If your predicates are missing, the FDW isn't pushing them down.

2. Check loops. If the loops are greater than 1 on a Foreign Scan, you are paying for repeated remote calls. Rewrite the query or reorder the joins to make the foreign scan run once.

3. Make predicates shippable. Replace volatile functions with constants or parameters. Ensure operators and functions are built‑in or explicitly allow‑listed via the extensions option [2].

4. Align types and collations. Use the same data types and collations on both sides to avoid semantic mismatches [1].

5. Push joins to the same server. Consolidate tables on one foreign server if possible. Joins across servers cannot be pushed down [6].

6. Use use_remote_estimate when planning seems off. Enabling remote estimates can improve join order selection [3].

7. Tune fetch_size and costs if your queries transfer many rows. A bigger fetch_size reduces round-trip; adjusting fdw_startup_cost and fdw_tuple_cost influences the planner [3].

8. Analyze foreign tables if you rely on local cost estimates. Keep in mind that stats can get stale quickly [3].

9. Monitor both servers. Use pg_stat_statements on local and remote servers to see how often remote queries run and how long they take.

10. Test version upgrades. Each major release improves FDW pushdown semantics (for example, aggregates in 10 [7], ORDER/LIMIT in 12 [8]). Retest after upgrading.

Case Study Takeaways

Querying remote data with PostgreSQL’s postgres_fdw can be fast and convenient if you respect the underlying mechanics. Pushdown is the difference between streaming a trickle of relevant rows and hauling an ocean of data across the network. It isn't simply a matter of moving CPU cycles – it changes how much data moves, how many network round-trip occur, and how much your local server has to do.

The rules may seem restrictive – use only immutable functions, avoid cross‑server joins, align types and collations – but they exist to preserve correctness while enabling optimization.

By reading EXPLAIN from the bottom up, inspecting the Remote SQL, and understanding the shippability rules, you can spot slow patterns quickly. Armed with tuning knobs like fetch_size and use_remote_estimate, and a willingness to rewrite queries to make joins and filters pushable, you can often achieve dramatic performance gains without touching your hardware.

This case study shows that rewriting a query to enable a single-joined remote query reduced runtime from around 166 ms to 25 ms. That sort of improvement is not rare. It’s what happens when you treat FDW queries as distributed queries rather than local queries in disguise.

The next time you debug a slow FDW query, remember this handbook. Check the Remote SQL. Count the loops. Ask yourself: “Am I doing the work close to the data, or am I bringing the data to the work?” Adjust accordingly, and you'll write queries that make the most of Postgres's federated capabilities while keeping your latency in check.

This section closes the case study loop and summarizes exactly what changed in the plan and why it produced a large end-to-end win. The following sections of the handbook turn that single win into a repeatable method: how Postgres determines what is shippable, how to quickly read FDW plans, which operations and versions matter, and how to debug common failure modes that prevent pushdown.

Advanced Operations: A Deeper Dive into Shippability

The previous sections introduced the basic rules around what can be pushed to the remote and why. To really make sense of those rules, you need to see how they play out on the operations you use every day.

This section walks through filters, joins, aggregates, ordering, and limits, DISTINCT queries, and window functions in more detail. By the end, you should have a mental map of which operations to trust and which to double‑check when reading your plans.

Filters and simple predicates

WHERE clauses matter more than you think

When you specify WHERE attribute = 'value' on a foreign table, the FDW will happily transmit that predicate to the remote server as long as the comparison uses built‑in types and immutable operators. For example:

• WHERE id = 42 is fine

• WHERE lower(username) = 'hamdaan' is fine if lower() is allow‑listed and immutable

• WHERE created_at >= now() - interval '7 days' is not shippable because now() is volatile

When such a predicate cannot be pushed, the FDW will fetch every row that matches all the shippable predicates and apply the rest locally. That means that a seemingly innocuous call to now() can blow up your network traffic.

The lesson is simple: compute volatile values up front (in your application or in a CTE) and reference them as constants in the query against the foreign table.

Complex expressions are not automatically unsafe

Suppose you have WHERE (status = 'active' AND (age BETWEEN 18 AND 29 OR age > 65)). This entire expression is shippable because it uses built‑in boolean logic, simple comparisons, and immutable operators. The FDW will deparse it into remote SQL and forward it. You only need to worry when one of the subexpressions introduces a function or operator that the FDW doesn’t recognize or cannot safely assume exists on the remote.

A good heuristic is: if you can express your filter using only simple comparisons, boolean logic, and built‑in functions, pushdown should work. When in doubt, check the Remote SQL.

Array and JSON operators

Modern Postgres makes heavy use of array and JSON functions. Many of these functions, like the array overlap operator && used in the case study, are built‑in and can be shipped. But some JSON functions are provided by extensions (like jsonb_path_query or functions from the pgjson family).

If your filter uses one of these, ensure that the extension is available and allow‑listed on the foreign server. Otherwise, the FDW will fetch rows and perform the JSON logic locally. This is rarely what you want when dealing with large JSON columns.

Joins: the good, the bad, and the ugly

Same‑server joins are your friend

If you join multiple foreign tables that are all defined on the same foreign server and user mapping, and if the join condition uses only shippable expressions, then the FDW can generate a single remote join. This is the ideal case.

For example, joining orders and customers on orders.customer_id = customers.id is pushable, as long as both tables reside on the same foreign server. The remote planner will use its own statistics and indexes to plan the join, and the local server will simply iterate through the result. Postgres 9.6 and later support this pattern [6].

Cross‑server joins break pushdown

If you attempt to join two foreign tables that live on different servers (or even on the same remote server but with different user mappings), postgres_fdw will fetch the tables separately and join them locally. This is almost always slower than pushing the join down, because you end up transferring both tables in their entirety.

The FDW design team chose not to support cross‑server joins because there is no portable way to tell two remote servers to cooperate on a join. Your options are: replicate one table on the other server, materialize the smaller table locally before joining, or restructure the query to filter aggressively on each side before joining locally.

Mixed local/foreign joins are tricky

Joining a local table to a foreign table cannot be pushed down, for straightforward reasons: the remote server has no access to your local data. A common pattern that triggers repeated remote calls looks like this:

SELECT u.id, a.value
FROM users u
LEFT JOIN user_attribute a
  ON a.user_id = u.id AND a.name = 'favorite_color';

If users is a local table and user_attribute is foreign, the plan may use a nested loop: for each local u, it executes a remote lookup in user_attribute to retrieve attributes.

The fix is to flip the query: retrieve all relevant rows from user_attribute in one remote scan, then join them locally. Or, if possible, create a small temporary table on the remote side with your u.id values, perform the join entirely remotely, and then fetch the results.

Join conditions matter

Even when joining two foreign tables on the same server, an unshippable join condition will force the join to be local. For example, JOIN ON textcol ILIKE '%foo%' is not pushable because ILIKE might not exist or behave identically on the remote.

If you need case‑insensitive matching, consider lowercasing both sides: LOWER(textcol) = 'foo' (assuming the remote server has the lower() function available and allowed). Similarly, joining on a cast expression (for example, JOIN ON CAST(a.id AS text) = b.text_id) can block pushdown. Define your columns with matching types instead.

Aggregates and grouping

Aggregates are where the data movement story shines. When you can push down a GROUP BY and aggregate functions like COUNT, SUM, AVG, or MAX, you reduce the result set to just the aggregated rows. This can be a difference of several orders of magnitude.

Postgres 10 introduced aggregate pushdown [7]. But not all aggregates are equal:

Simple aggregates such as COUNT(*), SUM(col), AVG(col), MIN(col), and MAX(col) are shippable when applied to shippable expressions. Even COUNT(DISTINCT col) is often shippable, because the remote can deduplicate before counting. The FDW will wrap the aggregate in a remote query and return just the aggregated row.

If you see a GroupAggregate node on the local side, check whether all involved columns and functions are shippable. If they are, ensure that the join conditions above are also pushable.

Filtered aggregates such as COUNT(*) FILTER (WHERE x > 5) or SUM(col) FILTER (WHERE status = 'active') are often pushable, because they translate into SUM(CASE WHEN condition THEN col ELSE 0 END) or COUNT(...). As long as the filter is shippable, the FDW will push it into the remote aggregate.

User‑defined aggregates are rarely pushable. If you have a custom aggregate function, the FDW will not assume that it exists or behaves the same on the remote server. Even if you install the function on both servers, postgres_fdw won't push it unless the function is in an allow‑listed extension.

Grouping sets and rollups are not currently pushable. When you write GROUP BY GROUPING SETS (...) or ROLLUP(...), Postgres will compute the grouping locally even if the underlying scan is remote.

If you need complex rollups, consider performing them in two steps: push down the initial grouping to the remote server to reduce rows, then perform the rollup locally.

ORDER BY, LIMIT, and DISTINCT

Ordering and limiting rows may seem like purely cosmetic features, but they affect how much data is transferred. If the remote can sort and limit, the local server only receives the top N rows. If it cannot, the local server must sort everything.

Postgres 12 expanded the cases where ORDER BY and LIMIT are pushed down [8]. Here are guidelines:

• Single foreign scan with simple sort: If your query selects from one foreign table and sorts by a shippable expression (for example, ORDER BY created_at DESC), the FDW will include ORDER BY in Remote SQL. It will also push down LIMIT and OFFSET. This is ideal because the remote server does the sort and sends only the top rows.

• Sort after join: If you sort after joining two foreign tables on the same server, and the join and sort expressions are shippable, the FDW may push both down. But if the sort requires columns from the local side or from a different remote server, the FDW cannot push it down.

• Sort after aggregation: Sorting aggregated results is often pushable as long as the aggregate itself is pushable. But when grouping occurs locally, the sort remains local.

• DISTINCT behaves like GROUP BY. If the distinct expression list is shippable, the FDW can push it down. If you write SELECT DISTINCT ON (col1) col2, col3 FROM ... and col3 is not part of the DISTINCT list, Postgres will treat this as GROUP BY and may push it. Be aware that DISTINCT ON semantics differ from plain DISTINCT and may not be pushable in older Postgres versions.

Window functions

Window functions (for example, ROW_NUMBER() OVER (PARTITION BY ...), RANK(), LAG(), LEAD()) rely on ordering and partitioning across rows.

Postgres has not yet taught postgres_fdw how to push window functions. When you see a WindowAgg node in your plan, it’s almost always local. The FDW will fetch the rows, and the local server will sort, partition, and compute the window. If you need to run window functions on remote data, plan to transfer the data locally.

Version‑specific quirks

The exact pushdown capabilities vary by release. When planning migrations or deciding whether to rely on a pushdown behavior, check the release notes:

• 9.6: first version to support pushdown of joins and sorts, and remote updates and deletes.

• 10: introduced aggregate pushdown [7], significantly reducing network use for GROUP BY queries.

• 11: improved partition pruning and join ordering for foreign tables.

• 12: expanded ORDER BY and LIMIT pushdown [8].

• 15: added pushdown for simple CASE expressions and additional built‑in functions.

• 17 (development at the time of writing) continues to expand shippable constructs. Always test on your target version because subtle improvements can change what the FDW can ship.

Common Anti‑Patterns and How to Avoid Them

Everyone has run into FDW queries that seemed reasonable but turned out to be bottlenecks. Here are a few of the most common mistakes and how to correct them. These examples are deliberately simplified – so you can adapt them to your schema.

Using volatile functions in predicates

Anti‑pattern:

SELECT *
FROM audit_logs
WHERE event_ts >= now() - interval '1 day';

now() is a volatile function, so the FDW refuses to push this predicate. It pulls all rows from audit_logs and filters them locally.

Better:

SELECT *
FROM audit_logs
WHERE event_ts >= $1;

Compute $1 (a timestamp) in your application or upstream query. Or compute it once in a CTE:

WITH cutoff AS (SELECT now() - interval '1 day' AS ts) SELECT * FROM audit_logs, cutoff WHERE event_ts >= cutoff.ts;

The FDW sees a constant and pushes the predicate.

Joining local and foreign data first

Anti‑pattern:

SELECT u.email, ua.value
FROM users u
LEFT JOIN user_attribute ua ON u.id = ua.user_id AND ua.name = 'favorite_movie';

This uses a local table (users) to drive a join to a foreign table (user_attribute). The FDW receives 10,000 individual remote queries if users have 10,000 rows. Each call fetches one or zero rows from user_attribute.

Better:

-- Fetch all favorite movies remotely and join locally
WITH remote_movies AS (
  SELECT ua.user_id, ua.value
  FROM user_attribute ua
  WHERE ua.name = 'favorite_movie'
)
SELECT u.email, rm.value
FROM users u
LEFT JOIN remote_movies rm ON u.id = rm.user_id;

Now the FDW issues one query to fetch all relevant attributes, and the join is done locally in one pass.

Cross‑server joins without materialization

Anti‑pattern:

SELECT *
FROM remote_db1.orders o
JOIN remote_db2.customers c ON o.customer_id = c.id;

This is not pushable because the two tables are on different foreign servers. Postgres will fetch orders and customers separately and join them locally. If orders have 1 million rows and customers have 50,000 rows, you will transfer 1.05 million rows.

Better: Replicate or materialize one side on the other server (or locally) before joining. For example, create a materialized view m_customers on remote_db1 containing just the id and name of the customers you need, then join orders and m_customers on the same server. Alternatively, copy customers into a temporary table on the local server and join there.

Complex expressions on join keys

Anti‑pattern:

SELECT *
FROM remote_table a
JOIN remote_table b ON CAST(a.key AS text) = b.key_text;

Casting a numeric key to text prevents pushdown. The remote server cannot use indexes and must return both tables. The local server performs the join and cast.

Better: Align your schemas so that the join columns use the same type. If you cannot change the schema, create a computed column on the remote server with the appropriate type and use it in the join.

Ignoring collation and type mismatches

Anti‑pattern:

SELECT *
FROM remote_table
WHERE citext_col = 'abc';

If the remote server doesn’t have the citext extension installed, the comparison semantics will differ, and the FDW will refuse to ship the filter. This appears harmless until you see the plan and realize all rows were fetched.

Better: Install the same extensions and collations on the remote server, or convert the column to a base type like text on both sides.

Extending Tuning: Calibrating Cost Models

Earlier, we discussed fetch_size, use_remote_estimate, and the cost knobs. This section expands on how to use them strategically.

Balancing fetch size and memory

fetch_size controls how many rows the FDW asks for in each round trip [9]. Think of it as the batch size. The default (100) works well for small result sets. If you expect to retrieve tens of thousands of rows, a higher fetch size reduces the overhead of many network requests. But there are trade‑offs:

• Memory consumption: Each foreign scan buffers rows until they are consumed. A huge fetch size (for example, 10,000) may allocate more memory than you expect, especially when multiple scans run concurrently. Monitor memory usage as you increase this setting.

• Latency hiding: If network latency is high, overlapping network requests with local processing can hide some latency. But postgres_fdw does not pipeline multiple fetches – it waits for one batch before requesting the next. This means that a larger batch size reduces the number of waits, but cannot overlap them. If you operate across data centers, consider using a connection pooler or caching layer instead of just increasing fetch_size.

Remote estimates vs. local estimates

The planner uses statistics to estimate how many rows each node will produce, which in turn influences join order. When use_remote_estimate is false (the default), the planner guesses based on local stats collected by ANALYZE on the foreign table. This can be wrong if the remote table has a different distribution than the local sample, or if the table has changed since the last ANALYZE.

Setting use_remote_estimate to true instructs the FDW to run EXPLAIN on the remote server during planning to obtain row counts and cost estimates [3]. This can improve join ordering, especially when joining multiple foreign tables or mixing local and foreign tables. The downside is increased planning time because each remote estimate runs an extra query.

In practice:

• Enable use_remote_estimate on queries with complex joins where the planner picks obviously wrong join orders. If enabling it improves the plan, consider leaving it on for that server or table.

• Use ANALYZE on foreign tables periodically if your remote data is relatively static. This populates local stats and can avoid the overhead of remote estimates.

• Don’t enable use_remote_estimate indiscriminately on simple lookups. The cost of additional round-trip remote flights may outweigh the benefit.

Tuning cost parameters

fdw_startup_cost and fdw_tuple_cost control how much the planner thinks it costs to start a foreign scan and fetch each row [3]. If these are too low, the planner may choose a nested loop that generates many small remote calls. If they are too high, the planner might avoid remote scans even when they are efficient.

You can adjust these parameters based on empirical measurement:

• Increase fdw_startup_cost to discourage the planner from using nested loops that call the remote table repeatedly. You might set it to the average cost of a round-trip remote.

• Increase fdw_tuple_cost if network bandwidth is limited or expensive. This indicates to the planner that each remote row incurs higher fetch costs than a local row. The planner will prefer plans that filter early on the remote side.

Always adjust these settings gradually and observe the effect on the plan. Keep separate settings per foreign server if network conditions differ.

When to analyze foreign tables

Running ANALYZE on a foreign table collects sample statistics by pulling a subset of rows from the remote server. This helps the planner estimate row counts when use_remote_estimate is off. It also helps decide whether to use an index on the remote side. You should analyze foreign tables when:

• The remote table is large and static, and you want accurate local estimates without the overhead of remote estimates.

• You have just defined a foreign table, and the default stats are empty.

• You changed the extensions allow‑list to enable more pushdown and want the planner to see the effect.

Conversely, if the remote data changes constantly, ANALYZE results will quickly become stale. In that case, rely on use_remote_estimate instead.

Further Case Studies and Practical Examples

The Keycloak coverage example is not the only place where pushdown matters. The following scenarios illustrate other patterns you may encounter.

Reporting on a sharded logging system

Imagine you store application logs across multiple shards, each a separate Postgres database. You want to produce a report of the number of error logs per service per day.

A naïve approach might join all shards in one query:

SELECT shard, service, date_trunc('day', log_time) AS day, COUNT(*)
FROM shard1.logs
UNION ALL
SELECT shard, service, date_trunc('day', log_time) AS day, COUNT(*)
FROM shard2.logs
...;

This approach will fetch all log rows to the local server and aggregate them locally. A better solution is to push the grouping to each shard:

SELECT shard, service, day, sum(count)
FROM (
  SELECT 1 AS shard, service, date_trunc('day', log_time) AS day, COUNT(*) AS count
  FROM shard1.logs
  WHERE log_time >= $1 AND log_time < $2
  GROUP BY service, day
  UNION ALL
  SELECT 2 AS shard, service, date_trunc('day', log_time) AS day, COUNT(*)
  FROM shard2.logs
  WHERE log_time >= $1 AND log_time < $2
  GROUP BY service, day
  ...
) x
GROUP BY shard, service, day;

Here, each foreign server returns a small set of aggregated rows instead of raw logs. The outer aggregation sums across shards. This pattern generalizes: push grouping and filtering to the remote side, then combine locally.

Combining remote and local data for analytics

Suppose you have a local table users and a remote table orders. You want to compute the average order amount per user segment. A naïve query might look like:

SELECT u.segment, AVG(o.amount)
FROM users u
JOIN orders o ON o.user_id = u.id
GROUP BY u.segment;

This is a local join driving a remote nested loop. The better approach is to aggregate orders remotely by user_id and join on the small result:

WITH remote_totals AS (
  SELECT user_id, SUM(amount) AS total, COUNT(*) AS n
  FROM orders
  GROUP BY user_id
)
SELECT u.segment, AVG(rt.total / rt.n)
FROM users u
JOIN remote_totals rt ON u.id = rt.user_id
GROUP BY u.segment;

This pushes the heavy aggregation to the remote and transfers only one row per user. The local join then groups by segment. As with other examples, the key is to reduce remote rows before they cross the network.

Avoiding pushdown for correctness

There are legitimate cases where you should prevent pushdown because of semantic differences. Postgres allows you to do this by adding OFFSET 0 or wrapping the foreign table in a CTE.

For example, if a built‑in function behaves differently on the remote due to a version mismatch, you can force local evaluation:

WITH local_eval AS (SELECT  FROM remote_table)  -- CTE prevents pushdown
SELECT 
FROM local_eval
WHERE some_complex_expression(local_eval.col) > 0;

Alternatively, a WHERE clause like random() < 0.1 will not push down because random() is volatile – you don't need to force it. But adding OFFSET 0 is a simple hack that prevents any pushdown:

SELECT * FROM remote_table OFFSET 0;

Knowing how to disable pushdown intentionally helps you debug. If a query returns different results when pushdown occurs, suspect type/collation mismatches or remote session settings [4].

Monitoring, Diagnostics, and Regression Testing

Monitoring doesn't end at counting remote rows. To make pushdown reliable in production, you need to set up mechanisms to detect regressions and gather evidence when performance changes.

Automate EXPLAIN regression tests

In addition to unit tests and integration tests, you can add tests that assert the shape of your plans. For instance, if a mission‑critical report must always push down a WHERE clause, you can write a test that runs EXPLAIN (VERBOSE) and checks that the Remote SQL contains the filter. You might even parse loops and assert that it is 1. When a developer inadvertently adds a non‑immutable function or changes a join, the test will fail. This is akin to snapshot testing for SQL.

Monitor pg_stat_statements across servers

Enable pg_stat_statements on both the local and remote servers. On the local side, track the total time, planning time, and rows for each FDW query. On the remote side, track which queries are being executed.

Look for outliers: a query whose remote calls spike or whose average remote rows jump from hundreds to thousands. Those are early signs of pushdown failure.

Log remote SQL with auto_explain

Setting auto_explain.log_min_duration_statement (for example, to 500ms) causes Postgres to automatically log slow queries with their plans. Combine this with auto_explain.log_verbose = true and auto_explain.log_nested_statements = true to capture remote SQL as well. When a federated query slows down, the log will show you exactly what remote SQL was executed and how often. This is invaluable in production, where you cannot always run EXPLAIN interactively.

Use connection pooling and prepare statements

postgres_fdw maintains a connection pool keyed on the user mapping. It reuses connections between queries, but you can also use connection pooling at the network level (for example, pgbouncer or pgcat).

Keeping connections warm reduces the startup cost, as captured by fdw_startup_cost. Meanwhile, preparing statements on the remote server (via PREPARE and EXECUTE) can save parse time when the same remote SQL is executed frequently. postgres_fdw can use server‑side prepared statements for parameterized scans.

Regression testing after version upgrades

Every major Postgres release brings improvements to postgres_fdw pushdown semantics. But new releases also change planner heuristics and remote SQL generation. After an upgrade, rerun your key queries with EXPLAIN (VERBOSE), compare the Remote SQL, and benchmark them.

In some cases, a release may push down something previously local, revealing a latent type mismatch or a function difference. In other cases, pushdown may be withheld due to a new rule. Don’t assume that an upgrade automatically improves performance – test it.

Extended Guidelines for Advanced DBAs

To close this handbook, here are consolidated guidelines distilled from the previous sections. They go beyond simple bullet points to capture nuances. Keep them handy for reference or print them out for your team.

1. Respect the FDW safety model. Immutable functions and built‑in operators are your friends. Anything outside that scope must be explicitly allowed or evaluated locally. Understand which items belong to each category and plan accordingly.

2. Always read the Remote SQL. Don’t trust your intuition about what is being pushed down. The Remote SQL string is the only source of truth. It indicates whether a predicate, join, sort, or limit operation is occurring remotely. It also shows parameter placeholders (for example, $1) that correspond to values passed from the local plan.

3. Reduce before you fetch. The network is the highest cost. If the remote can reduce rows through filtering, grouping, or limiting, let it. If it cannot, structure your query to enable it. Avoid queries that require pulling large raw tables and processing them locally.

4. Beware of join order. The planner sometimes chooses a nested loop with a foreign table as the inner side, resulting in repeated remote calls. Examine loops: if you see a high number, consider rewriting the query or adjusting cost parameters.

5. Use CTEs strategically. A CTE can isolate remote scans and let you control whether they are materialized once or inlined. Use MATERIALIZED to avoid repeated remote scans when a CTE is referenced multiple times. Use NOT MATERIALIZED to allow optimizations across CTE boundaries.

6. Instrument, monitor, iterate. Good FDW performance is not a one‑off fix. Monitor queries and plans. Use tests to catch regressions. Adjust tuning knobs and indexes as your data or workload changes. Document your reasoning so others can understand why a particular plan is expected.

7. Educate your team. Federated queries invite subtle bugs and performance traps. Share the high‑level rules – immutable functions only, cross‑server joins are local, always check remote SQL – so engineers write safer queries by default. A 30‑minute training can save hours of debugging later.

Bringing it All Together

This handbook has covered a lot of ground: from the high‑level principle that pushdown is about data movement, to the nitty‑gritty of join conditions and tuning knobs, to troubleshooting steps and case studies. It is intentionally opinionated and personal: these are the patterns and pitfalls encountered in real systems, not abstract guidelines. By sharing specific examples, I hoped to make the rules memorable and show how they interplay with actual workloads.

The goal is not just to tell you what to do, but to show you how to think and problem solve: review the plan, trace data movement, and determine whether the query is doing the heavy work in the right place.

That thinking process, practiced enough times, becomes second nature. When you write a new query, you'll automatically consider whether your predicates are immutable, whether the join can be shipped, and whether you are about to trigger an N+1 pattern. When you review plans, you'll start from the Foreign Scan nodes and remote SQL, not the top‑level node. When you tune, you'll know which knobs to twist and in which order.

Keep experimenting. Use the examples here as starting points. Try different structures in a test environment and measure the difference. The more you play with pushdown, the more comfortable you'll become with its constraints and superpowers.

If this handbook helps you avoid one performance incident or saves you from shipping a broken query, it has done its job. Enjoy exploring the federated world of Postgres.

References

[1] [2] [3] [4] [5] [6] [9] [10] PostgreSQL: Documentation: 18: F.38. postgres_fdw – access data stored in external PostgreSQL servers (https://www.postgresql.org/docs/current/postgres-fdw.html)

[7] PostgreSQL: Release Notes (https://www.postgresql.org/docs/release/10.0/)

[8] PostgreSQL: Release Notes (https://www.postgresql.org/docs/release/12.0/)

We just posted a course on the freeCodeCamp.org YouTube channel that will help you learn relational database design from the ground up. This course covers SQL fundamentals, entity-relationship modeling, normalization (1NF through BCNF), data types and constraints, indexing strategies, and query optimization.

This course is based on the book Grokking Relational Database Design by Dr. Qiang Hao and Dr. Michael Tsikerdekis (Manning Publications, 2025).

Here are the sections in the course:

• Relational Databases for Beginners — Tables, Entities, Keys & SQL

• SQL Filtering & Aggregation

• SQL Table Commands

• Foreign Keys in SQL

• How SQL JOINs Work

• How to learn SQL on your own

• Database Design Goals

• Database Design Lifecycle

• From Real-World Ideas to Tables

• Primary Key, Candidate Key, and Super Key

• Don't Use the Wrong SQL String Type

• The FLOAT Mistake That Crashed a Stock Exchange

• SQL Date and Time Types Explained

• Connecting Entities in an ER Diagram

• One-to-One Relationships

• One-to-Many Relationships

• Many-to-Many Relationships

• Strong vs Weak Entities

• First Normal Form - Primary Keys and Atomic Values

• Second Normal Form - Partial Keys and Functional Dependencies

• Third Normal Form - Transitive Dependencies

• The Untold Story of BCNF

• Primary Key vs Unique Constraints

• Foreign Key Constraints - ON DELETE & ON UPDATE

• Other Constraints: NOT NULL, DEFAULT, and CHECK

• Access Control, Hashing & Encryption

• B-Tree vs Full-Text Indexes

• Denormalization

Watch the full course on the freeCodeCamp.org YouTube channel (6-hour watch).

Not quite. While blue-green deployments work beautifully for stateless applications, they become significantly more complex when you introduce databases and stateful services into the equation. The moment your blue and green environments need to share a database, you're facing a fundamental challenge: how do you evolve your schema and data without breaking either version?

In this article, we'll tackle the real-world complexities of implementing blue-green deployments on Amazon ECS when your application depends on shared state. You'll learn practical strategies for handling database migrations, managing sessions, and maintaining data consistency across application versions.

💡 Complete Working Example: All code examples in this article are available in the bluegreen-deployment-ecs repository on GitHub. You can clone it and deploy the entire infrastructure to your AWS account.

Table of Contents

• The Problem with State in Blue-Green Deployments

• Database Migration Strategies for Blue-Green

• Handling Stateful Services in ECS

• Complete Implementation: End-to-End Example

• Rollback Strategies

• Monitoring During Deployments

• Best Practices

• When NOT to Use Blue-Green

• Alternative Deployment Strategies

• Cleanup

• Conclusion

• Further Resources

The Problem with State in Blue-Green Deployments

The elegance of blue-green deployments starts to crumble when you consider databases. Here's why: your blue environment runs application version 1, your green environment runs version 2, but they both connect to the same RDS instance.

Consider this scenario: you're adding a new feature that requires a new database column. Version 2 of your application expects this column to exist. You deploy green, run your migration to add the column, and switch traffic.

Everything works great until you need to rollback. Now version 1 is receiving traffic, but it doesn't know what to do with that new column. Worse, if your migration removed or renamed a column that version 1 depends on, your rollback will fail catastrophically.

Here are the specific challenges you'll face:

• Schema versioning conflicts: Your blue environment expects schema version N, while green expects version N+1. Any breaking schema change will cause one environment to fail.

• Data inconsistencies: If version 2 writes data in a new format that version 1 can't read, switching back to blue will result in errors or data corruption.

• Irreversible migrations: Some database changes are inherently destructive. Dropping a column, changing data types, or restructuring tables can't be easily undone.

• Failed rollbacks: The promise of instant rollback becomes hollow when your database has evolved beyond what the blue environment can handle.

Let's explore the strategies that solve these problems.

Database Migration Strategies for Blue-Green

Strategy 1: The Expand-Contract Pattern (Recommended)

The expand-contract pattern is the most practical approach for blue-green deployments with shared databases. It works by breaking schema changes into three phases, ensuring backwards compatibility throughout.

Phase 1: Expand

In this phase, you add new schema elements while keeping old ones intact. If you're renaming a column, add the new column without removing the old one. If you're changing table structure, create new tables alongside existing ones.

-- Example: Renaming 'user_name' to 'username'
-- Phase 1: Expand - Add new column
ALTER TABLE users ADD COLUMN username VARCHAR(255);

-- Populate new column from old column
UPDATE users SET username = user_name WHERE username IS NULL;

At this point, your database supports both the old schema (used by blue) and the new schema (used by green). Your application code needs to handle both as well.

Phase 2: Deploy

Now, deploy your green environment with code that uses the new schema. But this code should still write to both old and new columns to maintain compatibility.

# Version 2 code - writes to both columns
def update_user(user_id, username):
    db.execute(
        "UPDATE users SET username = %s, user_name = %s WHERE id = %s",
        (username, username, user_id)
    )

Traffic shifts from blue to green. Both environments work because the database supports both schemas. If you need to rollback, blue still functions perfectly because the old columns are intact.

Phase 3: Contract

After you're confident green is stable and you've decommissioned blue, remove the old schema elements in a separate deployment.

-- Phase 3: Contract - Remove old column
ALTER TABLE users DROP COLUMN user_name;

Update your application code to stop writing to the old columns. This is now version 3, deployed as a standard release.

When to use: This should be your default approach for most schema changes including adding/removing columns, renaming fields, changing constraints, and restructuring tables.

Strategy 2: Parallel Schemas or Databases

For major breaking changes where backwards compatibility is impractical, you might maintain entirely separate database versions. Version 1 connects to database A, version 2 connects to database B. This approach requires data synchronization between databases. AWS Database Migration Service (DMS) can replicate data in near real-time, or you can build custom replication logic using change data capture.

# Configuration for version-specific database connections
DATABASE_CONFIG = {
    'v1': {
        'host': 'blue-db.cluster-xxxxx.us-east-1.rds.amazonaws.com',
        'database': 'app_v1'
    },
    'v2': {
        'host': 'green-db.cluster-yyyyy.us-east-1.rds.amazonaws.com',
        'database': 'app_v2'
    }
}

During the transition period, you run DMS to keep both databases synchronized, with the understanding that writes go to the active version's database.

The challenge is that you're now managing data synchronization, dealing with replication lag, and paying for two databases. Eventually, you need to consolidate back to one database, which requires another migration. This is expensive and complex, which is why it's the "nuclear option."

When to use: Only for major architectural changes, complete data model redesigns, or when migrating between database types (for example, MySQL to PostgreSQL). If expand-contract can possibly work, use that instead.

Strategy 3: Feature Flags for Gradual Rollout

Feature flags allow you to decouple deployment from release. Both blue and green run the same codebase, but features are toggled on or off via configuration. This shifts the problem from schema compatibility to code-level compatibility.

def create_user(user_data):
    config = get_feature_config()
    if config['use_new_user_schema']:
        return create_user_v2(user_data)
    else:
        return create_user_v1(user_data)

Instead of having two separate deployments (blue and green), you have ONE deployment with conditional logic. The "switch" from old to new behavior happens via configuration change, not infrastructure change. This is technically not pure blue-green, but it's a powerful hybrid approach.

How it works

Your application checks AWS AppConfig (or similar service) for feature flags before executing code paths. When a flag is off, it uses the old schema/logic. When on, it uses the new schema/logic. You can even enable features for a percentage of users (5% get new behavior, 95% get old behavior) for gradual rollout.

The tradeoff is that your codebase temporarily contains both old and new logic with conditional branches everywhere. This increases complexity and requires disciplined cleanup after the feature is fully released. However, you gain fine-grained control and can toggle features on/off instantly without deploying new infrastructure.

When to use: For large features with uncertain stability, gradual rollouts to monitor impact, or when you want instant rollback capability without touching infrastructure. Also useful when combined with expand-contract for extra safety.

Handling Stateful Services in ECS

Beyond databases, several other stateful components require careful consideration during blue-green deployments.

Session Management

It’s a good idea to store sessions in ElastiCache or DynamoDB rather than application memory:

app.config['SESSION_TYPE'] = 'dynamodb'
app.config['SESSION_DYNAMODB'] = boto3.client('dynamodb')

Shared Resources

Beyond database sessions, your application likely depends on other stateful components that need coordination during blue-green deployments:

1. S3 buckets

If your application stores files or data in S3, schema changes to object metadata or file formats can cause compatibility issues between versions. To address this, you can enable S3 versioning to maintain multiple format versions simultaneously.

For example, if version 2 writes JSON files with a new structure, version 1 should still be able to read the old format. You can include a version prefix in object keys (like v1/user-data.json and v2/user-data.json) or embed version metadata in the objects themselves.

Message queues (SQS/SNS)

Messages sent by one version must be readable by the other during the transition. You can use versioned message schemas with a schema_version field in your message payload. Both blue and green should be able to parse messages from either version, even if they only produce messages in their preferred format. Consider using a schema registry or validation library to ensure compatibility.

Cache layers (ElastiCache/Redis)

Cached data structure changes can cause deserialization errors when switching between versions. Try versioning your cache keys by including the schema version: CACHE_VERSION = 'v2' and then cache_key = f"user:{CACHE_VERSION}:{user_id}". This ensures blue and green maintain separate cache namespaces, preventing cross-contamination. When you fully migrate to green, you can flush the old cache keys or let them expire naturally.

CACHE_VERSION = 'v2'
cache_key = f"user:{CACHE_VERSION}:{user_id}"

Implementation: End-to-End Example

Let's walk through a complete blue-green deployment with ECS, handling a database schema change using the expand-contract pattern. We'll migrate from a single address text field to structured street_address, city, state, and zip_code fields.

Here’s the scenario: You're running an e-commerce application on ECS. The current version (blue) stores customer addresses in a single address text field. Version 2 (green) splits this into structured fields: street_address, city, state, and zip_code.

Architecture Setup

Your infrastructure includes:

• ECS cluster running Fargate tasks

• Application Load Balancer with two target groups (blue and green)

• RDS PostgreSQL database (shared between environments)

• CodeDeploy for managing traffic shifts

• Parameter Store for database connection strings

💡 Implementation Note: The complete Terraform code for this architecture is available in the companion GitHub repository.

Prerequisites

Before starting, make sure that you have the following tools installed and your AWS credentials properly configured:

# Required tools
aws --version      # AWS CLI
terraform --version # Terraform >= 1.0
docker --version   # Docker
psql --version     # PostgreSQL client

# Configure AWS credentials
aws configure
aws sts get-caller-identity  # Verify your identity

Step 1: Deploy Infrastructure and Blue Environment

We’ll start by setting up the entire AWS infrastructure from scratch using Terraform, then deploying the initial version of our application (blue environment).

First, clone the repository and set up your environment:

# Clone the repository
git clone https://github.com/Caesarsage/bluegreen-deployment-ecs.git
cd bluegreen-deployment-ecs

# Create terraform variables
cd terraform
cat > terraform.tfvars <<EOF
aws_region         = "us-east-1"
project_name       = "ecommerce-bluegreen"
environment        = "production"
vpc_cidr           = "10.0.0.0/16"

# Database credentials (CHANGE THESE!)
db_username = "dbadmin"
db_password = "ChangeThisPassword123!"

# Container configuration
container_image = "PLACEHOLDER"  # Will update after building image
container_port  = 8080

# Scaling configuration
desired_count = 2
cpu           = "256"
memory        = "512"

# Notifications
notification_email = "your-email@example.com"
EOF

Security Note: Never commit terraform.tfvars to Git. It's already in .gitignore.

Next, initialize Terraform and create the ECR repository:

# Initialize Terraform
terraform init
terraform validate

# Create ECR repository
terraform apply -target=aws_ecr_repository.app

# Get ECR repository URL
export ECR_REPO=$(terraform output -raw ecr_repository_url)
echo "ECR Repository: $ECR_REPO"

We create the ECR repository first because we need somewhere to push our Docker image. Then we'll build the image, push it, and finally deploy the rest of the infrastructure that depends on that image existing.

Build and push the initial application like this:

cd ..  # Back to project root

# Set variables
export AWS_REGION=us-east-1
export AWS_ACCOUNT_ID=$(aws sts get-caller-identity --query Account --output text)
export ECR_REPOSITORY=ecommerce-bluegreen
export IMAGE_TAG=v1.0.0

# Login to ECR
aws ecr get-login-password --region $AWS_REGION | \
    docker login --username AWS --password-stdin $AWS_ACCOUNT_ID.dkr.ecr.$AWS_REGION.amazonaws.com

# Build the image
docker build --platform linux/amd64 -t $ECR_REPOSITORY:$IMAGE_TAG -f docker/Dockerfile .

# Tag and push to ECR
docker tag $ECR_REPOSITORY:$IMAGE_TAG \
    $AWS_ACCOUNT_ID.dkr.ecr.$AWS_REGION.amazonaws.com/$ECR_REPOSITORY:$IMAGE_TAG

docker push $AWS_ACCOUNT_ID.dkr.ecr.$AWS_REGION.amazonaws.com/$ECR_REPOSITORY:$IMAGE_TAG

# Update terraform.tfvars with the image URL
echo "container_image = \"$AWS_ACCOUNT_ID.dkr.ecr.$AWS_REGION.amazonaws.com/$ECR_REPOSITORY:$IMAGE_TAG\"" >> terraform/terraform.tfvars

The application code is a Flask application that handles both old and new schema formats based on the APP_VERSION environment variable.

Now deploy the complete infrastructure:

cd terraform
terraform apply  # Takes ~15-20 minutes

# Get outputs
export ALB_URL=$(terraform output -raw alb_url)
export TEST_URL=$(terraform output -raw test_url)
export DB_ENDPOINT=$(terraform output -raw db_endpoint)
export ECR_URL=$(terraform output -raw ecr_repository_url)
export BASTION_IP=$(terraform output -raw bastion_public_ip)

echo "Application URL: $ALB_URL"
echo "Test URL: $TEST_URL"
echo "Database Endpoint: $DB_ENDPOINT"

The production listener (port 80) is what your users hit. The test listener (port 8080) lets you test the green environment before shifting production traffic to it. This is crucial for validation.

You can see the complete Terraform configuration in terraform.

Step 2: Initialize Database Schema

Now you’ll need to initialize the database with the schema for version 1 (blue). We'll use Bastion for secure access:

# Copy the migration files to the bastion host from your local machine

scp -i ~/.ssh/id_rsa docker/init.sql ec2-user@$BASTION_IP:/tmp/
scp -i ~/.ssh/id_rsa migrations/*.sql ec2-user@$BASTION_IP:/tmp/

# Then SSH into it and run migrations
ssh -i ~/.ssh ec2-user@$BASTION_IP

# Inside the bastion:
psql -h $DB_ENDPOINT -U dbadmin -d ecommerce -f /tmp/init.sql

# Verify
psql -h $DB_HOST -U $DB_USER -d $DB_NAME -c "\d customers"

# Exit the container
exit

Step 3: Verify Blue Environment

We’ll want to test that everything works before we start the migration. This is your baseline: you want to confirm that the current system is healthy before introducing changes.

# Check health
curl $ALB_URL/health | jq

# Expected response:
# {
#   "status": "healthy",
#   "version": "blue",
#   "environment": "production",
#   "database": "connected",
#   "schema": "compatible"
# }

# Create a customer with the old schema (single address field)
curl -X POST $ALB_URL/api/customers \
    -H "Content-Type: application/json" \
    -d '{
      "name": "John Doe",
      "email": "john@example.com",
      "address": "123 Main St, New York, NY, 10001"
    }' | jq

# List customers
curl $ALB_URL/api/customers | jq

Step 4: Expand Phase – Add New Columns

This is the first phase of expand-contract. We're adding the new columns WITHOUT removing the old one, creating a database schema that supports both blue and green simultaneously.

Run the expand migration (migrations/001_expand_address.sql):

-- Migration: 001_expand_address_fields.sql
BEGIN;

ALTER TABLE customers 
  ADD COLUMN street_address VARCHAR(255),
  ADD COLUMN city VARCHAR(100),
  ADD COLUMN state VARCHAR(2),
  ADD COLUMN zip_code VARCHAR(10);

-- Populate new columns from existing data
-- This uses a simple parsing strategy; yours might be more sophisticated

UPDATE customers 
SET 
  street_address = SPLIT_PART(address, ',', 1),
  city = TRIM(SPLIT_PART(address, ',', 2)),
  state = TRIM(SPLIT_PART(address, ',', 3)),
  zip_code = TRIM(SPLIT_PART(address, ',', 4))
WHERE address IS NOT NULL;

COMMIT;

Critical observation: We're NOT dropping the address column. It's still there. Blue continues reading and writing to it, completely unaware that new columns exist. This is what makes the migration safe – nothing breaks.

# Then SSH into it and run migrations
ssh -i ~/.ssh ec2-user@$BASTION_IP

# Inside the bastion:
export DB_ENDPOINT = "" # from terraform output

psql -h $DB_ENDPOINT -U dbadmin -d ecommerce -f /tmp/001_expand_address.sql

# Verify new columns exist
psql -h $DB_ENDPOINT -U dbadmin -d ecommerce -c "\d customers"

exit

Verification: The \d customers command shows the table structure. You should see BOTH the old address column AND the new street_address, city, state, zip_code columns. This confirms the expand phase worked.

The database now supports both old (blue) and new (green) schemas. Blue is still running and working perfectly, and nothing has changed from its perspective.

Step 5: Build and Deploy Green Environment

Now we’ll build version 2 of our application that knows how to work with the new structured address fields, while maintaining backwards compatibility with the old schema.

Start by building version 2 with structured address support:

cd ..  # Back to project root

# Build new version
export IMAGE_TAG=v2.0.0

docker build --platform linux/amd64 -t $ECR_REPOSITORY:$IMAGE_TAG -f docker/Dockerfile .

docker tag $ECR_REPOSITORY:$IMAGE_TAG \
    $AWS_ACCOUNT_ID.dkr.ecr.$AWS_REGION.amazonaws.com/$ECR_REPOSITORY:$IMAGE_TAG

docker push $AWS_ACCOUNT_ID.dkr.ecr.$AWS_REGION.amazonaws.com/$ECR_REPOSITORY:$IMAGE_TAG

What’s different is that the v2 application code now has logic that:

• Reads from the new structured columns (street_address, city, and so on)

• Writes to BOTH new columns AND the old address column

• Accepts API requests with structured address format

Why write to both: This is crucial. Even though green prefers the new format, it maintains the old format, too. If you need to rollback to blue, all the data blue needs is there and up-to-date. Without this, rollback would be impossible: blue would see empty or stale address fields.

Now create and register green task definition:

cd terraform

# Get necessary ARNs
EXECUTION_ROLE_ARN=$(terraform output -raw ecs_task_execution_role_arn)
TASK_ROLE_ARN=$(terraform output -raw ecs_task_role_arn)
DB_SECRET_ARN=$(terraform output -raw db_secret_arn)

# Create task definition
cat > task-def-green.json <<EOF
{
  "family": "ecommerce-bluegreen",
  "networkMode": "awsvpc",
  "requiresCompatibilities": ["FARGATE"],
  "cpu": "256",
  "memory": "512",
  "executionRoleArn": "${EXECUTION_ROLE_ARN}",
  "taskRoleArn": "${TASK_ROLE_ARN}",
  "containerDefinitions": [{
    "name": "app",
    "image": "${AWS_ACCOUNT_ID}.dkr.ecr.${AWS_REGION}.amazonaws.com/${ECR_REPOSITORY}:${IMAGE_TAG}",
    "essential": true,
    "portMappings": [{
      "containerPort": 8080,
      "protocol": "tcp"
    }],
    "environment": [
      {"name": "APP_VERSION", "value": "green"},
      {"name": "ENVIRONMENT", "value": "production"},
      {"name": "AWS_REGION", "value": "${AWS_REGION}"},
      {"name": "DB_HOST", "value": "${DB_ENDPOINT}"},
      {"name": "DB_PORT", "value": "5432"},
      {"name": "DB_NAME", "value": "ecommerce"}
    ],
    "secrets": [
      {
        "name": "DB_USER",
        "valueFrom": "${DB_SECRET_ARN}:username::"
      },
      {
        "name": "DB_PASSWORD",
        "valueFrom": "${DB_SECRET_ARN}:password::"
      }
    ],
    "logConfiguration": {
      "logDriver": "awslogs",
      "options": {
        "awslogs-group": "/ecs/ecommerce-bluegreen",
        "awslogs-region": "${AWS_REGION}",
        "awslogs-stream-prefix": "ecs"
      }
    },
    "healthCheck": {
      "command": ["CMD-SHELL", "curl -f http://localhost:8080/health || exit 1"],
      "interval": 30,
      "timeout": 5,
      "retries": 3,
      "startPeriod": 60
    }
  }]
}
EOF

# Register the task definition
aws ecs register-task-definition --cli-input-json file://task-def-green.json

This JSON tells ECS everything about how to run your container:

• Which Docker image to use (the v2.0.0 we just built)

• How much CPU/memory to allocate (256 CPU units = 0.25 vCPU)

• Environment variables (notice APP_VERSION is set to "green")

• Secrets (database credentials pulled from AWS Secrets Manager)

• Health check configuration (curl the /health endpoint every 30 seconds)

• Logging configuration (send logs to CloudWatch)

Key detail: The APP_VERSION environment variable is how the application knows whether to behave as blue or green. Same codebase, different behavior based on configuration.

Step 6: Execute Blue-Green Deployment

Alright, now it’s time to create AppSpec and trigger the deployment:

TASK_DEF_ARN=$(aws ecs describe-task-definition \
  --task-definition ecommerce-bluegreen \
  --query 'taskDefinition.taskDefinitionArn' \
  --output text)

cat > appspec.json <<EOF
{
  "version": 0.0,
  "Resources": [{
    "TargetService": {
      "Type": "AWS::ECS::Service",
      "Properties": {
        "TaskDefinition": "${TASK_DEF_ARN}",
        "LoadBalancerInfo": {
          "ContainerName": "app",
          "ContainerPort": 8080
        }
      }
    }
  }]
}
EOF

# Deploy
APPSPEC=$(cat appspec.json | jq -c .)
aws deploy create-deployment \
  --application-name ecommerce-bluegreen \
  --deployment-group-name ecommerce-bluegreen-deployment-group \
  --deployment-config-name CodeDeployDefault.ECSLinear10PercentEvery3Minutes \
  --description "Blue-green deployment to structured address schema" \
  --cli-input-json "{
    \"revision\": {
      \"revisionType\": \"AppSpecContent\",
      \"appSpecContent\": {
        \"content\": $(echo \"$APPSPEC\" | jq -Rs .)
      }
    }
  }"

DEPLOYMENT_ID=$(aws deploy list-deployments \
    --application-name ecommerce-bluegreen \
    --deployment-group-name ecommerce-bluegreen-deployment-group \
    --query 'deployments[0]' --output text)

Monitor the deployment:

# Watch status
watch -n 10 "aws deploy get-deployment --deployment-id $DEPLOYMENT_ID \
    --query 'deploymentInfo.status' --output text"

# Monitor traffic distribution
while true; do
    echo "Production: $(curl -s $ALB_URL/health | jq -r '.version')"
    echo "Test: $(curl -s $TEST_URL/health | jq -r '.version')"
    sleep 30
done

The deployment shifts 10% of traffic every 3 minutes, completing in 30 minutes.

Step 7: Validate Green Environment

After the deployment begins, you need to validate that the green environment is functioning correctly with the new structured address format before allowing production traffic to reach it.

The CodeBuild dashboard below shows the Traffic migration and Deployment status:

We can also test through the test listener (port 8080), which provides isolated access to green tasks:

# Test new structured address API
curl -X POST $TEST_URL/api/customers \
    -H "Content-Type: application/json" \
    -d '{
      "name": "Jane Smith",
      "email": "jane@example.com",
      "address": {
        "street": "456 Oak Ave",
        "city": "Los Angeles",
        "state": "CA",
        "zip": "90001"
      }
    }' | jq

curl $ALB_URL/api/customers | jq

What you're validating:

• The green environment accepts the new structured address format

• Data is correctly written to both new columns (street_address, city, state, zip_code) and the old address column for backwards compatibility

• The API response matches expectations for the new schema

• Existing data from blue environment is still accessible and readable

If any of these tests fail, you can stop the deployment before production traffic reaches green, preventing customer impact.

Step 8: Post-Deployment Validation

Once CodeDeploy completes the traffic shift, all production requests route to green. This is your opportunity to verify that the deployment was successful and that the new version is handling real production traffic correctly.

# Verify all production traffic goes to green
# Running this multiple times confirms consistent routing
for i in {1..10}; do
    curl -s $ALB_URL/health | jq -r '.version'
done
# Expected output: "green" for all 10 requests

# Test complete CRUD operations with the new API
# Create a customer with structured address
CUSTOMER_ID=$(curl -s -X POST $ALB_URL/api/customers \
    -H "Content-Type: application/json" \
    -d '{"name": "Test User", "email": "test@example.com",
         "address": {"street": "789 Test St", "city": "Test City", 
         "state": "TX", "zip": "75001"}}' | jq -r '.id')

# Read the customer back to verify data persistence
curl $ALB_URL/api/customers/$CUSTOMER_ID | jq

# Update the customer to test modification
curl -X PUT $ALB_URL/api/customers/$CUSTOMER_ID \
    -H "Content-Type: application/json" \
    -d '{"address": {"street": "999 Updated Ave", "city": "Test City", 
         "state": "TX", "zip": "75001"}}' | jq

# Delete the test customer for cleanup
curl -X DELETE $ALB_URL/api/customers/$CUSTOMER_ID

What you're validating:

• Traffic routing is 100% to green with no requests reaching blue

• Create operations work with the new structured address format

• Read operations return correct data with proper address structure

• Update operations successfully modify existing records

• Delete operations work without errors

• The application correctly writes to both new columns and old address column (enabling potential rollback)

Check your CloudWatch logs and metrics during this validation period for any unexpected errors, increased latency, or database connection issues.

Step 9: Contract Phase (After 24-72 Hours)

This is the final phase of expand-contract. We're removing the old address column now that we're confident green is stable. This is the point of no return.

CRITICAL: Only proceed after green has been stable for your confidence period!

# Backup database first
aws rds create-db-snapshot \
    --db-instance-identifier ecommerce-bluegreen-db \
    --db-snapshot-identifier pre-contract-$(date +%Y%m%d-%H%M%S)

# Wait for snapshot
aws rds wait db-snapshot-completed \
    --db-snapshot-identifier pre-contract-$(date +%Y%m%d-%H%M%S)

# Run contract migration
psql -h $DB_ENDPOINT -U dbadmin -d ecommerce -f /tmp/002_contract_address.sql

# Verify old column is gone
psql -h $DB_ENDPOINT -U dbadmin -d ecommerce -c "\d customers"

The contract migration (migrations/002_contract_address.sql) removes the old address column.

Why wait 24-72 hours: You want to be absolutely certain green is stable before making irreversible changes. During this waiting period:

• All your monitoring should show green performing normally

• You've seen the system handle multiple daily traffic patterns (morning peak, evening peak, overnight)

• Weekly batch jobs have run successfully

• You've verified third-party integrations work

• No unusual errors or performance degradation

It’s important to snapshot first because once you drop that column, there's no undo button. The snapshot is your safety net. If you discover a critical issue after contracting, you can restore this snapshot and get back to a state where rollback is possible. Without it, you're gambling.

What the contract migration does:

-- migrations/002_contract_address.sql
BEGIN;
ALTER TABLE customers DROP COLUMN address;
COMMIT;

It's simple but permanent. The old address column is gone. The Blue environment will no longer work with this database, as it expects that column to exist. This is fine because blue has been decommissioned (no traffic, tasks terminated).

What to update: You should also deploy version 3 of your application that removes the dual-write logic. Version 2 (green) is still writing to both the new columns and the old address column. Version 3 can stop wasting cycles writing to a column that no longer exists.

The contract migration (migrations/002_contract_address.sql) removes the old address column. Your migration is now complete!

Rollback Strategies

During Deployment (Safe Window)

Use this strategy when you detect issues during the traffic shift, before all traffic has moved to green. CodeDeploy is still managing the deployment, which means it can automatically revert traffic distribution to the previous state.

# Immediate rollback
aws deploy stop-deployment \
    --deployment-id $DEPLOYMENT_ID \
    --auto-rollback-enabled

You should use this strategy when you notice increased error rates, degraded performance, or functional issues during the canary or linear traffic shift. CodeDeploy automatically shifts all traffic back to blue, and green tasks are terminated. This is the safest and fastest rollback option.

This works because the database still contains the old address column (expand phase), so blue can function normally. No data has been lost or made incompatible.

After Deployment (Before Contract)

Use this when the deployment completed successfully, but you discover issues hours or days later during the monitoring period, before you've run the contract migration. Both blue and green environments still exist, and the database supports both schemas.

# Manual listener update
aws elbv2 modify-listener \
    --listener-arn $(terraform output -raw alb_listener_arn) \
    --default-actions Type=forward,TargetGroupArn=$(terraform output -raw blue_target_group_arn)

Or use the provided script:

cd scripts
./rollback.sh

Use this when you discover bugs in green that weren't caught during initial testing, business metrics show unexpected changes (conversion rates drop, customer complaints increase), or third-party integration issues emerge.

This works because the database still has both old and new schema elements. Blue tasks still exist and can serve traffic immediately. Because green was writing to both old and new columns, blue sees all the latest data.

With this, the traffic immediately shifts from green back to blue. Green continues running for observability, but serves no traffic. You can debug green in place without customer impact.

After Contract Phase

Use this as a last resort when you've already removed the old address column, and blue can no longer function with the current database schema. This is significantly more complex and time-consuming than the previous two strategies.

# Restore from snapshot
aws rds restore-db-instance-from-db-snapshot \
    --db-instance-identifier ecommerce-bluegreen-db-restored \
    --db-snapshot-identifier pre-contract-YYYYMMDD-HHMMSS

Only use this strategy when you discover a critical, production-breaking issue after the contract phase, and you have no other option but to return to the previous version.

Why it's painful:

• Database restore takes 10-30 minutes depending on size

• You lose all data written after the snapshot was taken

• Requires updating connection strings to point to the restored instance

• Need to re-deploy blue environment

• Must communicate downtime to users

This is why you wait 24-72 hours before contracting, and take a snapshot immediately before the contract migration. The lengthy waiting period allows you to catch most issues while the safer rollback strategies are still available.

Monitoring During Deployments

Essential Metrics

During a blue-green deployment, you need to monitor both environments simultaneously to detect issues early and make informed decisions about proceeding or rolling back.For each target group (blue and green), track these CloudWatch metrics:

1. TargetResponseTime

Measures latency from when the load balancer sends a request to when it receives a response. You're looking for sudden spikes or gradual degradation. Green should have similar response times to blue (within 10-20%). If green's latency is significantly higher, you may have performance regressions, inefficient queries with the new schema, or resource constraints.

2. RequestCount

Shows traffic volume hitting each target group. During the deployment, you should see blue's count decreasing while green's increases proportionally. If the numbers don't add up (total requests drop significantly), users might be experiencing errors and not retrying. If green receives traffic but shows zero requests, health checks might be failing.

3. HTTPCode_Target_5XX_Count

Server errors indicate application problems. Even a single 5XX error during deployment warrants investigation. Green should have zero 5XX errors during the initial traffic shift. Any errors could indicate incompatibility issues with the new schema, missing environment variables, or database connection problems.

4. DatabaseConnections (from RDS metrics):

Shows active database connections from both environments. Watch for connection pool exhaustion, which manifests as a sudden spike or plateau at your max connections limit. If green uses more connections than blue did, you might have connection leaks or inefficient connection handling in the new code.

5. CPUUtilization

Monitor both ECS task CPU and RDS CPU. Green tasks should use similar CPU to blue tasks for the same request volume. Higher CPU might indicate less efficient code or more complex queries. RDS CPU spikes during deployment often indicate poorly optimized new queries or missing indexes for the new schema.

What to expect:

• First 5-10 minutes: Green receives 10% traffic, metrics should closely match blue's baseline

• 15-20 minutes: Green at 30-50% traffic, both environments should show stable metrics

• 25-30 minutes: Green at 100% traffic, metrics should stabilize at historical levels

• Any divergence from these patterns warrants stopping the deployment and investigating

Custom application metrics: Beyond infrastructure metrics, monitor business-critical metrics like checkout completion rates, API success rates, and user sign-up flows. Sometimes technical metrics look fine but user-facing functionality is broken.

Best Practices

Test Migrations in Staging

Always run your database migrations against a staging environment that mirrors production scale and complexity before touching production. Copy a recent production snapshot to staging and execute your expand migration there first.

Why this matters: Migrations that work fine on small datasets can timeout or lock tables on production-scale data. You might discover that adding an index to a 50-million-row table takes 2 hours, or that your column population query needs optimization.

What to test:

• Migration execution time (should complete in seconds/minutes, not hours)

• Table locks and their impact (can reads/writes continue during migration?)

• Query performance with new schema (are your indexes still effective?)

• Rollback procedures (can you undo the migration if needed?)

Use Migration Tools

Don't write raw SQL migrations manually. Use Flyway, Liquibase, Alembic (for Python), or your framework's built-in migration tools (Rails migrations, Django migrations, Entity Framework migrations).

Why this matters: Migration tools provide version tracking, rollback capabilities, checksums to prevent tampering, and a standardized way to manage schema changes across environments.

Configure Health Checks Properly

Your health check endpoint should verify that the application can actually function, not just that the process is running. A comprehensive health check validates database connectivity, schema compatibility, and dependent service availability.

@app.route('/health')
def health_check():
    checks = {
        'database': check_database(),
        'schema': check_schema_compatibility(),
        'cache': check_cache_connection()
    }

    if all(checks.values()):
        return jsonify(checks), 200
    else:
        return jsonify(checks), 503

def check_schema_compatibility():
    """Verify expected schema elements exist"""
    try:
        result = db.query("""
            SELECT column_name 
            FROM information_schema.columns 
            WHERE table_name = 'customers'
            AND column_name IN ('street_address', 'city', 'state', 'zip_code')
        """)
        return len(result) == 4
    except:
        return False

For ALB health checks specifically, make sure you configure appropriate thresholds in your target group settings. A healthy threshold of 2 means the target must pass 2 consecutive health checks before receiving traffic. An unhealthy threshold of 3 means it must fail 3 consecutive checks before being removed. Set your interval to 30 seconds and timeout to 5 seconds to balance responsiveness with stability.

# Terraform configuration for ALB health checks
resource "aws_lb_target_group" "green" {
  health_check {
    enabled             = true
    healthy_threshold   = 2
    unhealthy_threshold = 3
    timeout             = 5
    interval            = 30
    path                = "/health"
    matcher             = "200"
  }
}

This configuration ensures that ECS tasks aren't marked healthy prematurely (preventing traffic to broken tasks) while also not being overly sensitive to transient issues (preventing unnecessary task replacements).

Plan the Contract Phase

The contract phase is irreversible, so treat it with appropriate caution. Wait a minimum of 24-72 hours after green deployment before removing old schema elements. This waiting period isn't arbitrary: it ensures you've observed the system under various conditions.

What to verify before contracting:

• Green has handled multiple daily traffic patterns (morning rush, evening peak, overnight batch jobs)

• All scheduled jobs and cron tasks have run successfully with the new schema

• Weekly reports or analytics pipelines have completed

• Third-party integrations (payment processors, shipping APIs, analytics tools) are working

• No unusual error patterns in logs

• Business metrics (conversions, sign-ups, purchases) remain stable

• Customer support hasn't reported related issues

The pre-contract checklist:

# 1. Create a final snapshot
aws rds create-db-snapshot \
    --db-instance-identifier ecommerce-bluegreen-db \
    --db-snapshot-identifier pre-contract-$(date +%Y%m%d-%H%M%S)

# 2. Document current state
echo "Green tasks: $(aws ecs describe-services --cluster ecommerce --services ecommerce-green | jq '.services[0].runningCount')"
echo "Error rate: $(aws cloudwatch get-metric-statistics --namespace AWS/ApplicationELB --metric-name HTTPCode_Target_5XX_Count --start-time $(date -u -d '1 hour ago' +%Y-%m-%dT%H:%M:%S) --end-time $(date -u +%Y-%m-%dT%H:%M:%S) --period 3600 --statistics Sum)"

# 3. Notify team
echo "Running contract migration at $(date)"

# 4. Run migration
psql -h $DB_ENDPOINT -U dbadmin -d ecommerce -f migrations/002_contract_address.sql

# 5. Verify
psql -h $DB_ENDPOINT -U dbadmin -d ecommerce -c "\d customers"

Version Your APIs

When changing data formats, maintain backward compatibility by supporting both old and new API versions simultaneously. This allows API consumers (mobile apps, third-party integrations, other services) to migrate at their own pace without coordinating releases.

# Support both API versions during transition
@app.route('/api/v1/customers/<id>')
def get_customer_v1(id):
    customer = Customer.find(id)
    return jsonify({
        'id': customer.id,
        'name': customer.name,
        'address': customer.address  # Old format
    })

@app.route('/api/v2/customers/<id>')
def get_customer_v2(id):
    customer = Customer.find(id)
    return jsonify({
        'id': customer.id,
        'name': customer.name,
        'address': {  # New structured format
            'street': customer.street_address,
            'city': customer.city,
            'state': customer.state,
            'zip': customer.zip_code
        }
    })

To implement this, you can initially deploy both endpoints with blue-green. Then monitor usage of v1 endpoint over time. Once v1 traffic drops below 1% (meaning clients have migrated), deprecate it formally. Remove v1 endpoint in a subsequent release, not during the blue-green deployment itself.

Announce the new API version to consumers with a migration timeline. Give them 2-3 months to update their integrations. Send reminder emails at the halfway point and 2 weeks before v1 shutdown.

Monitor Both Environments

During the transition period, both blue and green are production environments serving real traffic. Monitor them separately to detect version-specific issues.

Set up separate CloudWatch dashboards for blue and green target groups with the same metrics arranged identically. This makes it easy to spot differences at a glance. If green's response time is 200ms while blue's is 50ms, that's a red flag.

Alert on metric divergence

Create alarms that trigger when green's metrics deviate significantly from blue's baseline. For example, if green's error rate is more than 2x blue's historical average, trigger an alert. If green's database query time is 50% higher, investigate before shifting more traffic.

Log aggregation

Ensure logs from both environments are tagged with their version (environment: blue or environment: green) so you can filter and compare them. Use CloudWatch Insights queries to spot patterns.

When NOT to Use Blue-Green

Blue-green isn't always the right choice. Avoid it when you have:

• Very large database migrations: If your migration takes hours or requires significant locks, use a traditional maintenance window.

• Highly stateful applications: Real-time collaboration tools or WebSocket applications with complex in-memory state may need rolling deployments instead.

• Cost constraints: Running two environments doubles costs. Consider canary deployments for cost-sensitive applications.

• Complex data model redesigns: Use the strangler fig pattern to gradually migrate functionality to a new service.

Alternative Deployment Strategies

Canary Deployments

Route a small percentage (5-10%) to the new version:

{
  "trafficRouting": {
    "type": "TimeBasedCanary",
    "timeBasedCanary": {
      "canaryPercentage": 10,
      "canaryInterval": 5
    }
  }
}

Rolling Deployments

Gradually replace old tasks with new ones:

{
  "deploymentConfiguration": {
    "maximumPercent": 200,
    "minimumHealthyPercent": 100
  }
}

Cleanup

After you've successfully completed your blue-green deployment, validated the green environment, and run the contract phase, you need to clean up the AWS resources to avoid unnecessary costs and resource sprawl.

What you're removing:

• The entire infrastructure stack (VPC, subnets, NAT gateways, load balancer, ECS cluster, RDS database, and all associated resources)

• This is appropriate for a tutorial/testing scenario where you deployed everything from scratch

Important considerations before cleanup:

• Ensure you have backups if you need to reference any data later

• Export any logs or metrics you want to retain

• Document lessons learned from the deployment

• Verify no production traffic is still using these resources

cd terraform

# Terraform will prompt you to confirm with "yes"
# Review the destruction plan carefully before confirming
terraform destroy  # Takes ~10-15 minutes

Partial cleanup: If you want to keep certain resources (like RDS snapshots for reference), you can remove them from Terraform state before destroying:

# Remove RDS from Terraform management before destroying
terraform state rm aws_db_instance.main
terraform destroy  # Now destroys everything except RDS

For production environments, you would NOT destroy everything. Instead, you'd decommission the blue environment specifically after confirming green is stable:

# Production scenario - remove only blue environment
terraform destroy -target=aws_ecs_service.blue
terraform destroy -target=aws_lb_target_group.blue

Conclusion

Blue-green deployments with databases require careful planning, but the expand-contract pattern makes it manageable.

Here are some key takeaways:

1. Use expand-contract as default – Maintains backwards compatibility and safe rollbacks.

2. Externalize state – Sessions, caches, and storage should use external services.

3. Plan for three phases – Don't rush to the contract phase.

4. Test everything in staging – Mirror production scale and complexity.

5. Monitor aggressively – Track technical and business metrics for both environments.

6. Know when to use alternatives – Blue-green isn't always the answer.

7. Document rollback procedures – Everyone should know the rollback process before deployment.

The expand-contract pattern requires more work upfront, but this investment pays dividends in reduced risk and maintained uptime. With the strategies and complete implementation provided here, you can successfully deploy even complex, stateful applications with confidence.

As always, I hope you enjoyed this guide and learned something. If you want to stay connected or see more hands-on DevOps content, you can follow me on LinkedIn.

For more practical hands-on Cloud/DevOps projects like this one, follow and star this repository: Learn-DevOps-by-building.

Further Resources

• Complete Code: github.com/Caesarsage/bluegreen-deployment-ecs

• Learn DevOps by Building: GitHub repo

• AWS ECS Blue/Green Documentation: AWS Docs

• AWS CodeDeploy for ECS: AWS Docs

There are many rules that govern how entities interact with each other, to make things work. For example, a student can’t take a course that the school doesn’t offer. A soccer player can’t have a jersey number less than 1 or greater than 99. And a car must always have a plate number.

Relational databases are also able to represent and enforce these rules using constraints. And in this article, I’ll explain how constraints work with practical examples.

Whether you’re a beginner or just looking to refresh your knowledge, this article will help you learn the essentials. If you need some more background, you can read this article on the basics of relational databases before continuing.

What We’ll Cover:

1. What is a Relational Database Constraint?

2. Types of Relational Database Constraints

   • Inherent Model-based Constraints (Implicit Constraints)

   • Schema-based Constraints (Explicit Constraints)

   • Application-based constraints (Semantic constraints)

3. Testing Constraints

   • How to Delete a Record

4. Summary

What is a Relational Database Constraint?

Relational database constraints are a set of database rules that are used to define or determine what set of values are acceptable or valid in a database. They’re usually based on the many rules of the real world.

They are put in place to:

• Ensure data accuracy: only values that would be acceptable in real life should be acceptable in the database. Learn more about data accuracy here.

• Ensure data integrity: values in the database remain correct, accurate, complete, and valid as long as the database exists. Learn more about data integrity here.

• Ensure data consistency: values always maintain same agreed form throughout their lifetime.

These rules limit what can be entered into a database or what can be deleted from it. They also limit data update to ensure validity after original creation.

These integrity constraints help enforce business rules on data in the tables to ensure the accuracy and reliability of the data. - AWS

Types of Relational Database Constraints

There are many ways to group or categorise database constraints, depending on how they’re applied or what they’re preventing. This article focuses on three popular types:

• Inherent model-based constraints (implicit constraints)

• Schema-based constraints (explicit constraints)

• Application-based constraints (semantic constraints)

Inherent Model-based Constraints (Implicit Constraints)

These rules are the base rules that come with the database and are enforced by the DMBS. Some of these rules are:

• Each row must be unique. This is with or without a UNIQUE or PRIMARY KEY constraint.

• Columns can only store one value at a time. The value of a field like age will always be one value like 23, not 23 and 35.

• Each column name in a table must be unique.

• Columns exist for all rows. Every row will have the same number of columns. For some of the rows, the data might be empty, but the column will always be there.

Schema-based Constraints (Explicit Constraints)

These constraints are expressed by the developer or database designer on database creation. They’re expressed directly in the database schemas, using the DDL.

These can be further broken down into:

• Domain constraints

• Key constraints

  • Entity integrity constraint (Primary key)

  • Unique constraint (Unique key)

  • Referential integrity constraint (Foreign key)

1. Domain Constraints

These are used to define a range or set of possible values for an attribute of a database table. They help ensure that column values are valid and consistent by defining acceptable data types, formats, and ranges for an attribute. This prevents incorrect or illogical data entry and maintains data integrity.

You can define them simply by specifying a data type that the values must follow. For example, the age of a person can only be a number, or could be a number between 18-60 if the database is for a company, or a number between 5-65 if it’s for an amusement park.

The database will enforce this rule by rejecting age values outside of the given range or type. The DDL for the age would look like this:

CREATE TABLE people (
    age INT, -- Any integer value is allowed
    age INT CHECK (age BETWEEN 18 AND 60), -- Only allows ages between 18 and 60
    age INT CHECK (age BETWEEN 5 AND 65) -- Only allows ages between 5 and 65
);

The INT means only integer values are accepted, and the CHECK is used with the BETWEEN and AND keywords to specify the sub-domain or range of values.

Other data types in SQL include: CHAR, BIT, DATE, VARCHAR and so on. You can use all of them to define the acceptable domain for database values.

CREATE TABLE employees (
    employee_id INT,
    name VARCHAR(100),
    age INT CHECK (age BETWEEN 18 AND 60)
);

As well as defining a range of acceptable values, you can also define the optionality of an attribute using the NOT NULL keyword. You’d use this in cases where the data must exist and must also be within the given range.

CREATE TABLE employees (
    employee_id INT NOT NULL,
    name VARCHAR(100) NOT NULL,
    age INT CHECK (age BETWEEN 18 AND 60)
);

In this example, every employee record needs to have an employee_id and a name but not an age. This works for real life situations where, although the range of values is known, the actual value is either unknown or doesn’t exist. An example would be the minor course of study of a student at a university – many students only have majors, and as such, the minor course of study will be empty (NULL) for those students.

2. Entity integrity constraint (Primary key)

This ensures that no primary key is NULL. The primary key is the one attribute or set of attributes that must be unique to each row in the database. It’s the primary value that uniquely identifies the rest of the data. This means that every row in the database will remain uniquely identifiable with a primary key.

A NULL primary key means that rows will not be unique, or identifiable, and the database can contain duplicates. Without the primary key, we can’t have data consistency.

For example, in a school, every student will have a unique student id number with which they can always be distinguished from other students. The government uses methods like passport numbers or tax ids to uniquely identify citizens.

In our example, it’s impossible to be a student without a student id number. You can implement this constraint by using the PRIMARY KEY keyword.

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(100),
    age INT CHECK (age BETWEEN 18 AND 60)
);

3. Unique constraint (Unique key)

This is similar to the Entity integrity constraint in that it only accepts unique values – but it’s different in that it accepts NULL values.

An example of this would be in a students table, every student must have a student id number that uniquely identifies them. This number cannot be NULL, and it must the unique. Students can also have an email address that the school can reach them on. This email must be unique for each student. But, not every student has to have an email. So the condition is: “If the value exists, it must be unique”.

You can implement this constraint using the UNIQUE keyword, like this:

CREATE TABLE students (
    student_id INT PRIMARY KEY, -- Must exist and must be unique
    email VARCHAR(255) UNIQUE -- Can be NULL, but must be unique if provided
);

4. Referential integrity constraint (Foreign key)

This constraint guards the relationship between two related tables. It is used to maintain consistency in the relationship. It requires that data from one table, A, being referenced in another table, B, must exist in the original table, A. For example, a student can’t register for a course the school doesn’t have.

To enforce this, the FOREIGN KEY keyword is used with the REFERENCES to define the table being referenced, and what attribute is being referred to.

CREATE TABLE courses (
    course_id INT PRIMARY KEY,
    course_name VARCHAR(100) NOT NULL
);

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    student_name VARCHAR(100) NOT NULL,
    course_id INT,
    FOREIGN KEY (course_id) REFERENCES courses(course_id)
);

In this example, every value provided in the course_id of the students must be in the courses table.

Application-based constraints (Semantic constraints)

These can also be called business rules. They can’t be directly expressed in the database schema, so they’re often implemented the application layer instead.

These are logical constraints, like saying “a course cannot have more than 30 students enrolled” or “a customer cannot place an order if it would exceed their credit limit”.

These rules are best implemented in the application, because it would be too complex (or sometimes impossible) to implement them on the database itself.

Testing Constraints

To demonstrate the constraints we’ve discussed here, let’s look at this sample school database setup:

CREATE TABLE courses (course_id INT PRIMARY KEY, course_name VARCHAR(100) NOT NULL, max_students INT CHECK (max_students > 0));

CREATE TABLE students (student_id INT PRIMARY KEY, student_name VARCHAR(100) NOT NULL, email VARCHAR(100) UNIQUE, age INT CHECK (age BETWEEN 5 AND 25));

CREATE TABLE enrollments (
    enrollment_id INT PRIMARY KEY,
    student_id INT NOT NULL,
    course_id INT NOT NULL,
    enrollment_date DATE NOT NULL,
    FOREIGN KEY (student_id) REFERENCES students (student_id),
    FOREIGN KEY (course_id) REFERENCES courses (course_id)
);

This shows the creation of a sample school database with three tables: courses, students, and enrollments.

The courses table includes a primary key for course IDs, course names, and a constraint ensuring that the maximum number of students is greater than zero. The students table contains a primary key for student IDs, student names, unique email addresses, and an age constraint between 5 and 25. The enrollments table links students to courses with primary keys for enrollment IDs and foreign keys referencing the students and courses tables, along with a non-null enrollment date.

At this point, the tables are created, and setup with the constraints guiding them.

Now we’ll test a few queries:

1. Insert courses, Mathematics and History, into the courses table:

INSERT INTO courses (course_id, course_name, max_students) VALUES (1, 'Mathematics', 30);
INSERT INTO
    courses (course_id, course_name, max_students)
VALUES
    (2, 'History', 25);

The query works perfectly, as the records get inserted.

2. Insert students, Alice and Bob, into the students table:

INSERT INTO
    students (student_id, student_name, email, age)
VALUES
    (101, 'Alice', 'alice@example.com', 20);

INSERT INTO
    students (student_id, student_name, email, age)
VALUES
    (102, 'Bob', NULL, 18);

The query works perfectly, as the records get inserted.

3. Enroll Alice into Mathematics:

INSERT INTO
    enrollments (enrollment_id, student_id, course_id, enrollment_date)
VALUES
    (1001, 101, 1, '2026-01-14');

The query works perfectly, as the record gets inserted.

4. Insert a new student, Charlie, into the students table:

INSERT INTO
    students (student_id, student_name, email, age)
VALUES
    (103, 'Charlie', 'charlie@example.com', 30);

This fails because Charlie has an age value of 30, which is outside of the specified range of age INT CHECK (age BETWEEN 5 AND 25). The record of Charlie never gets added.

Here’s a list of some other queries that will fail:

INSERT INTO
    students (student_id, student_name, email, age)
VALUES
    (104, 'David', 'alice@example.com', 19); -- Fails for duplicate email

INSERT INTO
    students (student_id, student_name, email, age)
VALUES
    (NULL, 'Evra', 'evra@example.com', 20); -- Fails for NULL primary key

INSERT INTO
    enrollments (enrollment_id, student_id, course_id, enrollment_date)
VALUES
    (1002, 999, 1, '2026-01-14'); -- Fails for invalid student reference

In each case, the DBMS will provide a reason for the rejection or failure.

5. Delete Bob from the students table:

DELETE FROM students
WHERE
    student_id = 102;

The query works perfectly, as the record gets deleted.

6. Delete Alice from the students table:

DELETE FROM students
WHERE
    student_id = 101; -- Fails for referential integrity constraint

This fails because Alice, with student_id of 101, has an enrollment record in the enrollments table. Deleting the record would mean there will be an enrollment record for a non-existent student which should not be possible.

How to Delete a Record

In some cases, you do want to delete a record, even though it has records tied to it. There are two main ways to go about this:

CASCADE

You can use this to define situations where, when a parent record is deleted, the child records cannot exist. All dependent (child) records in other tables are automatically deleted. You can use this to ensure that all enrollment records are deleted when the course is no longer available, or when a student is no longer in the school.

CREATE TABLE enrollments (enrollment_id INT PRIMARY KEY, student_id INT NOT NULL, course_id INT NOT NULL, FOREIGN KEY (course_id) REFERENCES courses (course_id) ON DELETE CASCADE);

DELETE FROM courses
WHERE
    course_id = 1;

SET NULL or SET DEFAULT

You can use these methods to define situations where child records can still exist without the parent. All dependent (child) records in other tables are automatically set to null or automatically set to a defined default.

A useful example is if a school had a mentor assigned to students, when the mentor leaves the school, you don’t want to delete the students – you want to set the mentor to NULL or a default staff.

CREATE TABLE teachers (teacher_id INT PRIMARY KEY, teacher_name VARCHAR(100) NOT NULL);

CREATE TABLE students (student_id INT PRIMARY KEY, student_name VARCHAR(100) NOT NULL, mentor_id INT, FOREIGN KEY (mentor_id) REFERENCES teachers (teacher_id) ON DELETE SET NULL);

7. Update Alice’s details. Change her email to a new one, and increase her age:

UPDATE students
SET
    email = 'alice.new@example.com',
    age = 22
WHERE
    student_id = 101;

The query works perfectly, as the record gets updated.

8. Update Alice’s age to 30:

UPDATE students
SET
    age = 30
WHERE
    student_id = 101;

This fails just like the 4th test for the same reason: the age is out of the stated range.

Here’s another query that will fail:

UPDATE enrollments
SET
    course_id = 999
WHERE
    enrollment_id = 1001;

This will fail because the new course_id does not exist in the courses table.

Summary

Databases are a pivotal part of everyday modern technology, and understanding their fundamental concepts can open doors to building and managing more accurate databases.

This article introduced you to what relational database constraints are, some of the different types, and how they’re enforced and violated. You should now have the essential knowledge to navigate the world of database constraints confidently.

If you’re curious to learn more, connect with me on LinkedIn, Twitter, or GitHub. Let’s continue this journey together toward mastering database systems!

Each certification is filled with hundreds of hours worth of interactive lessons, workshops, labs, and quizzes.

How Does the New Relational Databases Certification Work?

The new Relational Databases certification will teach you core concepts including Bash scripting, SQL, Git, and more.

The certification is broken down into several modules that include lessons, workshops, labs, review pages, and quizzes to ensure that you truly understand the material before moving onto the next module.

The lessons are your first exposure to new concepts. They provide crucial theory and context for how things work in the software development industry.

At the end of each lesson, there will be three comprehension check questions to test your understanding of the material from the lesson.

After the lesson blocks, you will do the workshops. These workshops are guided step-based projects that provide you with an opportunity to practice what you have learned in the lessons.

These workshops will not be done inside the regular freeCodeCamp editor in the browser. Instead you will need to do these workshops in one of three environments:

• GitHub Codespaces: This course runs in a virtual Linux machine using GitHub Codespaces.

• Your own local environment: This course runs in a virtual Linux machine on your computer.

• Ona: This course runs in a virtual Linux machine using Ona.

After the workshop, you will complete a lab which will help you review what you have learned so far. This will give you chance to start building projects on your own, which is a crucial skill for a developer. You will be presented with a list of users stories and will need to pass the tests to complete the lab.

At the end of each module, there is a review page containing a list of all of the concepts covered. You can use these review pages to help you study for the quizzes.

The last portion of the module is the quiz. This is a 20 question multiple choice quiz designed to test your understanding from the material covered in the module. You will need to get 18 out of 20 correct to pass.

Throughout the certification, there will be five certification projects you will need to complete in order to qualify for the exam.

Once you’ve completed all 5 certification projects, you’ll be able to take the 50 question exam using our new open source exam environment. The freeCodeCamp community designed this exam environment tool with two goals: respecting your privacy while also making it harder for people to cheat.

Once you download the app to your laptop or desktop, you can take the exam.

Frequently Asked Questions

Is all of this really free?

Yes. freeCodeCamp has always been free, and we’ve now offered free verified certifications for more than a decade. These exams are just the latest expansion to our community’s free learning resources.

What prevents people from just cheating on the exams?

Our goal is to strike a balance between preventing cheating and respecting people's right to privacy.

We've implemented a number of reliable, yet non-invasive, measures to help prevent people from cheating on freeCodeCamp's exams:

1. For each exam, we have a massive bank of questions and potential answers to those questions. Each time a person attempts an exam, they'll see only a small, randomized sampling of these questions.

2. We only allow people to attempt an exam one time per week. This reduces their ability to "brute force" the exam.

3. We have security in place to validate exam submissions and prevent man-in-the-middle attacks or manipulation of the exam environment.

4. We manually review each passing exam for evidence of cheating. Our exam environment produces tons of metrics for us to draw from.

We take cheating, and any form of academic dishonesty, seriously. We will act decisively.

This said, no one's exam results will be thrown out without human review, and no one's account will be banned without warning based on a single suspicious exam result.

Are these exams “open book” or “closed book”?

All of freeCodeCamp’s exams are “closed book”, meaning you must rely only on your mind and not outside resources.

Of course, in the real world you’ll be able to look things up. And in the real world, we encourage you to do so.

But that is not what these exams are evaluating. These exams are instead designed to test your memory of details and your comprehension of concepts.

So when taking these exams, do not use outside assistance in the form of books, notes, AI tools, or other people. Use of any of these will be considered academic dishonesty.

Do you record my webcam, microphone, or require me to upload a photo of my personal ID?

No. We considered adding these as additional test-taking security measures. But we have less privacy-invading methods of detecting most forms of academic dishonesty.

If the environment is open source, doesn't that make it less secure?

"Given enough eyeballs, all bugs are shallow." – Linus’s Law, formulated by Eric S. Raymond in his book The Cathedral and the Bazaar

Open source software projects are often more secure than their closed source equivalents. This is because a lot more people are scrutinizing the code. And a lot more people can potentially help identify bugs and other deficiencies, then fix them.

We feel confident that open source is the way to go for this exam environment system.

How can I contribute to the Exam Environment codebase?

It's fully open source, and we'd welcome your code contributions. Please read our general contributor onboarding documentation.

Then check out the GitHub repo.

You can help by creating issues to report bugs or request features.

You can also browse open help wanted issues and attempt to open pull requests addressing them.

Are the exam questions themselves open source?

For obvious exam security reasons, the exam question banks themselves are not publicly accessible. :)

These are built and maintained by freeCodeCamp's staff instructional designers.

What happens if I have internet connectivity issues mid-exam?

If you have internet connectivity issues mid exam, the next time you try submit an answer, you’ll be told there are connectivity issues. The system will keep prompting you to retry submitting until the connection succeeds.

What if my computer crashes mid-exam?

If your computer crashes mid exam, you’ll be able to re-open the Exam Environment. Then, if you still have time left for your exam attempt, you’ll be able to continue from where you left off.

Can I take exams in languages other than English?

Not yet. We’re working to add multi-lingual support in the future.

I have completed my exam. Why can't I see my results yet?

All exam attempts are reviewed by freeCodeCamp staff before we release the results. We do this to ensure the integrity of the exam process and to prevent cheating. Once your attempt has been reviewed, you'll be notified of your results the next time you log in to freeCodeCamp.org.

I am Deaf or hard of hearing. Can I still take the exams?

Yes! While some exams may include audio components, we do make written transcripts available for reading.

I am blind or have limited vision, and use a screen reader. Can I still take the exams?

We’re working on it. Our curriculum is fully screen reader accessible. We're still refining our screen reader usability for the Exam Environment app. This is a high priority for us.

I use a keyboard instead of a mouse. Can I navigate the exams using just a keyboard?

This is a high priority for us. We hope to add keyboard navigation to the Exam Environment app soon.

Are exams timed?

Yes, exams are timed. We err on the side of giving plenty of time to take the exam, to account for people who are non-native English speakers, or who have ADHD and other learning differences that can make timed exams more challenging.

If you have a condition that usually qualifies you for extra time on standardized exams, please email support@freecodecamp.org. We’ll review your request and see whether we can find a reasonable solution.

What happens if I fail the exam? Can I retake it?

Yes. You get one exam attempt per week. After you attempt an exam, there is a one-week (exactly 168 hour) “cool-down” period where you cannot take any freeCodeCamp exams. This is to encourage you to study and to pace yourself.

There is no limit to the number of times you can take an exam. So if you fail, study more, practice your skills more, then try again the following week.

Do I need to redo the projects if I fail the exam?

No. Once you’ve submitted a certification project, you do not need to ever submit it again.

You can re-do projects for practice, but we recommend that you instead build some of our many practice projects in freeCodeCamp’s developer interview job search section.

What happens if I already have the old Legacy Responsive Web Design certification? Should I claim the new one?

The new certification has more theory and practice as well as an exam. So if you’re looking to brush up on your skills, then you can go through the new version of this certification.

What will happen to my existing coursework progress on the Full Stack Certification? Does it transfer over to the Responsive Web Design course?

If you’ve already started the Certified Full Stack Developer Curriculum, all of your previously completed work should already be saved there.

To be clear, we’ve copied over all of the coursework from the full stack certification to this newer certification.

Can I still continue with the current Full Stack Developer Certification and just not do the new certification?

We’ve moved the coursework for the Full Stack Developer Certification over and broken it up into smaller certifications. Currently there are seven courses available for you to go through. Here is the complete list:

• Responsive Web Design Certification

• JavaScript Certification

• Frontend Libraries Certification

• Python Certification

• Relational Databases Certification

• Backend JavaScript Certification

• Certified Full Stack Developer Certification

The Certified Full Stack Developer Certification button will remain on the learn page for a short time to give people the opportunity to switch over to the new certifications. Over the next few months, though, this option will disappear.

Will my legacy certifications become invalid?

No. Once you claim a certification, it’s yours to keep.

Also note that we previously announced that freeCodeCamp certifications would have an expiration date and require recertification. We don’t plan to implement this anytime soon. And if we do decide to, we will give everyone at least a year’s notice.

Will the exam be available to take on my phone?

At this time, no. You’ll need to use a laptop or desktop to download the exam environment and take the exam. We hope to eventually offer these certification exams on iPhone and Android.

I have a disability or health condition that is not covered here. How can I request accommodations?

If you need specific accommodations for the exam (for example extra time, breaks, or alternative formats), please email support@freecodecamp.org. We’ll review your request and see whether we can find a reasonable solution.

Anything else?

Good luck working through freeCodeCamp’s coursework, building projects, and preparing for these exams.

Happy coding!

In the early days, when the volume of data was relatively small, engineers prioritized fast data retrieval and stored data in B-tree structures that made searching efficient.

But over time, we started building systems that needed to ingest massive amounts of data like logs, metrics, likes, chats and tweets. This made it necessary to design a storage system that would make writing faster.

One such storage system is the LSM-tree (Log-Structured Merge tree).

In this tutorial, rather than immediately diving into the theoretical concepts of an LSM-Tree Storage system, I’ll take a practical, problem-driven approach. I believe that learning through solving problems is far more effective and engaging than simple memorization of concepts.

By approaching these ideas progressively, my goal is to guide you step by step through real-world engineering challenges and solutions, giving you a front-row seat to the intricacies of building a robust storage system from scratch.

We’ll begin by identifying real-world challenges that arise in database design – like handling write-heavy workloads, ensuring data durability, or managing efficient storage. These challenges will set the stage for each feature and component of LSM-Trees.

Through this method, we’ll explore the foundations of LSM-Tree storage systems and dive deeper into their key components: MemTable, SSTable, Write-Ahead Log (WAL), and Manifest File.

We’ll also examine the Write and Read paths, explore Durability and Crash-Recovery mechanisms, and conclude with one of the most critical processes: Compaction.

By the end of this handbook, you’ll understand not just what these components are but also why they are designed the way they are and how they solve the unique challenges of building modern, high-performance databases.

What We’ll Cover:

1. Prerequisites

2. What is an LSM Tree?

3. Preface: Setting up to Build an LSM-Tree Database

   • Initial Feature Set: Laying the Foundation of the Database System

   • MemTable: In-Memory Data Storage

   • SSTable: Persisting Data for Durability

   • The WAL (Write Ahead Log ): Crash Recovery Made Simple

   • Manifest File: Tracking the State of the Database

   • Update and Delete: Handling Mutability in an Immutable System

   • Compaction: Cleaning Up Stale and Deleted Data

4. Conclusion

   • Complete Code

Prerequisites

While this tutorial is designed to be comprehensive and approachable, it’ll be helpful if you come in with some foundational knowledge in the following areas:

• Programming in Golang: Familiarity with Go syntax, error handling, and standard libraries (example os, encoding/gob, container/heap) will make it easier to work through the implementation examples.

• Basic data structures and algorithms: Concepts such as maps, heaps, some sorting algorithms, and early termination are leveraged throughout the tutorial.

• Understanding persistent storage: Awareness of the differences between in-memory and disk-based storage, as well as sequential versus random read/write operations will be helpful in grasping performance-related trade-offs.

• General database knowledge: If you're familiar with key-value databases or CRUD operations (Create, Read, Update, Delete), you’ll have a head start.

• Concurrency: Basic understanding of threads and concurrency.

While having experience in these areas will deepen your understanding of the concepts and reduce the learning curve, I will provide sufficient detail and practical explanations at every step ensuring you gain the insights necessary to follow along and build your own LSM-tree-based storage engine.

What is an LSM Tree?

A log-structured merge-tree (or LSM tree) is a data structure that makes database writes super fast by recording new data in memory first, then periodically sorting and merging it into larger files on disk.

The “log” in its name refers to the fact that it saves data in a log-structured format (rather than simply storing it). We will come to what those logs are in a little bit.

LSM trees keep appending new data to the existing data, instead of looking for something that exists and updating it. In other words, you don't have to spend any CPU cycles thinking about where to store data – just append it at the end.

An LSM tree also has "tree" in its name, but does it actually store data in a tree? Not really. The “tree” here is mostly an abstract concept. It refers to the hierarchical organization of levels (L0, L1, L2, and so on), not a tree data structure with nodes and pointers. Again, we will come to those levels in a little bit, but for now, let’s just say it makes sense to call it a tree given that it stores data in a leveled fashion.

Just note that there isn't a tangible tree structure in play (like a binary trees or graph) – it’s not node-based storage.

Finally, there is the "merge" part of the name. For now, suffice it to say that you’ll soon see how this storage engine merges data to save storage by avoiding duplication.

Personally, I think that "Log-Structured Merge System" would be clearer than "tree," but "LSM tree" is the established term in the industry, so that's what we'll use.

Preface: Setting up to Build an LSM-Tree Database

Now that we have set the context, lets put this theory into practice and start building our own LSM-tree-based database storage engine from scratch.

To follow along with this tutorial:

• Make sure you have Golang installed on your system. If not, you can download and install it from the official Go website.

• Set up your development environment and create a new Go module for this project by running: go mod init lsm-db

• Keep a code editor or IDE ready to try out the examples.

Initial Feature Set: Laying the Foundation of the Database System

When I’m designing or building a system, I like to think that the system already exists, and I assume that I can just start calling functions that support the features of the system. I’ll follow that pattern here and assume that the following functions of the LSM tree exist and we can invoke those functions from main.go.

db, err := NewDB[string, string](3, 3) // there is a feature to create new with some parameters we will get to
db.Put("a", "apple") // a feature to add key value
db.Delete("a") // a feature to delete a key
val, _ := db.Get("a") // a feature to get value given a key

As we progress through this journey, I’ll introduce essential features such as in-memory storage, flushing data to disk, and handling duplicate keys. We’ll also explore more advanced components, including a Write-Ahead Log (WAL) to ensure crash tolerance, a Manifest file to maintain the database state across application restarts, and a Compaction process to clean up redundant or stale data by merging older SSTables.

By the end of this tutorial, you will gain a clear understanding of how all these components work together to form a robust and efficient LSM-tree-based storage system.

MemTable: In-Memory Data Storage

We’re building a database storage system, so of course you’ll need a way to store data. This means you need some kind of backing storage. This backing storage in an LSM tree is called a MemTable. The "Mem" refers to its in-memory storage. The benefit of in-memory storage is that it’s orders of magnitude faster than storing on disk.

For simplicity, at the core of the MemTable you can use a map (or dictionary depending on the programming language) as the underlying data structure to store key-value pairs. The map allows for fast lookups, insertions, and deletions, making it ideal for in-memory storage where performance is crucial. So the structure for MemTable will look like:

type MemTable[K comparable, V any] struct {
    data map[K]V // this is primary storage map. It's generic so that you
                  // can store any kind of data
}

Above code defines a MemTable struct, where data is a map that acts as the main storage for our key-value pairs. Since the data field is a map, you’ll be able to quickly add, retrieve, or delete values associated with a given key.

You must have noticed something new in the code. The use of <K comparable, V any>. This syntax is Go’s generic types feature, which allows us to write flexible code that can handle different data types.

Generics are a way to write code that is independent of any specific data type. They allow you to write functions and data structures that can work with a string, int, float, or any custom type you define, without sacrificing type safety.

In the above code, K and V are type parameters. They say: "This MemTable can work with any Key type K that is comparable, and any value type V."

Now that you have the MemTable, think of what functions it should provide to its clients. Well, the clients need to be able to save and retrieve values associated with a key, so the following functions would fit naturally:

func (m *MemTable[K, V]) Put(key K, value V) {
    m.data[key] = value
}

func (m *MemTable[K, V]) Get(key K) (V, bool) {
    value, ok := m.data[key]
    var zero V
    if !ok {
        return zero, false
    }
    return value, true
}

The above code has Put and a Get functions – let’s break them down:

• Put: This function allows the client to insert a key-value pair into the MemTable. If the key already exists in the map, its value will be updated with the new value provided as an argument. This is effectively the write operation of our key-value store.

• Get: This function is responsible for retrieving a value associated with a give key from the MemTable. It returns two values, the value itself (of type V) and a boolean (true or false). The boolean indicates whether the key was found in the map. If the key does not exist, the function return a zero value (more on that below) along with false.

Did you notice var zero V?

It's pretty interesting. Think of a situation where we don't get a value from the map – say the key is not there, or something else is wrong. What should the function Get return in that case? Can it return an int (0), or a string "Not found", or some random object (foo)? You don't know anything about the type yet (Generics), so you can't tell it what to return.

In this case, the compiler comes to the rescue. Go has this zero value concept: everything should have a zero value. An int has 0, string has "", bool has false, and pointer, slice, and map have nil. By saying var zero V you are telling the compiler, "I don't know the type yet, you figure out the type while compiling and put that type here as the return type." Neat!

I missed one thing though: how would a client invoke these functions? Right, we need a way to build the MemTable type.

To construct and initialize a MemTable, we can use a factory function: a common programming pattern for creating and returning new objects or instances without directly exposing the underlying implementation details.

func NewMemTable[K comparable, V any]() *MemTable[K, V] {
    return &MemTable[K, V]{
        data: make(map[K]V),
    }
}

Notice how we’ve initialized the data field using the built-in make function. Here’s why we do this:

Go has a built-in function called make, which is used to allocate and initialize slices, maps, and channels. This allocation ensures that they are ready for use without the risk of runtime panics.

You might wonder, why not use the new function to allocate the map? After all, developers coming from other programming backgrounds (like C++ or Java) might expect to use new for all types of memory allocation. But Go differentiates how it manages memory for composite types versus basic/numeric types, and that’s where make comes in.

This distinction matters because the new function only allocates memory for an object and returns a pointer to that memory. The object itself is not initialized, meaning that while the memory is allocated, the map isn’t ready to use. If we try to perform operations (like adding a key-value pair) on a map only allocated using new, it will cause a runtime panic because the map wasn’t correctly initialized.

For example:

m := new(map[string]int) // Allocates a pointer to an uninitialized map
(*m)["a"] = 1            // This will panic because the map is not initialized

On the other hand, make both allocates and initializes the map, ensuring it’s fully functional right away. That’s why the correct way to create a map is:

m1 := make(map[string]int) // Initializes the map properly
m1["a"] = 1                // This works as expected

Now that you have the MemTable which can store data in memory, let's hook it up and use it.

But before that, do you remember at the beginning that I used functional invocations like db.Put and db.Get? Well, what is db? Because we are building a database storage system, it makes more sense to name the interface db instead of MemTable, right? And to be honest, it seems like MemTable is going to be part of the database system, not the whole system, doesn't it?

Even if it's not intuitive at the moment to define something like a DB type, let's just do it. Trust me, it will start to get clearer as we move along. This db type will wrap adding and retrieving data from MemTable.

type DB[K comparable, V any] struct {
    memtable *MemTable[K, V]
}

// factory function for DB type
func NewDB[K comparable, V any]() (*DB[K, V], error) {
    memtable := NewMemTable[K, V]()
    return &DB[K, V]{
        memtable: memtable,
    }, nil
}

Let's just define the Put and Get functions which will invoke corresponding functions in MemTable:

func (db *DB[K, V]) Put(key K, value V) error {
    db.memtable.Put(key, value)
    return nil
}

func (db *DB[K, V]) Get(key K) (V, error) {
    if val, ok := db.memtable.Get(key); ok {
        return val, nil
    }
    var zero V
    return zero, errors.New("key not found")
}

Let's integrate whatever we’ve built so far and run it. To run add below code in main.go and run using go run main.go

db, err := NewDB[string, string]()
if err != nil {
    log.Fatalf("Failed to create DB: %v", err)
}
db.Put("a", "apple")
val, _ := db.Get("a")
log.Printf("Get('a') = %s (should be 'apple')", val)

Look at that, you have built an in-memory database where your clients can store and fetch data from. It’s using generics so you can store any kind of values (int, string, objects).

Now say you shipped this solution and then it crashes. Your clients will lose all the data. Why will this crash? For one thing, memory is limited and at some point you’re going to run out of it. So there are two major problems with just in-memory storage:

1. It's not durable.

2. Unbounded memory usage is going to crash the system.

How do we solve these problems?

Here's a thought: what if we flush the MemTable data to disk at some regular interval? That way we can ensure that MemTable doesn't grow out of bounds. Also if the db crashes, we won’t lose all the data. We’ll still lose the data that hasn’t been flushed yet, but that's way better than losing all of it.

SSTable: Persisting Data for Durability

An SSTable is a sorted string table. I wish they’d called it a "Secondary Storage" table, but historically keys and values were strings – hence sorted string table. An SSTable is a persistent, ordered, and immutable file that stores key-value pairs. It’s a file stored on disk, so it's pretty clear that it’s persistent (durable).

Let’s discuss a couple key features of the SSTable:

• It’s ordered: There is an incentive to store the keys in a sorted order, and it makes searching keys faster and efficient. If not for that, you'd have to scan the whole file to be able to find a key. Later, I will point out some code that leverages sorted storage.

• It’s immutable: Once an SSTable file is written, it can’t be modified. To update or delete a key, you must write a new record in a newer SSTable. This simplifies the design and makes reads and writes very predictable.

But wait, how does that simplify the design?

One of the most complex things in software engineering is dealing with concurrency. Let’s say you’re writing to a file and another thread updates it underneath. How do you know you have the correct data?

With immutable design, you don't have to worry about this at all. You are 100% confident that the data you are reading has not been altered by anybody else. I’ll take that as a massive simplification: you don't have to deal with locks, starvation, staleness, and so on.

How does it make the write path predictable?

I will answer this partially here and come back to it when we have completed some more implementation. You’ll see that every write in our code follows the exact same steps. There is not a single different condition or edge case.

In a traditional database (using a B-Tree), a typical write involves:

1. Finding the data on disk.

2. Reading the block of data from disk into memory.

3. Modifying the data in memory.

4. Writing the entire block back to disk.

The more steps, the more unpredictable performance can get, because the write can be fast if data is already in the memory cache or slow if there are multiple disk seeks needed.

Granted, our code is an overly simplified version, but the extension of this concept still stands true in real LSM implementations.

How does it make the read path predictable?

Read is predictable because any number of threads can read the same SSTable file at the same time without any problem, with full confidence that data has not been updated.

In contrast, when reading from a mutable data structure, you have to worry that another thread might be in the process of changing the data you are trying to read.

To prevent this, B-Tree-based databases use complex locking mechanisms, and that adds overhead and unpredictability.

I should raise a caution here: the Read in LSM tree storage is not always predictable. It can be faster if data is read from memory and it can be very slow if multiple SSTables need to be looked up to find the key.

Having said that, you don't have to worry about other performance bottlenecks because of locks. Meaning, in B-Tree storage, your read query can be slower because another write query is holding a lock. In simple low-concurrency use cases, you will mostly get amazing read performance from a B-Tree structure, but this advantage wears off as concurrency increases.

LSM tree was built for highly concurrent, write-heavy use cases, and at times slower reads are a trade-off.

The takeaway that gives you ammunition to design better is that B-trees are better for read-heavy workloads. Reads are generally faster and more consistent, but performance can have unpredictable outliers under high write concurrency due to locking.

An LSM tree is better for write-heavy workloads. Writes are much faster. Reads are generally slower and more variable, but their performance profile is more predictable under high write concurrency because there is no read-write locking.

Let's implement an SSTable to see how it works.

The write path:

func writeSSTable[K comparable, V any](memtable *MemTable[K, V], path string) (*SSTable[K, V], error) {
    file, err := os.Create(path)
    if err != nil {
        return nil, err
    }
    defer file.Close()

    pairs := make([]Pair[K, V], 0, len(memtable.data))
    for k, v := range memtable.data {
        pairs = append(pairs, Pair[K, V]{Key: k, Value: v})
    }

    sort.Slice(pairs, func(i, j int) bool {
        return any(pairs[i].Key).(string) < any(pairs[j].Key).(string)
    })

    encoder := gob.NewEncoder(file)
    for _, pair := range pairs {
        if err := encoder.Encode(pair); err != nil {
            return nil, err
        }
    }

    return &SSTable[K, V]{path: path}, nil
}

The following things are important to note from the above code:

1. sort.Slice: Remember I spoke about order earlier? So we store data in the SSTable in a sorted fashion, and we will see how we leverage it in the read path.

2. I have used the gob encoding package. An encoder makes life simpler for you because it streams the data to and from Go data structures to binary streams that can be stored on disk. It handles all the complexity of representing types, field names, and values in a standardized binary format, so that you don't have to.

The read path:

func (s *SSTable[K, V]) Get(key K) (V, error) {
    file, err := os.Open(s.path)
    if err != nil {
        var zero V
        return zero, err
    }
    defer file.Close()

    decoder := gob.NewDecoder(file)

    for {
        var pair Pair[K, V]
        if err := decoder.Decode(&pair); err != nil {
            if err == io.EOF {
                break
            }
            var zero V
            return zero, err
        }

        // for simple comparison we are assuming key is just string
        keyInDB := any(pair.Key).(string)
        if keyInDB == any(key).(string) {
            if any(pair.Value).(string) == TOMBSTONE {
                var zero V
                return zero, ErrDeleted
            }
            return pair.Value, nil
        }

        if keyInDB > any(key).(string) {
            var zero V
            return zero, ErrNotFound
        }
    }

    var zero V
    return zero, ErrNotFound
}

On the read path, look at keyInDB > any(key).(string). This is one of the examples of how we took advantage of storing data in a sorted key order. The moment we find a key in the SSTable that is greater than the key we are looking for, we stop looking because it’s obvious all other keys will be greater than this, so we won't find our key anymore.

Now that you have implemented the SSTable, you just have to decide when to flush data from the MemTable to the SSTable. You can just define max size for MemTable and flush it to disk on the write path when the max size is reached.

I am skipping some variables, boilerplate code, and simplifying things for brevity. I will post a GitHub link with the complete implementation later.

type DB[K comparable, V any] struct {
    memtable        *MemTable[K, V]
    maxMemtableSize int
    memtableSize    int
    sstables        []*SSTable[K, V]
    sstableCounter  int
}

func NewDB[K comparable, V any](maxMemtableSize int) (*DB[K, V], error) {
    sstables := make([]*SSTable[K, V], 0)
    memtable := NewMemTable[K, V]()

    return &DB[K, V]{
        memtable:        memtable,
        maxMemtableSize: maxMemtableSize,
        sstables:        sstables,
    }, nil
}

func (db *DB[K, V]) Put(key K, value V) error {
    db.memtable.Put(key, value)
    db.memtableSize++

    if db.memtableSize >= db.maxMemtableSize {
        if err := db.flushMemtable(); err != nil {
            return err
        }
    }

    return nil
}

func (db *DB[K, V]) flushMemtable() error {
    sstablePath := fmt.Sprintf("data-%d.sstable", db.sstableCounter)
    sstable, err := writeSSTable(db.memtable, sstablePath)
    if err != nil {
        return err
    }

    db.sstables = append(db.sstables, sstable)
    db.sstableCounter++
    db.memtable = NewMemTable[K, V]()
    db.memtableSize = 0

    return nil
}

You'll notice that every time we flush to disk, we write to a new SSTable versus using a single SSTable for the whole database. This is the immutability aspect we discussed earlier.

func (db *DB[K, V]) Get(key K) (V, error) {
    if val, ok := db.memtable.Get(key); ok {
        return val, nil
    }

    for i := len(db.sstables) - 1; i >= 0; i-- {
        sstable := db.sstables[i]
        val, err := sstable.Get(key)

        if err != nil {
            var zero V

            if err == ErrDeleted {
                return zero, ErrNotFound
            }
            if err == ErrNotFound {
                continue
            }
            return zero, err
        }

        return val, nil
    }

    var zero V
    return zero, ErrNotFound
}

One important aspect to note on the read path is that we are reading the newest SSTable first. This is because the newest SSTable has the most updated value for the key.

So, say you have a key "a" with value "apple", and along the way you update that value for "a" to be "apricot". You'd have flushed it to a new SSTable (for immutability), and so if you were to read an older SSTable, first you'd get the older value. So by reading the newer SSTable first, we get the correct value and we don't have to worry about updating older SSTables.

The WAL (Write Ahead Log ): Crash Recovery Made Simple

Now that we have an SSTable, our data is durable and we are safe from losing data upon crashes. Are we really safe, though? Think of a scenario where a crash happens before we flush to the SSTable. We know MemTable has a max threshold, and until then, data lives in memory. So we’re still prone to losing data if a crash happens before the flush.

This is where the WAL (Write Ahead Log) comes into the picture. It’s the single most important aspect of the LSM tree.

We’ll follow a simple rule: "Before we write a piece of data to the in-memory MemTable, we first write it to a log file on disk."

If a crash happens and the database starts again, the first thing it does is look for a WAL, read it if one is found, and replay all the data into MemTable. This process reconstructs the MemTable to the exact state it was in right before the crash.

It's natural to think that if all of your writes are first written to disk it will impact performance. You aren’t wrong, but at the same time there are nuances.

The writes to WAL are different in that they are append-only sequential writes, meaning random disk seeks are not required. On a traditional spinning hard drive (HDD), this is fast because the disk's read/write head does not have to move to a new location. On a modern solid-state drive (SSD), sequential writes are also much faster than random writes.

Whatever small performance impact we accept is a trade-off for durability.

Now that we know what WAL does, let's implement it. Two key functions of WAL are to write to a file on disk and replay MemTable upon start.

Note that in the factory function below (NewWAL), the file has been opened in append mode.

func NewWAL[K comparable, V any](path string) (*WAL[K, V], error) {
    file, err := os.OpenFile(path, os.O_APPEND|os.O_CREATE|os.O_WRONLY, 0644)
    if err != nil {
        return nil, err
    }
    return &WAL[K, V]{
        file:    file,
        encoder: gob.NewEncoder(file),
    }, nil
}

func (wal *WAL[K, V]) Write(key K, value V) error {
    entry := WALEntry[K, V]{Key: key, Value: value}
    return wal.encoder.Encode(&entry)
}

func ReplayWAL[K comparable, V any](path string) (*MemTable[K, V], error) {
    memtable := NewMemTable[K, V]()
    file, err := os.Open(path)
    if err != nil {
        if os.IsNotExist(err) {
            // If the file doesn't exist, that's fine. Return an empty memtable.
            return memtable, nil
        }
        return nil, err
    }
    defer file.Close()

    decoder := gob.NewDecoder(file)
    for {
        var entry WALEntry[K, V]
        if err := decoder.Decode(&entry); err != nil {
            if err == io.EOF {
                break // We've reached the end of the file.
            }
            return nil, err
        }
        memtable.Put(entry.Key, entry.Value)
    }

    return memtable, nil
}

A couple notes about the above code:

• NewWAL: This function creates an instance of the WAL for our database. It takes in the file path where the WAL data should be stored and opens the file using Go’s os.OpenFile function. Also, a gob.Encoder is initialized to simplify the encoding of Go data structures into binary format for efficient storage in the WAL file.

• Write: The Write function appends a new key-value pair to the WAL file. Every write operation to the MemTable first calls this function to ensure the update is durably recorded:

• ReplayWAL: This is the most important function. In the event of crash, this function comes to our rescue by reconstructing the MemTable from the WAL file. It replays the entries stored in the WAL file and writes it into MemeTable. Following it how it works:

  1. The function begins by creating a new empty MemTable instance that will be populated with key-value pairs.

  2. It then attempts to open the WAL file. If the file does not exist (example – if this is the first startup), the function assumes there’s nothing to recover and simply returns the empty MemTable.

  3. A gob.Decoder is used to read the WAL file, which helps to deserialize the saved binary-encoded WALEntry data back into key-value pairs.

  4. For each successfully decoded WALEntry, the key-value pair is added back into the MemTable using the Put function.

With this, the database can fully recover its state by replaying all the operations recorded in the WAL.

As far as integration is concerned, every time you create a new DB, you should think of replaying from an existing WAL and opening the WAL in append mode. Also, Put should first write to WAL.

func NewDB[K comparable, V any](maxMemtableSize int) (*DB[K, V], error) {
    walPath := "db.wal"
    memtable, err := ReplayWAL[K, V](walPath) // this is the replay
    if err != nil {
        return nil, err
    }
    //open WAL in append mode
    wal, err := NewWAL[K, V](walPath)
    if err != nil {
        return nil, err
    }

    return &DB[K, V]{
        memtable:        memtable,
        maxMemtableSize: maxMemtableSize,
        memtableSize:    len(memtable.data),
        wal:             wal,
        walPath:         walPath,
        sstables:        make([]*SSTable[K, V], 0),
    }, nil
}

func (db *DB[K, V]) Put(key K, value V) error {
//first write to WAL
    if err := db.wal.Write(key, value); err != nil {
        return err
    }

    db.memtable.Put(key, value)
    db.memtableSize++

    if db.memtableSize >= db.maxMemtableSize {
        if err := db.flushMemtable(); err != nil {
            return err
        }
    }

    return nil
}

Manifest File: Tracking the State of the Database

By this point, the database is pretty robust and durable, but an important question lingers: upon restarts, how does our database know about SSTables? Knowing about all SSTables is important for fetching data.

So say our database crashed after writing several SSTables. Without knowing about these SSTables, the database will create a new slice of SSTables and all of our old data is gone – queries won't read those files.

To solve this problem, we introduce an inventory of SSTables called MANIFEST. Every time we successfully create a new SSTable in flushMemtable, we add its path to the MANIFEST and save the MANIFEST to disk.

The very first thing NewDB does on startup is read the MANIFEST. This gives it the list of all the file paths, and it uses this list to perfectly reconstruct its SSTables slice.

In short, MANIFEST determines the state of the DB.

Manifest contains a slice of SSTablePaths. The Read function will read the MANIFEST file to restore the knowledge of the SSTables. The Write function will write a new manifest file.

type Manifest struct {
    SSTablePaths []string
}

func ReadManifest(path string) (*Manifest, error) {
    file, err := os.Open(path)
    if err != nil {
        if os.IsNotExist(err) {
            // If manifest doesn't exist, return empty manifest
            return &Manifest{SSTablePaths: []string{}}, nil
        }
        return nil, err
    }
    defer file.Close()

    var manifest Manifest
    decoder := gob.NewDecoder(file)
    err = decoder.Decode(&manifest)
    if err != nil {
        return nil, err
    }

    return &manifest, nil
}

func WriteManifest(path string, manifest *Manifest) error {
    tmpPath := path + ".tmp"
    file, err := os.Create(tmpPath)
    if err != nil {
        return err
    }

    encoder := gob.NewEncoder(file)
    if err := encoder.Encode(manifest); err != nil {
        file.Close()
        os.Remove(tmpPath)
        return err
    }

    if err := file.Close(); err != nil {
        return err
    }
    // Atomic Rename
    return os.Rename(tmpPath, path)
}

You'll notice that we aren’t modifying the existing MANIFEST file directly. Instead, we’re creating a temporary file, writing all the data to it, closing it, and then atomically renaming it to replace the old MANIFEST.

The os.Rename() operation is atomic on most filesystems, meaning it either completely succeeds or completely fails – there's no in-between state. This is crucial because if the system crashes while updating the MANIFEST, we need to ensure we don't end up with a corrupted file. We’ll discuss this again below when we’re talking about compaction.

With this approach, we either have the old valid MANIFEST or the new valid MANIFEST, never a partially written corrupted file.

From an integration standpoint, NewDB will read the manifest and set its SSTable slice based on that. The flush method, given that it writes to SSTable, will also write SSTable info to manifest to keep the db updated about new SSTables.

type DB[K comparable, V any] struct {
    memtable        *MemTable[K, V]
    maxMemtableSize int
    memtableSize    int
    sstables        []*SSTable[K, V]
    sstableCounter  int
    wal             *WAL[K, V]
    walPath         string
    manifest        *Manifest
    manifestPath    string
}

func NewDB[K comparable, V any](maxMemtableSize int) (*DB[K, V], error) {
    walPath := "db.wal"
    memtable, err := ReplayWAL[K, V](walPath)
    if err != nil {
        return nil, err
    }

    wal, err := NewWAL[K, V](walPath)
    if err != nil {
        return nil, err
    }

    manifestPath := "MANIFEST"
    manifest, err := ReadManifest(manifestPath)
    if err != nil {
        return nil, err
    }

    sstables := make([]*SSTable[K, V], len(manifest.SSTablePaths))
    for i, path := range manifest.SSTablePaths {
        sstables[i] = &SSTable[K, V]{path: path}
    }

    return &DB[K, V]{
        memtable:        memtable,
        maxMemtableSize: maxMemtableSize,
        memtableSize:    len(memtable.data),
        wal:             wal,
        walPath:         walPath,
        manifest:        manifest,
        manifestPath:    manifestPath,
        sstables:        sstables,
    }, nil
}

func (db *DB[K, V]) flushMemtable() error {
    sstablePath := fmt.Sprintf("data-%d.sstable", db.sstableCounter)
    sstable, err := writeSSTable(db.memtable, sstablePath)
    if err != nil {
        return err
    }

    db.sstables = append(db.sstables, sstable)
    db.sstableCounter++

    db.manifest.SSTablePaths = append(db.manifest.SSTablePaths, sstablePath)
    if err := WriteManifest(db.manifestPath, db.manifest); err != nil {
        return err
    }

    db.memtable = NewMemTable[K, V]()
    db.memtableSize = 0

    return nil
}

At this point, our DB has almost everything. It can write to memory (MemTable), persist to disk (sstable), and recover from crashes (WAL and manifest). You should include the update and delete feature for completeness – so let’s look at those next.

Update and Delete: Handling Mutability in an Immutable System

By this time, you should know that in an LSM storage system, data is never updated – rather, new data is written. For example, if you have a data pair ("a": "apple") and over time this has to change to the pair ("a": "apricot"), a new pair will be written to a different SSTable without any change to the existing pair. And yes, this leads to duplicates.

Also, interestingly, data isn't even deleted during write operations. The reason for that is, in a traditional sense, if you have to delete ("a":"apple"), you will have to find where it lives on disk and remove it. This makes writes slow. So instead, a clever mechanism is used: instead of removing the data directly, you can mark the key as deleted by writing a special TOMBSTONE value.

So, in the case of deleting (a : apple), you wouldn't remove the key from any SSTable. Instead, you’d write a new key-value pair such as ("a": "TOMBSTONE"). Here’s what this achieves:

• The "TOMBSTONE" serves as a marker within the SSTable, telling the system that the key "a" has been logically deleted, even though it still physically exists in older SSTables.

• During future reads, any value associated with "TOMBSTONE" will be treated as deleted, ensuring that the entry no longer shows up in query results.

• This mechanism avoids the need for immediate deletions or expensive in-place updates, making write operations faster and simpler.

But this also raises the following questions:

1. How do you accurately read when there are duplicates? Meaning, how do users get ("a": "apricot") instead of ("a": "apple") because the former is latest and accurate?

2. How do you handle deletes to ensure deleted keys are not returned (and instead, a proper error message is returned)?

3. These stale and deleted data are garbage. How do you get rid of them to save on storage space?

As long as data is in MemTable (in-memory map), the duplicates are easy to handle: new values will just replace the old values.

But it gets tricky when data is in multiple SSTables. There is a very simple solution to this problem, and that is to just read the newer SSTable before older ones. That way, you will always read the latest value for a given key and exit early.

The following code in the read path ensures reading from newer SSTables before moving to older ones (note the loop starts from len(db.sstables) - 1):

func (db *DB[K, V]) Get(key K) (V, error) {
    // Check memtable first
    if val, ok := db.memtable.Get(key); ok {
        if any(val).(string) == TOMBSTONE {
            var zero V
            return zero, ErrNotFound
        }
        return val, nil
    }

    // Then check sstables from newest to oldest
    for i := len(db.sstables) - 1; i >= 0; i-- {
        sstable := db.sstables[i]
        val, err := sstable.Get(key)

        if err == nil {
            return val, nil
        }

        var zero V
        if err == ErrDeleted {
            return zero, ErrNotFound
        }
        if err == ErrNotFound {
            continue
        }
        return zero, err
    }

    var zero V
    return zero, ErrNotFound
}

And for delete, you could just add a new value "TOMBSTONE":

func (db *DB[K, V]) Delete(key K) error {
    return db.Put(key, any(TOMBSTONE).(V))
}

Note: This implementation assumes V is a string type. In a production system, you would need a more robust way to handle tombstones that works with any value type.

Handling deleted keys becomes simple now. You can check for the value (in MemTable and SSTable) and return an error if the value is "TOMBSTONE":

// db.go
func (db *DB[K, V]) Get(key K) (V, error) {
    if val, ok := db.memtable.Get(key); ok {
        if any(val).(string) == TOMBSTONE { //got TOMBSTONE, return zero
            var zero V
            return zero, ErrNotFound
        }
        return val, nil
    }
    // ... rest of function
}

// sstable.go
func (s *SSTable[K, V]) Get(key K) (V, error) {
    // ... earlier code

    keyInDB := any(pair.Key).(string)
    if keyInDB == any(key).(string) {
        if any(pair.Value).(string) == TOMBSTONE {
            var zero V
            return zero, ErrDeleted
        }
        return pair.Value, nil
    }

    // ... rest of function
}

Compaction: Cleaning Up Stale and Deleted Data

We have handled all the scenarios so far except for one. It’s not a concern of serving read/write traffic but something that’s important for the health of the storage system.

Over time, the system has developed a lot of garbage (stale, deleted data) and needs a garbage collection mechanism. Compaction is a background maintenance process that cleans up and reorganizes data in an LSM storage system.

As the system grows, multiple SSTables have been created. This leads to reads needing multiple file operations to get values. By compacting (or merging) multiple SSTables into a single one, you avoid disk operation overhead. Along the way, you should also permanently delete data that has been TOMBSTONED.

Note: Compaction is the only time data is permanently deleted from an LSM storage system.

To grasp the concept of compaction, we are going to implement something called Full Compaction where you will merge all the existing SSTables into one larger SSTable. In real-world database implementations, the strategy is more complex, there are multi level compaction involved.

Compaction Algorithm

We’re going to implement K-way merge to perform compaction. It’s a general algorithm that takes K sorted lists and merges them into a single, combined sorted list. In this case, the K sorted lists are the SSTables, and you are going to merge all of them into a single SSTable.

Our SSTables are already sorted, so the idea of merging them involves:

1. Taking the smallest (first) keys from each SSTable

2. Finding the smallest among those keys

3. Storing the found smallest key into new SSTable file

4. Fetching next key from the SSTable the smallest key belongs to

5. Repeating this process for all SSTables

Here’s a simple example with numbers:

Assume we have 3 sorted lists:
List A : [4, 8, 12]
List B : [3, 9]
List C : [7, 10, 11]

In the first iteration, we will take (4, 3, 7) because those are the smallest keys for individual lists. 
We find the smallest among those, which is 3, and store 3 in the result list.

In the second iteration, we will take (4, 9, 7). Note that 3 has already been accounted for. 
We pick 4 and store it to the result list.

Repeating this until all lists are empty, we get:
Result List : [3, 4, 7, 8, 9, 10, 11, 12]

The core part of this algorithm is to find the smallest key among the smallest keys from the individual SSTables. Fortunately, we have a data structure called Min-Heap that does this for us. So, you’re going to take the smallest key from each SSTable and put them all onto a Min Heap for it to return the smallest among those. We’re going to leverage go’s container/heap package to get the Min-Heap data structure and corresponding algorithm to find minimum value and put it at the top of heap.

Min Heap needs you to provide a function for it to determine what is the smaller key between two keys, as it uses that logic to determine global minimum. The following function is implemented for that:

func (h MinHeap[K, V]) Less(i, j int) bool {
    // again for simple comparison assume string key
    keyI := any(h[i].Pair.Key).(string)
    keyJ := any(h[j].Pair.Key).(string)
    if keyI != keyJ {
        return keyI < keyJ
    }
    // this is needed for the case when you have duplicate keys,
    // you will want to pick the one that is in newer sstable because that is latest
    return h[i].SSTableIndex > h[j].SSTableIndex
}

One important aspect about the above shown Less function is how it handles ties. So if we have two pairs with same key, which is lesser? Let’s assume two pairs as (a: apple) and (a: apricot), where (a: apple) is the older value (written to an older SSTable), which pair should the Less function return as the lesser value?

The answer is the one which is in the newer SSTable (see h[i].SSTableIndex > h[j].SSTableIndex). It ensures that the SSTable with higher index (that is, latest) becomes the lesser value, so (a: apricot) wins. It’s is important to always get the newer value of a given key.

The code for compaction looks something like the following. Note that we’re discarding deleted values (TOMBSTONE) and the older values.

// put this in a new file compaction.go
func MergeSSTables[K comparable, V any](sstables []*SSTable[K, V], newPath string) (*SSTable[K, V], error) {
    newFile, err := os.Create(newPath) // create a new sstable file
    if err != nil {
        return nil, err
    }
    defer newFile.Close() // prevent memory leak by ensuring file is closed

    newEncoder := gob.NewEncoder(newFile) // initialize encoder for new SSTable file


    files := make([]*os.File, len(sstables)) // open all the sstables
    decoders := make([]*gob.Decoder, len(sstables)) // initialize one decoder per ssltable file
    for i, sstable := range sstables {
        files[i], err = os.Open(sstable.path)
        if err != nil {
            return nil, err
        }
        defer files[i].Close() // prevent memory leak by ensuring file is closed
        decoders[i] = gob.NewDecoder(files[i])
    }

    // read first pair from each sstable and store in a pair array
    pairs := make([]Pair[K, V], len(decoders))
    emptySSTables := make([]bool, len(decoders)) // track empty sstables
    for i, decoder := range decoders {
        if err := decoder.Decode(&pairs[i]); err != nil {
            if err == io.EOF {
                emptySSTables[i] = true
                continue
            }
            return nil, err
        }
    }

    // push those pairs onto heap
    h := &MinHeap[K, V]{}
    for i, pair := range pairs {
        if !emptySSTables[i] {
            heap.Push(h, &HeapItem[K, V]{Pair: pair, SSTableIndex: i})
        }
    }

    // init the min-heap calculation algorithm from container/heap package
    heap.Init(h)

    var lastKey K
    firstKey := true

    // pop the min item from heap and store it into new sstable
    for h.Len() > 0 {
        item := heap.Pop(h).(*HeapItem[K, V])

        // If this key is a duplicate of the last one we saw, skip it
        if !firstKey && item.Pair.Key == lastKey {
            // We only care about the version from the newest SSTable,
            // which we have already processed
        } else {
            if any(item.Pair.Value).(string) != TOMBSTONE {
                // discard deleted
                if err := newEncoder.Encode(item.Pair); err != nil {
                    return nil, err
                }
            }
        }

        lastKey = item.Pair.Key
        firstKey = false

        // Push the next item from the same SSTable into the heap
        var nextPair Pair[K, V]
        if err := decoders[item.SSTableIndex].Decode(&nextPair); err == nil {
            heap.Push(h, &HeapItem[K, V]{Pair: nextPair, SSTableIndex: item.SSTableIndex})
        } else if err != io.EOF {
            return nil, err
        }
    }

    return &SSTable[K, V]{path: newPath}, nil
}

All the compaction magic has been packed in one function, MergeSSTables. The function has the following logical steps (and you can check the inline comments in the code to follow along):

1. We create a new destination SSTable file and initialize corresponding gob.Encoder

2. We open all the existing SSTable files, and store their references to files array. Also, we initialize one gob.Decoder per exiting SSTable file. To prevent memory leak, a defer statement ensures that each file will be closed once the function completes its work.

3. Each decoder reads the first key-value pair from its corresponding SSTable and stores it in the pairs array.

4. SSTables that are already exhausted (for example, are empty or have hit the end of the file) are marked as such in the emptySSTables slice, and we skip pushing them onto the heap.

5. We push each pair from the pairs array to Min-Heap and then initialize the Min-Heap calculation algorithm. This algorithm is present in Go’s container/heap package.

6. Each time the smallest key-value pair is popped from the min-heap, it’s compared with the previously processed key (lastKey). Duplicate keys (those whose values are already written) are skipped.

7. Values marked with a "TOMBSTONE" (logically deleted entries) are ignored and not written to the new SSTable, effectively cleaning up deleted data.

8. To continue the merge, the next key-value pair from the same SSTable (as the one we just processed) is read and pushed onto the heap, unless the end of the SSTable (io.EOF) has been reached.

To integrate this with the DB, you could use a compaction threshold and trigger compaction as part of the flush when this threshold is reached:

type DB[K comparable, V any] struct {
    memtable            *MemTable[K, V]
    maxMemtableSize     int
    memtableSize        int
    sstables            []*SSTable[K, V]
    sstableCounter      int
    wal                 *WAL[K, V]
    walPath             string
    manifest            *Manifest
    manifestPath        string
    compactionThreshold int
}

func NewDB[K comparable, V any](maxMemtableSize int, compactionThreshold int) (*DB[K, V], error) {
    walPath := "db.wal"
    memtable, err := ReplayWAL[K, V](walPath)
    if err != nil {
        return nil, err
    }

    wal, err := NewWAL[K, V](walPath)
    if err != nil {
        return nil, err
    }

    manifestPath := "MANIFEST"
    manifest, err := ReadManifest(manifestPath)
    if err != nil {
        return nil, err
    }

    sstables := make([]*SSTable[K, V], len(manifest.SSTablePaths))
    for i, path := range manifest.SSTablePaths {
        sstables[i] = &SSTable[K, V]{path: path}
    }

    return &DB[K, V]{
        wal:                 wal,
        walPath:             walPath,
        memtable:            memtable,
        memtableSize:        len(memtable.data),
        maxMemtableSize:     maxMemtableSize,
        manifestPath:        manifestPath,
        manifest:            manifest,
        sstables:            sstables,
        compactionThreshold: compactionThreshold,
    }, nil
}

// a new compact function
func (db *DB[K, V]) Compact() error {
    compactedSSTablePath := fmt.Sprintf("data-compacted-%d.sstable", db.sstableCounter)
    compactedSSTable, err := MergeSSTables(db.sstables, compactedSSTablePath)
    if err != nil {
        return err
    }
    // write new SSTable to MANIFEST file
    db.manifest.SSTablePaths = []string{compactedSSTablePath}
    if err := WriteManifest(db.manifestPath, db.manifest); err != nil {
        return err
    }
    //note delete only after writing manifest
    for _, sstable := range db.sstables {
        if err := os.Remove(sstable.path); err != nil {
            log.Printf("Failed to remove old sstable %s: %v", sstable.path, err)
        }
    }

    db.sstables = []*SSTable[K, V]{compactedSSTable}
    db.sstableCounter++

    return nil
}

func (db *DB[K, V]) flushMemtable() error {
    sstablePath := fmt.Sprintf("data-%d.sstable", db.sstableCounter)
    sstable, err := writeSSTable(db.memtable, sstablePath)
    if err != nil {
        return err
    }

    db.sstables = append(db.sstables, sstable)
    db.sstableCounter++

    db.manifest.SSTablePaths = append(db.manifest.SSTablePaths, sstablePath)
    if err := WriteManifest(db.manifestPath, db.manifest); err != nil {
        return err
    }

    db.memtable = NewMemTable[K, V]()
    db.memtableSize = 0

    // trigger compaction
    if len(db.sstables) >= db.compactionThreshold {
        if err := db.Compact(); err != nil {
            log.Printf("Compaction failed: %v", err)
            return err
        }
    }

    return nil
}

Notice the Compact() function in the integrated DB code? This is where we invoke previously defined the MergeSSTables function to trigger the compaction process. After invoking MergeSSTables, we write a new SSTable to the MANIFEST file and then delete the older SSTables.

Previously, in the Manifest File: Tracking the State of the Database, I spoke about atomic renaming os.Rename(tmpPath, path). Let’s talk about why the atomic renaming of MANIFEST matters for compaction.

During compaction, we're making a major change to the database state: replacing multiple SSTables with a single compacted one. The MANIFEST update is critical here because it's the source of truth for which SSTables exist.

Let’s think about what could go wrong without atomic renaming:

1. You start writing the new MANIFEST (which points to the compacted SSTable)

2. System crashes mid-write

3. MANIFEST is corrupted and unreadable

4. On restart, the database has no idea which SSTables exist

5. All data is effectively lost

With atomic renaming:

1. We write the new MANIFEST to MANIFEST.tmp

2. We fully close and sync it to disk

3. We atomically rename MANIFEST.tmp to MANIFEST using os.Rename(tmpPath, path)

4. If crash happens before step 3: old MANIFEST is intact, we retry compaction

5. If crash happens during step 3: atomic operation either completes or doesn't – no corruption

6. If crash happens after step 3: new MANIFEST is in place, we're good

This is also why we delete the old SSTables only after successfully updating the MANIFEST. If we deleted them before updating MANIFEST and then crashed, the MANIFEST would still point to files that no longer exist.

Complete Picture:

Conclusion

Congratulations! You've built a working LSM tree storage engine from scratch. By following the problem-driven approach – discovering issues and implementing solutions as they arose – you've experienced how engineers think about building robust storage systems. I hope this is better than just memorizing the concepts.

Key Takeaways

• Append-only writes make LSM-trees fast for write-heavy workloads

• Immutability eliminates complex concurrency issues

• Trade-off is that LSM-tree favor writes over reads (opposite of B-trees)

• Durability requires multiple mechanism working together (WAL, MANIFEST, atomic operations)

• Background maintenance (compaction) is essential for long-term health and cost.

Important note: This is a learning implementation. This means that I intentionally simplified the code, so it’s not production-ready. Key limitations include:

• No concurrency control (missing mutexes/locks)

• No bloom filters for efficient lookups

• Simplified compaction strategy

• Type safety issues with generic tombstones

• Missing robust error recovery

Complete Code:

Like I’ve mentioned before, I've omitted boilerplate code and helper functions for brevity. The complete, runnable implementation is available at this GitHub repo.

To learn more about production LSM implementations, study RocksDB, LevelDB, or read the original LSM tree paper by O'Neil et al: https://www.cs.umb.edu/~poneil/lsmtree.pdf

Like, what in the world – out of every other possible name, they decided to name a database after a literal cockroach? 🤣

I mean, I get it: cockroaches are known for being resilient, and the devs were probably trying to say “our database never dies”… but still…a cockroach?

The name aside, out of all the databases out there, you might be wondering why would you choose CockroachDB? And if you did choose it, where would you even start when trying to host and deploy it? Would you go for a managed cloud service? Or could you actually self-manage it?

If you ever thought of doing it yourself – maybe in a dev environment, or even introducing it to your company – how would you go about it?

Well, just calm your nerves 😄

In this book, we’ll explore everything you need to know about deploying and managing CockroachDB on Kubernetes. We’ll dive deep into:

• Understanding how CockroachDB’s masterless (multi-primary) architecture actually works

• Setting up and deploying CockroachDB on a Kubernetes cluster

• Automating backups to Google Cloud Storage using just a few queries in the CockroachDB cluster

• Managing service accounts and authentication securely

• Tuning CockroachDB’s memory settings for stable performance

• Scaling the cluster horizontally and vertically without downtime

• Monitoring and maintaining the database like a pro

By the end, you’ll not only understand how CockroachDB works, you’ll be confident enough to deploy and manage your own resilient, production-ready instance. 🚀

Table of Contents

1. What Even Is CockroachDB? 🤔

   • Simple Definition

   • Who Made CockroachDB? When Was it Released?

   • What Problems Does CockroachDB Try to Solve?

   • Key Terms You Should Know (in plain language):

   • Why the name “CockroachDB”? 😅

2. Why Choose CockroachDB Over PostgreSQL or MongoDB 🤷🏾‍♂️?

   • How Fault Tolerance is Handled in PostgreSQL and MongoDB

   • How CockroachDB Handles It Differently

3. How CockroachDB Works Behind the Scenes ⚙️

   • Ranges: The Small Pieces of Data

   • Replication: Many Copies for Safety

   • Raft Consensus: How All Copies Agree

   • MultiRaft: Keeping Raft Efficient When Things Scale

   • Rebalancing: Movement for Balance

   • Distributed Transactions: Doing Work Across Multiple Ranges

   • How It All Fits Together: Read + Write Flow (What Happens When You Use It)

   • Why This All Matters (Putting It in Plain English)

4. Where (and How) Should You Host CockroachDB? ☁️

   • Option 1: CockroachDB Cloud (fully managed by Cockroach Labs)

   • Option 2: Bring Your Own Cloud (BYOC)

   • Option 3: Use Cloud Marketplaces (AWS, GCP, Azure)

   • Option 4 (My Favorite 😁): Self-Hosting — Especially Using Kubernetes

5. Setting Up Your Local Environment 🧑‍💻

   • Why these tools?

   • Step 1: Install Minikube

   • Step 2: Install kubectl

   • Step 3: Install Helm

6. Deploying CockroachDB on Minikube (The Fun Part Begins 😁!)

   • Step 1: Visit ArtifactHub

   • Step 2: Explore the Helm Chart

   • Step 3: Copy the Default Values

   • Step 4: Create a Folder for Our Project

   • Step 5: Understanding the Key Configurations

   • Step 6: Create a Simplified Values Config for the CockroachDB Helm Chart

   • Overview of the YAML values

   • 🚀 Step 7: Install the CockroachDB Cluster Using Helm

7. Accessing the CockroachDB Console & Viewing Metrics

   • Step 1: Locate the CockroachDB Public Service

   • Step 2: Learn More About the Service

   • Step 3: Access the CockroachDB Dashboard

   • Step 4: Visit the Dashboard

   • Step 5: Exploring the Metrics Dashboard

   • Step 6: Creating a Little Load on the CockroachDB Cluster

   • Step 7: Viewing the Metrics from the Load

   • Step 8: View the List of Created Items in the Database

8. Backing Up CockroachDB to Google Cloud Storage ☁️

   • Why Backups Are Absolutely Critical

   • Connecting to Our DB – Installing Beekeeper Studio

   • How to Install Beekeeper Studio

   • Connecting Beekeeper Studio to CockroachDB

   • Exposing the Cluster for Local Access

   • 🐝 Connecting via Beekeeper Studio

   • Verify the Connection

   • Creating a Google Cloud Account

   • Creating a Google Cloud Storage Bucket

   • Giving CockroachDB Access to the Bucket

   • Attaching the Key to Our CockroachDB Cluster

   • Testing Our Backup — Disaster Recovery Time

9. Managing Resources & Optimizing Memory Usage

   • How CockroachDB Uses Memory

   • The Memory Usage Formula You Must Follow

   • Where You Find These Settings

   • Concrete Example (Step-by-Step)

   • ⚠️ On Requests vs Limits in Kubernetes

   • Overriding the Default Fractions

10. Scaling CockroachDB the Right Way

    • Key Metrics to Understand

    • When (and What) to Scale Based on Your Metrics

    • Disk-Bound Situations — What to Do When Your Disk Is the Limiting Factor

    • Memory Pressure — What to Do When Your Database Hits the Limit

    • When Queries Are Slow but Everything Else (CPU, Memory & Disk) Looks “Fine”

    • Understanding Disk Speed (IOPS & Throughput) Across Cloud Providers

    • Downsizing the Cluster (Reducing Replicas)

    • ⚠️ The Wrong Way to Downscale

    • Decommissioning a Node Before Scaling Down the Cluster

11. What to Consider When Deploying CockroachDB on Google Kubernetes Engine (GKE) ☁️

    • Creating Your GKE Cluster

    • Connecting to your GKE cluster

    • Deploying CockroachDB in Production (on GKE)

    • Understanding the Configuration

    • Installing the CockroachDB Cluster on GKE

    • Connecting to Our CockroachDB Cluster (Now That TLS + mTLS Are Enabled)

    • Connecting via Mutual TLS (mTLS) — Why We Need a Certificate for Our root User

    • Let’s Explore Our Cluster’s Certificate

    • Understanding the Certificate Sections (Explained Super Simply)

    • Creating a Client Certificate (So We Can Finally Connect to CockroachDB)

    • Connecting to Our CockroachDB Cluster Securely (Using mTLS)

    • Restoring Our Previous Database into the New GKE CockroachDB Cluster (without SA keys)

    • Restoring Our Previous Database from Google Cloud Storage

    • Now, Let’s Restore the Data 🎉

    • Connecting to the Database with a New User

    • Connecting with Passwordless Authentication (Mutual TLS)

    • Connecting via Mutual TLS (mTLS) from Our Apps on Kubernetes

12. How to Get a CockroachDB Enterprise License for FREEE!

    • Three Types of Licenses

    • How to Apply for the Free Enterprise License

    • Adding Your License to the CockroachDB Cluster

13. Conclusion & Next Steps ✨

    • About the Author 👨🏾‍💻

What Even Is CockroachDB? 🤔

Hey! before we jump into setting up our Kubernetes cluster and deploying our CockroachDB cluster, let’s get grounded in what CockroachDB really is. (Because if you don’t understand the why and how, the implementation and practical session will just feel like magic 😅.)

Simple Definition

CockroachDB is a distributed SQL database. This means it gives you the features of a relational database (tables, SQL queries, JOINS, transactions) but copies data across multiple replicas (servers, nodes, instances). No need for sharding manually. 😃

It’s built to survive failures, scale easily (compared to other SQL databases), and keep your data consistent no matter what (across all the instances).

Who Made CockroachDB? When Was it Released?

CockroachDB was created by Cockroach Labs, founded by Spencer Kimball, Peter Mattis, and Ben Darnell. The idea first started taking shape around 2014, and by 2015 Cockroach Labs was formally founded.

Its 1.0 “production-ready” version was announced in 2017, marking its transition from beta to being suitable for real-world use.

What Problems Does CockroachDB Try to Solve?

Traditional relational databases are great, but they run into real challenges when your app grows. CockroachDB was built to solve those. Here are the key pain points and how CockroachDB addresses them:

So instead of fighting your database as it grows, CockroachDB handles much of the hard work for you.

Key Terms You Should Know (in plain language):

• Node: Duplicates or copies of your database. These are also known as replicas. They can be read-only (databases from which data can only be read, for example using SELECT statements), OR read-write (databases from which data can be read, created, updated, and deleted).

• Replication: making copies of data on multiple nodes. If one node fails, others still have the data.

• Raft (consensus algorithm): a system that ensures copies (replicas) agree on changes in a safe, reliable way. For example, when you want to write data, Raft ensures that most copies agree before it’s accepted.

• Sharding / Ranges: Instead of putting all your data in one big blob, CockroachDB splits it into smaller chunks called ranges. Each range is replicated and can move between nodes.

• Distributed transaction: a transaction (series of operations) that might touch data stored in different nodes. CockroachDB manages this, so you still get ACID (atomic, consistent, isolated, durable) properties.

Why the name “CockroachDB”? 😅

You might wonder: Why name a database after a cockroach? It sounds weird at first, but there's a reason:

Cockroaches are known for surviving harsh conditions: radiation, natural disasters, and so on. The founders wanted a database that feels almost “impossible to kill,” that can survive node failures, outages, and network splits. The name is a tongue-in-cheek nod to resilience.

Why Choose CockroachDB Over PostgreSQL or MongoDB 🤷🏾‍♂️?

Let’s compare the classic setup (Postgres / MongoDB) to CockroachDB, especially why you might want to go with CockroachDB, and how it helps ease scaling. I’ll also explain some terms to make sure you’re following.

In many setups, when you use Postgres or MongoDB, you’ll often have one “primary” node that handles all writes (that is, inserts, updates, deletes).

Then you have multiple “read replicas” that copy the primary’s data and serve read requests (selects). That works okay – reads can be spread out – but all write traffic goes to that one primary node.

Usually, the primary eventually gets stressed when the write volume grows (for example, more customers create accounts and products on your platform).

You can add more read replicas (horizontal scaling for reads, for example customers trying to view their accounts, or previously created products on your site), but scaling the primary is much harder.

To scale the primary, you often resort to upgrading its resources (CPU, RAM, disk) – that’s vertical scaling – which often needs downtime (shut down the primary database, increase its CPU and RAM, then spin it back up).

Or you’d have to manually shard (split) your data across multiple primaries, route traffic carefully, and manage complexity.

How Fault Tolerance is Handled in PostgreSQL and MongoDB

When you try to make Postgres (or MongoDB) highly available and fault tolerant in a self-managed setup, you often need two+ read replicas and one primary.

The tricky part is handling what happens when the primary fails (or is taken down temporarily for an upgrade). You need something that can promote a replica to a primary automatically.

In Postgres land, that’s often handled by Patroni or repmgr (tools that handle cluster management, failover, leader election, and so on).

In MongoDB, such logic is part of the replica set behavior: it does automatic elections among replicas.

Here are some of the core challenges with that classic model:

• Every write must go to a single primary. If that primary fails or is overloaded, your whole system suffers.

• Scaling reads is easy (add more replicas), but scaling writes is hard.

• Vertical scaling (give more resources to one server) has its cons. If the primary node needs more resources, you might experience some downtime when it’s being scaled up.

• Manual sharding is messy: you decide which piece of data goes to which shard, handle cross-shard queries, and build routing logic. That’s a lot of maintenance and can lead to unexpected issues if not handled properly.

• One service (or load balancer/proxy) points to the primary (for ALL write queries).

• Another service or routing logic handles read queries and can share reads across replicas.

• You might use HAProxy, pgpool-II, or pgBouncer for Postgres to route traffic, do read/write splitting, or manage connection pooling. These are external (not part of the database core) tools you have to configure.

So when the primary fails, Patroni (or repmgr, and so on) will detect it and promote one of the read replicas to be the new primary.

But that promotion, reconfiguration, and traffic rerouting often cause a brief window of downtime (when your primary database node becomes unavailable).

How CockroachDB Handles It Differently

CockroachDB changes the rules:

• All replicas are equal for reads and writes. You don’t have a special “primary” that handles writes. Every node in the cluster can accept write requests.

• CockroachDB breaks your data into small chunks (ranges) and replicates them across nodes. If you add a new node, data moves around automatically to balance the load.

• Every write is automatically copied to other replicas, and consistency is managed by a protocol (Raft), so you don’t have to build this yourself.

• No manual sharding needed. Because the database handles how data is split and moved, you don’t need to decide how to shard by hand.

• You don’t need a special service to route writes vs reads queries. Any node can accept both reads and writes.

• During scaling, you don’t have to worry about which node is the primary – because there is no primary.

• You can scale your nodes one at a time (rollout style). When one node is being upgraded, the others continue to serve traffic. You won’t hit a downtime window just because you're scaling the “primary.”

• Because there's no replica promotion logic to fight with, there's no moment where a replica needs to be “elevated” to primary – it’s all just nodes continuing to serve.

How CockroachDB Works Behind the Scenes ⚙️

In CockroachDB, there are many moving parts behind the scenes. But they work together, so you don’t have to babysit them. The core ideas, which we’ve mostly already touched on, are:

• Splitting data into pieces (ranges)

• Keeping multiple copies of each piece (replicas/replication)

• Making sure all copies agree via Raft consensus

• Moving pieces around to balance the load (automatic rebalancing/distribution)

• Coordinating transactions that might touch many pieces

Let’s go through each of those, one by one.

Ranges: The Small Pieces of Data

Imagine you have a giant book of recipes. If you try to carry the whole thing, it’s heavy. So you split the book into smaller booklets, each covering recipes for a certain range of meals: breakfasts, lunches, dinners, desserts.

In CockroachDB, data is split into ranges, which are like those smaller booklets:

• Each range covers a certain block of data (like “all users whose ID is 1-1000”)

• When a range gets too big (like having too many recipes in one booklet) it’s cut/split into two smaller ones. That makes each piece easier to manage.

• If two neighboring ranges have become very small (few recipes), they might be merged (joined) back together so you’re not keeping too many tiny booklets.

• These splits and merges happen automatically, behind the scenes, so the database stays smooth as things grow or shrink.

This chopping helps the system in many ways: moving pieces, copying them, balancing load, recovering from node failures becomes easier.

Replication: Many Copies for Safety

Nobody likes losing their work, so you keep backup copies. CockroachDB does this for data as well.

For each range, there are usually 3 copies (replicas) stored on different machines (nodes). If one machine dies, you still have others. (cockroachlabs.com). And these copies are always kept in sync: when you write something (for example, insert or update), the change is propagated to the other copies.

The database also tolerates failures. If one node goes down, the system detects it and eventually makes a new copy elsewhere to replace it. So the target number of copies is maintained. This gives you fault tolerance: your data stays safe even when parts of your system fail.

Raft Consensus: How All Copies Agree

Having copies is useful, but you also need them to agree with each other – like all your recipe booklets have the same content in each copy. The Raft protocol is a way to make sure that happens reliably.

Here’s how Raft works in simple terms:

• Each range has a group of replicas. One of these replicas acts as the leader. Others are followers.

• All write requests for that range go through the leader. The leader gets the request, then tells followers to record the same change.

• Once most of the copies (a majority) say “yep, we got it,” the change is considered final (committed). Then the leader tells the client, “Done.”

• If the leader stops working (the machine dies or the network fails), the followers notice it (they stop getting regular “I’m alive” messages), then they hold an election to pick a new leader, and the show goes on.

• This way, the system ensures everyone has the same final data and no conflicting changes happen.

So Raft is the agreement protocol that keeps all copies in sync and safe.

MultiRaft: Keeping Raft Efficient When Things Scale

When you have many ranges (many pieces of the booklets), each range has its own Raft group. That can mean a lot of “are you alive?” messages between nodes, and a lot of overhead. MultiRaft is the trick CockroachDB uses to make this efficient.

MultiRaft groups together Raft work for many ranges that share nodes, so overhead is reduced. Instead of sending separate heartbeat (are you alive?) messages for each range, some of the messages are bundled.

This reduces network chatter and resource waste and helps the database scale smoothly when you have tons of data and many pieces.

Rebalancing: Movement for Balance

When your ranges are not evenly spread across nodes (machines), some machines are doing way too much work, and some hardly any. That’s not good. So CockroachDB automatically moves pieces around to balance things.

• The system watches how busy each node is (how many ranges it holds, how much data, how much read/write traffic).

• If one node is overloaded, it will move some ranges to other nodes.

• If a node dies, the system notices and makes sure that ranges that were on that node get copied somewhere else so safety (replica count) is maintained.

• If you add a new node, the system starts moving ranges to the new node so its resources are used.

This happens without you having to manually decide “move this here, move that there.”

Distributed Transactions: Doing Work Across Multiple Ranges

Often, an operation touches multiple ranges. For example, “transfer money from account A (in range 1) to account B (in range 2)”. That must be handled carefully so that either both parts succeed, or neither do.

CockroachDB supports distributed transactions, meaning a single transaction can work across many ranges. It uses “intent” writes (temporary placeholders) and once everything is ready, it commits the transaction so it becomes permanent. If something fails, it aborts (cancels) the whole thing. The system ensures atomic behavior: all or nothing.

How It All Fits Together: Read + Write Flow (What Happens When You Use It)

Let’s picture a write, step by step:

1. Your app sends a write (for example, “add new user”) to any node in the CockroachDB cluster.

2. That node figures out which range(s) are involved (which pieces hold the data you want to write).

3. For each range, the write goes to that range’s leader.

4. The leader writes the change to their own copy, then tells followers to do the same.

5. Once most copies confirm they have the change, the leader declares it “committed” and tells your app, “yes, write done.”

6. If a node is busy or down, others still handle traffic.

Read flow:

• Your app sends a read (for example “get user by ID”) to any node.

• That node checks its copies. If it has a fresh copy, it answers. If not, it asks the node that does.

Everything works so data is correct, up to date, and reliably available even if machines fail or network lags.

Why This All Matters (Putting It in Plain English)

All these tweaks are important for several key reasons. First of all, because data is chopped into ranges and replicated, no single node is a bottleneck. Also, Raft ensures consensus, so you can trust that data is consistent across all working replicas.

Beyond this, rebalancing is automatic, you don’t have to micromanage shards or worry about nodes drowning in load. And because transactions that touch multiple ranges are coordinated, you can trust ACID properties even in a distributed setup.

Where (and How) Should You Host CockroachDB? ☁️

There isn’t just one “right” way to host CockroachDB. There are a few paths you can pick, each with pros and cons. What you pick depends on cost, control, ease of use, and your risk tolerance.

In this section, we’ll explore:

• Cockroach Labs’ own managed cloud (CockroachDB Cloud)

• “Bring Your Own Cloud” (BYOC) – letting Cockroach Labs manage it inside your cloud account

• Hosting via cloud marketplaces (AWS, GCP, Azure)

• Self-hosting / Kubernetes / your own infrastructure

• And notes on DigitalOcean support

Let’s dive in.

Option 1: CockroachDB Cloud (fully managed by Cockroach Labs)

This is the easiest option if you want to offload operations. You don’t manage nodes (computers, Virtual machines, and so on), upgrades, or backups, as Cockroach Labs handles all that.

What it offers:

• You sign up and click “create cluster.”

• Automatic scaling, zero-downtime upgrades, and managed backups.

• It supports multiple cloud providers behind the scenes (you pick region(s)).

• You get tools, APIs, and Terraform integration to automate it.

• They often give free credits to get started.

Tradeoffs:

• You have less control over underlying infrastructure, for example Virtual Machines, networking, disks, and so on (you trade control for convenience).

• You pay for the managed service premium.

• You rely on Cockroach Labs’ SLAs, uptime, and support.

If you want, you can check it out here: CockroachDB Cloud (managed by Cockroach Labs).

Option 2: Bring Your Own Cloud (BYOC)

This is a middle ground: you keep your cloud environment, but let Cockroach Labs manage the database. It gives you control over infrastructure, billing, network, and so on, while still offloading operational complexity.

How it works:

• You run CockroachDB Cloud inside your cloud account (AWS, GCP, and so on).

• Cockroach Labs still handles provisioning, upgrades, backups, and observability. You manage roles, networking, and logs.

• Useful for complying with regulations, keeping data within your cloud folder/account, and using your cloud discounts.

Tradeoffs:

• You still need to set up cloud aspects (VPCs, IAM, roles) correctly.

• There’s more complexity than pure managed, but more control as well.

• Cockroach Labs needs access to certain parts of your account (permissions).

If you want to explore BYOC, you can read more here: CockroachDB Bring Your Own Cloud.

Option 3: Use Cloud Marketplaces (AWS, GCP, Azure)

If you already use a cloud provider, sometimes the easiest way is to deploy via their marketplace offerings. It gives you familiarity, billing simplicity, and so on.

• GCP Marketplace – CockroachDB is available on the Google Cloud Marketplace, making it easier to deploy within your GCP environment. You can learn more here: GCP Marketplace.

• AWS Marketplace – CockroachDB is listed there: AWS Marketplace.

• Azure Marketplace – Also supported for Azure deployments (SaaS/managed listings): Azure Marketplace.

• DigitalOcean – There is support for CockroachDB deployment on DigitalOcean using their infrastructure: Deploy CockroachDB on DigitalOcean.

These options let you stay in your cloud console, use your existing cloud accounts, and integrate with other resources you already have.

But you're still responsible for certain operational tasks (networking, security, monitoring, backups) depending on how the marketplace offering is configured.

Option 4 (My Favorite 😁): Self-Hosting — Especially Using Kubernetes

If you self-host CockroachDB, you get full control. You’re the boss of everything: the machines, storage, networking, backups, upgrades, monitoring – all of it.

What’s even better is that using Kubernetes means your setup isn’t tied to one cloud provider. You can run it on AWS, GCP, Azure, or even on-premises later, with very little change. Kubernetes gives you a “portable infra” layer.

Managed CockroachDB services charge you extra for “maintenance, upgrades, backup, etc.” – those are baked into the price. But when you self-host, you accept the burden, but also avoid paying that extra margin. You pay for compute, disks, network, and your time/ops work.

You can also self-host in the cloud (using cloud VMs) but still manage every layer: disks, network, security, and so on. Using Kubernetes, there is a sweet middle ground: you get cloud reliability for VMs, but you fully control everything above that.

Why Kubernetes Beats Tools Like Docker Swarm or Hashicorp Nomad for Databases

Because CockroachDB is a stateful system (it holds data), you need strong support for “data that stays even when a pod restarts or moves.” Kubernetes is designed with good primitives for that. Other tools don’t always shine there.

Here’s the comparison in simple terms:

• Docker Swarm / Docker Compose: Great for stateless apps (web servers, APIs), but when it comes to databases, it struggles. Swarm doesn’t natively support persistent volume claims at a cluster level, so if a container (database replica) moves to a different node (VM), it might lose access to its storage. Devs often pin containers to specific nodes manually to avoid this.

• Nomad: More flexible and simpler in some ways, but it’s not as rich in features around connectivity, storage management, and built-in tooling for containers. It works well in mixed workloads, but handling complex databases usually means you need to build extra layers.

• Kubernetes: It has built-in support for stateful workloads:

  • StatefulSets (Properly managing data for each database): This ensures that each CockroachDB replica (pod) keeps its identity and storage intact even if the pod restarts. So the database replica doesn’t lose its “name” or data when things change.

  • Persistent volumes and persistent volume claims (external disks): These are like dedicated hard drives or disks attached to pods (database replicas). Even if a pod moves, crashes, or restarts, the disk (data) stays. Kubernetes makes sure the data stays safe.

  • StorageClasses (choose your disk): You can customize the disks in which your data will be stored, that is:

    • HDD (most affordable, but slower),

    • Balanced Disk (SSD enabled, a balance between costs and speed),

    • Fast SSD (Very fast, recommended by the CockroachDB team, but a bit more expensive than a Balanced Disk).

    • Rolling updates, anti-affinity, (No Downtime, High Availability, Fault tolerance).
      Anti-affinity means you can tell Kubernetes, “don’t put more than one CockroachDB replica on the same VM or physical machine.” This protects you if one VM goes bad, other replicas are safe.

    • Rolling updates let you update one replica at a time (configuration, version, resources) without bringing down the whole cluster. While one replica updates, others serve traffic. That helps avoid downtime.

    • Kubernetes also has ordered start/stop for replicas (via StatefulSets) so things are predictable and safe

  • Vertical vs horizontal scaling (earlier talk – reminder)
    You remember we talked about scaling in prior sections:

    • Horizontal scaling means adding more replicas (more pods, more nodes) so load spreads out.

    • Vertical scaling means increasing the resources (CPU, RAM, disk) of existing nodes/replicas.

In tools like Nomad or Docker Swarm, vertical scaling tends to be harder, often involves stopping services, shutting things down, and restarting VMs, which causes downtime.

Kubernetes makes vertical and horizontal scaling easier at the pod level (you can resize one pod CPU + RAM) and manage rolling upgrades so you don’t take everything down at once.

You can also add more database replicas to the cluster easily (to balance load and make the database process queries faster), and the data is automatically copied to the new database replica (replication), especially when you use the official CockroachDB Helm Chart.

Why Other Tools (Swarm / Nomad / Docker Compose) Don’t Match Up Here

Docker Swarm and Docker Compose are simpler to use and are good when you don’t have much complexity. But they lack robust features for stable storage, default support for replication, vertical scaling, horizontal scaling of stateful services, and so on. For example, Swarm doesn’t have built-in StatefulSets or dynamic volume provisioning like Kubernetes.

Nomad is more flexible than Swarm in some ways, but many users say storage plugins (CSI) are weaker than what Kubernetes has. Also, less built-in for ordering things, rolling updates for stateful apps.

So while these work fine for simpler apps (stateless services, small apps), when you have a distributed stateful SQL database like CockroachDB, Kubernetes gives you more safety, more control, less chance of data loss or misconfiguration.

Because of all this, running CockroachDB on Kubernetes gives you the tools you need baked in, reducing how much custom plumbing you must write yourself.

Trade-offs (things to watch out for)

• You have to manage everything: backups, monitoring the ENTIRE CockroachDB cluster, withstanding failures (fault tolerance), and upgrades. That’s work 🥲.

• You need to know your way around infra (VMs, disks, networking, and inter-node connections) and operations (or have teammates who do – DevOps Engineers, Cloud Architects, Site Reliability Engineers).

• Using managed Kubernetes (like GKE, EKS, AKS) helps as you offload the control plane. You still manage the nodes, storage, and higher layers.

• But even with that, you avoid paying for “database management as a service” markup – you're only paying for infrastructure plus your time.

Setting Up Your Local Environment 🧑‍💻

Alright, we’ve learned quite a bit so far: what CockroachDB is, how it works behind the scenes, and where you can host it. Now, it’s time to roll up our sleeves and get our hands dirty with some practical setup.

Before we deploy CockroachDB, we need a safe “playground” where we can test and experiment without touching the cloud or spending a dime.

Why these tools?

Before we jump into running commands, here’s a quick lookup of what tools we’ll use and why:

• Minikube: A tool that runs a small Kubernetes cluster on your computer. It gives you a local “mini cloud” where you can deploy and experiment.

• Kubectl: The command line tool you’ll use to talk to your Kubernetes cluster to deploy apps, check status, and manage resources.

• Helm: A package manager for Kubernetes. It helps you install complex applications (like CockroachDB) with fewer manual steps.

Step 1: Install Minikube

What is Minikube?
Minikube is a lightweight tool that helps you run a small Kubernetes cluster on your personal computer.

Think of it as your own mini-cloud environment where you can test, deploy, and learn Kubernetes (and in our case, CockroachDB) locally. It’s perfect for learning and experimenting before deploying on the cloud.

Here’s how to get it on different operating systems:

🪟 Windows

1. Make sure you have a hypervisor (VirtualBox, Hyper-V) or Docker installed.

2. Open PowerShell as Administrator.

3. Run:

    choco install minikube
   

   or use:

    winget install minikube
   

4. After installation, check the version:

    minikube version
   

   If it returns a version number, you’re good 👍🏾

If you don’t have the choco or winget package manager, you can install Minikube via PowerShell by following the steps in the docs.

🍎 macOS

1. Ensure you have Homebrew installed.

2. In Terminal, run:

    brew install minikube
   

3. Start the cluster:

    minikube start
   

4. Verify:

    minikube version
   

🐧 Linux

1. Ensure you’re on a supported distribution (Ubuntu, Fedora, and so on) and virtualization (Docker, KVM, and so on) is enabled.

2. Run:

    curl -LO https://storage.googleapis.com/minikube/releases/latest/minikube-linux-amd64
    sudo install minikube-linux-amd64 /usr/local/bin/minikube
    rm minikube-linux-amd64
   

3. Start the cluster:

    minikube start
   

4. Verify:

    minikube status
   

✅ At this point you should have a local Kubernetes cluster up and running on your machine! Next, we’ll install Kubectl so you can talk to the cluster from your command line.

Step 2: Install kubectl

What kubectl does:
kubectl is the command-line tool that lets you talk to your Kubernetes cluster. Using it, you can deploy applications, check your cluster’s health, and manage resources inside your cluster.

You’ll use it a lot when working with Kubernetes on Minikube and later when you deploy CockroachDB.

Here’s how to install it on Windows, macOS, and Linux:

🪟 Windows

1. Open PowerShell as Administrator.

2. Run:

    choco install kubernetes-cli
   

   or if you prefer:

    choco install kubectl
   

3. Then check the version:

    kubectl version --client
   

   If it prints a version number, you’re good.

🍎 macOS

1. Open Terminal.

2. If you have Homebrew installed, run:

    brew install kubectl
   

3. Check the version:

    kubectl version --client
   

   That should show something like “Client Version: v1.x.x”.

🐧 Linux

1. Open your terminal.

2. Download the latest kubectl binary:

    curl -LO "https://dl.k8s.io/release/$(curl -L -s https://dl.k8s.io/release/stable.txt)/bin/linux/amd64/kubectl"
   

3. Make it executable and move it into your PATH:

    chmod +x ./kubectl
    sudo mv ./kubectl /usr/local/bin/kubectl
   

4. Verify:

    kubectl version --client
   

After this, you’ll have kubectl installed and ready to use with your local Minikube cluster. Next up we’ll install Helm, which will make deploying CockroachDB much easier.

Step 3: Install Helm

Helm is basically the package manager for Kubernetes. Think of it like how you use apt, yum, or brew to install software on your computer. Helm does something similar for Kubernetes apps.

With Kubernetes, deploying a full app often means writing lots of configs (manifests – Deployments, Services, PersistentVolumes, ConfigMaps, and so on). Helm lets us bundle all of that into a single “package” (called a chart) so we don’t have to manually create the resources one-after-the-other (which could be hectic to manage btw 😖).

Because our goal is to deploy a pretty complex system (CockroachDB) on Kubernetes – which includes stateful nodes, persistent storage, networking, SSL/TLS, and so on – using a Helm chart makes it so much easier than crafting dozens of YAML files from scratch.

So before we install CockroachDB, we’ll install Helm. This gives us the toolkit to deploy and manage our cluster much more easily.

Let’s install Helm on each platform. After this, you’ll have the helm command ready to deploy apps into your Kubernetes cluster.

🪟 Windows

1. Open PowerShell as Administrator.

2. If you have Chocolatey installed, run:

    choco install kubernetes-helm
   

   Alternatively:

    choco install helm
   

3. Confirm installation:

    helm version
   

   You should see something like version.BuildInfo{Version:"v3.x.x",…}.

🍎 macOS

1. Open Terminal.

2. With Homebrew installed, run:

    brew install helm
   

3. Verify:

    helm version
   

   If you see version info, you’re good.

🐧 Linux

1. Open your terminal.

2. Download and install the binary (example for the latest version):

    curl -fsSL -o get_helm.sh https://raw.githubusercontent.com/helm/helm/main/scripts/get-helm-3
    chmod 700 get_helm.sh
    ./get_helm.sh
   

   Or you can directly download the binary and move it into your PATH.

3. Check version:

    helm version
   

✅ After this, you have helm installed and you’re ready to use it.

In the next part, we’ll use Helm to install CockroachDB into your local Minikube cluster. We’ll add the CockroachDB chart, configure it, and spin up a multi-node replica setup right on your PC.

Deploying CockroachDB on Minikube (The Fun Part Begins 😁!)

Before we go to the cloud, we’ll deploy CockroachDB locally on Minikube using Helm.

This process will help us:

• Understand how CockroachDB runs in a cluster

• Learn how Kubernetes manages database replicas

• Gain hands-on experience before deploying to the cloud

Step 1: Visit ArtifactHub

ArtifactHub is like an App Store for Kubernetes Helm Charts – a huge collection of open-source Helm charts and packages you can easily install.

1. Go to https://artifacthub.io

2. In the search bar, type CockroachDB

3. Click the CockroachDB Helm chart result (you’ll see it published by Cockroach Labs).

You’ll see something like this 👇🏾

Step 2: Explore the Helm Chart

You’ll notice a lot of information on the page:

• README – the documentation for installing and customizing CockroachDB

• Default Values – all the settings that define how the database runs

Don’t worry if it looks overwhelming. We’ll walk through it together 😉

Step 3: Copy the Default Values

Every Helm chart has a default configuration file. These defaults are usually too advanced or too heavy for local setups, so we’ll create our own lighter version. But first, let’s copy the original for reference.

1. On the CockroachDB chart page, click the Default Values button.

2. A modal window will pop up showing a long YAML file.

3. Click the Copy icon in the top-right corner to copy all the default values.

Step 4: Create a Folder for Our Project

We’ll keep everything organized in a single folder.

mkdir cockroachdb-tutorial
cd cockroachdb-tutorial

Inside this folder, create a new file called:

nano cockroachdb-original-values.yml

Now paste all the default values you copied earlier (use Ctrl+V or right-click → Paste), then save and exit (Ctrl+O, then Ctrl+X in nano).

If you’re on Windows, just open Notepad/VSCode, paste the content, and save the file in the same folder.

Step 5: Understanding the Key Configurations

Let’s break down a few important values you’ll notice in the file.

🧩 statefulset.replicas

This tells CockroachDB how many database nodes (replicas) to run in the cluster. By default, it’s set to 3, meaning you’ll have 3 independent database instances that can all read and write data.

⚙️ statefulset.resources.requests and statefulset.resources.limits

These settings tell Kubernetes how much CPU and memory to give CockroachDB.

• requests: the minimum guaranteed amount

• limits: the maximum allowed amount

CockroachDB can be a bit greedy with memory 😅, so limits make sure it doesn’t take everything and leave no room for other apps.

💾 storage.persistentVolume.size

This defines how much disk space each CockroachDB node gets. For example, if you set it to 10Gi and you have 3 replicas, total usage = 30Gi.

💽 storage.persistentVolume.storageClass

This defines the type of disk to use:

• standard: HDD (cheap but slow)

• standard-rwo: SSD (faster and affordable)

• pd-ssd or fast-ssd: NVMe (super fast but pricey)

You can check available storage classes in your Minikube cluster using:

kubectl get sc

On Minikube, the default storage class is usually standard.

You can learn more about Google Cloud storage classes here.

🔐 tls.enabled

This controls whether CockroachDB requires TLS certificates for secure connections.

If true, you’ll need to generate certificates for any app or client that connects to your cluster (instead of using a username and password). This is strongly recommended for production, but for our local Minikube setup, we’ll disable it so it’s easier to play around and test connections.

Step 6: Create a Simplified Values Config for the CockroachDB Helm Chart

We’ll now create a new config file with lighter resource settings for our local test environment.

In the same folder, create:

nano cockroachdb-values.yml

Then paste this:

statefulset:
  replicas: 3
  podSecurityContext:
    fsGroup: 1000
    runAsUser: 1000
    runAsGroup: 1000
  resources:
    requests:
      memory: "1Gi" # You should have 3GB+ of RAM free on your device; else, you can reduce this to 500Mi (this will result in your PC needing just 1.5 GB of RAM free)
      cpu: 1  # The same with this, you can reduce it to 500m CPU if you don't have up to 3 CPU cores (1 CPU core * 3 replicas)
    limits:
      memory: "1Gi"
      cpu: 1
  podAntiAffinity:
    type: ""
  nodeSelector:
    kubernetes.io/hostname: minikube

storage:
  persistentVolume:
    size: 5Gi # Make sure you have 15GB+ of free storage on your local machine, if not, you can reduce it to 2 - 3 Gi
    storageClass: standard

tls:
  enabled: false

init:
  jobs:
    wait:
      enabled: true

Setting the requests and limits to the same value ensures Kubernetes won’t terminate CockroachDB pods due to high memory or CPU usage.

You can read more about this here.

Overview of the YAML values

Now, let’s understand the content of the cockroachdb-values.yml file together

podSecurityContext – why you needed it on Minikube:

podSecurityContext:
  fsGroup: 1000
  runAsUser: 1000
  runAsGroup: 1000

This block sets the Linux user and group IDs that the CockroachDB process runs as inside the container, and the group ownership for mounted files.

Why this matters, simply:

• The CockroachDB process runs as UID 1000 inside the container. If the disk mount (the persistent volume) is owned by a different UID, Cockroach can’t create files there and fails with permission denied.

• runAsUser and runAsGroup make the container process run as UID/GID 1000.

• fsGroup makes the mounted volume be accessible to that group, so the process can write to /cockroach/cockroach-data.

In short, these lines make sure the DB process has permission to create and write files on the mounted disk (volume), which is especially important on Minikube and other local setups where host-mounted storage can have odd permissions.

podAntiAffinity and nodeSelector – what they do:

podAntiAffinity:
  type: ""

nodeSelector:
  kubernetes.io/hostname: minikube

podAntiAffinity is the default behavior. Normally this tells Kubernetes to spread pods across different nodes (VMs), so replicas don’t run on the same physical machine. This is good for high availability, because one node failing won’t kill multiple replicas.

By setting type: "" (empty), you disabled that spreading rule, so Kubernetes can place multiple CockroachDB replicas on the same node.

nodeSelector tells Kubernetes to schedule pods only on nodes that match the label you set (here kubernetes.io/hostname: minikube). That forces all pods to run on the node named minikube.

Quick summary of the effect:

• Good for local testing on a multi-node Minikube cluster, when only one node has properly mounted writable storage.

• Not recommended for production, because it places all replicas on the same machine (single point of failure).

PS: If you’re using another Kubernetes cluster provider, for example K3s, Kind, and so on… this might not get deployed due to the nodeSelector property targeting minikube nodes. So, I'd advise removing the nodeSelector property entirely.

...
nodeSelector:
    kubernetes.io/hostname: minikube
...

✅ At this point, we’ve:

• Copied the default CockroachDB Helm chart configuration

• Created a lightweight version for Minikube

• Learned what each key property means

🚀 Step 7: Install the CockroachDB Cluster Using Helm

Great job so far! You’ve created your cockroachdb-values.yml file and set up your custom configuration for Minikube. Now we’ll actually deploy the cluster.

What we’re going to do:
We’ll use Helm to install the official CockroachDB Helm chart using our custom values. This will spin up your 3-node cluster locally so you can play with it.

Command to run:

helm install crdb cockroachdb/cockroachdb -f cockroachdb-values.yml

Here:

• crdb is the name we’re giving this release (you can pick something else if you like).

• cockroachdb/cockroachdb tells Helm which chart to use.

• -f cockroachdb-values.yml tells Helm to use our custom file instead of default values.

After the command runs:

After a little while the command completes, and you’ll see output telling you what resources were created (pods, services, persistent volume claims, and so on).

Now to check if everything is working, do this:

kubectl get pods | grep -i crdb

This filters pods with “crdb” in the name (our release prefix).

You should see something like:

The three primary pods (0, 1, 2) should be in Running state. The init job or pod (crdb-cockroachdb-init-xxx) should show Completed. This means the initialization tasks (cluster bootstrap) succeeded.

If you see that, congratulations! You’ve got your local CockroachDB cluster up and running! 🎉

Accessing the CockroachDB Console & Viewing Metrics

Alright! Now that our CockroachDB cluster is up and running, let’s take a peek behind the scenes and explore the CockroachDB Admin Console. It’s a beautiful web dashboard that helps us visualize everything happening in our database cluster.

In this section, we’ll learn how to:

• Access the CockroachDB admin console right from your browser 🖥️

• Understand what each built-in dashboard shows (CPU, memory, disk, SQL performance)

• Confirm that our cluster is healthy and that all 3 nodes are working together perfectly

Step 1: Locate the CockroachDB Public Service

CockroachDB automatically creates a public service that allows us to connect to the database and also access its dashboard.

Let’s check it out by running:

kubectl get svc | grep -i crdb

You should see a line similar to:

crdb-cockroachdb-public   ClusterIP   10.x.x.x   <none>   26257/TCP,8080/TCP   ...

This service (crdb-cockroachdb-public) is what we’ll use to connect to both:

• The database itself (via port 26257)

• The dashboard UI (via port 8080)

Step 2: Learn More About the Service

Let’s dig a little deeper to understand it:

kubectl describe svc crdb-cockroachdb-public

Here’s what you’ll notice:

• Port 26257 is used for gRPC connections (this is how applications connect to send and receive SQL queries).

• Port 8080 is used for the web dashboard, where we can view metrics and monitor performance.

Step 3: Access the CockroachDB Dashboard

Now, let’s make the dashboard available on your local computer. Run this command:

kubectl port-forward svc/crdb-cockroachdb-public 8080:8080

This command simply tells Kubernetes:

“Hey, please open a tunnel from my local computer’s port 8080 to the CockroachDB service’s port 8080 in the cluster.”

Once you see something like: