Postgres Data Partition in Rails 7.0.8

Question

We have situation in the database, where we have to make one table schema of entire tables as data partitioned based on tenant id clause

Using

create_table "billing_schedule_lines_old", id: :serial, force: :cascade do |t|
    t.string "product_name", null: false
    t.string "plan_id"
    t.string "plan_name"
    t.string "charge_external_id"
    t.string "charge_name", null: false
    t.string "charge_type", null: false
    t.datetime "charge_ready_date", precision: nil
    t.datetime "start_date", precision: nil
    t.datetime "end_date", precision: nil
    t.float "included_quantity", default: 0.0
    t.float "quantity", null: false
    t.float "price", null: false
    t.float "discount", null: false
    t.bigint "effective_price", null: false
    t.boolean "invoiced", default: false, null: false
    t.datetime "created_date", precision: nil, null: false
    t.datetime "updated_date", precision: nil, null: false
    t.integer "created_by_id", null: false
    t.integer "updated_by_id", null: false
    t.integer "billing_schedule_id"
    t.integer "customer_id", null: false
    t.integer "invoice_id"
    t.integer "tenant_id", null: false
    t.string "product_id", null: false
    t.integer "subscription_id", null: false
    t.boolean "latest", default: true
    t.string "price_base"
    t.string "pricing_method", default: "per_unit"
    t.boolean "coupon_line", default: false
    t.float "conversion_rate", default: 1.0
    t.boolean "prepayment", default: false
    t.jsonb "custom_fields", default: {}
    t.integer "prepayment_period", default: 0
    t.integer "closed_fp_contract", default: 0
    t.boolean "user_driven", default: false
    t.boolean "bonus", default: false
    t.float "unused_prepayment_units", default: 0.0
    t.float "charge_included_units", default: 0.0
    t.integer "line_type", default: 0
    t.float "prepayment_units", default: 0.0
    t.integer "parent_link"
    t.jsonb "applied_tiers", default: []
    t.integer "prepaid_units_expiry", default: 1
    t.float "list_price"
    t.string "list_price_base"
    t.boolean "fmv_allocation_enabled", default: false
    t.float "fmv_effective_price", default: 0.0
    t.bigint "fmv_allocation", default: 0
    t.bigint "expired_units", default: 0
    t.integer "subscription_charge_id"
    t.integer "regular_line_link"
    t.string "uid"
    t.string "parent_uid"
    t.integer "tier_offset", default: 0
    t.integer "seq", default: 0
    t.jsonb "usage_data"
    t.string "billing_schedule_uid"
    t.string "subscription_charge_uid"
    t.float "base_fx_effective_price", default: 0.0
    t.index ["billing_schedule_id", "start_date"], name: "billing_lines_idx_billing_id_start_date"
    t.index ["billing_schedule_id"], name: "index_billing_schedule_lines_on_billing_schedule_id"
    t.index ["billing_schedule_uid"], name: "index_billing_schedule_lines_on_billing_schedule_uid"
    t.index ["charge_ready_date"], name: "index_billing_schedule_lines_on_charge_ready_date"
    t.index ["company_id"], name: "index_billing_schedule_lines_on_company_id"
    t.index ["created_by_id"], name: "index_billing_schedule_lines_on_created_by_id"
    t.index ["customer_id"], name: "index_billing_schedule_lines_on_customer_id"
    t.index ["invoice_id"], name: "index_billing_schedule_lines_on_invoice_id"
    t.index ["subscription_charge_id"], name: "index_billing_schedule_lines_on_subscription_charge_id"
    t.index ["subscription_charge_uid"], name: "index_billing_schedule_lines_on_subscription_charge_uid"
    t.index ["subscription_id", "charge_external_id"], name: "index_billing_schedule_lines_on_sub_id_and_charge_ext_id"
    t.index ["subscription_id", "prepayment_period"], name: "index_billing_schedule_lines_on_sub_id_and_prepay_period"
    t.index ["subscription_id", "start_date"], name: "billing_lines_idx_subscription_id_start_date"
    t.index ["subscription_id"], name: "index_billing_schedule_lines_on_subscription_id"
    t.index ["uid"], name: "index_billing_schedule_lines_on_uid", unique: true
    t.index ["updated_by_id"], name: "index_billing_schedule_lines_on_updated_by_id"
  end

CREATE TABLE billing_schedule_lines (
        LIKE billing_schedule_lines_old INCLUDING DEFAULTS
    ) PARTITION BY LIST (tenant_id);


DO $$
DECLARE
    row_data record;
    partition_value int;
BEGIN
    FOR row_data IN
    SELECT id as partition_value
    FROM companies
    LOOP
    partition_value := row_data.partition_value;
        --RAISE NOTICE 'Creating partition table of billing_schedule_lines for company: %',partition_value;
        -- Construct the partition table name dynamically
    EXECUTE format('CREATE TABLE IF NOT EXISTS billing_schedule_lines_%s (id SERIAL PRIMARY KEY) PARTITION OF billing_schedule_lines FOR VALUES in (%s)',
               partition_value, partition_value);
    END LOOP;
END $$;

Using this data partition was done, after this change Single

line = BillingScheduleLine.last is not working it says

ActiveRecord::IrreversibleOrderError: Relation has no current order and table has no primary key to be used as default order

bulk insert is failing with error message

Caused by PG::NotNullViolation: ERROR:  null value in column "id" of relation "billing_schedule_lines_491" violates not-null constraint 

column_default is nextval('billing_schedule_lines_id_seq'::regclass)

Similar kind of problem is with update.

Postgres: 15.5 Rails : 7.0.8

Answer 1

billing_schedule_lines_old lacks a primary key, and so does your partitioned table created from it (billing_schedule_lines), just as the message says. Adding a primary key to the partitions does not help.

Similarly your attempts to insert are failing because the partitioned table lacks an id column, while your partitions require it. It looks like the bulk insert is passing a null id. I can't say more without seeing your insert code, but I doubt this can be made to work.

By default, last uses the primary key to order rows. Since BillingScheduleLine lacks a primary key, BillingScheduleLine.last does not work.

Presumably billing_schedule_lines had a primary key or BillingScheduleLine.last would never have worked. So there might have been a mistake transcribing billing_schedule_lines to billing_schedule_lines_old.

There's probably a better way to do the conversion from an unpartitioned table to partitioned table, but we don't have enough context to help.

Finally, you're partitioning into what looks like hundreds of tables. Partitioning is not a magic bullet. Done without careful consideration it often leads to more problems. billing_schedule_lines is quite large with many references. Rethinking the table design might be a better solution, but we don't know your problem.

---

Notes:

t.index ["x"] is redundant with t.index ["x", "y"]. So you can drop the indexes on subscription_id, billing_schedule_id.