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

[machty]

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?

[rkrage]

To be honest, I haven't done a ton of performance testing with the PgParty model cache. I remember running the test suite with and without caching and I think it was a few seconds faster with caching, but that was hardly a scientific test. Have you experimented with turning caching off entirely?

It would be awesome if some of the PgParty features could get merged into Rails core, but I haven't started any discussions at this point. Recently, I haven't been using Rails a ton in my professional career and haven't been keeping up with all the new features. So, maybe something like Model.with_table_name('foo') already exists?

I'm not sure if I have the time to put together a Rails PR, but I'd encourage you to start a conversation on the mailing list: https://discuss.rubyonrails.org/c/rubyonrails-core/5

[andyatkinson]

@machty @rkrage I'd be interested in discussing how to make this happen as well. Please contact me via GH or https://andyatkinson.com/ if you're interested. I know it's been almost 2 years since your comments were created.

[machty]

@andyatkinson I don't have any bandwidth to spare on this unfortunately :(

[igor-alexandrov]

@andyatkinson If this is still a case, I have a bandwidth to do this.

[rkrage]

@igor-alexandrov let me know if / when you start a discussion on the mailing list and I can join the conversation. I don't think I have the bandwidth to actually implement these features in Rails, but I am curious if they're open to the idea

[andyatkinson]

@igor-alexandrov @rkrage I'm sort of of in the same boat. I'm happy to contribute to a discussion on why I think more table partitioning support in Rails would help. I can't commit to code though since I'm involved heavily in my book now (which also covers this topic). :) I'd also need to explore what table partitioning options there are in MySQL and Sqlite as well if any. Do you think PostgreSQL only would fly?

For example one thing a team member just hit on a table partitioning yesterday with PostgreSQL is trying to create an index "concurrently" on a partitioned table. Active Record lets you do this, but it's not supported in PostgreSQL. There is a workaround to add the index concurrently to all the partitions of the partitioned table, and then the lock period for the parent partitioned table is minimal. Active Record could intercept that and raise an error if it knew that the table backing a model was a partitioned table, or even possibly perform that kind of DDL migration to all the partitions.

I proposed this talk for RailsConf this year but it wasn't accepted, but it's a case study of how we used pgslice to partition a very large (> 1 TB) table. Since then I have a little familiarity with pg_partman as well (an extension). The pgslice codebase is nice and small, and sort of integrated into the app as a class in lib that calls different pgslice commands. I have a sample Rails app I can show if interested.

https://andyatkinson.com/blog/2023/08/17/postgresql-sfpug-table-partitioning-presentation

Maybe we could start with a draft proposal about how Active Record could support table partitioning, what the benefits are, and what developers would need to know.

[rkrage]

Do you think PostgreSQL only would fly?

Probably not. There is some precedent where options passed to some methods are only relevant for a particular database, but I think it's pretty rare to see public methods that only work for one database.

Other databases support partitioning, but I think the differences are so nuanced that creating generic partitioning functionality will be extremely difficult (at least for the migration methods). It might be possible to create generic model methods however (partitions, in_partition, partition_key_eq, etc.).

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.

When I start work on this new gem, maybe I can experiment a bit with other databases and we can revisit this conversation.

Would folks be okay with me closing this issue?