Size partitioned tables by their largest partition in UpdateTableSizes keep

V K Adhithiya Kumarrequested to merge
gitlab-community/gitlab-org/gitlab:dev-adhithiya/629648-size-partitioned-tables-by-largest-partition into master

What does this MR do and why?

Keeps::UpdateTableSizes classified every table from the postgres_table_sizes row whose table_name matched the dictionary entry in the public schema. For a hash- or list-partitioned table that row is the routing parent, which holds no data, so every partitioned table was written back to small on each weekly run (or skipped entirely on PostgreSQL versions where the parent has no row in the view). Manual corrections such as !256776 (closed) could not stick.

The database dictionary defines the size of a partitioned table as the size of its largest partition. This MR makes the keep implement that rule.

Changes

Keeps::Helpers::PostgresAi#fetch_postgres_table_size

  • Builds a candidate set of relations: the table itself in public, plus, when that relation is a partitioned table (relkind = 'p'), every partition attributed to it by pg_inherits.
  • Joins the candidates to postgres_table_sizes and returns the single largest relation with its classification.
  • Returns two extra columns: identifier (the relation that was measured) and is_partition (whether it is a partition rather than the table itself).

Keeps::UpdateTableSizes

  • Parses the new row shape. tables_to_update still maps table_name => classification, so make_change! and the rubocop-migrations.yml handling are unchanged.
  • Adds a partitioned_tables entry to the change context and a Partitioned tables section to the generated MR description listing each partitioned table and the partition that drove its classification. This makes the blast radius of the first corrected run visible to the reviewer, as asked for in #629648.

Why pg_inherits rather than a name prefix

The issue asked for this decision to be stated. A prefix match on issue_search_data% is cheap but wrong at the edges: it would also pick up a future issue_search_data_archive or any sibling that happens to share the prefix. pg_inherits is the catalog's own record of which relations are partitions of which parent, so it cannot be fooled by naming, and it costs nothing on a view that is already built over pg_stat_user_tables. The query resolves direct children only; GitLab does not use multi-level partitioning, and a sub-partitioned child would report zero bytes and lose the ordering anyway.

Behaviour that is intentionally unchanged

  • table_write_locked? is still scoped to the public relation. The lock-writes trigger is attached to the parent, and a same-named relation in another schema must still not decide a skip.
  • table_has_data? is not touched, per the issue.
  • Size thresholds and the rubocop-migrations.yml mapping (large / over_limit only) are unchanged.
  • Unpartitioned tables resolve to exactly the same row as before; the public table is always a candidate and is preferred on a size tie.

How to verify

The #fetch_postgres_table_size spec now runs the real query against the test database with the postgres_table_sizes view swapped for a table. It creates a hash-partitioned _test_ table whose parent reports 0 bytes and whose largest partition is 42 GiB, and asserts the result is medium from that partition, both with and without a parent row in the view. It also asserts a same-named relation in another schema and a same-prefix sibling table are ignored. These examples fail against master, which returns the parent row and small.

bin/rspec spec/keeps/helpers/postgres_ai_spec.rb spec/keeps/update_table_sizes_spec.rb

Against a replica, the following should return the largest gitlab_partitions_static.issue_search_data_NN row and medium:

-- keeps/helpers/postgres_ai.rb, fetch_postgres_table_size, with $1 = 'issue_search_data'

Expected effect on the next keep run

The first run after this lands will reclassify every hash- and list-partitioned table currently marked small whose largest partition exceeds 10 GiB. The generated MR will list each one alongside its largest partition. Tables promoted to large or over_limit will also be added to rubocop/rubocop-migrations.yml, which is the existing behaviour for those bands.

References

Closes #629648

Related: #629647, !256776 (closed)

Parent epic: &23629

MR acceptance checklist

  • This MR has been reviewed against the MR acceptance checklist.
  • This contribution was prepared with the help of an AI assistant. I reviewed the changes and take responsibility for them.

Merge request reports