pgslice

Postgres partitioning as easy as pie

Install

Run

gem install pgslice

Steps

  1. Specify your database credentials
export PGSLICE_URL=postgres://localhost/myapp_development
  1. Create an intermediate table
pgslice prep <table> <column> <period>

Period can be `day` or `month`.

This creates a table named `<table>_intermediate` with the appropriate trigger for partitioning.
  1. Add partitions
pgslice add_partitions <table> --intermediate --past 3 --future 3

This creates child tables that inherit from the intermediate table.

Use the `--past` and `--future` options to control the number of partitions.
  1. Optional, for tables with data - Fill the partitions in batches with data from the original table
pgslice fill <table>

Use the `--batch-size` and `--sleep` options to control the speed.
  1. Swap the intermediate table with the original table
pgslice swap <table>

The original table is renamed `<table>_retired` and the intermediate table is renamed `<table>`.
  1. Fill the rest
pgslice fill <table> --swapped
  1. Archive and drop the original table

Adding Partitions

pgslice add_partitions <table> --future 3

Additional Commands

To undo prep and delete partitions, use:

pgslice unprep <table>

To undo swap, use:

pgslice unswap <table>

Upgrading

Run:

gem install pgslice

To use master, run:

gem install specific_install
gem specific_install ankane/pgslice

Contributing

Everyone is encouraged to help improve this project. Here are a few ways you can help: