PL/pgSQL,
or: moving the code
to the data

Where should this logic live?

PL/pgSQL is not a replacement for your application layer — it's a tool for logic that is inseparable from the data. Click any leaf node below to see the reasoning.

The round-trip tax

Updating 20 rows from the app with per-row statements costs 20 network round-trips. A function (or a single set-based statement) costs one. Press run and watch the packets.

The point isn't that PL/pgSQL is fast — it's that the network is slow. Work done next to the data skips the tax entirely. (And note: a single set-based UPDATE skips it too, with no function at all.)

Latency math: when the loop becomes a crime

Total time for the app-loop approach is roughly rows × (latency + per-row work). For the in-database approaches it's latency + rows × per-row work. Drag the sliders — the log-scale chart shows why nobody notices in dev (localhost, 0.2 ms) and everybody notices in prod (cross-AZ, 1–10 ms).

Notice the third bar: a plain set-based UPDATE usually beats the PL/pgSQL row-by-row function as well. The hierarchy is set-based SQL → PL/pgSQL → app loop. Use the procedural language when the logic genuinely can't be expressed as a set operation.

The trigger pipeline

Triggers are the one place with no app-layer alternative: they fire on every write path — your service, a migration, a DBA in psql at 2 a.m. Press the button to push an UPDATE through the pipeline.

Key facts to hold onto: BEFORE triggers can mutate or veto the row (RETURN NULL skips it); AFTER triggers see the final row and are where audit logs live; everything runs inside the same transaction — if the trigger raises, the whole statement rolls back, audit entry included.

The case against: why & when not to use it

Every line of PL/pgSQL lives outside your normal engineering pipeline. The code itself is rarely the problem — the operational gravity around it is. Click each row to see what you give up compared to keeping the logic in your application layer.

The scaling argument (the big one)

Your application tier scales horizontally — more pods, done. Your primary database scales vertically, painfully, and is usually the scarcest resource in the system. Every CPU cycle a function burns is a cycle stolen from the thing only the database can do: serving queries. Heavy procedural logic in the DB means you eventually buy a bigger database to run business logic — the most expensive compute you own.

Smells that mean you've gone too far

• Business rules in triggers. "Why did this row change?" should never require archaeology. Triggers are for mechanical invariants (audit, updated_at, counters) — not pricing rules, eligibility checks, or workflow state machines.
• Trigger cascades. A trigger that fires a trigger that fires a trigger. Debugging this is spelunking; one UPDATE becomes an unpredictable avalanche of side effects.
• The 2,000-line stored procedure. If it has paragraphs of IF/ELSIF implementing domain decisions, it's an application that happens to live in your database — untested, unversioned, unloved.
• Wrapping one SQL statement in a function "for reuse." You added planner opacity (section 06, step 7) and a deploy artifact, and saved nothing. Use a view, or just write the SQL.
• Row-by-row loops over big sets. If the function body is FOR r IN SELECT … LOOP UPDATE …, a set-based statement almost certainly does it faster (section 03's amber vs green bars).
• Functions calling out. Needing HTTP calls, queues, or retries from inside the DB means the logic wants to be a service. PL/pgSQL has no async, no real concurrency primitives, no sane way to handle a slow third party mid-transaction.

The portability & people costs

PL/pgSQL is Postgres dialect — fine if you're committed to Postgres (a good commitment), but it's logic you rewrite if anything ever changes, and it's invisible to most static analysis. And on a team, it concentrates knowledge: the median backend engineer reads Kotlin fluently and PL/pgSQL haltingly. Code only one person can review is a liability regardless of how well it runs.

The lab — follow along in a terminal

Seven steps, ~30 minutes. Each one has a goal, the code, and an experiment. Click a step to expand it; tick the checkbox when done. Copy buttons everywhere.

docker run -d --name pgplay -e POSTGRES_PASSWORD=dev -p 5432:5432 postgres:17
docker exec -it pgplay psql -U postgres

CREATE TABLE accounts (id serial PRIMARY KEY, owner text, balance numeric);

DO $$
DECLARE
  i int;
BEGIN
  FOR i IN 1..5 LOOP
    RAISE NOTICE 'hello from iteration %', i;
  END LOOP;
END $$;

CREATE TABLE audit_log (
  id bigserial PRIMARY KEY,
  table_name text, op text,
  old_row jsonb, new_row jsonb,
  at timestamptz DEFAULT now()
);

CREATE OR REPLACE FUNCTION audit() RETURNS trigger AS $$
BEGIN
  INSERT INTO audit_log (table_name, op, old_row, new_row)
  VALUES (TG_TABLE_NAME, TG_OP, to_jsonb(OLD), to_jsonb(NEW));
  RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER accounts_audit
AFTER INSERT OR UPDATE OR DELETE ON accounts
FOR EACH ROW EXECUTE FUNCTION audit();

INSERT INTO accounts (owner, balance) VALUES ('alice', 100), ('bob', 50);

CREATE OR REPLACE FUNCTION transfer(from_id int, to_id int, amount numeric)
RETURNS void AS $$
DECLARE
  from_balance numeric;
BEGIN
  SELECT balance INTO from_balance FROM accounts WHERE id = from_id FOR UPDATE;
  IF from_balance IS NULL THEN
    RAISE EXCEPTION 'account % not found', from_id;
  END IF;
  IF from_balance < amount THEN
    RAISE EXCEPTION 'insufficient funds: % < %', from_balance, amount;
  END IF;
  UPDATE accounts SET balance = balance - amount WHERE id = from_id;
  UPDATE accounts SET balance = balance + amount WHERE id = to_id;
END;
$$ LANGUAGE plpgsql;

INSERT INTO accounts (owner, balance)
SELECT 'user_' || g, random() * 1000 FROM generate_series(1, 100000) g;

CREATE OR REPLACE FUNCTION apply_fee_loop() RETURNS int AS $$
DECLARE
  r record; n int := 0;
BEGIN
  FOR r IN SELECT id FROM accounts WHERE balance > 500 LOOP
    UPDATE accounts SET balance = balance * 0.99 WHERE id = r.id;
    n := n + 1;
  END LOOP;
  RETURN n;
END;
$$ LANGUAGE plpgsql;

\timing on
SELECT apply_fee_loop();

UPDATE accounts SET balance = balance * 0.99 WHERE balance > 500;

ALTER TABLE accounts ADD COLUMN updated_at timestamptz;

CREATE OR REPLACE FUNCTION touch() RETURNS trigger AS $$
BEGIN
  NEW.updated_at := now();
  RETURN NEW;   -- BEFORE triggers return the (possibly modified) row
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER accounts_touch
BEFORE UPDATE ON accounts
FOR EACH ROW EXECUTE FUNCTION touch();

EXPLAIN ANALYZE SELECT * FROM accounts WHERE balance > 500;

CREATE OR REPLACE FUNCTION rich_accounts() RETURNS SETOF accounts AS $$
BEGIN
  RETURN QUERY SELECT * FROM accounts WHERE balance > 500;
END;
$$ LANGUAGE plpgsql;

EXPLAIN ANALYZE SELECT * FROM rich_accounts();

Cleanup

docker rm -f pgplay