PgDice is a utility for creating and maintaining partitioned database tables that builds on top of the excellent gem https://github.com/ankane/pgslice

PgDice is intended to be used by scheduled background jobs in frameworks like Sidekiq where logging and clear exception messages are crucial.

This project is stable and used daily in production.

Add this line to your application's Gemfile:

gem 'pgdice'

And then execute:

$ bundle

Or install it yourself as:

$ gem install pgdice

You must configure PgDice before you can use it, otherwise you won't be able to perform any manipulation actions on tables.

This is an example config from a project using Sidekiq

require 'pgdice'
PgDice.configure do |config|
  # This defaults to STDOUT if you don't specify a logger
  config.logger_factory = proc { Sidekiq.logger }
  
  # database_url *must be set*
  # Rails users: see FAQ for method on how to generate this from your Rails config. 
  config.database_url = ENV['PGDICE_DATABASE_URL'] # postgresql://[user[:password]@][host][:port][/dbname][?param1=value1&...]
 
  # Set a config file or build the tables manually
  config.config_file = Rails.root.join('config', 'pgdice.yml') # If you are using rails, else provide the absolute path.
  # and/or
  config.approved_tables = PgDice::ApprovedTables.new(
    PgDice::Table.new(table_name: 'comments', past: 90, future: 7, period: 'day'),
    PgDice::Table.new(table_name: 'posts', past: 6, future: 2, period: 'month')
  )
end

• database_url - Required: The postgres database url to connect to.

  • This is required since pgslice requires a postgres url.
  • PgDice will throw an error if this value is not a valid postgres url.

• logger_factory - Optional: A factory that will return a logger to use.

  • Defaults to proc { Logger.new(STDOUT) }

• approved_tables - Optional: (but not really) The tables to allow modification on.

  • If you want to manipulate database tables with this gem you're going to need to provide this data.
    • See the Approved Tables Configuration section for more.

• dry_run - Optional: Boolean value to control whether changes are executed on the database.

  • Defaults to false
  • true will make PgDice log out the commands but not execute them.

• batch_size - Optional: Maximum number of tables you can drop in one drop_old_partitions call.

  • Defaults to 7.
  • Keep in mind the size of your tables, drop operations are done in one command. Large tables will take longer to drop per table and could time out if there is activity on the parent table.

All of the following parameters are optional and honestly you probably will never need to mess with these.

• pg_connection - This is a PG::Connection object used for the database queries made from pgdice.
  • By default it will be initialized from the database_url if left nil.
  • Keep in mind the dependency pgslice will still establish its own connection using the database_url so this feature may not be very useful if you are trying to only use one connection for this utility.

In order to maintain the correct number of partitions over time you must configure a PgDice::Table.

An example configuration file has been provided at config.yml if you would rather declare your approved_tables in yaml.

If you want to declare your PgDice::ApprovedTables in your configuration block instead, you can build them like so:

require 'pgdice'
PgDice.configure do |config|
  config.approved_tables = PgDice::ApprovedTables.new(
    PgDice::Table.new(table_name: 'comments', # Table name for the (un)partitioned table
                      past: 90, # The minimum number of tables to keep before dropping older tables.
                      future: 7, # Number of future tables to always have.
                      period: 'day', # day, month, year
                      column_name: 'created_at', # Whatever column you'd like to partition on.
                      schema: 'public'), # Schema that this table belongs to.
    PgDice::Table.new(table_name: 'posts') # Minimum configuration (90 past, 7 future, 'day' period).
  )
end

It is possible to use both the configuration block and a file if you so choose. The block will take precedence over the values in the file.

This should only be used on smallish tables and ONLY after you have tested it on a non-production copy of your production database. In fact, you should just not do this in production. Schedule downtime or something and run it a few times on a copy of your database. Then practice restoring your database some more.

This command will convert the existing comments table into 98 partitioned tables (90 past, 7 future, and one for today).

For more information on what's going on in the background see https://github.com/ankane/pgslice

PgDice.partition_table('comments')

If you have a table with existing data and you want that data to be split up and copied to your new partitions you can use:

PgDice.partition_table('comments', fill: true)

This will create the partitions and then insert data from the old table into the newly partitioned tables.

• You can override values configured in the PgDice::Table by passing them in as a hash.
  • For example if you wanted to create 30 future tables instead of the configured 7 for the comments table you could pass in future: 30.

If you mess up, again you shouldn't use this in production, you can call:

PgDice.undo_partitioning!('comments')

This method will revert the changes made by partitioning a table. Don't rely on this in production if you mess up; you need to test everything thoroughly.

If you have existing tables that need to periodically have more tables added you can run:

PgDice.add_new_partitions('comments')

• The above command would add up to 7 new tables and their associated indexes all based on the period that the partitioned table was defined with.
  • The example comments table we have been using was configured to always keep 7 future partitions above.

Sometimes you just want to know what's out there and if there are tables ready to be dropped.

To list all eligible tables for dropping you can run:

PgDice.list_droppable_partitions('comments')

If you want to know exactly which partitions will be dropped you can call:

PgDice.list_droppable_partitions_by_batch_size('comments')

This method will show partitions that are within the configured batch_size.

• This method uses the past value from the PgDice::Table to determine which tables are eligible for dropping.
• Like most commands, if you want to override the values it will use to check just pass them in.

Dropping tables is irreversible! Do this at your own risk!!

If you want to drop old tables (after backing them up of course) you can run:

PgDice.drop_old_partitions('comments')

• The above example command would drop partitions that exceed the configured past table count for the PgDice::Table.
  • The example comments table has been configured with past: 90 tables. So if there were 100 tables older than today it would drop up to batch_size tables.

If you've got background jobs creating and dropping tables you're going to want to ensure they are actually working correctly.

To validate that your expected number of tables exist, you can run:

PgDice.assert_tables('comments')

An InsufficientTablesError will be raised if any conditions are not met.

This will check that there are 7 future tables from now and that there are 90 past tables per our configuration above.

If you want to only assert on past tables you could use the example below. The same goes for future

PgDice.assert_tables('comments', only: :past)

Sometimes you might need to know the tables configured for PgDice. To list the configured tables you can run:

PgDice.approved_tables

The ApprovedTables object responds to the most common enumerable methods.

All methods for PgDice take a hash which will override whatever values would have been automatically supplied.

An example of this would be like so:

PgDice.list_droppable_partitions('comments', past: 60)

This example would use 60 instead of the configured value of 90 from the comments table we configured above.

1. Here's an example on how to use PgDice in AWS and the README which will guide you through what is going on.

2. Here's an example on how to write a config.yml for PgDice

1. How do I get a postgres url if I'm running in Rails?

 def build_postgres_url
  config = Rails.configuration.database_configuration
  host = config[Rails.env]["host"]
  database = config[Rails.env]["database"]
  username = config[Rails.env]["username"]
  password = config[Rails.env]["password"]

  "postgres://#{username}:#{password}@#{host}/#{database}"
 end

1. I'm seeing off-by-one errors for my assert_tables calls?
   • You should make sure your database is configured to use UTC. https://www.postgresql.org/docs/10/datatype-datetime.html

1. Full PG::Connection support (no more database URLs).
2. Non time-range based partitioning. PgParty might be a good option!
3. Hourly partitioning

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.

You're going to need to have postgres 10 or greater installed.

Run the following commands from your terminal. Don't run these on anything but a development machine.

1. psql postgres -c "create role pgdice with createdb superuser login password 'password';"
2. createdb pgdice_test
3. Now you can run the tests via guard or rake test

Bug reports and pull requests are welcome on GitHub at https://github.com/IlluminusLimited/pgdice. This project is intended to be a safe, welcoming space for collaboration, and contributors are expected to adhere to the Contributor Covenant code of conduct.

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

There are some features in this gem which allow you to drop database tables, due to the dangerous nature of dropping database tables, please ensure you have a tested and working backup and restore strategy.

THIS SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED. By using this software you agree that the creator, maintainers, and any affiliated parties CANNOT BE HELD LIABLE FOR DATA LOSS OR LOSSES OF ANY KIND.

See the LICENSE for more information.

Everyone interacting in the Pgdice project’s codebases, issue trackers, chat rooms and mailing lists is expected to follow the code of conduct.