database-pro

Run EXPLAIN (ANALYZE, BUFFERS) before and after each change, modifying one variable at a time. Look for sequential scans on large tables, then add indexes matched to the query pattern and rewrite row-by-row logic as joins, window functions, or CTEs.

Use CREATE INDEX CONCURRENTLY in PostgreSQL, which cannot run inside a transaction block. In MySQL, verify the online DDL ALGORITHM and LOCK behavior for your version. A failed concurrent build can leave an invalid index that must be removed before retrying.

Key differences include auto-increment (SERIAL vs AUTO_INCREMENT), string concatenation (|| vs CONCAT), upserts (ON CONFLICT vs ON DUPLICATE KEY), boolean handling, and JSONB versus JSON operators. Window function frame specifications with intervals work only in PostgreSQL.

Use the expand-migrate-contract pattern: add the new nullable structure, deploy code compatible with both versions, backfill in bounded idempotent batches, verify consistency, then remove old structures only after no supported application version uses them.

Bloat comes from dead tuples that VACUUM has not reclaimed. Check n_dead_tup in pg_stat_user_tables, tune autovacuum scale factors per table for high-churn data, and remove existing bloat with pg_repack or REINDEX CONCURRENTLY instead of locking VACUUM FULL.

Use JSONB for variable or sparse attributes, indexed with GIN or expression indexes on hot paths. Extract frequently updated or heavily filtered values into regular typed columns, since JSONB suits containment queries but not high-update workloads.