Feature request: Ongoing creation of partition tables

[justinperkins]

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.

[rkrage]

Hey @justinperkins,

Do you have any thoughts on how to accomplish this? Are you imagining something at the model level where you can just say "hey, create the next partition" and it just knows how to do that based on some configuration?

[justinperkins]

Some things I've been tossing around for ... Scheduled cron job (gross but effective), begin ... rescue around creates to check for missing partitions (also gross, and slow), manually checking for partition existence before create (maybe the only decent idea?)

Could go with the triggers ala PG9 style partitions? Not a big fan of those

[arrowcircle]

@rkrage Hey! Thanks for the great gem. Wanted to share my code with example. Maintenance method creates tables for previous, current and next months if they do not exist. It's great to run this method in tests before suite or even in the migration. Method is executed every day with sidekiq-cron.

If this code is ok, I can make a pull request to the docs.

Is this approach ok? Any ideas on the best practices on partition names?

class EventLogArchive < ApplicationRecord
  self.primary_key = nil
  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 = EventLogArchive.partition_name_for(day)
      next if ActiveRecord::Base.connection.table_exists?(name)
      EventLogArchive.create_partition(
        name: name,
        start_range: day.beginning_of_month,
        end_range: day.end_of_month
      )
    end
  end

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

[rkrage]

This is awesome! Would love an example like this in the documentation.

In regards to naming, as long as it doesn't result in collisions and it's descriptive to you, 🤷‍♂

[7200rpm]

So I'm trying to use this approach with subpartitions. I have a list partition with range partitions under it. But it doesn't look like there are any model methods for this? Is the only way to do this through migrations? (like in the example). Would love to see the model methods added.

[subelsky]

FYI can also use triggers to create them automatically, check about halfway down this article: https://evilmartians.com/chronicles/a-slice-of-life-table-partitioning-in-postgresql-databases

pasting in the screenshot from the article:

[rkrage]

As noted in the readme, I don't think this should be in the scope of this gem: https://github.com/rkrage/pg_party#future-work

I don't think it's super worthwhile to rewrite pg_partman in ruby