When the deploy becomes a PostgreSQL program: the inversion proposed by pgmi

Every team that adopts a migration tool eventually goes looking for a flag. The release needs something the tool did not anticipate: a check that runs after the schema change but before the commit, a CREATE INDEX CONCURRENTLY in the middle of a transactional deploy, an ordering rule that a file name cannot express. Then begins the pilgrimage through the configuration reference, the issue tracker, and the changelog of a version that has not shipped yet.

It is from this symptom that the article "Your Deployment Is a PostgreSQL Program", by Alexey Evlampiev, published on Planet PostgreSQL, starts. The thesis is direct: every migration tool is, at bottom, a program that executes your SQL. Therefore, all deploy semantics (what runs, in what order, inside which transaction, and whether the result can or cannot commit) belong to the tool's vocabulary. Anything outside that vocabulary becomes a feature request.

What an external tool is forced to assume

Evlampiev does not accuse the tools of being poorly built, and the text is explicit about that. The argument is structural: by running as a separate process, outside the database, the tool is forced to take on three responsibilities.

The first is recognizing the boundaries of each statement. PostgreSQL's simple-query protocol accepts a string with multiple statements, so crossing the connection does not, by itself, require a split on the client. The problem appears one step further: any tool that executes statements separately, classifies them, or varies transactional handling needs to know where each one ends. Dollar-quoted function bodies and DO blocks, semicolons inside literals and comments, and routines written with BEGIN ATOMIC turn this into a lexical problem. The scanner that solves this becomes a second compatibility surface between the SQL the project wrote and the SQL PostgreSQL actually receives.

The second is deciding the transactional context before the migration's SQL runs. The file can contain BEGIN and COMMIT, but it cannot choose the context it is placed in, because the tool has already opened (or refused) a transaction around it. That is why Flyway exposes executeInTransaction=false for cases like CREATE INDEX CONCURRENTLY. The setting is correct, but it reveals something: the transactional boundary became metadata about your SQL, instead of a statement inside the deploy program.

The third is maintaining a model of what has already run. The history table is a durable record of what the tool believes it has applied. Since it is kept apart from the database's actual state, the two can diverge, hence the need for repair, baseline, or reconciliation paths in mature tools.

The inversion: the project takes over the loop

The proposal changes exactly one thing. Instead of the tool opening the connection and sending statements according to its own model, it prepares a PostgreSQL session, materializes the project as relations inside it, and executes a single file that the project owns. The tool keeps the execution mechanism; the project takes on the policy.

The destination fits on a single screen, and the author shows it before making the argument:

-- deploy.sql: the deploy, as a program
BEGIN;

DO $$
DECLARE v_file record;
BEGIN
  FOR v_file IN
    SELECT path, content
    FROM pg_temp.pgmi_plan_view
    ORDER BY execution_order
  LOOP
    EXECUTE v_file.content;
  END LOOP;
END $$;

CALL pgmi_test();  -- each test in its own savepoint
COMMIT;            -- only reached here if all tests passed

No external configuration decided this flow. The loop defines the execution order because the project wrote the loop; the commit is conditional because the project placed the test call above it. These are ordinary SQL statements, reviewed and changed like ordinary SQL. This is what makes the tool described, pgmi, essentially inversion of control applied to database deploy: what takes over the control flow is the project's program, not the tool.

The handover is a small session API

The center of the design is what the tool hands over before deploy.sql runs, and this public surface is small enough to fit in your head.

Files arrive as rows. All of the project's files, not just the .sql ones, are loaded into a session-scoped temporary table and exposed through a view. Each row carries path, content as text, directory, extension, size, and two checksums. A project.json, a reference-data CSV, or a policy YAML also arrive as rows, readable with content::jsonb in the same query. The two checksums answer different questions: one checks whether the bytes are identical; the other, under normalized content (comments removed, case unified, whitespace collapsed), checks whether something meaningful changed. Which of the two counts as the file's identity is a policy decision, so both are available.

Parameters arrive twice: as rows in pgmi_parameter_view and as session settings, accessible with current_setting('pgmi.env', true). The second form matters more than it seems, because a parameter stays readable from inside any function called during the deploy, at any depth, without being passed as an argument.

And the plan is a view, not a list. pgmi_plan_view is derived by joining the source table with parsed metadata. Because it is a relation and not a report, three properties follow. It is queryable: the project can make assertions about its own plan before executing it (for example, ensuring nothing precedes the tenancy migration, or that two migrations do not claim the same sort key) with an EXCEPT or EXISTS inside the same transaction. It is derived: an idempotent file can contribute several execution rows, running early to create roles and again later to grant on new objects. And its order does not depend on the server's locale, because the ordering uses COLLATE "C" (byte order), which removes plan drift between the dev's laptop and production.

Tests live in a separate tree, under __test__/, and CALL pgmi_test() expands, before the SQL reaches PostgreSQL, into inline SQL that walks that tree with savepoint isolation for each test.

Where the handover is not clean

The author makes a point of naming the two spots where the tool still rewrites the project's SQL, instead of hiding them. The first is the CALL pgmi_test() macro, expanded in Go, whose expansion contains SAVEPOINT; since PostgreSQL does not allow savepoints in the implicit transactional block of a multi-statement query, the generated SQL requires an explicit BEGIN ... COMMIT around it.

The second is a lexical classification: pgmi locates the first top-level transaction terminator and sends everything up to that point as a single unit (the atomic head, where the test gate lives). What comes after is sent statement by statement, under PostgreSQL's normal autocommit, the same model as psql. This is what makes a CREATE INDEX CONCURRENTLY possible without a second invocation of the tool or a per-file setting. The practical consequence is real and comes with a warning: statements after a COMMIT in the middle of the file are not grouped, and a failure there leaves the previously autocommitted statements applied. The tail's work needs to be safe to restart after a partial success, and a failed CREATE INDEX CONCURRENTLY can leave behind an invalid index, which must be handled explicitly.

When it is not worth it

The most honest part of the article is the one that lists the costs, and it should weigh on any DBA's decision.

• You write the orchestration you used to inherit. A migration tool's defaults represent years of accumulated decisions about order, failure handling, and idempotency. In the inverted model, those decisions become yours, in PL/pgSQL. A team without fluency in PL/pgSQL should not choose this.
• For the simple case, it is worse. If the deploy is a linear sequence of numbered files applied in order, the tool's model fits the problem directly. Evlampiev himself recommends Flyway there, with a shallower learning curve and a bigger ecosystem. The decision rule: the inverted model only pays for its cost when the deploy has shape (phases, conditions, gates, data-dependent branching).
• The durable ledger disappears by default. An external tool's history table survives the session and the operator. Here, an environment's history is only as good as the program that maintains it, and whoever does not write tracking has none. Three lines in the starter recover apply-once semantics, but the choice now belongs to the team.
• A session is the model, and transaction pooling breaks it. The handover lives in session-local objects. A pooler in transaction mode does not pin the client to the same backend, so pg_temp disappears. Deploys need a direct connection or session pooling.
• Everything goes through one connection. The documented envelope is hundreds of SQL files and dozens of data files, not multi-gigabyte loads. Bulk data remains COPY's job.
• The errors are PostgreSQL's errors. No migration-specific error taxonomy is layered on top; failures show up as SQLSTATEs. "For a DBA this is usually preferable," the author writes, but for whoever expects the tool to interpret the failure, it is a downgrade.

The read is worth it for the clarity of the trade-off, more than for adopting the tool. Modeling and integrity first: when the deploy is simple and linear, the ready-made tool is the right, cheaper choice. When the deploy gains real shape, the argument that deploy policy should be SQL the project reviews, versions, and tests in the same transaction as the work it governs is solid, and it forces the right question before you go looking for the next flag.