pg-osc: schema changes in PostgreSQL without a long lock

The table is copied, changed, and swapped

pg-online-schema-change, shortened to pg-osc, is described as a tool for making schema changes, any ALTER statements, in Postgres tables with minimal locks, to help achieve zero downtime schema changes against production workloads. The gem is `pg_online_schema_change`, and the command is `pg-online-schema-change`.

The mechanism is the shadow table. At a high level, the tool creates a shadow table that looks structurally the same as the primary table, performs the schema change on the shadow table, copies the contents from the primary table to the shadow table, and swaps the table names at the end, while preserving all the changes made to the primary table using triggers and an audit table.

That sequence is the whole design, and it explains both the strength and the cost. The lock that matters is the one taken at the very end when the two table names are exchanged, and it is short because the names are swapped rather than the data being moved. Everything expensive, which is the copy and the replay of concurrent writes, happens outside that lock. The project is explicit that the design is inspired by `pg_repack` and by `pt-online-schema-change` for MySQL, so the approach is a known one rather than a new idea.

The supported operations are narrow on purpose. pg-osc supports a column being added, dropped or renamed with no data loss, copies over indexes and foreign keys, and can either drop or retain the original table at the end. It also reduces bloat, because the old table is dropped after the swap, and the documentation notes that a new table is created and the old one dropped.

Triggers and the audit table carry the concurrent writes

The part that makes the copy safe is that the primary table stays live while the copy runs. To do that, the tool has to know about every write that happens during the copy window, and that is what the triggers and the audit table are for.

The changes made to the primary table during the copy are recorded in the audit table, and the tool replays them onto the shadow table before the swap. The README points at a section on tuning how slow or fast the replays are from the audit or log table, which tells you the replay rate is a knob the operator controls rather than a fixed cost.

Two options govern the replay. `--pull-batch-count` sets the number of rows replayed on each iteration after the copy, which can be tuned for faster catch up and swap, and is best used together with the delta count. `--delta-count` indicates how many rows should be remaining before a swap is performed, tuned for faster catch up and swap, especially on tables with high volume, and also best used with the pull batch count. The two are designed to be tuned together: a bigger batch moves the remaining rows faster, and the delta count is the threshold at which the tool stops replaying and swaps.

The consequence is that the tool's runtime on a busy table is not a fixed number. It depends on how much data is being written to the primary table while the copy is running, and on how aggressively you let the replay run. On a table under constant write load, the copy has to keep pace with the writes, and a deliberately slow replay makes the operation longer in exchange for less replication pressure on the server.

The trigger-based approach also explains the permission requirement, which comes next.

The command line and the options that matter

The tool is driven by a single subcommand, `perform`, with the target table given as the schema and the alter statement. The help output gives the shape of it:

pg-online-schema-change help perform

Usage:
  pg-online-schema-change perform -a, --alter-statement=ALTER_STATEMENT -d, --dbname=DBNAME -h, --host=HOST -p, --port=N -s, --schema=SCHEMA -u, --username=USERNAME

Options:
  -a, --alter-statement=ALTER_STATEMENT                                    # The ALTER statement to perform the schema change
  -s, --schema=SCHEMA                                                      # The schema in which the table is
                                                                           # Default: public
  -d, --dbname=DBNAME                                                      # Name of the database
  -h, --host=HOST                                                          # Server host where the Database is located

Two of these options are worth reading twice. The `--password` option is marked DEPRECATED, with the instruction to pass the `PGPASSWORD` environment variable instead, which is the ordinary Unix convention and keeps the secret out of the process list. And the `--kill-backends` option terminates other competing queries and backends when trying to acquire the lock for the shadow table creation and the swap; it waits for the `--wait-time-for-lock` duration before killing backends and tries up to three times. The wait time defaults to 10 seconds.

That option is the sharpest edge in the tool. Killing backends to get a lock is exactly what a zero downtime tool should be careful about, and the documentation offers the safer path explicitly: if `--kill-backends` is not true, it tries up to three times and then exits if it cannot acquire the lock. A tool that will terminate a production connection needs to be a deliberate choice rather than a default.

The backfill mechanism is the `--copy-statement` option, which takes a `.sql` file location where you can provide a custom query to be played, for example backfills, when pg-osc copies data from the primary to the shadow table:

Backfills run during the copy, not after it

Backfilling a new column is a case that a plain ALTER cannot handle, and pg-osc handles it inside the copy rather than after the swap. The feature list puts it plainly: it backfills old and new columns as data is copied from the primary table to the shadow table, and then performs the swap. The README has a dedicated backfill example section for it.

The mechanism is the `--copy-statement` option, which points at a `.sql` file whose query is played while the data is being copied. The help text for the option spells out the intent: it takes a `.sql` file location where you can provide a custom query to be played, for example backfills, when pg-osc copies data from the primary to the shadow table, with more examples in the README.

Doing the backfill during the copy is the right shape for a large table, because a separate UPDATE after the swap would rewrite every row again and hold locks for the duration. Folding it into the copy means the column is populated exactly once, as the rows move across.

The same reasoning applies to the replay, and it is why the two tuning options sit next to the copy in the documentation. The whole operation is a pipeline: copy the rows, run your custom statement on them, replay the concurrent changes, and then swap. Each stage has a knob, and the tool is at its best when those knobs are set for the write volume of the specific table.

Two smaller options round out the picture. `--drop` decides whether the original table is dropped at the end after the swap, and `--skip-foreign-key-validation` skips the foreign key validation after the swap, which the documentation says you should not need unless you have a very specific use case, like manually validating foreign key constraints after the swap.

Three ways to install it

The tool is distributed as a Ruby gem named `pg_online_schema_change`, and there are three documented routes.

In a Rails application the gem goes in the Gemfile:

gem "pg_online_schema_change"

and then the dependencies are installed:

    $ bundle install

Installing it directly, outside a bundle, is the second route:

    $ gem install pg_online_schema_change

The documentation notes that this includes all the dependencies accordingly, and reminds you to make sure the requirements are satisfied.

The third route is the container image:

    docker pull shayonj/pg-osc:latest

The image is published on Docker Hub as `shayonj/pg-osc`, and the repository's own Dockerfile is minimal, since it installs the released gem at a version passed in as a build argument:

FROM ruby:3.0

ARG VERSION

RUN gem install pg_online_schema_change -v $VERSION

The requirements are short and one of them is the important one. PostgreSQL 9.6 and later, and the database user should have permissions for `TRIGGER` and/or a `SUPERUSER`. The trigger requirement follows directly from the design, since the audit trail depends on triggers installed on the table, and a `SUPERUSER` would cover the operations that need it.

For local development the repository ships a compose file that starts a PostgreSQL 9.6 server on port 5432 with a `jamesbond` user, and a `docker-compose.yml` alongside the `Dockerfile`, plus a `spec/` directory and an Rakefile for the RSpec suite.

Requirements, caveats and a release gap

The README has a section called 'Few things to keep in mind', with the caveats and the description of how the tool works, and a reader should treat that section as part of the decision rather than an appendix. The project is explicit elsewhere that it acquires minimal locks throughout the process and links to the caveats from the feature list.

The practical constraints are the ones already noted. The tool needs PostgreSQL 9.6 or later and a privileged user, and the copy step is a full table copy, which on a large table is a lot of IO and WAL. The `--kill-backends` option can terminate other sessions, and the alternative is to give up after three attempts.

The release history is the other thing to weigh. The tagged releases are v0.9.10 on 2024-10-13, v0.9.9 on 2024-10-13 and v0.9.8 on 2024-01-15, so two versions shipped on the same afternoon nearly two years ago, and the project is still at version 0.x. The repository is not archived and the last push was on 2026-06-23, so work is continuing on the branch while the gem version has not moved since 2024. The `CHANGELOG.md` in the repository is where the history lives, and the README also links a section with preliminary load test figures under `docs/`.

The licence is MIT, with the licence text in `LICENSE.txt`, so there is no friction in using it in a commercial project.

For a team already using a migration framework, the sensible comparison is not pg-osc against nothing but pg-osc against the framework's own zero downtime path. The framework knows about the application, and it knows the order the migrations must run in. pg-osc knows about one table and one ALTER statement, and it is the better tool for a single change on a hot table, and the worse choice for a coordinated release with a dozen dependent changes.

Editorial conclusion

pg-osc suits an engineer who needs to add, drop or rename a column on a large PostgreSQL table without holding a lock long enough to block writes. It does not suit a team that cannot grant TRIGGER permissions or superuser access, and it is not a substitute for a migration tool that knows about your application. Before running it on a production table, read the caveats section, test the copy and swap on a copy of the data, and confirm that the trigger and audit table overhead is acceptable, because the last push was on 2026-06-23 while the newest tagged release is v0.9.10 from 2024-10-13.

Frequently asked questions

Official sources

1. Issues
2. License: MIT
3. README
4. Releases
5. shayonj/pg-osc on GitHub