pgslice moves a Postgres table into range partitions with a rename, not a rewrite

The cutover is a rename, and that is the whole trick

Converting a populated Postgres table to range partitioning normally means a rewrite, and rewrites mean locks. pgslice avoids that by never touching the original table during the copy. It builds a parallel table, moves rows into it, and then swaps the two names in a single short transaction.

The swap SQL shows the shape of it. The original table is renamed to `<table>_retired`, the intermediate table is renamed to `<table>`, and the sequence is reattached with `ALTER SEQUENCE ... OWNED BY` so that inserts into the new table keep using the same id sequence. The whole thing runs inside `BEGIN` and `COMMIT` with `SET LOCAL lock_timeout = '5s'`, so if the rename cannot acquire its lock in five seconds, the transaction aborts rather than queueing behind a long running query.

That five second ceiling is the safety net of the whole design, and also its main constraint. A busy table with a statement that holds a conflicting lock for longer than that will make the swap fail, and you retry. This is a cutover that fails fast rather than a migration that queues indefinitely.

For the application, the visible change is minimal, which is the point the project leads with. The table name stays the same, and the columns, defaults, constraints, storage, comments, statistics, generated columns and compression are carried over by the `LIKE ... INCLUDING` clause used at prep time.

Batched fill uses the primary key as a watermark

The fill step is the part that touches a lot of rows, so it is deliberately incremental. The default batch size is 10000 and the default sleep between batches is 0, and both are adjustable with `--batch-size` and `--sleep`.

The generated SQL explains the strategy. Each batch is an `INSERT INTO... SELECT` from the original table bounded by a primary key range, with the lower bound exclusive and the upper bound inclusive, so batches tile the id space without overlapping:

/* 1 of 3 */
INSERT INTO "public"."visits_intermediate" ("id", "user_id", "ip", "created_at")
    SELECT "id", "user_id", "ip", "created_at" FROM "public"."visits"
    WHERE "id" > 0 AND "id" <= 10000 AND "created_at" >= '2026-01-01'::date AND "created_at" < '2026-04-01'::date

The id range is the watermark, and the date range constrains the rows to the partitions that exist. The consequence for an operator is that the id column has to be a workable ordering key, since pgslice is walking the table in id order and copying what it passes. The docs do not discuss a non integer primary key, so a composite or uuid key is worth testing on a copy before you point the tool at production.

Rows written to the original table between the start of the fill and the swap are not lost. After the swap, a second fill with `--swapped` picks up whatever landed in the retired table in the meantime, so the sequence is fill, swap, fill again.

Partitions are range partitions named after their period

Prep creates a partitioned table named `<table>_intermediate` using range partitioning, and a comment on the table records the configuration, for example a column, period, cast and version:

COMMENT ON TABLE "public"."visits_intermediate" is 'column:created_at,period:month,cast:date,version:3';

Each partition is a child table created with `PARTITION OF` for a bounded date range, and the primary key is added to it individually. A monthly setup produces names like `visits_202601` for January 2026, `visits_202602` for February, and so on.

The partition column has to be a `timestamp`, `timestamptz` or `date` column, and the period is one of `day`, `month` or `year`. That is a narrower scope than Postgres itself, which supports list and hash partitioning as well, and it is the clearest limit on what pgslice can do for you. If your data is bucketed by something other than a time range, this tool is not the answer.

Partitions are added with a command that controls how far back and forward they reach:

pgslice add_partitions <table> --intermediate --past 3 --future 3

The `--past` and `--future` counts are how you keep a runway of empty partitions ahead of the data, which matters because an insert into a range with no matching partition fails. The docs suggest running `add_partitions` from cron so a new partition appears on schedule, with a day, month or year example, and a SQL monitor query that checks whether the expected partition name exists, using `to_char(NOW() + INTERVAL '3 days', 'YYYYMMDD')` style formatting.

Every command prints the SQL it runs, and --dry-run prints without executing

pgslice prints the SQL commands it executed on the server, which makes the tool unusually easy to review. A run of prep against a visits table emits the create table, the index and the comment. add_partitions emits one `CREATE TABLE ... PARTITION OF` plus `ALTER TABLE ... ADD PRIMARY KEY` per period. swap emits the renames and the sequence reassignment, wrapped in a transaction with the lock timeout.

The same transparency exists for planning, since the `--dry-run` option prints without executing. That is the feature to reach for when you are staging a migration: run the whole sequence with `--dry-run` against a copy, read the SQL, confirm the statement that touches your data matches what you expected, then run it for real.

The analyze step is separate and easy to overlook. It runs `ANALYZE VERBOSE` on each child partition and on the intermediate table, and the output shows the per-partition statements followed by the parent. Skipping it leaves the planner working from stale statistics on a table that just changed shape, which is the kind of thing that shows up later as a surprising query plan.

Archiving old partitions is a dump and a drop

Once the table is partitioned, keeping its size in check means removing old partitions on a schedule, and pgslice leaves this part to standard Postgres tooling. The documented pattern is a custom format dump of one partition followed by a drop:

pg_dump -c -Fc -t <table>_202601 $PGSLICE_URL > <table>_202601.dump
psql -c "DROP TABLE <table>_202601" $PGSLICE_URL

Dropping a partition is fast because the data leaves with the table, which is exactly the property that makes partitioning attractive for time series. The docs also show pushing the dump to object storage with `s3cmd put`, naming s3cmd as a convenient tool if you use Amazon S3 for backups.

The same dump and drop pattern applies to the retired table after a swap, which is how you clear the copy of the original data once the new partitioned table is verified.

Schema changes have their own rule once a table is partitioned. The documentation states that updates go on the master table only, not on the partitions, and this includes adding, removing and modifying columns as well as adding and removing indexes and foreign keys. The reason is structural: a partition inherits from the parent, and Postgres propagates the change, so editing a child directly either fails or leaves the two out of sync.

Install paths, undo commands and where pgslice stops

pgslice is a command line tool, so installation is a gem, and the docs note that you may need dependencies if the install fails:

gem install pgslice

That gives you the `pgslice` command. Homebrew and Docker are offered as alternatives, and the repository contains a Dockerfile built on `ruby:3-alpine` that installs `libpq-dev` and the `pg` gem before installing pgslice itself, with `pgslice` as the entrypoint.

Two undo commands cover the two irreversible-looking steps. `pgslice unprep <table>` deletes the partitions and undoes the prep, and `pgslice unswap <table>` undoes a swap. Having both is unusual for a migration tool and it changes the risk calculus, since a bad swap can be reversed without restoring from the dump.

There are limits worth stating. The tool works with range partitioning on a time column, and the `--tablespace` option sets a tablespace when adding partitions, for example `pgslice add_partitions <table> --tablespace fastspace`. The docs describe a `cast:date` field in the table comment, so the partition key is cast to a date for range boundaries, and the time column semantics of your data need to line up with that.

The alternative approach is doing the same work by hand: create the partitioned table, copy rows with your own `INSERT ... SELECT` loop, rename, and manage partitions with a scheduled job. pgslice is that script with the lock timeout, the batching, the sequence reassignment and the undo paths already handled, which is the difference in approach rather than a different outcome. The project also points to pgsync for syncing data across different databases, and it is MIT licensed. The repository has no GitHub releases, and the last push was on 2026-06-29.

Editorial conclusion

pgslice suits teams that need to partition a table that already holds data and cannot afford a long lock, because the cutover is a rename inside a transaction with a five second lock timeout, and because `unprep` and `unswap` exist for second thoughts. Do not reach for it when the table is still small enough to recreate, when you need list partitioning, since the tool works with range partitioning on a timestamp, timestamptz or date column, or when you would rather not grant a client a connection string that can rename and drop tables. Before running it, take a `pg_dump` of the original table, confirm the partition column is the one your queries filter on, and check the last push date, 2026-06-29, against the Postgres version on your server.

Frequently asked questions

Official sources

1. ankane/pgslice on GitHub
2. Issues
3. License: MIT
4. README