Skip to content

Change primary key definition to bigint

Andreas Brandl requested to merge ab/partitioning-pk into master

What does this MR do?

This changes the primary key datatype from integer to bigint while creating the partitioned table. This is a good time to do this in order to prevent overflows from integer primary keys, because the new table is still empty at this time.

Compatibility-wise, integer values are just being coerced into bigint - so this is not a problem. However, if other tables were referencing this table - they would also need to reflect this change, before actual values hit the int4 limit.

We already track a couple of tables with risk of integer overflow:

Since this MR doesn't include a migration example, the following example would turn audit_events into a partitioned table with a bigint primary key:

    partition_table_by_date :audit_events, :created_at, min_date: Date.parse('2019-01-01'), max_date: Date.today + 1.month

The resulting schema is:

\d audit_events_part_5fc467ac26
                Table "public.audit_events_part_5fc467ac26"
   Column    |            Type             | Collation | Nullable | Default 
-------------+-----------------------------+-----------+----------+---------
 id          | bigint                      |           | not null | 
 author_id   | integer                     |           | not null | 
 type        | character varying           |           | not null | 
 entity_id   | integer                     |           | not null | 
 entity_type | character varying           |           | not null | 
 details     | text                        |           |          | 
 updated_at  | timestamp without time zone |           |          | 
 created_at  | timestamp without time zone |           | not null | 
Partition key: RANGE (created_at)
Indexes:
    "audit_events_part_5fc467ac26_pkey" PRIMARY KEY, btree (id, created_at)
Number of partitions: 20 (Use \d+ to list them.)

Issue: #221091 (closed)

Edited by 🤖 GitLab Bot 🤖

Merge request reports