Guide
Four ways to track schema changes in PostgreSQL: event triggers, log_statement, pgaudit and catalog snapshots. What each one records and what it misses.
A query that worked yesterday fails with column "discount_code" does not exist, and the migration folder has no file that drops it. PostgreSQL itself keeps no history of schema changes: the system catalog describes the schema as it is right now and nothing about how it got there. Any history has to be recorded on purpose, and there are four ways to do it, each catching a different slice of what happened.
Event triggers are PostgreSQL's DDL hook. They fire on ddl_command_end after a CREATE, ALTER or DROP, and pg_event_trigger_ddl_commands() tells the function exactly which objects were touched:
CREATE TABLE audit.schema_changes (
id bigserial PRIMARY KEY,
changed_at timestamptz NOT NULL DEFAULT now(),
changed_by text NOT NULL DEFAULT current_user,
command_tag text,
object_type text,
object_identity text,
statement text
);
CREATE OR REPLACE FUNCTION audit.log_ddl() RETURNS event_trigger
LANGUAGE plpgsql AS $$
DECLARE r record;
BEGIN
FOR r IN SELECT * FROM pg_event_trigger_ddl_commands() LOOP
INSERT INTO audit.schema_changes (command_tag, object_type, object_identity, statement)
VALUES (r.command_tag, r.object_type, r.object_identity, current_query());
END LOOP;
END $$;
CREATE EVENT TRIGGER log_ddl ON ddl_command_end EXECUTE FUNCTION audit.log_ddl();
DROP statements need a second trigger on sql_drop, reading pg_event_trigger_dropped_objects(), because a dropped object is gone by the time ddl_command_end runs.
This gives the richest record: who, when, which object and the full statement. The costs are real too. Creating an event trigger needs superuser, or the provider's admin role on managed services, where support varies. The trigger runs inside every DDL transaction, so an error in the function blocks migrations. Event triggers are per database, do not fire for shared objects such as roles and databases, and can be disabled by anyone with enough privileges, which is exactly the person most likely to make an unplanned change.
The lightest option is a configuration line:
log_statement = 'ddl'
Every DDL statement goes to the server log with the user and timestamp from log_line_prefix. Nothing runs inside the transaction and nothing can break a migration. The history lives as long as the log files do, which is usually days, and searching it means grepping text files or the log viewer of the managed service. It records statements, not effects: a DROP SCHEMA ... CASCADE appears as one line, however many tables went with it.
The pgaudit extension, available on most managed PostgreSQL services, writes structured audit records to the same server log. With pgaudit.log = 'ddl' it records schema changes with the object type and name in a fixed format that is easier to parse than plain statement logging. Retention and searching work the same way as above, and it is the usual answer when an auditor asks for evidence of who changed what.
The three methods above record events as they happen. A snapshot records state: read the catalog on a schedule, store a complete description of every table, column, index, view, function and trigger, and compare each snapshot with the previous one.
A minimal homemade version is a nightly job that saves the output of pg_dump --schema-only and diffs it against yesterday's file. It works and shows the difference as text, which is fine for one database and gets noisy with many, because the dump also changes when object order or formatting changes.
Snapshots give up two things: they do not know who made the change, and a change made and reverted between two snapshots is invisible. In exchange they need no superuser, nothing inside the transaction, and they keep working on every managed service, on replicas and on databases where nobody remembered to install a trigger.
Taavik's schema change detection is the snapshot approach without the text diff. An agent inside the network reads pg_catalog with a read-only login every 6, 12 or 24 hours or weekly, with daily as the most frequent option on the free plan, and the workspace compares each scan with the previous one object by object.
A table that comes back under a new name with the same columns is reported as a rename rather than a drop and a create. Each change carries a severity: a removed table or column, a changed data type or a nullability flip is likely breaking, a modified view or function needs attention, an added object or a comment edit is informational. Alerts send the changes that match a rule to Slack, email or a webhook, and the generated documentation keeps a revision with a diff for each change.
One gap is worth knowing: the PostgreSQL scan reads plain tables, so partitioned parent tables and their partitions are not part of the snapshot.
For teams that need both the who and a long history, the practical setup is pgaudit or an event trigger for the statement and the user, plus snapshots for the history that the logs do not keep.
The same question on other engines: SQL Server schema change history, MySQL without DDL triggers, Azure SQL Database, and what Flyway and Liquibase drift checks miss.