Skip to content

Set sharding key for the topics table to organization_id

Sharding keys need to be set for the tables: topics to organization_id.

See the related discussion !152834 (comment 1918055153).

Update from Aug. 7th, 2024:

  1. As decided in !152834 (comment 1918055153), the sharding key for topics will be organization_id
  2. The table has 244090 records, which makes it easier to migrate:
Details 
SELECT * FROM topics;

 Seq Scan on public.topics  (cost=0.00..37460.90 rows=244090 width=328) (actual time=2.201..461.680 rows=244090 loops=1)
   Buffers: shared read=8755 dirtied=1490
   I/O Timings: read=398.397 write=0.000
  1. The table has no organization_id column yet.
Details 
Table "public.topics"
           Column           |           Type           | Collation | Nullable |              Default               | Storage  | Compression | Stats target | Description 
----------------------------+--------------------------+-----------+----------+------------------------------------+----------+-------------+--------------+-------------
 id                         | bigint                   |           | not null | nextval('topics_id_seq'::regclass) | plain    |             |              | 
 name                       | text                     |           | not null |                                    | extended |             |              | 
 created_at                 | timestamp with time zone |           | not null |                                    | plain    |             |              | 
 updated_at                 | timestamp with time zone |           | not null |                                    | plain    |             |              | 
 avatar                     | text                     |           |          |                                    | extended |             |              | 
 description                | text                     |           |          |                                    | extended |             |              | 
 total_projects_count       | bigint                   |           | not null | 0                                  | plain    |             |              | 
 non_private_projects_count | bigint                   |           | not null | 0                                  | plain    |             |              | 
 title                      | text                     |           |          |                                    | extended |             |              | 
 slug                       | text                     |           |          |                                    | extended |             |              | 
Indexes:
    "topics_pkey" PRIMARY KEY, btree (id)
    "index_topics_non_private_projects_count" btree (non_private_projects_count DESC, id)
    "index_topics_on_lower_name" btree (lower(name))
    "index_topics_on_name" UNIQUE, btree (name)
    "index_topics_on_name_trigram" gin (name gin_trgm_ops)
    "index_topics_on_slug" UNIQUE, btree (slug) WHERE slug IS NOT NULL
    "index_topics_total_projects_count" btree (total_projects_count DESC, id)
Check constraints:
    "check_0eda72aeb0" CHECK (char_length(slug) <= 255)
    "check_223b50f9be" CHECK (char_length(title) <= 255)
    "check_26753fb43a" CHECK (char_length(avatar) <= 255)
    "check_5d1a07c8c8" CHECK (char_length(description) <= 1024)
    "check_7a90d4c757" CHECK (char_length(name) <= 255)
Referenced by:
    TABLE "project_topics" CONSTRAINT "fk_34af9ab07a" FOREIGN KEY (topic_id) REFERENCES topics(id) ON DELETE CASCADE
Access method: heap

Deliverables

  • Add the organization_id column to topics, NOT NULL, with default organization 1 !163410 (merged)
  • Create FK to organizations !163410 (merged)
  • Update the code to always set organization_id when a record is created
  • Remove the column default
Edited by Krasimir Angelov