PgPartitioning¶ ↑

<img src=“https://codeclimate.com/github/victor-magarlamov/pg_partitioning/badges/gpa.svg” /> <img src=“https://codeclimate.com/github/victor-magarlamov/pg_partitioning/badges/coverage.svg” />

This project rocks and uses MIT-LICENSE.

Put this line in your Gemfile:

gem 'pg_partitioning'

Then bundle:

% bundle install

% 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 …

Given we have table ‘bandits’:

| id | name | specialization | born_at

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)

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.

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