rkrage / pg_party

ActiveRecord PostgreSQL Partitioning

PgParty

ActiveRecord migrations and model helpers for creating and managing PostgreSQL 10+ partitions!

Features

• Migration methods for partition specific database operations
• Model methods for querying partitioned data, creating adhoc partitions, and retreiving partition metadata

Limitations

• Partition tables are not represented correctly in db/schema.rb — please use the :sql schema format

Compatibility

This gem is tested against:

• Rails: 6.1, 7.0, 7.1
• Ruby: 3.0, latest (currently 3.2 at the time of this commit)
• PostgreSQL: 11, 12, 13, 14, 15, 16

Future Work

I plan to separate out the model functionality into a new gem and port the migration functionality into pg_ha_migrations (some of which has already been done). I will continue to maintain this gem (bugfixes / support for new versions of Rails) until that work is complete.

I originally planned to add a feature for automatic partition creation, but I think that functionality would be better served by pg_partman.

Installation

Add this line to your application's Gemfile:

gem 'pg_party'

And then execute:

$ bundle

Or install it yourself as:

$ gem install pg_party

Note that the gemspec does not require pg, as some model methods may work for other databases. Migration methods will be unavailable unless pg is installed.

Configuration

These values can be accessed and set via PgParty.config and PgParty.configure.

• caching
  • Whether to cache currently attached partitions and anonymous model classes
  • Default: true
• caching_ttl
  • Length of time (in seconds) that cache entries are considered valid
  • Default: -1 (never expire cache entries)
• schema_exclude_partitions
  • Whether to exclude child partitions in rake db:structure:dump
  • Default: true
• create_template_tables
  • Whether to create template tables by default. Use the template: option when creating partitioned tables to override this default.
  • Default: true
• create_with_primary_key
  • Whether to add primary key constraints to partitioned (parent) tables by default.
    • This behavior is disabled by default as this configuration usually requires composite primary keys to be specified and ActiveRecord does not natively support composite primary keys. There are workarounds such as the composite_primary_keys gem.
    • This is not supported for Postgres 10 (requires Postgres 11+)
    • Primary key constraints must include all partition keys, for example: primary_key: [:id, :created_at], partition_key: :created_at
    • Partition keys cannot use expressions
    • Can be overridden via the create_with_primary_key: option when creating partitioned tables
  • Default: false
• include_subpartitions_in_partition_list
  • Whether to include nested subpartitions in the result of YourModelClass.partiton_list mby default. You can always pass the include_subpartitions: option to override this.
  • Default: false (for backward compatibility)

Note that caching is done in-memory for each process of an application. Attaching / detaching partitions will clear the cache, but only for the process that initiated the request. For multi-process web servers, it is recommended to use a TTL or disable caching entirely.

Example

# in a Rails initializer
PgParty.configure do |c|
  c.caching_ttl = 60
  c.schema_exclude_partitions = false
  c.include_subpartitions_in_partition_list = true
  # Postgres 11+ users starting fresh may consider the below options to rely on Postgres' native features instead of
  # this gem's template tables feature.
  c.create_template_tables = false
  c.create_with_primary_key = true
end

Usage

Migrations

Methods

These methods are available in migrations as well as ActiveRecord::Base#connection objects.

• create_range_partition
  • Create partitioned table using the range partitioning method
  • Required args: table_name, partitition_key:
• create_list_partition
  • Create partitioned table using the list partitioning method
  • Required args: table_name, partition_key:
• create_hash_partition (Postgres 11+)
  • Create partitioned table using the hash partitioning method
  • Required args: table_name, partition_key:
• create_range_partition_of
  • Create partition in range partitioned table with partition key between range of values
  • Required args: table_name, start_range:, end_range:
  • Create a subpartition by specifying a partition_type: of :range, :list, or :hash and a partition_key:
• create_list_partition_of
  • Create partition in list partitioned table with partition key in list of values
  • Required args: table_name, values:
  • Create a subpartition by specifying a partition_type: of :range, :list, or :hash and a partition_key:
• create_hash_partition_of (Postgres 11+)
  • Create partition in hash partitioned table for partition keys with hashed values having a specific remainder
  • Required args: table_name, modulus:, remainder
  • Create a subpartition by specifying a partition_type: of :range, :list, or :hash and a partition_key:
  • Note that all partitions in a hash partitioned table should have the same modulus. See Examples for more info.
• create_default_partition_of (Postgres 11+)
  • Create a default partition for values not falling in the range or list constraints of any other partitions
  • Required args: table_name
• attach_range_partition
  • Attach existing table to range partitioned table with partition key between range of values
  • Required args: parent_table_name, child_table_name, start_range:, end_range:
• attach_list_partition
  • Attach existing table to list partitioned table with partition key in list of values
  • Required args: parent_table_name, child_table_name, values:
• attach_hash_partition (Postgres 11+)
  • Attach existing table to hash partitioned table with partition key hashed values having a specific remainder
  • Required args: parent_table_name, child_table_name, modulus:, remainder
• attach_default_partition (Postgres 11+)
  • Attach existing table as the default partition
  • Required args: parent_table_name, child_table_name
• detach_partition
  • Detach partition from both range and list partitioned tables
  • Required args: parent_table_name, child_table_name
• create_table_like
  • Clone any existing table
  • Required args: table_name, new_table_name
• partitions_for_table_name
  • List all attached partitions for a given table
  • Required args: table_name, include_subpartitions: (true or false)
• parent_for_table_name
  • Fetch the parent table for a partition
  • Required args: table_name
  • Pass optional traverse: true to return the top-level table in the hierarchy (for subpartitions)
  • Returns nil if the table is not a partition / has no parent
• table_partitioned?
  • Returns true if the table is partitioned (false for non-partitioned tables and partitions themselves)
  • Required args: table_name
• add_index_on_all_partitions
  • Recursively add an index to all partitions and subpartitions of table_name using Postgres's ADD INDEX CONCURRENTLY algorithm which adds the index in a non-blocking manner.
  • Required args: table_name, column_name (all add_index arguments are supported)
  • Use the in_threads: option to add indexes in parallel threads when there are many partitions. A value of 2 to 4 may be reasonable for tables with many large partitions and hosts with 4+ CPUs/cores.
  • Use disable_ddl_transaction! in your migration to disable transactions when using this command with in_threads: or algorithm: :concurrently.

Examples

Create range partitioned table on created_at::date with two partitions:

class CreateSomeRangeRecord < ActiveRecord::Migration[5.1]
  def up
    # proc is used for partition keys containing expressions
    create_range_partition :some_range_records, partition_key: ->{ "(created_at::date)" } do |t|
      t.text :some_value
      t.timestamps
    end

    # optional name argument is used to specify child table name
    create_range_partition_of \
      :some_range_records,
      name: :some_range_records_a,
      start_range: "2019-06-07",
      end_range: "2019-06-08"

    # optional name argument is used to specify child table name
     create_range_partition_of \
       :some_range_records,
       name: :some_range_records_b,
       start_range: "2019-06-08",
       end_range: "2019-06-09"
  end
end

Create list partitioned table on id with two partitions:

class CreateSomeListRecord < ActiveRecord::Migration[5.1]
  def up
    # symbol is used for partition keys referring to individual columns
    create_list_partition :some_list_records, partition_key: :id do |t|
      t.text :some_value
      t.timestamps
    end

    # without name argument, child partition created as "some_list_records_<hash>"
    create_list_partition_of \
      :some_list_records,
      values: 1..100

    # without name argument, child partition created as "some_list_records_<hash>"
     create_list_partition_of \
       :some_list_records,
       values: 101..200
    
    # default partition support is available in Postgres 11 or higher
     create_default_partition_of \
       :some_list_records
  end
end

Create hash partitioned table on account_id with two partitions (Postgres 11+ required):

• A hash partition can be used to spread keys evenly(ish) across partitions
• modulus: should always equal the total number of partitions planned for the table
• remainder: is an integer which should be in the range of 0 to modulus-1

class CreateSomeHashRecord < ActiveRecord::Migration[5.1]
  def up
    # symbol is used for partition keys referring to individual columns
    # create_with_primary_key: true, template: false on Postgres 11 will rely on PostgreSQL's native partition schema
    # management vs this gem's template tables
    # Note composite primary keys will require a workaround in ActiveRecord, such as through the use of the composite_primary_keys gem
    create_hash_partition :some_hash_records, partition_key: :account_id, primary_key: [:id, :account_id],
    create_with_primary_key: true, template: false do |t|
      t.bigserial :id, null: false
      t.bigint :account_id, null: false
      t.text :some_value
      t.timestamps
    end

    # without name argument, child partition created as "some_list_records_<hash>"
    create_hash_partition_of \
      :some_hash_records,
      modulus: 2,
      remainder: 0

    # without name argument, child partition created as "some_list_records_<hash>"
    create_hash_partition_of \
      :some_hash_records,
      modulus: 2,
      remainder: 1
  end
end

Advanced example with subpartitioning: Create list partitioned table on account_id subpartitioned by range on created_at with default partitions. This example is for a table with no primary key... perhaps for some analytics use case.

• Default partitions are only supported in Postgres 11+

class CreateSomeListSubpartitionedRecord < ActiveRecord::Migration[5.1]
  def up
    create_list_partition :some_list_subpartitioned_records, partition_key: :account_id, id: false,
      template: false do |t|
      t.bigint :account_id, null: false
      t.text :some_value
      t.created_at
    end

    create_default_partition_of \
      :some_list_subpartitioned_records,
      name: :some_list_subpartitioned_records_default,
      partition_type: :range,
      partition_key: :created_at

    create_range_partition_of \
      :some_list_subpartitioned_records_default,
      name: :some_list_subpartitioned_records_default_2019,
      start_range: '2019-01-01',
      end_range: '2019-12-31T23:59:59'
    
    create_default_partition_of \
      :some_list_subpartitioned_records_default

    create_list_partition_of \
      :some_list_subpartitioned_records,
      name: :some_list_subpartitioned_records_1,
      values: 1..100,
      partition_type: :range,
      partition_key: :created_at
  
    create_range_partition_of \
      :some_list_subpartitioned_records_1,
      name: :some_list_subpartitioned_records_1_2019,
      start_range: '2019-01-01',
      end_range: '2019-12-31T23:59:59'

    create_default_partition_of
      :some_list_subpartitioned_records_1

     create_list_partition_of \
       :some_list_subpartitioned_records,
       name: :some_list_subpartitioned_records_2,
       values: 101..200,
       partition_type: :range,
       partition_key: :created_at

    create_range_partition_of \
      :some_list_subpartitioned_records_2,
      name: :some_list_subpartitioned_records_2_2019,
      start_range: '2019-01-01',
      end_range: '2019-12-31T23:59:59'

    create_default_partition_of \
      :some_list_subpartitioned_records_2
  end
end

Template Tables

Unfortunately, PostgreSQL 10 doesn't support indexes on partitioned tables. However, individual partitions can have indexes. To avoid explicit index creation for every new partition, we've introduced the idea of template tables. For every call to create_list_partition and create_range_partition, a clone <table_name>_template is created. Indexes, constraints, etc. created on the template table will propagate to new partitions in calls to create_list_partition_of and create_range_partition_of:

• Subpartitions will correctly clone from template tables if a template table exists for the top-level ancestor
• When using Postgres 11 or higher, you may wish to disable template tables and use the native features instead, see Configuration
  but this may result in you using composite primary keys, which is not natively supported by ActiveRecord.

class CreateSomeListRecord < ActiveRecord::Migration[5.1]
  def up
    # template table creation is enabled by default - use "template: false" or the config option to opt-out
    create_list_partition :some_list_records, partition_key: :id do |t|
      t.integer :some_foreign_id
      t.text :some_value
      t.timestamps
    end

    # create index on the template table
    add_index :some_list_records_template, :some_foreign_id

    # create partition with an index on "some_foreign_id"
    create_list_partition_of \
      :some_list_records,
      values: 1..100

    # create partition with an index on "some_foreign_id"
    create_list_partition_of \
      :some_list_records,
      values: 101..200
  end
end

Attaching Existing Tables as Partitions

Attach an existing table to a range partitioned table:

class AttachRangePartition < ActiveRecord::Migration[5.1]
  def up
    attach_range_partition \
      :some_range_records,
      :some_existing_table,
      start_range: "2019-06-09",
      end_range: "2019-06-10"
  end
end

Attach an existing table to a list partitioned table:

class AttachListPartition < ActiveRecord::Migration[5.1]
  def up
    attach_list_partition \
      :some_list_records,
      :some_existing_table,
      values: 200..300
  end
end

Attach an existing table to a hash partitioned table:

class AttachHashPartition < ActiveRecord::Migration[5.1]
  def up
    attach_hash_partition \
      :some_hash_records,
      :some_existing_table,
      modulus: 2,
      remainder: 1
  end
end

Detach a partition from any partitioned table:

class DetachPartition < ActiveRecord::Migration[5.1]
  def up
    detach_partition :parent_table, :child_table
  end
end

Safely cascading add_index commands

Postgres 11+ will automatically cascade CREATE INDEX operations to partitions and subpartitions, however CREATE INDEX CONCURRENTLY is not supported, meaning table locks will be taken on each table while the new index is built. Postgres 10 provides no way to cascade index creation natively.

• The add_index_on_all_partitions method solves for these limitations by recursively creating the specified index on all partitions and subpartitions. Index names on individual partitions will include a hash suffix to avoid conflicts.
• On Postgres 11+, the created indices are correctly attached to an index on the parent table
• On Postgres 10, if you are using Template Tables, you will want to add the index to the template table separately.
• This command can also be used on subpartitions to cascade targeted indices starting at one level of the table hierarchy

class AddSomeValueIndexToSomeListRecord < ActiveRecord::Migration[5.1]
  # add_index_on_all_partitions with in_threads option may not be used within a transaction
  # (also, algorithm: :concurrently cannot be used within a transaction)
  disable_ddl_transaction!

  def up
    add_index :some_records_template, :some_value # Only if using Postgres 10 with template tables

    # Pass the `in_threads:` option to create indices in parallel across multiple Postgres connections
    add_index_on_all_partitions :some_records, :some_value, algorithm: :concurrently, in_threads: 4
  end
end

For more examples, take a look at the Combustion schema definition and integration specs:

• https://github.com/rkrage/pg_party/blob/master/spec/dummy/db/schema.rb
• https://github.com/rkrage/pg_party/blob/master/spec/integration/migration_spec.rb

Models

Methods

Class methods available to all ActiveRecord models:

• partitioned?
  • Check if a model is backed by either a list or range partitioned table
  • No arguments
• range_partition_by
  • Configure a model backed by a range partitioned table
  • Required arg: key (partition key column) or block returning partition key expression
• list_partition_by
  • Configure a model backed by a list partitioned table
  • Required arg: key (partition key column) or block returning partition key expression
• hash_partition_by
  • Configure a model backed by a hash partitioned table
  • Required arg: key (partition key column) or block returning partition key expression

Class methods available to both range and list partitioned models:

• partitions
  • Retrieve a list of currently attached partitions
  • Optional include_subpartitions: argument to include all subpartitions in the returned list
• in_partition
  • Retrieve an ActiveRecord model scoped to an individual partition
  • Required arg: child_table_name
• partition_key_eq
  • Query for records where partition key matches a value
  • Required arg: value

Class methods available to range partitioned models:

• create_partition
  • Dynamically create new partition with partition key in range of values
  • Required args: start_range:, end_range:
• partition_key_in
  • Query for records where partition key in range of values
  • Required args: start_range, end_range

Class methods available to list partitioned models:

• create_partition
  • Dynamically create new partition with partition key in list of values
  • Required arg: values:
• partition_key_in
  • Query for records where partition key in list of values
  • Required arg: list of values

Class methods available to hash partitioned models:

• create_partition
  • Dynamically create new partition with hashed partition key divided by modulus equals remainder
  • Required arg: modulus:, remainder:
• partition_key_in
  • Query for records where partition key in list of values (method operates the same as for list partitions above)
  • Required arg: list of values

Examples

Configure model backed by a range partitioned table to get access to the methods described above:

class SomeRangeRecord < ApplicationRecord
  # block is used for partition keys containing expressions
  range_partition_by { "(created_at::date)" }
end

Configure model backed by a list partitioned table to get access to the methods described above:

class SomeListRecord < ApplicationRecord
  # symbol is used for partition keys referring to individual columns
  list_partition_by :id
end

Configure model backed by a hash partitioned table to get access to the methods described above:

class SomeHashRecord < ApplicationRecord
  # symbol is used for partition keys referring to individual columns
  hash_partition_by :id
end

Dynamically create new partition from range partitioned model:

# additional options include: "name:" and "primary_key:"
SomeRangeRecord.create_partition(start_range: "2019-06-09", end_range: "2019-06-10")

Dynamically create new partition from list partitioned model:

# additional options include: "name:" and "primary_key:"
SomeListRecord.create_partition(values: 200..300)

Dynamically create new partition from hash partitioned model:

# additional options include: "name:" and "primary_key:"
SomeHashRecord.create_partition(modulus: 2, remainder: 1)

For range partitioned model, query for records where partition key in range of values:

SomeRangeRecord.partition_key_in("2019-06-08", "2019-06-10")

For list and hash partitioned models, query for records where partition key in list of values:

SomeListRecord.partition_key_in(1, 2, 3, 4)

For all partitioned models, query for records matching partition key:

SomeRangeRecord.partition_key_eq(Date.current)

SomeListRecord.partition_key_eq(100)

For all partitioned models, retrieve currently attached partitions:

SomeRangeRecord.partitions

SomeListRecord.partitions(include_subpartitions: true) # Include nested subpartitions

For both all partitioned models, retrieve ActiveRecord model scoped to individual partition:

SomeRangeRecord.in_partition(:some_range_records_partition_name)

SomeListRecord.in_partition(:some_list_records_partition_name)

To create range partitions by month for previous, current and next months it's possible to use this example. To automate creation of partitions, run Log.maintenance every day with cron:

class Log < ApplicationRecord
  range_partition_by { '(created_at::date)' }

  def self.maintenance
    partitions = [Date.today.prev_month, Date.today, Date.today.next_month]

    partitions.each do |day|
      name = Log.partition_name_for(day)
      next if ActiveRecord::Base.connection.table_exists?(name)
      Log.create_partition(
        name: name,
        start_range: day.beginning_of_month,
        end_range: day.next_month.beginning_of_month
      )
    end
  end

  def self.partition_name_for(day)
    "logs_y#{day.year}_m#{day.month}"
  end
end

For more examples, take a look at the model integration specs:

• https://github.com/rkrage/pg_party/tree/master/spec/integration/model

Development

The development / test environment relies heavily on Docker.

Start the containers in the background:

$ docker-compose up -d

Install dependencies:

$ bin/de bundle
$ bin/de appraisal

Run the tests:

$ bin/de appraisal rake

Open a Pry console to play around with the sample Rails app:

$ bin/de console

Contributing

Bug reports and pull requests are welcome on GitHub at https://github.com/rkrage/pg_party. 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.

License

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

Code of Conduct

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

pg_party's Issues

Any talks to move some of these features into Rails core?

I was just wondering if there's been any talks/RFCs/proposals to inline some pg_party functionality into Rails core?

In particular, it feels kinda bad that you have to do all this work essentially just to scope a class/ActiveRelation to a particular child table.

In particular, in my app, we have Inventory objects, and each inventory has its own set of partitioned relations such as inventory_items, departments, etc, and due to some issues with locking parent partitioned tables, when doing mass imports into these tables, we need to reference the partitioned child table directly, which means using in_partition and caching this child-table-specific class in the PgParty cache.

That means if we have X inventories and Y partitioned relations, the size of this cache will grow to X*Y, and no our app probably hasn't hit a point where we should worry, but it still feels like a bad leak (especially if we add any more partitioned-by-inventory resources).

So I was thinking it'd be nice if Rails had something like Model.with_table_name('foo') or something like that that could be created adhoc to override the table name for queries (and any other pg_party needs). Does something like that exist? Have there been any talks?

Rails 6.1 support

Are you planning to change specification in Gemfile to allow activerecord >= 6?

Feature request: Ongoing creation of partition tables

Having to manually create partition tables based on new criteria (dates for range partitions) or new list-related records leaves a lot of "figure it out for yourself!" up to the gem user. Would be really nice to have something in place to mitigate the looming question of lifetime partition maintenance.

No changelog entry for 1.5.0

There's no changelog entry for version 1.5.0 (which we're currently on making it rather moot) but at some point we will want to upgrade to any new version (I'm upgrading gems for the Ruby 3.0 keyword change and found pg_party-1.5.0/lib/pg_party/adapter/postgresql_methods.rb:62: warning: Using the last argument as keyword parameters is deprecated; maybe ** should be added to the call). A changelog (and preferably a ommitment to semantic versioning) would help with the upgrade process by making it clearer when there are safe/dangerous changes we need to handle.

Model method to list currently attached partitions

Might be able to accomplish this by querying the pg_inherits table: https://www.postgresql.org/docs/10/static/catalog-pg-inherits.html

Create child partitions from template tables

It might make sense to add functionality to create_range_partition_of and create_list_partition_of to create child partitions from template tables. This would be useful to copy over indexes and constraints.

pg_dump: error: cross-database references are not implemented

I have partitioned by range tables in a separate database schema. When I try to run db:migrate I get an exception:

pg_dump: error: cross-database references are not implemented: *.time_tracking.entries_y2022_m12_date_idx
rake aborted!
failed to execute:
pg_dump --schema-only --no-privileges --no-owner --file /Users/igor/workspace/onetribe/db/structure.sql onetribe_development -T *.time_tracking.entries_y2022_m12_date_idx -T *.time_tracking.entries_y2022_m11 -T *.time_tracking.entries_y2022_m12_pkey -T *.time_tracking.daily_entries_y2022_m12_member_id_task_uuid_date_idx -T *.time_tracking.daily_entries_y2022_m11_pkey -T *.time_tracking.daily_entries_y2022_m12_pkey -T *.time_tracking.daily_entries_y2022_m11_company_id_idx -T *.time_tracking.entries_y2023_m1 -T *.time_tracking.daily_entries_y2023_m1_company_id_idx -T *.time_tracking.daily_entries_y2022_m12 -T *.time_tracking.entries_y2023_m1_pkey -T *.time_tracking.entries_y2022_m11_pkey -T *.time_tracking.daily_entries_y2023_m1_pkey -T *.time_tracking.daily_entries_y2022_m11 -T *.time_tracking.entries_y2022_m11_date_idx -T *.time_tracking.entries_y2023_m1_date_idx -T *.time_tracking.entries_y2022_m12_company_id_member_id_task_uuid_idx -T *.time_tracking.entries_y2023_m1_company_id_member_id_task_uuid_idx -T *.time_tracking.entries_y2022_m11_company_id_member_id_task_uuid_idx -T *.time_tracking.daily_entries_y2022_m12_company_id_idx -T *.time_tracking.daily_entries_y2023_m1_member_id_task_uuid_date_idx -T *.time_tracking.entries_y2022_m12 -T *.time_tracking.daily_entries_y2023_m1 -T *.time_tracking.daily_entries_y2022_m11_member_id_task_uuid_date_idx

Please check the output above for any errors and make sure that `pg_dump` is installed in your PATH and has proper permissions.


Tasks: TOP => db:schema:dump
(See full trace by running task with --trace)

create_range_partition and Ruby 3

Hello! We're in the process of upgrading to Ruby 3, and we're running into an issue with create_range_partition:

# How we're using it in our DB migration
def up
  create_range_partition(:table_name, partition_key: :created_at, primary_key: [:id, :created_at]) do |t|
    ...
  end
end

# Error that occurs due to the Ruby 3 keyword arguments change
wrong number of arguments (given 2, expected 1; required keyword: partition_key)

Looking through the stacktrace, the issue seems to come from this module in pg_party:

module PgParty
  module Adapter
    module PostgreSQLMethods
      ruby2_keywords def create_range_partition(*args, &blk)
        PgParty::AdapterDecorator.new(self).create_range_partition(*args, &blk)
      end
    ...
  end
end

The arguments are coming in as:

["table_name", {:partition_key=>:created_at, :primary_key=>[:id, :created_at]}]

So that when it bubbles up to the AdapterDecorator, it can't parse out the partition key properly:

def create_range_partition(table_name, partition_key:, **options, &blk)
  create_partition(table_name, :range, partition_key, **options, &blk)
end

At least, that's what I understand it to be doing? Are we using create_range_partition incorrectly, or should there be a fix? Thanks so much!

Migrating from 0.7.3 to 1.6.0 process

See edit below, this isn't even possible for us :(

Hey there, this isn't an issue or bug, more of a question. I'm running on a pretty old version of pg_party (which isn't even in your official tag list lol), this is on an old Rails app running Rails 5 & Ruby 2.2. We also have a new app running Rails 7 & Ruby 3.2.

Both of these apps share the same database, postgres 14.7. Back when we first started using pg_party we were running postgres 10 and we set things up to create the partition indexes after a partition is created. It looks like since pg_party 1.0, we no longer need to manually create the partition indexes, is that correct?

We want to create a new nested partition table (partition by list, then partitioned by range), which only exists in new versions of pg_party.

My thinking is we should be able to upgrade to 1.6.0 on our Rails 5 app, remove the code that creates indexes on partitions after they're created, and everything else can stay as-is. Or maybe I can just leave all that code as-is and it'll continue to create the indexes manually?

Then I can create my new partitioned table with the subpartions using the features from the latest gem version and we're off to the races.

Does that track with you @rkrage?

EDIT
Due to Ruby & Rails versions, the highest we can go with pg_party is 1.0.1, which is not too far off from what 0.7.3 offers. I think we're going to have to resort to some hackery to accomplish our goals in created the partitioned and subpartitioned tables.

Final Edit:
We're going to run the main migration in our legacy app (running pg_party 1.0) to create the list-based table partition, then use our modern app (pg_party 1.6.0) to create the sub-partitions on the fly as needed

Convert a normal table to partitioned table

Hi, I hope you are doing well. I'm have some performance issues with huge table (millions of records) so I'm interested in convert this table to a partitioned table because it fits perfectly with what we need. ¿Is there a way to alter this table to make it partitioned or is required create another table?

Migration from `partitioned` gem to `pg_party`

Good day.

Does anybody know how to migrate from https://github.com/fiksu/partitioned to pg_party?
We have a super old project and try to update.

The partitioned gem uses other schema for partitioned tables, e.g.:

• table p201511, schemaname: my_table_name_partitions
• table my_table_name, schemaname: public

Second, the partitioned gem doesn't use native PG table partitioning :(

We use partitioning by date.

Thanks,
Aleksei.

Multiple Key Partition

Hello,

Currently, I'm trying to define a 3 key range partition, but I can't make it work (in terms of PostgreSQL query).

First of all the code:

`

create_range_partition_of \
  :scores,
  name: "scores_2007".to_sym,
  partition_key: ->{ "recent, deleted, played_at" },
  start_range: [false, false, "1980-01-01"],
  end_range: [false, false, "2008-01-01"]

`

It all works, the partitions get created and I can see them in PGAdmin, but when running a query that is expected to select a certain partition it does not work.

explain select * from scores where recent = false and deleted = false and played_at = DATE '2006-03-03' - It checks on all partitions.

However, if I only do a 2 key range partition all works fine.

`

create_range_partition_of \
  :scores,
  name: "scores_2007".to_sym,
  partition_key: ->{ "recent, played_at" },
  start_range: [false, "1980-01-01"],
  end_range: [false, "2008-01-01"]

`

explain select * from scores where recent = false and deleted = false and played_at = DATE '2006-03-03' - Checks only scores_2007 partition.

Do you have any Idea if this is doable, or there are certain constraints that don't allow it to be done with 3 keys?

Thank you!

Add scope to query by partition name

Maybe create anonymous class in the model decorator. Something like:

def in_partition(name)
  Class.new(__getobj__) do
    self.table_name = name
  end
end

Run tests on multiple ruby versions in CI

Schema file contains partion tables

When migrations are run and a new schema.sql file is generated, it contains the user-created partition tables for any partitions in the database. This is expected, but also seems wrong since in a multi-dev environment, there will be constant differences in this file since everyone will have different partitions.

Perhaps there's a way to instruct the schema generator to only dump the master partition record.

Add support for complex partition key query methods

Since ActiveRecord does some crazy aliasing when joins are involved, I think we'll need to do some subquery magic for partition_key_eq and partition_key_in to be chainable.

Address Rails 6.1 deprecation warnings

Seeing stuff like this:

Using `bin/rails db:structure:dump` is deprecated and will be removed in Rails 6.2. Configure the format using `config.active_record.schema_format = :sql` to use `structure.sql` and run `bin/rails db:schema:dump` instead

Merging ("uuid_string_ranges"."some_string" >= 'a') and ("uuid_string_ranges"."some_string" < 'z') no longer maintain both conditions, and will be replaced by the latter in Rails 6.2. To migrate to Rails 6.2's behavior, use `relation.merge(other, rewhere: true)`

Use #create_list_partition_of in rake task?

Is it possible to call create_list_partition_of in a rake task?

I've added require 'pg_party' at the top of the file but haven't had any luck with things like:
create_list_partition_of()
PgParty.create_list_partition_of()

Is there a different way I should be accessing this method outside of migration context?

Add self.name method to abstract class

I've caught an ArgumentError when I call model_name method for my partition table.

class TMainDatum < ApplicationRecord
  range_partition_by :date
end

For the parent class it's ok:

TMainDatum.model_name
=> #<ActiveModel::Name:0x007ffd671791b8
 @collection="t_main_data",
 @element="t_main_datum",
 @human="T main datum",
 @i18n_key=:t_main_datum,
 @klass=TMainDatum (call 'TMainDatum.connection' to establish a connection),
 @name="TMainDatum",
 @param_key="t_main_datum",
 @plural="t_main_data",
 @route_key="t_main_data",
 @singular="t_main_datum",
 @singular_route_key="t_main_datum">

and for the child class it raises an error:

TMainDatum.in_partition('t_main_data_2017_34').model_name
ArgumentError: Class name cannot be blank. You need to supply a name argument when anonymous class given
from /ruby/gems/2.3.0/gems/activemodel-5.1.4/lib/active_model/naming.rb:149:in `initialize

Maybe you could simply to add self.name to ModelDecorator, couldn't you?

def child_class(table_name) do 
  Class.new(__getobj__) do
    ...
    def self.name
      superclass.name
    end
  end
end

The real usage of model_name dug into activerecord-import: https://github.com/zdennis/activerecord-import/blob/master/lib/activerecord-import/import.rb#L650

Feature request: select range partitions by one of date

At first, thanks for the gem!

I have couple ideas, that I hope could make using the gem little easier.

1. For now, it's possible to select partitions by two dates - start date and end date. I guess it would be nice to have them optional, like:

Model.partitions(start_date: Date.today) or Model.partitions(end_date: Date.today)

Example: I need to find the last record, but not later than today.

2. Maybe it worth to return list of partitions in sorted order. Even more, would be good to allow order from model, like:

Model.partitions(order: :asc) or Model.partitions(order: :desc)

Document new features

Update readme examples for the new features on master

Question: schema_exclude_partitions = false development workflow

If schema_exclude_partitions = false, how do ensure there is a writable partition in development/test environments?

ActiveRecord doesn't support composite primary keys?

I'm on Rails 5.2 and I see this when I instantiate a record from a partitioned table:

WARNING: Active Record does not support composite primary key.

partitioned_inventory_items has composite primary key. Composite primary key is ignored.

I've heard that AR doesn't support composite primary keys, but yet pg_party seems to suggest in the README that you use composite primary keys? I'm also wondering how this hasn't already come up in the issues...

Create a sample app or clear instructions

Hello,
It would be nice to create a simple tutorial or whole sample app.
Currently, it's not clear how to use work with it. Plus it can bring more people to use this gem
Thanks

The way I setup on Rails 6.1

Version: Rails 6, PostgreSQL 12

1. Config setting

# config/initializers/pg_party.rb
PgParty.configure do |c|
  c.create_with_primary_key = false
end

2. Create table migration and run rails db:migrate

class CreateOrdersWithTablePartitioning < ActiveRecord::Migration[6.1]
  def up
    create_range_partition :orders, partition_key: :created_at do |t|
      t.string :product_name
      t.decimal :price
      t.integer :status
      t.references :product

      t.timestamps
    end

    execute "ALTER TABLE public.orders ADD PRIMARY KEY (id, created_at);"

    create_range_partition_of(
      :orders,
      name: "orders_#{Date.today.year}_#{Date.today.month}",
      start_range: Date.today.beginning_of_month,
      end_range: Date.today.next_month.beginning_of_month
    )
  end

  def down
    drop_table :orders
  end
end

3. Add model bypassing migration
   rails g model Order --skip-migration

4. Remove the warning of "WARNING: Active Record does not support composite primary key."

# /app/models/order.rb
class Order < ApplicationRecord
  self.primary_key = :id
end

Thanks for author's awesome work.

Create index helpers for models

Since the partitioned tables will/can get created outside the scope of a migration, it makes sense to add those helper methods (add_index) to the model helpers.

This would compliment the existing helpers to manage creating partitioned tables, like "create_partition".

Address code climate issues / introduce rubocop

For better code quality / maintainability

Add support for Rails 4.2

This will require a custom implementation of the bigserial column type that only exists in Rails 5.x

How to list partition tables?

Hi,

I just wonder how to check if a partition table for a given range exists.

The Readme describes the SomeRangeRecord.partitions functions, which gives just cryptic table names, but not telling for which ranges they were created.

What is the recommended way to insert some value if it is unknown whether a partition table exists for that range? Catch an exception, create a partition, and retry?

Fix ruby 2.7 keyword argument warnings

The 2.7 builds have a ton of deprecation warnings like:

Using the last argument as keyword parameters is deprecated; maybe ** should be added to the call

This is due to the work to separate positional and keyword args: https://www.ruby-lang.org/en/news/2019/12/12/separation-of-positional-and-keyword-arguments-in-ruby-3-0/

Explanation needed: Are many "partition tables" a problem?

Hi there,

this question may be a bit too "broad" but i wonder if these "partition tables" can be used for "personal report data".

That means big tables split up in maybe hundreds of "partitioned tables" which belong to several certain users.
(Yearly report for user X, monthly report for user Y and so on)

Are there known drawbacks if a user has, for example 10 partitioned tables?

Is this a use case for "partitioned tables" in postgres?

Thanks in advance!
Niklas

Add support for more complex partition keys

To allow for partition tables like this:

CREATE TABLE measurement_year_month (
    logdate         date not null,
    peaktemp        int,
    unitsales       int
) PARTITION BY RANGE (EXTRACT(YEAR FROM logdate), EXTRACT(MONTH FROM logdate));

It will be pretty difficult to convert something like this to Arel for use in the model scopes. Maybe for the first iteration we could just refrain from injecting scope methods for complex keys?

Update readme for 1.2.0 release

Add some notes about configuration and caching behavior.

Missing in db/schema.rb

Hi,
I'm just trying and playing around with it, and I found, that the migrations creating partitions are missing in db/schema.rb, thus the database cannot be recreated with the common rails tools.

Raise error when running migrations on incompatible Postgres versions

To implement this, it might make sense to delegate migration methods to a class that wraps the postgres adapter

Child Partition Names

Just found this gem, and it looks like an excellent start to help manage partitions under PGSQL 10.

There are a couple of suggestions that I have. I have decided to use pg_partman to help manage my native child partitions, and this extension automatically creates a pool of new partitions before I need them.

The partitions are named "master_table_p0", "master_table_p1" etc based on the single value in my range that I am using as the partition key.

It would be nice in pg_party if I could provide the name of the partition when creating child partitions so that I can keep the same convention. The name convention will also help me manage old partitions that I no longer need.

Also, creating a child partition does not seem to allow for the use of a template table so that the indexes can be copied and built on the new partition.

Partition by ForeignKey?

Could you clarify if partitioning by foreign key is supported?

I see this example in the ReadMe:

create_list_partition :some_list_records, partition_key: :id do |t|
      t.integer :some_foreign_id
      t.text :some_value
      t.timestamps
end

But is it possible to use partition_key: :some_foreign_id like so:

create_list_partition :some_list_records, partition_key: :some_foreign_id do |t|
      t.references :some_foreign, foreign_key: true
      t.string :name
      t.timestamps
end

?

So that if I want a partition per :some_foreign_id, could I do something like:

SomeForeign.all.map do |foreign|
  create_list_partition_of :some_list_records, name: foreign.name, partition_key: :some_foreign_id, values: [foreign.id]
 end

with each partition getting the respective name of each foreign record?

[Question] [Enhancement] - Partitioning an existing table and creating new paritions

I want to take a large table I have and split it per user.
Is it possible to:

1. Migrate a table into a partitioned table? ( Even if need to create a new one )
2. Since new users will come and go , is there support to create a new table when a new user is created?

rspec tests fail unless using structure.sql

I noticed that unless I switch from schema.rb to structure.sql, I get:
PG::InvalidObjectDefinition: ERROR: table "x" is not partitioned

when calling create_list_partition_of in rspec.

Is there any way to run tests on partitioned tables without having to switch to structure.sql?