Always use parameterised statements, never string concatenation

Rationale: concatenating user input into SQL is the root cause of SQL injection, the most damaging and common database vulnerability. Parameterised (prepared) statements separate query structure from data so input can never change the statement's meaning, and they let the database reuse the plan. Do: SELECT * FROM users WHERE email = $1 with a bound parameter. Don't: build the string as "... WHERE email = '" + input + "'", ever, not even for "trusted" admin tools.

Define constraints in the schema, not only in application code

Rationale: the database is the last line of defence for data integrity, and multiple applications, scripts, and migrations may touch the same tables. Encode NOT NULL, UNIQUE, FOREIGN KEY, and CHECK so invalid data physically cannot be stored. Do: add CHECK (amount >= 0) and a foreign key on order.customer_id. Don't: rely solely on an application validation layer that a bulk import or a second service will bypass.

Index for your actual query patterns, and verify with EXPLAIN

Rationale: indexes only help if the planner uses them, and every index taxes writes and storage. Create indexes that match the columns and order used in your WHERE, JOIN, and ORDER BY clauses, then confirm with EXPLAIN that the plan changed from a sequential scan to an index scan. Do: add a composite index (customer_id, created_at) for a query filtering on customer and sorting by date. Don't: add an index per column "just in case" and never check whether any of them are used.

Keep transactions short and scoped to the work that must be atomic

Rationale: long transactions hold locks and accumulate row versions, increasing contention, deadlocks, and bloat. Open a transaction immediately before the writes that must succeed or fail together and commit promptly. Do: read data, compute, then BEGIN, write, COMMIT. Don't: open a transaction, call a slow external API or wait for user input, and only then write, pinning locks for seconds.

Normalize first, denormalize only with evidence

Rationale: a normalized schema stores each fact once, which keeps writes correct and avoids update anomalies. Denormalization buys read speed but creates redundant data you must keep consistent. Do: design to 3NF, then denormalize a specific hot read path after profiling proves the join cost is the bottleneck. Don't: pre-emptively duplicate columns across tables before you have measured a real read problem.

Rationale: lazy-loading associations row by row turns one logical request into hundreds of queries, which is invisible in development and crippling under load. Decide at the query boundary exactly what related data the use case needs and fetch it in one round trip. Do: use JOIN FETCH, an entity graph, or eager include and inspect the generated SQL. Don't: iterate a collection of parents and touch a lazy field inside the loop or in a serializer.

Use the right isolation level per transaction, defaulting low

Rationale: stronger isolation buys consistency at the cost of blocking and serialization-failure retries, and most operations do not need it. Keep the default (usually Read Committed) and raise to Repeatable Read or Serializable only for the specific transactions whose correctness depends on it. Do: use Serializable for a balance-transfer that must not interleave. Don't: globally set Serializable and then suffer throughput collapse and frequent retries everywhere.

Tune the connection pool to the database, not to the app

Rationale: each pooled connection is a concurrent session the database must serve, and far fewer connections than threads usually maximises throughput because the bottleneck is CPU and disk, not connection count. Size the pool from the database's capacity and measure. Do: start small (e.g. a pool around the number of effective cores plus disk parallelism) and adjust with metrics. Don't: set the pool to hundreds of connections per instance and overwhelm the server with context switching and lock contention.

Make schema changes through versioned, reversible migrations

Rationale: ad-hoc manual DDL drifts environments apart and cannot be reproduced or rolled back. Use a migration tool (Flyway, Liquibase, Prisma Migrate, Alembic) so every change is versioned, ordered, and applied identically everywhere. Do: write a forward migration and test it against a copy of production data. Don't: run ALTER TABLE by hand in production and hope staging matches.

Add columns and indexes online to avoid downtime

Rationale: naive DDL can take long locks that block all traffic on a large table. Use the engine's online options. Do: in PostgreSQL use CREATE INDEX CONCURRENTLY and add nullable columns (or columns with a constant default on modern versions) which are metadata-only; backfill data in batches. Don't: add a NOT NULL column with a computed default to a huge table in one statement during peak hours, holding an exclusive lock.

Write sargable predicates so indexes can be used

Rationale: applying a function or arithmetic to an indexed column forces the optimizer to scan because it cannot match the index. Keep the column bare on one side of the comparison. Do: rewrite WHERE YEAR(created_at) = 2026 as WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01'. Don't: wrap indexed columns in LOWER(), CAST(), or date functions in the predicate unless you have a matching expression index.

Store time as UTC in a timezone-aware type

Rationale: naive local timestamps are ambiguous across daylight-saving transitions and impossible to compare correctly across regions. Persist instants in a timezone-aware type normalised to UTC and convert only for display. Do: use PostgreSQL timestamptz and format to the user's zone in the UI. Don't: store wall-clock local time in a plain timestamp and lose the offset.

Back up regularly and rehearse restores

Rationale: a backup you have never restored is a hope, not a recovery plan. Automate logical and/or physical backups, keep the WAL/transaction log for point-in-time recovery, and periodically restore to a scratch instance to verify integrity and measure restore time. Do: run scheduled pg_dump/base backups plus archived WAL and test a full restore quarterly. Don't: assume nightly dumps work without ever restoring one or measuring your recovery time objective.

Grant least privilege and separate application accounts

Rationale: a compromised or buggy service should not be able to drop tables or read unrelated data. Give each application role only the privileges it needs, and avoid using a superuser for routine connections. Do: grant SELECT/INSERT/UPDATE/DELETE on specific tables to the app role and keep DDL rights with the migration role. Don't: connect the web app as the database owner or a superuser for convenience.