Postgresql Review

PostgreSQL Review Skill

Review and optimize PostgreSQL queries, schemas, and patterns for Nav applications. Covers EXPLAIN analysis, index strategies, JSONB patterns, and common anti-patterns.

Query Analysis

Run EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) to analyze queries:

sqlCopy code
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM vedtak
WHERE bruker_id = '12345678901'
  AND status = 'aktiv'
ORDER BY opprettet_dato DESC
LIMIT 10;

Red Flags in EXPLAIN Output

Index Strategies

sqlCopy code
-- Simple index for lookups
CREATE INDEX idx_vedtak_bruker_id ON vedtak(bruker_id);

-- Composite index — columns in order of selectivity
CREATE INDEX idx_vedtak_bruker_status ON vedtak(bruker_id, status);

-- Partial index — only relevant rows
CREATE INDEX idx_vedtak_aktive ON vedtak(bruker_id)
WHERE status = 'aktiv';

-- Covering index — avoids table lookup
CREATE INDEX idx_vedtak_covering ON vedtak(bruker_id, status)
INCLUDE (opprettet_dato, belop);

-- Concurrent — no table locking (requires outside transaction)
CREATE INDEX CONCURRENTLY idx_vedtak_dato ON vedtak(opprettet_dato);

When to Use What?

JSONB Patterns

sqlCopy code
-- ✅ Correct — GIN index for JSONB queries
CREATE INDEX idx_metadata_gin ON hendelser USING GIN (metadata);

-- Query JSONB
SELECT * FROM hendelser
WHERE metadata @> '{"type": "vedtak", "tema": "dagpenger"}';

-- Fetch nested values
SELECT
    id,
    metadata->>'type' AS type,
    metadata->'detaljer'->>'belop' AS belop
FROM hendelser;

-- ❌ Wrong — casting in WHERE without index
SELECT * FROM hendelser
WHERE (metadata->>'opprettet')::timestamp > NOW() - INTERVAL '7 days';
-- ✅ Better — use expression index
CREATE INDEX idx_metadata_opprettet ON hendelser (((metadata->>'opprettet')::timestamp));

Common Table Expressions (CTEs)

sqlCopy code
-- ✅ Correct — CTE for readability
WITH aktive_vedtak AS (
    SELECT bruker_id, COUNT(*) AS antall
    FROM vedtak
    WHERE status = 'aktiv'
    GROUP BY bruker_id
),
siste_aktivitet AS (
    SELECT bruker_id, MAX(opprettet_dato) AS sist_aktiv
    FROM aktivitetslogg
    GROUP BY bruker_id
)
SELECT
    av.bruker_id,
    av.antall,
    sa.sist_aktiv
FROM aktive_vedtak av
JOIN siste_aktivitet sa USING (bruker_id)
WHERE av.antall > 1;

Window Functions

sqlCopy code
-- Ranking within group
SELECT
    bruker_id,
    vedtak_id,
    opprettet_dato,
    ROW_NUMBER() OVER (PARTITION BY bruker_id ORDER BY opprettet_dato DESC) AS rn
FROM vedtak
WHERE rn = 1;  -- Latest vedtak per user

-- Running total
SELECT
    dato,
    antall,
    SUM(antall) OVER (ORDER BY dato) AS kumulativt
FROM daglig_statistikk;

Anti-patterns

N+1 Queries

kotlinCopy code
// ❌ Wrong — N+1: one query per user
val brukere = repository.findAll()
brukere.forEach { bruker ->
    val vedtak = vedtakRepository.findByBrukerId(bruker.id)  // N extra queries
}

// ✅ Correct — JOIN or batch query
val brukereOgVedtak = repository.findAllWithVedtak()  // Single query with JOIN

SELECT *

sqlCopy code
-- ❌ Wrong — fetches all columns incl. large JSONB/TEXT
SELECT * FROM dokument WHERE bruker_id = '12345';

-- ✅ Correct — only necessary columns
SELECT id, tittel, opprettet_dato FROM dokument WHERE bruker_id = '12345';

Missing LIMIT on Unbounded Data

sqlCopy code
-- ❌ Wrong — can return millions of rows
SELECT * FROM hendelse WHERE type = 'innlogging';

-- ✅ Correct — always limit result set
SELECT * FROM hendelse WHERE type = 'innlogging'
ORDER BY opprettet_dato DESC
LIMIT 100;

Connection Pooling

kotlinCopy code
// HikariCP — recommended configuration for Nais
HikariDataSource().apply {
    jdbcUrl = System.getenv("DB_JDBC_URL")
        ?: "jdbc:postgresql://${System.getenv("DB_HOST")}:5432/${System.getenv("DB_DATABASE")}"
    username = System.getenv("DB_USERNAME")
    password = System.getenv("DB_PASSWORD")
    maximumPoolSize = 5       // Nais: start low, scale up as needed
    minimumIdle = 1
    connectionTimeout = 10_000
    idleTimeout = 300_000
    maxLifetime = 600_000
    validationTimeout = 5_000
}

Migration Strategies for Large Tables

sqlCopy code
-- Add column with default (PostgreSQL 11+ — instant, no rewrite)
ALTER TABLE stor_tabell ADD COLUMN ny_kolonne BOOLEAN DEFAULT false;

-- Create index without locking the table
CREATE INDEX CONCURRENTLY idx_ny ON stor_tabell(ny_kolonne);

-- Batch update (avoid long transaction)
-- Run in application code with batches of 10,000 rows:
UPDATE stor_tabell SET ny_kolonne = true WHERE id BETWEEN $1 AND $2;

Checklist

• Do all WHERE columns have indexes?
• Has EXPLAIN ANALYZE been run for new/changed queries?
• Are we avoiding SELECT * in production code?
• Do we have LIMIT on queries that can return many rows?
• Are JSONB columns indexed with GIN?
• Is connection pool size appropriate (5-10 for Nais)?
• Are migrations on large tables run with CONCURRENTLY?

Review contract

Axes: the Checklist above — indexing, EXPLAIN evidence, column and row bounds, JSONB, connection pooling, migrations.

The review ends here, in this shape. A review with no output section has not run.

Judge primary evidence. The diff, the file, the EXPLAIN output, the rendered page — never your own summary of the change, and never your memory of what you meant to write. Resolve the base first, then cover committed, staged, unstaged and untracked changes. Read untracked files in full: diff output omits them.

Every axis reports. Each axis produces at least one finding, or one line saying what it inspected and what that evidence does not prove. An axis that says nothing has not looked.

Report what you inspected. "No findings in the two files I opened" and "no findings in the change" are different claims, and only the first one is ever true.

Copy code
Inspected:     <files, queries, URLs, viewports actually opened>
Not inspected: <in scope, not examined, and why>
Findings:      <n blocking, n concerns>
Verdict:       BLOCK | CONCERNS | CLEAN

• BLOCK — at least one finding that, shipped as written, risks data loss, a security or privacy breach, a production incident, or a wrong answer to a user.
• CONCERNS — no blocking finding, but at least one a maintainer should fix or answer first.
• CLEAN — every axis inspected against primary evidence, nothing at either bar. CLEAN claims only the axes above and the files on the Inspected line, and it is wrong if a defect is later found in them.

Not a review: LGTM; restating what the change does; cosmetic findings only; reading the changed lines without the code they call.