0

I need to create a Postgres table partitioned by range. The partitioning approach is essential because the table will need to store large volumes of data and be queried frequently. The plan is to set up a job that periodically drops old, unused partitions.

My backend is in Rails (version 7.2). I know that partitioning support was tricky before due to ActiveRecord's lack of support for composite primary keys, but my understanding is that this was addressed in Rails 7. However, I'm still a bit unsure how to go about implementing this.

All the examples I've found so far are for previous versions of Rails, where people used workarounds, such as:

  • Creating a partitioned table without a primary key and monkey-patching ActiveRecord to make the save and update functions work, or
  • Creating a partitioned table with a primary key on the id column and a composite index on both the id and the partition key.

How do I create a new partitioned table leveraging Rails 7's support for composite primary keys?

Improve this question
3
  • Rails 8 introduced some support for native partitioning so I would consider if you can upgrade before you try to implement it. The Rails 7 Ruby schema dumper doesn't understand partioning so if you're not using SQL schema dumps the partitioning will just disappear when recreating the DB from the schema in development. Commented Mar 14 at 11:48
  • Thanks @max! Unfortunately upgrading rails version is not an option at the moment but I do use structured.sql. Would this help? what are my options? Commented Mar 14 at 13:59
  • 2
    John Nunemaker has a pretty good rundown. johnnunemaker.com/rails-postgres-native-partitioning Commented Mar 14 at 14:15
Related questions
1251
Getting error: Peer authentication failed for user "postgres", when trying to get pgsql working with rails
527
Postgres and indexes on foreign keys and primary keys
583
updating table rows in postgres using subquery
Load 4 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.