Next steps: the PostgreSQL ecosystem and migrations

Migration tooling for real production schemas, managed Postgres trade-offs, monitoring that matters, and a reading path forward.

Migrating a real database

-- the pattern that makes a large schema change safe
-- migration 1: add, nullable, no rewrite
alter table invoice add column settled_at timestamptz;

-- migration 2: backfill in batches from the application or a script
-- update invoice set settled_at = paid_at
-- where settled_at is null and id between 1 and 10000;   -- repeat

-- migration 3: constraint, validated separately
alter table invoice add constraint invoice_settled_check
  check (settled_at is null or settled_at >= issued_at) not valid;
alter table invoice validate constraint invoice_settled_check;

-- migration 4, a release later: tighten and clean up
alter table invoice alter column settled_at set default now();
alter table invoice drop column legacy_settlement_flag;

Managed versus self-hosted

• Managed services give you backups, failover, patching and monitoring. You give up superuser access, some extensions, and control over the exact minor version.
• Confirm the extensions you depend on are available before committing - a missing extension can invalidate an architecture decision.
• Check the connection story: a pooler in front, the maximum connection limit, and whether prepared statements work through it.
• Verify the restore path yourself. A provider's dashboard reporting "backups active" is not evidence that your specific restore works within your recovery objective.
• Read replica lag and failover behaviour differ by provider; test a failover in staging so the promotion is a rehearsal, not a discovery.

-- monitoring queries worth a dashboard
select count(*) filter (where state = 'active')             as active,
       count(*) filter (where state = 'idle in transaction') as idle_in_txn,
       count(*)                                             as total
from pg_stat_activity where backend_type = 'client backend';

select schemaname, relname,
       round(100 * n_dead_tup / greatest(n_live_tup + n_dead_tup, 1), 1) as dead_pct,
       last_autovacuum
from pg_stat_user_tables where n_dead_tup > 10000
order by dead_pct desc limit 10;

Where to go next

• Read the official documentation chapter by chapter. It is unusually good, and the concurrency and performance chapters answer most production questions directly.
• Turn on log_min_duration_statement in staging and read the slowest queries weekly. That habit finds problems before users do.
• Practise a restore and a failover on a scratch cluster. Operational confidence comes from having done it, not from having read about it.
• Learn EXPLAIN properly: the ability to read a plan is the difference between tuning and guessing.
• Keep an eye on incremental improvements in each major release - MERGE, logical replication improvements, and better partitioning each removed a reason to add another system.

The goal is not to become a database administrator. It is to know enough about the planner, the locking model and the operational path that you can design a schema and a deployment that will not need heroics at 3am.

FAQ

Related

Backup, PITR and replication Query planning and performance tuning

Last refreshed 2026-09-18.