POSTGRESQL / CATALOG REVIEW
Investigating PostgreSQL schema drift
Schema Migrator compares selected PostgreSQL catalog definitions and table presence, but does not compare column definitions or nullability. A clean report therefore cannot rule out column drift. The checks below compare expected objects and control records with the live catalog, separately from SQL-file checksum changes.
Establish the expected state and target scope
Start with the reviewed SQL manifest and its control state, then identify the target database and schemas being inspected. A catalog read needs access to that target. Record when it was read so findings are not mistaken for a timeless view.
- Keep manifest definitions, migration apply statuses, source paths and recorded checksums with the report.
- Check whether control state was available. A warning about unavailable control state leaves part of the comparison unresolved.
- A pending or failed apply status is a control-state issue, not proof that someone edited a catalog object manually.
Compare a synthetic expected catalog with an observed catalog
The following teaching example is constructed, not captured from a database. Assume tracked objects without a pending status have an applied control status. The final object is pending.
| Object | Expected | Observed | Finding |
|---|---|---|---|
| public.observations_source_idx | Index on source | Same index name, indexed column is observed_at | definition_changed |
| public.observations_observed_at_idx | Tracked index on observed_at | Index absent | missing_actual |
| public.review_notes | Absent from manifest and control records | Table present | untracked_actual |
| public.observations_source_order_idx | Tracked index. Apply_status=pending | Index absent | pending_or_failed_control |
- A missing object can result from incomplete application or later removal. The category alone does not establish the cause.
- An untracked object may be an intended addition that has not been recorded. Compare ownership and change history before proposing removal.
- The pending index is reported through its control status rather than counted again as an ordinary missing object.
Read the definition behind a changed-index finding
Names are insufficient: the expected and observed object must also agree on the compared definition. This synthetic index comparison changes the indexed column while retaining the same index name.
Expected: CREATE INDEX observations_source_idx
ON public.observations (source);
Observed: CREATE INDEX observations_source_idx
ON public.observations (observed_at);
Finding: definition_changedA condensed teaching view, not a literal API response. Retain the complete reported DDL for an actual review.
- Definition comparisons cover functions, procedures, triggers, views, materialized views and indexes. The reader checks table presence, but does not compare column definitions or nullability. Those need a separate check.
- An index definition finding describes structure, not how a proposed replacement will behave under load. Review data size, permissions and locking conditions before execution.
Turn a finding into a reviewed change
Preserve the observed definitions and check the source history. Decide whether the manifest or the live object reflects the intended design. Write a new ordered migration when a correction is needed, rehearse it against non-production data, and inspect the catalog again afterward.
- Keep active ordered migrations append-only. Do not conceal drift by rewriting an already recorded file.
- Handle target credentials and DDL through the deployment workflow. Catalog inspection may create and drop temporary session views to normalize view definitions. It applies no permanent correction.
- A clean report establishes agreement within the inspected scope and comparison logic, not complete database health.
Source code
The implementation behind the examples. Examples are educational inputs, not production records or measured results.
- PostgreSQL drift diff engine
Finding types, control-state handling and expected-versus-observed comparisons.
- PostgreSQL catalog reader
The live catalog scope that supports drift inspection.