Skip to content

Use database helpers to create partitioned tables

Marius Bobin requested to merge mb-docs-partitioned-tables into master

What does this MR do and why?

It turns out that it is possible to use the options: attribute on create_table to append partitioning arguments for the table creation. This MR updates the current docs to make table creation easier.

Screenshots or screen recordings

[1] pry(main)>     Ci::Build.connection.create_table(:p_ci_examples, primary_key: [:id, :partition_id], options: 'PARTITION BY LIST (partition_id)', if_not_exists: true) do |t|
      t.bigserial :id, null: false
      t.bigint :partition_id, null: false
      t.bigint :build_id, null: false
[1] pry(main)> Ci::Build.connection.create_table(:p_ci_examples, primary_key: [:id, :partition_id], options: 'PARTITION BY LIST (partition_id)', if_not_exists: true) do |t|
  t.bigserial :id, null: false
  t.bigint :partition_id, null: false
  t.bigint :build_id, null: false
   (3.6ms)  CREATE TABLE IF NOT EXISTS "p_ci_examples" ("id" bigserial NOT NULL, "partition_id" bigint NOT NULL, "build_id" bigint NOT NULL, PRIMARY KEY ("id", "partition_id")) PARTITION BY LIST (partition_id) /*application:console,db_config_name:ci,console_hostname:rocket-sled.local,console_username:marius,line:(pry):1:in `__pry__'*/
=> #<PG::Result:0x00000001378ce230 status=PGRES_COMMAND_OK ntuples=0 nfields=0 cmd_tuples=0>
gitlabhq_development_ci=# \d+ p_ci_examples
                                            Partitioned table "public.p_ci_examples"
    Column    |  Type  | Collation | Nullable |                  Default                  | Storage | Stats target | Description
--------------+--------+-----------+----------+-------------------------------------------+---------+--------------+-------------
 id           | bigint |           | not null | nextval('p_ci_examples_id_seq'::regclass) | plain   |              |
 partition_id | bigint |           | not null |                                           | plain   |              |
 build_id     | bigint |           | not null |                                           | plain   |              |
Partition key: LIST (partition_id)
Indexes:
    "p_ci_examples_pkey" PRIMARY KEY, btree (id, partition_id)
Number of partitions: 0

How to set up and validate locally

Numbered steps to set up and validate the change are strongly suggested.

MR acceptance checklist

This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.

Edited by Marius Bobin

Merge request reports