Honest Limitations#

pgmi trades framework-managed complexity for SQL-level control. That trade has real costs. This page lists them honestly so you can decide if they apply to your team and project.

PL/pgSQL expertise required#

pgmi’s deploy.sql is a PL/pgSQL program, not configuration. Your team needs to be comfortable with:

FOR v_file IN (SELECT ...) LOOP ... END LOOP
EXECUTE v_file.content
BEGIN ... EXCEPTION WHEN OTHERS THEN ... END
current_setting('pgmi.key', true)

Honest test: If your team would struggle to write a PL/pgSQL function that loops over a query result, calls EXECUTE on each row, and handles exceptions — pgmi’s power is inaccessible. The basic template works out of the box, but customizing deployment logic requires PL/pgSQL fluency.

This is an intentional constraint, not an oversight. pgmi’s flexibility comes from giving you a real programming language (PL/pgSQL) instead of a configuration DSL.

Migration tracking is a choice you make, not a default you inherit#

There is no built-in schema_version table. pgmi does not track migrations, because pgmi does not decide what runs — your deploy.sql does.

Basic template, default: every deployment re-runs every file. Your SQL must be idempotent (CREATE OR REPLACE, IF NOT EXISTS, ON CONFLICT DO NOTHING). Nothing to drift.

Basic template, apply-once: deploy.sql ships with a three-line tracking block — a _migration ledger, a NOT EXISTS filter, an INSERT after each file. Uncomment the lines marked (A), (B), (C) and each migration runs once per path, with its checksum stored. That is all it does. It does not order by version, and it does not stop on a checksum mismatch: whether a changed file should warn, fail or be ignored is an IF you write in deploy.sql. The template README compares both models side by side, and examples/apply-once-tracking runs the apply-once model with drift detection in CI.

Advanced template: a fuller PL/pgSQL tracking system recording script UUIDs, checksums, and execution history. More capable, and more code you own.

The honest tradeoff is not “pgmi has no tracking”. It is that you pick the model — path-based? UUID-based? does a changed checksum warn, fail, or get ignored? Flyway makes that choice for you; pgmi makes you make it, in three lines you can read.

Debugging is raw PostgreSQL errors#

When a migration fails, pgmi shows:

execution failed: ERROR: relation "users" does not exist (SQLSTATE 42P01)

pgmi surfaces PostgreSQL’s DETAIL, HINT, and WHERE fields when the server sends them, and names the project file the error came from by matching the text PostgreSQL was executing against the files it loaded. For parse and analysis errors (syntax, unknown column) PostgreSQL also reports a position, so pgmi prints the line and column in that file. Runtime errors (a constraint violation, division by zero) carry no position: you get the file, not the line.

The limit is an exception handler in deploy.sql that re-raises with RAISE EXCEPTION: that builds a new error and discards the text and position. Let the error propagate, or re-raise with a bare RAISE;. See deploy.sql guide .

CREATE INDEX CONCURRENTLY#

CREATE INDEX CONCURRENTLY cannot run inside a transaction block. This is a PostgreSQL constraint, not a pgmi limitation — the same issue affects Flyway, Liquibase, Prisma, Goose, and Drizzle.

pgmi’s execution contract handles it directly: before your first top-level COMMIT, pgmi’s atomic mode; after it, psql mode. The head of deploy.sql (through the first top-level transaction terminator) runs as one transaction; every top-level statement after it runs per-statement autocommit on the same session — which is exactly what CREATE INDEX CONCURRENTLY needs. pgmi’s temp views survive the COMMIT (session-scoped) and stay queryable:

-- Phase 1: transactional migrations (atomic head)
BEGIN;
-- ... migrations ...
COMMIT;

-- Phase 2: concurrent indexes, psql mode (per-statement autocommit)
CREATE INDEX CONCURRENTLY idx_user_email ON users(email);

-- Phase 3: an atomic backfill says so explicitly
BEGIN;
UPDATE users SET email_normalized = lower(email) WHERE email_normalized IS NULL;
COMMIT;

-- Phase 4: more concurrent work
CREATE INDEX CONCURRENTLY idx_order_date ON orders(created_at);

The trade-offs to know:

After the first COMMIT, statements are not implicitly grouped — a later atomic phase writes its own BEGIN ... COMMIT. A mid-tail failure keeps earlier autocommitted statements applied, so tail statements should be idempotent. For a concurrent index that means reaping an INVALID leftover before CREATE INDEX CONCURRENTLY IF NOT EXISTS — IF NOT EXISTS alone matches on name and would skip the wreckage forever; see making a concurrent index re-runnable .
Concurrent index statements cannot go through EXECUTE inside a DO block — PostgreSQL refuses CREATE INDEX CONCURRENTLY from any function execution context (“cannot be executed from a function”), even after a COMMIT. Write them explicitly at top level; there are no loops or variables there.

See deploy.sql guide for the full contract and examples/lock-safe-deploy/ for a runnable project.

No built-in test report format#

By default, pgmi tests produce NOTICE messages:

NOTICE: [pgmi] Test: ./__test__/test_user_crud.sql

The default reporter has no JUnit XML, TAP, JSON report, or timing information. A test either succeeds (the suite continues) or fails (RAISE EXCEPTION aborts the transaction), and a full run ends with [pgmi] Test suite passed.

Structured output is a callback you write or copy. CALL pgmi_test('pattern', 'pg_temp.my_callback') hands every test event to a PL/pgSQL function, which can emit any format. examples/tap-reporter/ is a working TAP 14 reporter. See Testing for the function signature.

No GUI, no IDE plugin, no ecosystem#

pgmi is a CLI tool. There is no:

VS Code extension
IntelliJ/DataGrip plugin
Maven or Gradle plugin
Spring Boot starter
Jenkins plugin
Commercial support or training programs
Web dashboard

Documentation is README.md, these docs, pgmi ai skills, and the embedded AI documentation.

File loading has practical limits#

pgmi loads all project files into Go memory, then batch-inserts them into PostgreSQL session-scoped temporary tables. This means:

A 100 MB project uses ~100 MB Go memory + wire transfer time + PostgreSQL storage for temp tables
PostgreSQL temp tables use local buffers (temp_buffers, default 8 MB) and automatically spill to disk when data exceeds the buffer — there is no inherent RAM limitation on temp table size
Files are loaded as text and assumed to be UTF-8
A file with a NUL byte or invalid UTF-8 fails the deploy before pgmi connects, naming the file
Each file is capped at 10 MiB; set PGMI_MAX_FILE_SIZE (bytes) to change the cap

Practical thresholds:

The bottleneck for large projects is INSERT throughput (parameterized row-by-row inserts) and wire transfer time, not memory:

Scale	Works well
Hundreds of SQL files, dozens of JSON/CSV files (1 KB–10 MB each)	Yes
Multi-gigabyte bulk data loads	No — use `COPY` or external ETL
Millions of CSV rows via `string_to_array` in PL/pgSQL	Slow — use `COPY` for bulk imports

pgmi is designed for schema deployment and reference data loading, not bulk data pipelines.

Connection poolers are incompatible#

pgmi requires session-scoped temporary tables that survive for the entire deployment. Connection poolers in transaction or statement mode reassign backends between operations, destroying the temp tables.

Pooler	Session mode	Transaction mode	Statement mode
PgBouncer	Works	Breaks	Breaks
Pgpool-II	Works	Breaks	N/A
AWS RDS Proxy	Works (pinned)	Breaks	N/A
Azure PgBouncer	Works	Breaks	Breaks

Solution: Use the direct PostgreSQL endpoint (port 5432) for pgmi deployments, not the pooled endpoint (port 6432). Your application traffic continues to use the pooler as usual.

See Connections for details.

The advanced template is a real program#

Scope: advanced template only. This is SQL that pgmi init --template advanced copied into your project, not behaviour of the pgmi binary.

The advanced template’s deploy.sql is several hundred lines of PL/pgSQL that handles:

XML parameter declaration and validation
Database role setup (owner, admin, api, customer)
Migration tracking with UUID-based idempotency
Audit logging to internal.deployment_script_execution_log
Test execution gating
Five application schemas (internal, core, api, common, membership) plus extensions for extension objects

If it breaks, you debug PL/pgSQL exception handling, not framework configuration. You own this code — pgmi scaffolds it, but you maintain it.

For teams comfortable with PL/pgSQL, this is a feature. For teams that want a tool to handle complexity, this is a cost.

Who should use pgmi#

Good fit:

Teams fluent in SQL/PL/pgSQL who want deployment logic in the database’s native language
Projects that need conditional deployment, data ingestion, or custom transaction strategies
Multi-cloud PostgreSQL deployments (same deploy.sql works everywhere)
Teams that value transparency — every piece of deployment state is queryable SQL

Not a good fit:

Teams that prefer framework-managed migrations with zero SQL beyond DDL
Projects that need multi-database support (pgmi is PostgreSQL-only)
Organizations that require GUI tools, commercial support, or enterprise ecosystem integrations

See Why pgmi for when pgmi’s approach makes sense.

Trade-offs