PostgreSQL in application code: schemas, indexes and query behaviour

The database schema is an executable contract

Application validation improves feedback, but PostgreSQL constraints protect data regardless of which process writes it. Primary keys, foreign keys, uniqueness, nullability and check constraints should represent invariants the database can enforce.

Do not duplicate every domain rule in SQL. A rule requiring external state or a complex workflow may belong in application behaviour. Put durable relational facts in the schema and give constraint failures a deliberate application translation.

CREATE TABLE account (
  id uuid PRIMARY KEY,
  display_name text NOT NULL CHECK (length(trim(display_name)) > 0),
  status text NOT NULL CHECK (status IN ('pending', 'active', 'closed')),
  external_reference text NOT NULL UNIQUE,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE payment (
  id uuid PRIMARY KEY,
  account_id uuid NOT NULL REFERENCES account(id),
  amount numeric(18, 2) NOT NULL CHECK (amount > 0)
);

Normalisation makes updates less ambiguous

Separate facts that have different identity and lifecycle. Repeating an account name across every payment makes renaming expensive and permits contradictory copies. A foreign key records the relationship without duplicating the account.

Denormalisation can be justified for measured read paths or historical snapshots, but it creates a synchronisation rule the system must own. Record whether copied data is a snapshot, cache or second source of truth.

Indexes should follow real access paths

An index can make selected reads much faster while adding storage and write overhead. Index the columns and order used by important filters, joins and sorting rather than adding one index for every column.

A multicolumn B-tree index is most useful when its leading columns match the query pattern. Partial and expression indexes can target narrower workloads. Unique indexes enforce identity as well as accelerate lookup.

CREATE INDEX payment_account_created_idx
  ON payment (account_id, created_at DESC);

SELECT id, amount, created_at
FROM payment
WHERE account_id = $1
ORDER BY created_at DESC
LIMIT 50;

Read the plan instead of guessing

EXPLAIN shows the plan selected by PostgreSQL. EXPLAIN ANALYZE executes the statement and reports actual timing and row counts, allowing estimated rows to be compared with reality. Use it carefully on writes because the statement really runs unless contained and rolled back.

Look for the first large divergence between estimated and actual rows, unexpected repeated loops, expensive sorting and reads that touch far more rows than they return. A sequential scan is not automatically wrong; it can be cheapest for a small table or a query returning much of it.

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, amount, created_at
FROM payment
WHERE account_id = '2f20b77a-7e6a-4ab9-a28c-f271ef28ea31'
ORDER BY created_at DESC
LIMIT 50;

Transaction boundaries belong to the use case

A transaction should contain the writes and reads that must succeed as one decision. Putting each repository call in its own hidden transaction can leave a workflow partially applied; holding a transaction open across slow external HTTP calls increases contention.

Keep transaction ownership in the application operation that understands atomicity. Handle expected uniqueness conflicts explicitly and make retries safe where isolation failures can occur.

Persistence code should expose query behaviour

Repository abstractions should use domain-oriented inputs and results without pretending all persistence is free. Pagination, locking, missing rows and uniqueness affect the service contract and tests.

Integration tests should apply real migrations and exercise representative queries. Monitor slow queries and connection use in production, then use that evidence to revise indexes or access patterns rather than relying on local sample data.

• PostgreSQL index documentation ↗
• PostgreSQL EXPLAIN guide ↗