PgPartitionManager

Inspired by pg_partman, this gem helps you manage partitioned tables in PostgreSQL >= 10.

Add this line to your application's Gemfile:

gem 'pg_partition_manager'

And then execute:

bundle

Or install it yourself as:

gem install pg_partition_manager

This is meant to be used via a daily cron job, to ensure that new tables are created before they are needed, and old tables are dropped when they aren't needed anymore.

Imagine a cron job like this:

@daily cd /app && ./bin/bundle exec ./script/make_partitions.rb

And a Ruby script like this:

#!/usr/bin/env/ruby

require "pg_partition_manager"

PgPartitionManager::Time.process([
  {parent_table: "public.events", period: "month", premake: 1, retain: 3},
  {parent_table: "public.stats", period: "week", premake: 4, retain: 4},
  {parent_table: "public.observations", period: "day", retain: 7, ulid: true, cascade: true},
  # self referencing comment table (parent_id) so we can have comments on other comments
  {parent_table: "public.comments", period: "day", retain: 7, ulid: true, cascade: true, truncate: true},
])

If the cron job runs on Monday, September 30th, 2019, and you had created the events, stats, and observations tables in your public schema, the following tables would be created:

• public.events_p2019_09_01
• public.events_p2019_10_01
• public.stats_p2019_09_30
• public.stats_p2019_10_07
• public.stats_p2019_10_14
• public.stats_p2019_10_21
• public.observations_p2019_09_30
• public.observations_p2019_10_01
• public.observations_p2019_10_02
• public.observations_p2019_10_03
• public.observations_p2019_10_04

The premake option specifies how many tables to create for dates after the current period, and the retain option specifies how many tables to keep for dates before the current period. You can additionally add the ulid flag if your database uses ULIDs for primary keys (see Starr's article for a good overview on "web-scale" ids).

A couple of notes regarding the cascade and truncate flags. Use the cascade option if you have dependent views or foreign key contraints (see details on the drop table command). Use the truncate if you have dependent tables that are not partitioned can afford a truncate table (cleans out any forign key dependent rows) before the drop (see details on the pg truncate command). The truncate option might also be useful if you have self referencing tables (clean out dependent rows). If using truncate it must be used in conjucation with the cascade flag.

This gem uses the pg gem to connect to your database, and it assumes the DATABASE_URL environment variable is populated with connection info. If this environment variable isn't defined, a connection to the server running on localhost will be attempted.

After checking out the repo, run bin/setup to install dependencies. Then, run rake test to run the tests. You can also run bin/console for an interactive prompt that will allow you to experiment.

To install this gem onto your local machine, run bundle exec rake install. To release a new version, update the version number in version.rb, and then run bundle exec rake release, which will create a git tag for the version, push git commits and tags, and push the .gem file to rubygems.org.

Bug reports and pull requests are welcome on GitHub at https://github.com/honeybadger-io/pg_partition_manager.

The gem is available as open source under the terms of the MIT License.