DEV Community: Clara Decker

Postgres Indexes Under Write Load: Every Index Is a Tax on Every Write

Clara Decker — Mon, 05 Oct 2026 06:53:45 +0000

An index speeds up reads and slows down writes. That trade is well known and almost never quantified, so tables accumulate indexes nobody removes.
The cost is larger than it looks. Every INSERT updates every index on the table. Every update that changes an indexed column updates that index — and critically, an update that touches any indexed column may lose the HOT (heap-only tuple) optimization, which turns a cheap in-page update into one that writes to every index on the table. One badly chosen index can measurably slow writes that do not even reference it.
Three techniques give you most of the read benefit at a fraction of the write cost.

Partial indexes If your query always filters on a predicate, put the predicate in the index: -- Full index: every row, updated on every insert. CREATE INDEX idx_orders_status ON orders (status);

-- Partial: only rows in a state you actually query, and one that is a
-- small fraction of the table. Rows that never match are never indexed,
-- so inserting a completed order touches this index not at all.
CREATE INDEX idx_orders_pending ON orders (created_at)
WHERE status IN ('pending', 'processing');
On a table where 99% of rows are completed, the partial index is roughly 1% of the size, and — because completed orders never enter it — inserting and updating them skips it entirely. This is the single highest-leverage index pattern in Postgres and it is underused.
The catch: the planner will only use it if it can prove your query’s WHERE clause implies the index predicate. WHERE status = 'pending' works. WHERE status = ANY($1) with a parameter array does not, because the value is not known at plan time. Check with EXPLAIN using realistic parameters, not literals.

Covering indexes with INCLUDE An index-only scan avoids touching the heap at all — but only if every column the query needs is in the index. INCLUDE adds payload columns without making them part of the search key: CREATE INDEX idx_orders_customer_covering ON orders (customer_id, created_at DESC) INCLUDE (total_cents, status); INCLUDE columns live only in leaf pages, so they do not bloat internal nodes or affect the sort order. The index is larger than a plain one but supports index-only scans. Index-only scans depend on the visibility map, which is maintained by VACUUM. On a heavily-updated table with lazy autovacuum, the visibility map is stale and the “index-only” scan falls back to heap fetches anyway. Check Heap Fetches in EXPLAIN (ANALYZE, BUFFERS) — if it is high, tune autovacuum before adding more covering indexes.
Find what you are paying for UNUSED_INDEXES = """ SELECT s.schemaname, s.relname AS table_name, s.indexrelname AS index_name, pg_size_pretty(pg_relation_size(s.indexrelid)) AS size, pg_relation_size(s.indexrelid) AS size_bytes, s.idx_scan FROM pg_stat_user_indexes s JOIN pg_index i ON i.indexrelid = s.indexrelid WHERE s.idx_scan < %(min_scans)s AND NOT i.indisunique -- unique indexes enforce constraints AND NOT i.indisprimary AND pg_relation_size(s.indexrelid) > %(min_bytes)s ORDER BY pg_relation_size(s.indexrelid) DESC; """

DUPLICATE_INDEXES = """
-- Indexes whose column list is a PREFIX of another index's column list.
-- (a) is redundant if (a, b) exists: the composite serves both.
SELECT a.indexrelid::regclass AS redundant,
b.indexrelid::regclass AS covered_by,
pg_size_pretty(pg_relation_size(a.indexrelid)) AS wasted
FROM pg_index a
JOIN pg_index b
ON a.indrelid = b.indrelid
AND a.indexrelid <> b.indexrelid
AND array_to_string(b.indkey, ' ') LIKE array_to_string(a.indkey, ' ') || '%%'
WHERE NOT a.indisunique AND NOT a.indisprimary;
"""

WRITE_AMPLIFICATION = """
-- Index count and total index bytes per write. The ratio of index size to
-- table size is a rough proxy for how much each write costs you.
SELECT
t.relname AS table_name,
count(i.indexrelid) AS index_count,
pg_size_pretty(pg_relation_size(t.oid)) AS table_size,
pg_size_pretty(sum(pg_relation_size(i.indexrelid))) AS index_size,
round(sum(pg_relation_size(i.indexrelid))::numeric
/ NULLIF(pg_relation_size(t.oid), 0), 2) AS index_to_table_ratio,
s.n_tup_ins + s.n_tup_upd + s.n_tup_del AS writes
FROM pg_class t
JOIN pg_stat_user_tables s ON s.relid = t.oid
LEFT JOIN pg_index i ON i.indrelid = t.oid
WHERE t.relkind = 'r'
GROUP BY t.relname, t.oid, s.n_tup_ins, s.n_tup_upd, s.n_tup_del
HAVING count(i.indexrelid) > 3
ORDER BY index_to_table_ratio DESC;
"""

def audit(conn, min_scans: int = 50, min_bytes: int = 10 * 1024 * 1024) -> dict:
"""An index-to-table ratio above ~1.5 with high write volume is a strong
candidate for pruning. Read idx_scan with care: stats reset on
restart and on pg_stat_reset(), so a 'never used' index may just be
new. Check pg_stat_get_db_stat_reset_time() before believing it."""
with conn.cursor() as cur:
cur.execute("SELECT stats_reset FROM pg_stat_database WHERE datname = current_database()")
reset_at = cur.fetchone()[0]
cur.execute(UNUSED_INDEXES, {"min_scans": min_scans, "min_bytes": min_bytes})
unused = cur.fetchall()
cur.execute(DUPLICATE_INDEXES)
dupes = cur.fetchall()
cur.execute(WRITE_AMPLIFICATION)
amplification = cur.fetchall()
return {
"stats_since": reset_at,
"unused": unused,
"wasted_bytes": sum(r[4] for r in unused),
"redundant": dupes,
"write_amplification": amplification,
}
The stats_since field is not decoration. Dropping an index because idx_scan = 0 when statistics were reset two days ago is how you delete the index that serves the monthly reporting job.
Dropping safely
Postgres 12+ can deactivate an index without dropping it:
UPDATE pg_index SET indisvalid = false
WHERE indexrelid = 'idx_orders_status'::regclass;
-- Planner now ignores it. Writes still maintain it, so this tests the READ
-- impact only - but that is the risky half. Watch for a week, then DROP.
And always build with CONCURRENTLY on a live table:
CREATE INDEX CONCURRENTLY idx_new ON orders (customer_id);
-- No ACCESS EXCLUSIVE lock. Slower, two table passes, and it can leave an
-- INVALID index behind if it fails - check indisvalid afterwards and drop
-- the corpse, or the next CREATE INDEX CONCURRENTLY will trip over it.
The habit worth building
Add index review to the same cadence as dependency updates. Every quarter: run the audit, drop what is genuinely unused, and convert full indexes to partial where a predicate is stable.
The reason this matters more over time is that indexes are added in response to a slow query and essentially never removed when that query changes. After three years a hot table has eleven indexes, four of which serve queries that no longer exist, and every write pays for all eleven.

We do data platform and database performance work at SoluLab — the InfuseNet case study covers one at scale.

Model Extraction: Rate Limiting Buys Time, It Does Not Stop Distillation

Clara Decker — Mon, 05 Oct 2026 06:47:12 +0000

Model extraction is straightforward in principle: query the API, collect input-output pairs, train a student model on them. For a classifier with a well-defined decision boundary, a few tens of thousands of well-chosen queries can produce a student that agrees with the victim on the large majority of inputs.
What makes it efficient is active learning. The attacker does not sample randomly — they concentrate queries near the decision boundary, where each answer is maximally informative. Uncertainty sampling can cut the query budget by an order of magnitude versus random sampling.
Two things follow that shape every defence:
What leaks scales with output granularity. A full probability vector leaks far more per query than a top-1 label. Confidence scores are the attacker’s gradient signal.
Prevention is not achievable. If the API is useful, it is extractable — the information the legitimate user needs is the information the attacker needs. The goal is to make extraction cost more than licensing, and to detect it while it is in progress.
Defence 1: reduce what each answer reveals
from future import annotations

import hashlib
import math
from dataclasses import dataclass

@dataclass
class OutputPolicy:
top_k: int = 3 # never return the full vector
round_to: int = 2 # decimal places on probabilities
temperature: float = 1.0 # >1 flattens, leaking less boundary detail
deterministic_noise: bool = True

def defend_output(
probs: dict[str, float], request_key: str, policy: OutputPolicy
) -> dict[str, float]:
"""Truncate, round, and add small DETERMINISTIC noise.

Determinism matters: random noise per call is averaged away by an
attacker who queries the same point repeatedly, and it costs you
reproducibility. Derive the perturbation from the input so the same
input always gets the same answer."""
if policy.temperature != 1.0:
    t = policy.temperature
    logits = {k: math.log(max(v, 1e-9)) / t for k, v in probs.items()}
    m = max(logits.values())
    exp = {k: math.exp(v - m) for k, v in logits.items()}
    total = sum(exp.values())
    probs = {k: v / total for k, v in exp.items()}

top = dict(sorted(probs.items(), key=lambda kv: -kv[1])[: policy.top_k])

if policy.deterministic_noise:
    seed = int.from_bytes(
        hashlib.blake2b(request_key.encode(), digest_size=4).digest(), "big")
    for i, k in enumerate(top):
        # +/- half a rounding unit, stable per input.
        jitter = (((seed >> (i * 5)) % 1000) / 1000 - 0.5) * (10 ** -policy.round_to)
        top[k] = max(0.0, min(1.0, top[k] + jitter))

return {k: round(v, policy.round_to) for k, v in top.items()}

Rounding to 2 decimal places sounds trivial. It is not — it removes most of the fine-grained boundary information that uncertainty sampling depends on, and it is invisible to nearly every legitimate consumer. This is the highest value-per-effort defence on the list, and the one most often skipped because it feels like degrading the product.
Defence 2: detect the query distribution, not the volume
An extractor’s query pattern is statistically distinct from a user’s. Legitimate traffic clusters around a few common cases. Extraction traffic is unusually uniform over the input space, unusually close to the decision boundary, and has unusually low repetition.
@dataclass
class ExtractionSignals:
"""Per-principal, over a rolling window. None of these is conclusive
alone; the combination is."""
queries: int = 0
unique_inputs: int = 0
near_boundary: int = 0 # confidence in [0.4, 0.6]
mean_pairwise_distance: float = 0.0
distinct_classes_returned: int = 0

def score(self) -> tuple[float, list[str]]:
    reasons: list[str] = []
    s = 0.0
    if self.queries < 200:
        return 0.0, ["insufficient volume to assess"]

    uniqueness = self.unique_inputs / self.queries
    if uniqueness > 0.97:
        s += 0.3
        reasons.append(f"near-zero repetition ({uniqueness:.2f})")

    boundary_frac = self.near_boundary / self.queries
    if boundary_frac > 0.35:
        s += 0.4
        reasons.append(f"{boundary_frac:.0%} of queries near the decision boundary")

    # Real users do not sweep the whole input space evenly.
    if self.mean_pairwise_distance > 0.8:
        s += 0.2
        reasons.append("queries uniformly spread across input space")

    if self.distinct_classes_returned >= 0.9 * 10:
        s += 0.1
        reasons.append("coverage of nearly all output classes")

    return min(s, 1.0), reasons

The boundary-concentration signal is the strongest. A user classifying real documents gets confident predictions most of the time; an attacker doing uncertainty sampling is deliberately querying where the model is unsure, and that shows up immediately as an anomalous confidence histogram.
Defence 3: watermark the decision boundary
You cannot stop extraction, so make a stolen model provable. Train the victim to produce specific, arbitrary outputs on a secret set of trigger inputs — points far from the natural data distribution, where the behaviour costs nothing on real traffic. A student distilled from the API inherits them.
def verify_ownership(suspect_predict, triggers: list[tuple], alpha: float = 1e-6) -> dict:
"""Under the null hypothesis (independent model), matching the trigger
labels is chance. With enough triggers the binomial p-value is tiny."""
n = len(triggers)
matches = sum(1 for x, y in triggers if suspect_predict(x) == y)
p_chance = 1 / 10 # number of classes
p_value = sum(
math.comb(n, k) * p_chance*k * (1 - p_chance) * (n - k)
for k in range(matches, n + 1)
)
return {"triggers": n, "matches": matches, "p_value": p_value,
"claim_supported": p_value < alpha}
With 100 triggers over 10 classes, chance gives about 10 matches; 40 matches produces an astronomically small p-value. That is evidence you can put in front of a lawyer, which is the actual remedy — technical defences delay extraction, legal ones address it.
What to actually deploy
In order of value per unit of effort:
Round and truncate outputs. Free, instant, removes most of the leak.
Authenticate everything and rate limit per principal. An unauthenticated endpoint cannot be defended at all, because the attacker rotates IPs and there is nothing to bind the budget to.
Log the confidence distribution per principal and alert on anomalies. Cheap, and it catches extraction while it is running rather than after.
Watermark. Moderate effort, and the only one that gives you a remedy after the fact.
Differential privacy on outputs. A real guarantee, a real accuracy cost. Reach for it when the model itself is the product.
The honest limitation
None of this stops a determined, well-resourced attacker who is patient. They can spread queries across accounts, sample slowly, mimic legitimate distributions and accept a lower-fidelity student.
What the defences do is change the economics. Extraction that needs 500,000 queries across 50 accounts over three months, with a detection risk at every step, is a different proposition from one that needs 20,000 queries in an afternoon. For most models that difference is the whole game — and for the handful where it is not, the answer is not to serve the model over a public API at all.

We build and secure ML systems at SoluLab — more on our machine learning development work.

Canary Deployments on Error Budgets: A Fixed Error-Rate Threshold Is a Coin Flip

Clara Decker — Mon, 05 Oct 2026 06:19:05 +0000

The standard canary: shift 5% of traffic to the new version, watch for five minutes, abort if the error rate exceeds 1%.
Consider what that actually measures. 5% of 200 requests/minute over five minutes is 50 requests. One error is a 2% error rate — abort. Zero errors “proves” nothing; a version with a genuine 1.5% regression has roughly a 47% chance of producing zero errors in 50 requests.
You are flipping a coin and calling it a deployment gate. It fails both ways: false aborts on healthy deploys, and false confidence on broken ones. Teams respond by loosening the threshold, which only removes the false aborts and keeps the false confidence.
Two things fix it: compare against the baseline rather than an absolute number, and require enough samples for the comparison to mean anything.
Sample size first
from future import annotations

import math
from dataclasses import dataclass

def required_samples(
baseline_rate: float,
min_detectable_effect: float,
power: float = 0.8,
alpha: float = 0.05,
) -> int:
"""Samples per arm needed to detect a rate change of min_detectable_effect.

Two-proportion z-test. The output is usually much larger than teams
expect, and that is the actual finding: at low traffic you cannot canary
on error rate at all, and pretending otherwise is theatre."""
p1 = max(baseline_rate, 1e-6)
p2 = p1 + min_detectable_effect
p_bar = (p1 + p2) / 2

z_alpha = 1.959963985 if alpha == 0.05 else abs(_z(1 - alpha / 2))
z_beta = 0.841621234 if power == 0.8 else abs(_z(power))

num = (
    z_alpha * math.sqrt(2 * p_bar * (1 - p_bar))
    + z_beta * math.sqrt(p1 * (1 - p1) + p2 * (1 - p2))
) ** 2
return math.ceil(num / (min_detectable_effect ** 2))

def _z(p: float) -> float:
"""Inverse normal CDF, Acklam's rational approximation."""
a = [-3.969683028665376e+01, 2.209460984245205e+02, -2.759285104469687e+02,
1.383577518672690e+02, -3.066479806614716e+01, 2.506628277459239e+00]
b = [-5.447609879822406e+01, 1.615858368580409e+02, -1.556989798598866e+02,
6.680131188771972e+01, -1.328068155288572e+01]
c = [-7.784894002430293e-03, -3.223964580411365e-01, -2.400758277161838e+00,
-2.549732539343734e+00, 4.374664141464968e+00, 2.938163982698783e+00]
d = [7.784695709041462e-03, 3.224671290700398e-01, 2.445134137142996e+00,
3.754408661907416e+00]
plow, phigh = 0.02425, 1 - 0.02425
if p < plow:
q = math.sqrt(-2 * math.log(p))
return (((((c[0]*q+c[1])*q+c[2])*q+c[3])*q+c[4])*q+c[5]) / \
((((d[0]*q+d[1])*q+d[2])*q+d[3])*q+1)
if p > phigh:
q = math.sqrt(-2 * math.log(1 - p))
return -(((((c[0]*q+c[1])*q+c[2])*q+c[3])*q+c[4])*q+c[5]) / \
((((d[0]*q+d[1])*q+d[2])*q+d[3])*q+1)
q = p - 0.5
r = q * q
return (((((a[0]*r+a[1])*r+a[2])*r+a[3])*r+a[4])*r+a[5])*q / \
(((((b[0]*r+b[1])*r+b[2])*r+b[3])*r+b[4])*r+1)

A 0.5% baseline and wanting to catch a 0.5pp regression needs

4,673 requests per arm. At 5% canary traffic and 200 rps that is

~8 min. At 20 rps it is ~1.3 hours - and that is the

honest answer, not 5 minutes.

The gate itself
@dataclass
class ArmStats:
requests: int
errors: int
latency_p99_ms: float

@property
def rate(self) -> float:
    return self.errors / self.requests if self.requests else 0.0

@dataclass
class Verdict:
action: str # "promote" | "hold" | "abort"
reason: str
confidence: float = 0.0

def two_proportion_z(a: ArmStats, b: ArmStats) -> float:
"""Positive z means the canary (b) is worse."""
n1, n2 = a.requests, b.requests
if n1 == 0 or n2 == 0:
return 0.0
p_pool = (a.errors + b.errors) / (n1 + n2)
se = math.sqrt(p_pool * (1 - p_pool) * (1 / n1 + 1 / n2))
return 0.0 if se == 0 else (b.rate - a.rate) / se

def evaluate(
baseline: ArmStats,
canary: ArmStats,
budget_remaining: float, # fraction of the SLO error budget left
min_samples: int,
hard_abort_rate: float = 0.05,
) -> Verdict:
# Hard circuit breaker, no statistics required. A canary returning 5%
# errors is broken; do not wait for significance.
if canary.requests >= 50 and canary.rate >= hard_abort_rate:
return Verdict("abort", f"canary error rate {canary.rate:.1%} exceeds hard limit")

if canary.requests < min_samples:
    return Verdict("hold", f"{canary.requests}/{min_samples} samples")

z = two_proportion_z(baseline, canary)
if z > 1.96:
    return Verdict("abort", f"canary significantly worse (z={z:.2f})", 0.95)

# Error budget governs how much risk you may take, not the canary itself.
# Nearly exhausted budget means small steps and long bakes.
if budget_remaining < 0.1:
    return Verdict("hold", "error budget nearly exhausted; deploys frozen")

if canary.latency_p99_ms > baseline.latency_p99_ms * 1.25:
    return Verdict("abort", "p99 latency regression >25%")

return Verdict("promote", f"no significant regression (z={z:.2f})", 0.95)

Why baseline comparison, not an absolute threshold
Your error rate is not constant. It varies with time of day, with a flaky upstream, with a bot crawl. An absolute 1% threshold aborts a perfectly good canary during a period when the baseline is at 1.2%, and it passes a bad canary during a quiet period when the baseline is 0.05%.
Comparing arms at the same moment cancels all of that. Both versions see the same upstream, the same traffic mix, the same hour. That is the entire reason to run a canary rather than deploying to staging.
Traffic splitting has to be sticky
A user who hits the canary on request one and the baseline on request two experiences a version-mismatch bug you will never reproduce. Split on a stable hash of a session or user identifier, not per request:
import hashlib

def in_canary(session_id: str, percentage: float) -> bool:
"""Stable per session. Also means increasing the percentage only ever
ADDS sessions to the canary - nobody gets moved back."""
h = hashlib.blake2b(session_id.encode(), digest_size=8).digest()
return (int.from_bytes(h, "big") % 10_000) < percentage * 100
The monotonicity property matters. With a naive random split, raising the canary from 5% to 10% reshuffles everyone; with a stable hash, the original 5% stay put and 5% more join. That makes the comparison valid across steps and makes rollback clean.
What the error budget is actually for
The budget does not decide whether this canary is healthy — the statistics do. It decides how much risk you are permitted to take right now.
Full budget: 20% steps, short bakes, automatic promotion. Half consumed: 5% steps, longer bakes, a human approves promotion. Under 10%: no deploys except fixes. That policy converts the SLO from a dashboard into something that changes behaviour, which is the only reason to have one.
If your traffic is too low
Accept it. At 20 requests per second you cannot statistically canary a 0.5pp regression in a useful timeframe, and no amount of dashboard sophistication changes that.
What works instead: shadow traffic (mirror production requests to the new version and compare responses without serving them), heavier pre-production testing, and feature flags that let you turn off behaviour without redeploying. Those are the tools for low-volume services, and reaching for a canary there produces a gate that looks rigorous and decides nothing.

We build delivery pipelines and platform tooling at SoluLab — more on our software consulting work.