diff options
author | GitLab Bot <gitlab-bot@gitlab.com> | 2020-05-20 17:34:42 +0300 |
---|---|---|
committer | GitLab Bot <gitlab-bot@gitlab.com> | 2020-05-20 17:34:42 +0300 |
commit | 9f46488805e86b1bc341ea1620b866016c2ce5ed (patch) | |
tree | f9748c7e287041e37d6da49e0a29c9511dc34768 /doc/development/ordering_table_columns.md | |
parent | dfc92d081ea0332d69c8aca2f0e745cb48ae5e6d (diff) |
Add latest changes from gitlab-org/gitlab@13-0-stable-ee
Diffstat (limited to 'doc/development/ordering_table_columns.md')
-rw-r--r-- | doc/development/ordering_table_columns.md | 90 |
1 files changed, 45 insertions, 45 deletions
diff --git a/doc/development/ordering_table_columns.md b/doc/development/ordering_table_columns.md index cbfd05e731d..b68602ea30d 100644 --- a/doc/development/ordering_table_columns.md +++ b/doc/development/ordering_table_columns.md @@ -77,64 +77,64 @@ always be at the end of a table. Let's use the `events` table as an example, which currently has the following layout: -| Column | Type | Size | -|:------------|:----------------------------|:---------| -| id | integer | 4 bytes | -| target_type | character varying | variable | -| target_id | integer | 4 bytes | -| title | character varying | variable | -| data | text | variable | -| project_id | integer | 4 bytes | -| created_at | timestamp without time zone | 8 bytes | -| updated_at | timestamp without time zone | 8 bytes | -| action | integer | 4 bytes | -| author_id | integer | 4 bytes | +| Column | Type | Size | +|:--------------|:----------------------------|:---------| +| `id` | integer | 4 bytes | +| `target_type` | character varying | variable | +| `target_id` | integer | 4 bytes | +| `title` | character varying | variable | +| `data` | text | variable | +| `project_id` | integer | 4 bytes | +| `created_at` | timestamp without time zone | 8 bytes | +| `updated_at` | timestamp without time zone | 8 bytes | +| `action` | integer | 4 bytes | +| `author_id` | integer | 4 bytes | After adding padding to align the columns this would translate to columns being divided into fixed size chunks as follows: -| Chunk Size | Columns | -|:-----------|:------------------| -| 8 bytes | id | -| variable | target_type | -| 8 bytes | target_id | -| variable | title | -| variable | data | -| 8 bytes | project_id | -| 8 bytes | created_at | -| 8 bytes | updated_at | -| 8 bytes | action, author_id | +| Chunk Size | Columns | +|:-----------|:----------------------| +| 8 bytes | `id` | +| variable | `target_type` | +| 8 bytes | `target_id` | +| variable | `title` | +| variable | `data` | +| 8 bytes | `project_id` | +| 8 bytes | `created_at` | +| 8 bytes | `updated_at` | +| 8 bytes | `action`, `author_id` | This means that excluding the variable sized data and tuple header, we need at least 8 * 6 = 48 bytes per row. We can optimise this by using the following column order instead: -| Column | Type | Size | -|:------------|:----------------------------|:---------| -| created_at | timestamp without time zone | 8 bytes | -| updated_at | timestamp without time zone | 8 bytes | -| id | integer | 4 bytes | -| target_id | integer | 4 bytes | -| project_id | integer | 4 bytes | -| action | integer | 4 bytes | -| author_id | integer | 4 bytes | -| target_type | character varying | variable | -| title | character varying | variable | -| data | text | variable | +| Column | Type | Size | +|:--------------|:----------------------------|:---------| +| `created_at` | timestamp without time zone | 8 bytes | +| `updated_at` | timestamp without time zone | 8 bytes | +| `id` | integer | 4 bytes | +| `target_id` | integer | 4 bytes | +| `project_id` | integer | 4 bytes | +| `action` | integer | 4 bytes | +| `author_id` | integer | 4 bytes | +| `target_type` | character varying | variable | +| `title` | character varying | variable | +| `data` | text | variable | This would produce the following chunks: -| Chunk Size | Columns | -|:-----------|:-------------------| -| 8 bytes | created_at | -| 8 bytes | updated_at | -| 8 bytes | id, target_id | -| 8 bytes | project_id, action | -| 8 bytes | author_id | -| variable | target_type | -| variable | title | -| variable | data | +| Chunk Size | Columns | +|:-----------|:-----------------------| +| 8 bytes | `created_at` | +| 8 bytes | `updated_at` | +| 8 bytes | `id`, `target_id` | +| 8 bytes | `project_id`, `action` | +| 8 bytes | `author_id` | +| variable | `target_type` | +| variable | `title` | +| variable | `data` | Here we only need 40 bytes per row excluding the variable sized data and 24-byte tuple header. 8 bytes being saved may not sound like much, but for tables as |