postgres-migrations

Functions used in index expressions must be declared IMMUTABLE so PostgreSQL can safely use them during index creation. Wrap your function with the IMMUTABLE keyword, or create an immutable wrapper function that calls your existing logic. This ensures the function's output depends only on its inputs.

Idempotent migrations are designed to run safely multiple times without errors or side effects. Use patterns like CREATE TABLE IF NOT EXISTS and DROP TABLE IF EXISTS so re-running a migration doesn't fail. This is critical for reliable deployments across multiple PostgreSQL instances.

Design migrations that are reversible, test them in staging first, and use transactions to group related schema changes. Avoid locks on large tables during peak hours, add indexes separately from constraint changes, and validate data integrity before and after each migration step.

Generated columns compute values automatically from other columns and are supported in PostgreSQL 12+. When adding generated columns in migrations, use the GENERATED ALWAYS AS syntax and ensure any functions referenced are marked IMMUTABLE to avoid migration failures.

Create indexes concurrently using CREATE INDEX CONCURRENTLY to avoid blocking writes on production tables. Plan index placement based on query patterns, drop unused indexes to reduce write overhead, and use EXPLAIN ANALYZE to validate index effectiveness before and after migrations.

Circular dependencies occur when tables reference each other. Break cycles by deferring constraints, creating tables without foreign keys first, then adding constraints in a separate migration step. Use ALTER TABLE ADD CONSTRAINT with DEFERRABLE INITIALLY DEFERRED for flexible ordering.