README.md

SqlPartitioner

SqlPartitioner provides a PartitionsManager class to help maintain partitioned tables in MySQL.
If you have a table that is partitioned based on a timestamp, you will likely need to regularly add new partitions
into the future as well as remove older partitions to free up space. This gem will help.

Supported Features

SqlPartitioner works with MySQL partitioned tables that are partitioned by a timestamp column, expressed as an integer
representing a Unix epoch timestamp in either seconds or micro-seconds.

You can use ActiveRecord or DataMapper.

Supported functionality:

initializing partitioning on a table

adding new partitions of a given size (expressed in months or days)

removing partitions older than a given timestamp or number of days

You can run the above operations directly or pass a flag to only do a dry-run.

Unsupported Features

Does not yet support databases other than MySQL. Target table can only be partitioned by its timestamp column representing seconds or micro-seconds.

Getting Started

You'll need to require 'sql_partitioner'.

Here's an example for initializing a PartitionsManager instance, using DataMapper:

If you are using ActiveRecord, you can instead supply the following for :adapter:

SqlPartitioner::ARAdapter.new(ActiveRecord::Base.connection)

Regarding the :lock_wait_timeout option: any partitioning statement must acquire a table lock on the partitioned table,
and while it is waiting to acquire this lock, any subsequent queries on that table will be blocked and have to wait.
It may take a long time to acquire a table lock if there were already long-running queries in progress.
Therefore, setting a short timeout (e.g. 1 second) ensures the partitioning statement will timeout quickly,
so any other SQL operations on that table will not be delayed.
If the partitioning command times-out, it will have to be retried later.
MySQL's default value for lock_wait_timeout is 1 year.

Initialize partitioning

Here's an example for initializing partitioning on the table. It will create partitions of size 30 days, as needed, to cover 90 days into the future: