Postgresql Code Review

Review: @>, ?, ?&, ?| vs ad-hoc ->> equality; GIN vs btree; avoid huge JSONB blobs without partial indexing strategy.

Arrays

-- Often poor: = ANY(array_col) without a supporting index strategy
SELECT * FROM products WHERE 'electronics' = ANY(categories);

-- Prefer @> / && with GIN on the array column when filtering by membership
CREATE INDEX idx_products_categories ON products USING gin (categories);
SELECT * FROM products WHERE categories @> ARRAY['electronics'];

Review: GIN default vs jsonb_path_ops; avoid repeated concatenation in hot loops; prefer set-based updates.

Schema design

-- Generic types where PostgreSQL can model intent more tightly
CREATE TABLE users (
    id INTEGER,
    email VARCHAR(255),
    created_at TIMESTAMP
);

-- Prefer BIGSERIAL/identity, TIMESTAMPTZ, TEXT or citext (if extension), CHECK/ENUM
CREATE TABLE users (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    metadata JSONB NOT NULL DEFAULT '{}',
    CONSTRAINT users_email_format CHECK (email ~* '^[^@]+@[^@]+\.[^@]+$')
);
CREATE INDEX idx_users_metadata ON users USING gin (metadata);

Review: TIMESTAMPTZ vs TIMESTAMP; TEXT vs arbitrary VARCHAR(n); identities vs manual sequences; validation at DB boundary where appropriate.

Custom types and domains

CREATE TYPE currency_code AS ENUM ('USD', 'EUR', 'GBP', 'JPY');
CREATE TYPE transaction_status AS ENUM ('pending', 'completed', 'failed', 'cancelled');
CREATE DOMAIN positive_amount AS numeric(12, 2) CHECK (VALUE > 0);

CREATE TABLE transactions (
    amount positive_amount NOT NULL,
    currency currency_code NOT NULL,
    status transaction_status NOT NULL DEFAULT 'pending'
);

Review: ENUM evolution (value adds are awkward); domains for repeated constraints.

Anti-patterns (PostgreSQL)

Performance

• Missing GIN/GiST/BRIN where the workload needs them.
• JSONB treated as opaque strings; no containment/index strategy.
• Arrays scanned with operators that cannot use indexes.
• Partitioning without a stable, selective partition key and constraint exclusion benefits.

Schema

• Unbounded VARCHAR for small fixed sets instead of ENUM or FK to lookup table.
• Missing CHECK / NOT VALID workflows when migrating large tables.
• Relying on application-only validation for invariants.

Functions and triggers

CREATE OR REPLACE FUNCTION update_modified_time()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  NEW.updated_at := now();  -- column should be timestamptz
  RETURN NEW;
END;
$$;

CREATE TRIGGER trg_table_name_updated_at
  BEFORE UPDATE ON table_name
  FOR EACH ROW
  WHEN (OLD IS DISTINCT FROM NEW)
  EXECUTE FUNCTION update_modified_time();

Review: STABLE/IMMUTABLE correctness; avoid heavy work in row triggers; security definer risks; lock duration in triggers.

Extensions

CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE EXTENSION IF NOT EXISTS pg_trgm;

Review: extension is justified, version pinned in migrations where teams require it, privileges documented.

Security

RLS

ALTER TABLE sensitive_data ENABLE ROW LEVEL SECURITY;

CREATE POLICY sensitive_data_isolation ON sensitive_data
  FOR ALL
  TO app_role
  USING (user_id = current_setting('app.user_id', true)::bigint);

Privileges

-- Avoid
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO app_user;

-- Prefer least privilege per table/sequence/routine
GRANT SELECT, INSERT, UPDATE ON specific_table TO app_user;
GRANT USAGE ON SEQUENCE specific_table_id_seq TO app_user;

Review: RLS + FORCE ROW LEVEL SECURITY where appropriate; policies testable; no superuser-only assumptions in app paths.

Checklist

Schema

• Appropriate types: JSONB, arrays, ENUM/FK, TIMESTAMPTZ, domains where repeated.
• Constraints encode invariants (CHECK, NOT NULL, FK ON DELETE behavior explicit).

Performance

• Index type matches access pattern (GIN for JSONB/arrays, GiST for ranges/geo, etc.).
• JSONB uses containment or path ops that match indexes.
• Arrays use @>, &&, or indexed expressions consistent with queries.

Features

• Extensions named and necessary; search_path safe for SECURITY DEFINER.
• PL/pgSQL used where it reduces round-trips; error handling and transaction boundaries clear.

Security

• RLS where multi-tenant or user-scoped data requires it.
• Roles and grants minimal; sequences and routines granted explicitly.

Review output

Structure findings as: Issue → PostgreSQL-specific rationale → Concrete fix or pattern (SQL snippet or migration step). Treat PostgreSQL as PostgreSQL, not as generic SQL.