Liquibase changesets ↔ PostgreSQL: Pagination of large queries

Question

Goal

During a data‑migration Liquibase changesets often need to process millions of rows. The objective is to use a pagination strategy (LIMIT/OFFSET or cursor‑based) within a changeset so that each batch fits comfortably into a single transaction and does not lock the entire table.

Constraints

Liquibase’s <sql> and <sqlFile> tags accept raw SQL, so pagination syntax must be written for each database engine.
All statements in a single changeset execute in one transaction unless the changeset is split.
Large paginated queries can still hold locks on the full result set if the batch size is not carefully tuned.

Unresolved Decision

The Liquibase community has not yet decided whether to introduce a cross‑database pagination helper (e.g., a custom change type) that abstracts away engine‑specific LIMIT/OFFSET or cursor logic.

Questions

What is the optimal batch size for a LIMIT/OFFSET pagination in a Liquibase changeset targeting PostgreSQL to avoid exceeding transaction limits and minimize lock contention?
Should a database‑agnostic pagination helper be implemented as a custom change type, or is it preferable to embed engine‑specific SQL directly in <sql> tags?
How can we verify that a paginated batch INSERT does not lock the entire source table when executed as part of a Liquibase changeset?

Tasadduq Burney · Answer

Direct Answers

There is no universal optimal batch size for PostgreSQL. Start with 1,000–5,000 rows per batch and tune based on transaction duration, WAL volume, and replication lag. For deep pagination, prefer keyset pagination (WHERE id > last_seen ORDER BY id LIMIT n) over LIMIT/OFFSET, because PostgreSQL scans and discards skipped rows in OFFSET queries.

For a PostgreSQL-only target, embed pagination SQL directly in <sql> or <sqlFile> tags rather than building a custom change type. A custom change is justified only when identical logic must run across multiple database engines.

To verify that a paginated batch does not lock the entire source table, inspect pg_locks joined with pg_stat_activity during execution. A read-only paginated SELECT under MVCC acquires an ACCESS SHARE table lock, which conflicts with DDL but not with concurrent DML; it should not hold ACCESS EXCLUSIVE or SHARE UPDATE EXCLUSIVE locks.

Batch Sizing and Transaction Scope

Liquibase wraps all statements in a changeset in one transaction by default. Large batches extend transaction lifetime, increasing bloat, vacuum pressure, and replication lag. If a single changeset processes millions of rows, split it into multiple changesets or disable transactional wrapping (if supported by your Liquibase version and change type) to bound peak transaction size—accepting that rollback becomes per-changeset rather than global.

Use keyset pagination when the sort key is stable and indexed. LIMIT/OFFSET without ORDER BY can produce duplicate or missing pages if concurrent writes occur during the migration.

Safe Verification Queries

Run these on staging while the migration executes:

-- Check lock modes held by active Liquibase session
SELECT a.pid, a.state, l.locktype, l.mode, l.granted
FROM pg_locks l
JOIN pg_stat_activity a ON a.pid = l.pid
WHERE a.datname = current_database()
  AND l.locktype = 'relation';

-- Confirm index usage for the batch query
EXPLAIN ANALYZE
SELECT id FROM source_table
WHERE id > last_seen
ORDER BY id
LIMIT 5000;

Monitor pg_stat_statements or PostgreSQL logs to ensure WAL generation and checkpoint frequency remain acceptable for the chosen batch size.

When to Consider a Custom Change

A custom change type requires Java development, testing, and alignment with your Liquibase version. It adds maintenance overhead. Use it only if the same paginated migration must execute identically on PostgreSQL, MySQL, Oracle, and similar. Otherwise, engine-specific SQL in <sql> is simpler and more debuggable.