database

Use CREATE INDEX CONCURRENTLY to avoid table locks when adding indexes, and add columns as nullable first before setting NOT NULL constraints in separate steps. Split incompatible changes like column renames into multi-deploy sequences: add new column, copy data, then drop the old one.

Use six-digit zero-padded sequential numbers computed as max existing number plus one within the target migrations directory only. Name files as {number}_{description}.up.sql and {number}_{description}.down.sql with matching stems, and never renumber already committed migrations.

The standard database/sql package provides the base interface, sqlx adds struct mapping extensions, and pgx is the native PostgreSQL driver. For migration management, goose or migrate are the recommended tools.

Replace per-row queries inside loops with batch queries using IN clauses or JOINs. For example, instead of calling FindOrdersByUserID for each user, collect all user IDs and call FindOrdersByUserIDs once.

Run EXPLAIN ANALYZE to compare estimated versus actual row counts and check the scan type. A Seq Scan suggests a missing index, while large row estimate gaps indicate stale table statistics that need refreshing.