Liquibase changesets ↔ PostgreSQL: Pagination of large queries

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

1. 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?
2. 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?
3. How can we verify that a paginated batch INSERT does not lock the entire source table when executed as part of a Liquibase changeset?