PgPartitioning

This project rocks and uses MIT-LICENSE.

Install

Put this line in your Gemfile:

gem 'pg_partitioning'

Then bundle:

% bundle install

Usage

% RAILS_ENV=production rails g partitioning

1) Select the partitioning mode:

by single column value
by dates template
by range

2) Enter table name.

3) Enter column name.

4) Enter condition depending on the selected mode - template pattern or range step.

What will happen then…

1) The generator will create two triggers: before insert and after insert. The before insert trigger executes the procedure that created nested table and inserts a new record into it. The after trigger clears the master table.

2) Foreign keys which reference to the master table will delete.

3) Old data will be migrated from master table to child tables.

For more details, see …

Examples

Given we have table ‘bandits’:

| id | name | specialization | born_at

Example 1: Partitioning by single column value

Enter mode: 0
Enter table: bandits
Enter column: specialization

Create two bandits:

Bandit.create([{name: 'Al Capone', specialization: 'bootlegger'},
               {name: 'Black Bart', specialization: 'robber'}])

Now we have three tables:

bandits - master - not contains real data
bandits_bootlegger - nested - contains only the bootleggers
bandits_robber - nested - contains only the robbers

Check this…

SELECT COUNT(*) FROM bandits; (2)
SELECT COUNT(*) FROM ONLY bandits; (0)
SELECT COUNT(*) FROM ONLY bandits_bootlegger; (1)
SELECT COUNT(*) FROM ONLY bandits_robber; (1)

Example 2: Partitioning by range

Enter mode: 1
Enter table: bandits
Enter column: id
Enter step: 10

All bandits with ID from 0 to 9 will be recorded to table ‘bandits_0’. All bandits with ID from 10 to 19 will be recorded to table ‘bandits_1’. All bandits with ID from 20 to 29 will be recorded to table ‘bandits_2’ etc.

Example 3: Partitioning by date template

Enter mode: 2
Enter table: bandits
Enter column: born_at
Enter pattern: YYYYMM

Create two bandits:

Bandit.create([{name: 'Al Capone', born_at: '1899-01-17'},
               {name: 'Charles Luciano', born_at: '1897-11-24'}])

And now we have two child tables:

bandits_1899_01
bandits_1897_11