关于数据库:百亿美金的设计深度剖析-GitLab-的-Postgres-数据库-schema

40次阅读

共计 15622 个字符，预计需要花费 40 分钟才能阅读完成。

原文链接

这篇文章写于 2022 年，前一年 GitLab 刚好实现 IPO。目前 GitLab 市值超过 100 亿美金，它的所有支出都来源于同名产品 GitLab，而这篇文章就是全面剖析 GitLab 这个产品的数据库 schema。

我花了一些工夫钻研 GitLab 的 Postgres schema。GitLab 是 Github 的一个替代品。你能够自部署 GitLab，因为它是一个开源的 DevOps 平台。

我之所以要理解 Gitlab 这样的大我的项目的 schema，是为了与我正在设计的 schema 进行比拟，并从他们的 schema 定义中学到一些最佳实际。我的确从中受害良多。

我分明的晓得，最佳实际取决于具体情况，不能自觉利用。

GitLab 的数据库 schema 文件 structure.sql [1] 蕴含超过 34000 行代码。GitLab 实质上是一个集成式的 Ruby on Rails 利用。尽管通常咱们会用 schema.rb 文件来治理数据库的版本迁徙，但 GitLab 团队在他们的问题追踪零碎中的一个探讨 [2] 阐明了他们抉择 structure.sql 的起因。

起因在于 schema.rb 只能蕴含规范的迁徙操作（应用 Rails DSL），这样做是为了使数据库 schema 文件对数据库系统保持中立，抽象化特定的 SQL 操作。这导致咱们无奈利用 PostgreSQL 的一些高级个性，如触发器、分区、物化视图等。

为了充分利用这些高级个性，咱们应该思考应用纯 SQL 格局的 schema 文件 structure.sql，而不是 Ruby/Rails 的规范架构文件 schema.rb。

这意味着咱们须要批改配置 config.active_record.schema_format = :sql，并从新以 SQL 格局生成数据库 schema。这可能还须要调整一些构建流程。
当初，让咱们回顾一下我从 GitLab Postgres schema 中学到的货色。

为表应用正确的主键类型

在我的工作中，我犯了主键类型标准化的谬误。这意味着将 bigint 或 uuid 标准化，这样所有表无论其构造、拜访模式和增长速度如何，都将具备雷同的类型。

当数据库规模较小时，这不会产生任何显著的影响，但当数据库规模扩充时，主键就会对存储空间、写入速度和读取速度产生显著的影响。因而，咱们在为表抉择正确的主键类型时应进行适当的思考。

正如我在之前的一篇文章 [3] 中所探讨的，当你应用 Postgres 本地 UUID v4 类型而不是 bigserial 类型时，表的大小会减少 25%，插入率则会降落到 bigserial 类型的 25%。这是一个很大的差异。我还与 ULID 进行了比拟，但它的性能也很差。其中一个起因可能是 ULID 的实现。

在这种状况下，我很想理解 GitLab 是如何抉择主键类型的。

在 573 个表中，380 个表应用 bigserial 主键类型，170 个表应用 serial4 主键类型，其余 23 个表应用复合主键。他们没有应用 uuid v4 主键或其余相似 ULID 的深奥键类型的表。

1 quintillion = 10 亿 billion

抉择 serial 还是 bigserial 取决于表中记录的数量。

application_settings、badges、chat_teams、notification_settings、project_settings 等表应用串行类型。对于一些表，如 issues、web_hooks、merge_requests 和 projects，我很诧异地发现它们应用了 serial 类型。

这个 serial 类型可能实用于自部署的社区或企业版本，但对于 GitLab.com SaaS 服务，这可能会造成问题。例如，GitHub 在 2020 年领有 1.28 亿个公共仓库 [4]。即便每个仓库有 20 个问题，也会超过序列范畴。此外，更改表格类型的老本也很高。表须要被重写，意味着你须要急躁期待。如果要对表进行分片，这同样会成为一个问题。

我做了一个疾速试验，结果表明，对于我这个有两列和 1000 万条记录的表，将数据类型从 integer 改为 bigint 须要 11 秒。

create table exp_bs(id serial primary key, n bigint not null)

插入 1000 万条记录 

insert into exp_bs(n) select g.n from generate_series(1,10000000) as g(n)

变更数据类型 

alter table exp_bs alter column id TYPE bigint;

ALTER TABLE
Time: 10845.062 ms (00:10.845)

您还必须更改序列以扭转其类型。这种操作很快。

alter sequence exp_bs_id_seq as bigint;

ALTER SEQUENCE
Time: 4.505 ms

所有 bigserial 序列都从 1 开始，直到 bigint 的最大值。

CREATE SEQUENCE audit_events_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

应用外部和内部 ID

通常，不向内部世界公开主键是一个好习惯。这在你应用 integer 或 bigint 型的程序自增标识符时尤为重要，因为它们是可预测的。

因而，我很想晓得在创立 GitLab 问题时会产生什么。是向内部用户公开主键 id，还是应用其余 id？如果公开 issues 表的主键 id，那么在我的项目中创立 issue 时，它就不会以 1 结尾，你能够很容易猜出 GitLab 中存在多少个问题。这既不平安，用户体验也很差。

为了防止将主键裸露给终端用户，常见的解决方案是应用两个 ID。第一个是你的主键 id，它放弃在零碎外部，从不裸露于任何公共环境。第二个 id 是咱们与内部世界共享的。依据我过来的教训，我应用 UUID v4 作为内部 id。正如咱们在前一点中探讨的，应用 UUID 有存储老本。

GitLab 也在须要与内部世界共享 id 的表中应用外部和内部 id。像 issues、ci_pipelines、deployments、epics 以及其余一些表有两个 id – id 和 iid。上面是 issue schema 的一部分。如下所示，iid 的数据类型为 integer。

CREATE TABLE issues (
    id integer NOT NULL,
    title character varying,
    project_id integer,
    iid integer,
    // ……)

正如你所看到的，有 id 和 iid 两列。iid 列的值与最终用户共享。一个 issue 应用 project_id 和 iid 进行惟一标识。这是因为可能有多个 issue 具备雷同的 iid。为了更分明地阐明这一点，如果您创立了两个我的项目，并在每个版本库中创立了一个 issue，那么这两个我的项目的可见 ID 都必须是 1，如下图所示。sg 和 sg2 我的项目都以 issue ID 1 开始。

https://gitlab.com/shekhargulati123/sg/-/issues/1
https://gitlab.com/shekhargulati123/sg2/-/issues/1

它们在 project_id 和 iid 上都有一个惟一索引，以便疾速无效地获取 issue。

CREATE UNIQUE INDEX index_issues_on_project_id_and_iid ON public.issues USING btree (project_id, iid);

应用带有查看束缚的 text 字符类型

Postgres 文档 [5] 中形容了三种字符类型。

我次要应用 character varying(n) 或 varchar(n) 来存储字符串值。GitLab schema 既应用 character varying(n)，也应用 text，但更罕用的是 text 类型。上面是一个这样的示例表。

CREATE TABLE audit_events (
    id bigint NOT NULL,
    author_id integer NOT NULL,
    entity_id integer NOT NULL,
    entity_type character varying NOT NULL,
    details text,
    ip_address inet,
    author_name text,
    entity_path text,
    target_details text,
    created_at timestamp without time zone NOT NULL,
    target_type text,
    target_id bigint,
    CONSTRAINT check_492aaa021d CHECK ((char_length(entity_path) <= 5500)),
    CONSTRAINT check_83ff8406e2 CHECK ((char_length(author_name) <= 255)),
    CONSTRAINT check_97a8c868e7 CHECK ((char_length(target_type) <= 255)),
    CONSTRAINT check_d493ec90b5 CHECK ((char_length(target_details) <= 5500))
)
PARTITION BY RANGE (created_at);

能够看到，除了 entity_type 之外，所有其余列都是 text 类型。他们应用 CHECK 来定义长度束缚。

正如网上多篇文章 [6,7] 所述，这两种类型的性能差异不大。它们底层都采纳了 varlena 类型来存储。

varchar(n) 的问题在于，如果 n 的限度变多，就须要独占锁。这可能会导致性能问题，具体取决于表的大小。

而应用 CHECK 束缚的 text 列则没有这个问题。但在写入过程中会有一点代价。
让咱们做一个简略的试验来证实这一点。咱们先创立一个简略的表 

create table cv_exp (id bigint primary key, s varchar(200) default gen_random_uuid() not null);
create index sidx on cv_exp (s);

插入 1000 万条记录 

insert into cv_exp(id) select g.n from generate_series(1,10000000) as g(n);

如果咱们将 s 的长度从 200 减少到 300，那么它很快执行完。

alter table cv_exp alter column s type varchar(300);

ALTER TABLE
Time: 37.460 ms

然而，如果咱们将 s 的长度从 300 缩小到 100，就会破费相当多的工夫。

alter table cv_exp alter column s type varchar(100);

ALTER TABLE
Time: 35886.638 ms (00:35.887)

让咱们对 text 列做同样的解决。

create table text_exp (id bigint primary key, s text default gen_random_uuid() not null,CONSTRAINT check_15e644d856 CHECK ((char_length(s) <= 200)));

插入 1000 万条记录。

insert into text_exp(id) select g.n from generate_series(1,10000000) as g(n);

在 Postgres 中没有 alter 束缚。您必须删除束缚，而后增加新束缚。

alter table text_exp drop constraint check_15e644d856;

从新增加。

alter table text_exp add constraint check_15e644d856 CHECK ((char_length(s) <= 100));

ALTER TABLE
Time: 1870.250 ms (00:01.870)

正如您所看到的，与具备长度查看的 character varying 或 varchar(n) 相比，具备 CHECK 束缚的 text 类型能够让你轻松改良 schema。

我还留神到，他们在不须要长度查看的中央应用了 character varying，如下图所示。

CREATE TABLE project_custom_attributes (
    id integer NOT NULL,
    created_at timestamp with time zone NOT NULL,
    updated_at timestamp with time zone NOT NULL,
    project_id integer NOT NULL,
    key character varying NOT NULL,
    value character varying NOT NULL
);

命名标准

命名遵循以下规定。

  • 所有表都采纳复数模式，如 issues、projects、audit_events、abuse_reports、approvers 等。

  • 表应用模块名称作为前缀，以提供命名空间。例如，所有与合并申请性能相干的表都以 merge_request 前缀结尾，如下所示：

    • merge_request_assignees
    • merge_request_blocks
    • merge_request_cleanup_schedules
    • merge_request_context_commit_diff_files
    • merge_request_context_commits
    • 等等 …
  • 表和列的命名遵循蛇形命名法（snake_case），即应用下划线将两个或更多单词连接起来，例如 title、created_at、is_active。

  • 表白布尔值的列依据其用处遵循以下三种命名规定之一：

    • 性能开关，如 create_issue、send_email、packages_enabled、merge_requests_rebase_enabled 等。
    • 实体状态，如 deployed、onboarding_complete、archived、hidden 等。
    • 限定词，以 is_xxx 或 has_xxx 结尾，如 is_active、is_sample、has_confluence 等。我认为这些能够通过上述两种形式来表白。
  • 索引的命名遵循 index_#{table_name}_on_#{column_1}_and_#{column_2}_#{condition}，例如 index_services_on_type_and_id_and_template_when_active、index_projects_on_id_service_desk_enabled。

带时区和不带时区的工夫戳

GitLab 同时应用了 带时区的工夫戳 和 不带时区的工夫戳。
依据我的了解，零碎执行动作时采纳的是 不带时区的工夫戳，而波及用户操作时则应用 带时区的工夫戳。例如，以下 SQL 示例中的 created_at 和 updated_at 字段就采纳了不带时区的工夫戳，而 closed_at 字段则应用了带时区的工夫戳。

CREATE TABLE issues (
    id integer NOT NULL,
    title character varying,
    created_at timestamp without time zone,
    updated_at timestamp without time zone,
    closed_at timestamp with time zone,
    closed_by_id integer,
);

另一个例子是 merge_request_metrics 表，其中 latest_closed_at、first_comment_at、first_commit_at 和 last_commit_at 字段应用了带时区的工夫戳，而 latest_build_started_at、latest_build_finished_at 和 merge_at 则采纳了不带时区的工夫戳。你可能会对 merge_at 不应用时区感到纳闷。我认为这是因为零碎能够基于特定的条件或查看来执行合并操作。

CREATE TABLE merge_request_metrics (
    id integer NOT NULL,
    latest_build_started_at timestamp without time zone,
    latest_build_finished_at timestamp without time zone,
    merged_at timestamp without time zone,
    created_at timestamp without time zone NOT NULL,
    updated_at timestamp without time zone NOT NULL,

    latest_closed_at timestamp with time zone,
    first_comment_at timestamp with time zone,
    first_commit_at timestamp with time zone,
    last_commit_at timestamp with time zone,
    first_approved_at timestamp with time zone,
    first_reassigned_at timestamp with time zone
);

外键束缚

外键束缚是两个表之间行的逻辑关联。通常在查问中应用外键来连贯表。

外键束缚是一种数据库机制，用于强制保护外键关系的完整性（参考完整性），确保子表只有在父表中存在对应行时能力进行援用。此束缚还通过多种形式避免不同表中呈现“孤儿行 (orphaned row)”。

过来几年里，我参加的多个我的项目中，团队 / 架构师决定不采纳外键束缚，次要起因是放心性能影响。

外键创立时可能会导致性能降落的一个状况是，当它与 ON DELETE CASCADE 操作一起应用时。这种操作的机制是，如果父表中的一行被删除，子表中所有援用该行的记录也会在同一事务中被删除。原本可能预期删除一行，后果却可能导致删除数百、数千乃至更多的子表行。但这种状况只有在一个父行与大量子表行关联时才会呈现问题。

团队不采纳外键束缚的另外两个起因是：

  • 在线 DDL schema 迁徙操作中，特地是在 MySQL 中，外键束缚的兼容性不好。
  • 一旦将数据分片至多个数据库服务器，保护外键束缚变得艰难。

相似 PlanetScale（基于开源 Vitess 数据库）的兼容 MySQL 的 serverless 数据库不反对外键。

因而，我很想晓得 GitLab 是否应用外键束缚。

除了 audit_events、abuse_reports、web_hooks_logs 和 spam_logs 等少数几个表之外，GitLab 在大多数表中都应用了外键束缚。我认为不在这些表中应用外键束缚有两个次要起因。这两个起因是：

  • 这些表在实质上是不可变的。一旦条目被写入，你就不想再更改它们了
  • 这些表的行数可达数百万（或更多），因而即便是很小的性能损失也会造成很大影响

GitLab 应用外键的其余表都应用了 ON DELETE CASCADE、ON DELETE RESTRICT 和 ON DELETE SET NULL 操作。上面别离举例说明。

ALTER TABLE ONLY todos
    ADD CONSTRAINT fk_rails_a27c483435 FOREIGN KEY (group_id) REFERENCES namespaces(id) ON DELETE CASCADE;
ALTER TABLE ONLY projects
    ADD CONSTRAINT fk_projects_namespace_id FOREIGN KEY (namespace_id) REFERENCES namespaces(id) ON DELETE RESTRICT;
ALTER TABLE ONLY authentication_events
    ADD CONSTRAINT fk_rails_b204656a54 FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL;
  • ON DELETE SET NULL 会将匹配记录的子表援用列设置为空。这会导致记录成为孤儿，但因为 NULL 的存在，你能够很容易地辨认它们。在此操作中，删除一条记录也会导致子表中更新多条记录。这可能会导致大型事务、适度锁定和复制滞后。
  • ON DELETE RESTRICT 能够避免删除援用的子表记录。这不会导致子表成为孤儿，因为如果有子表援用父表记录，就无奈删除父表记录。你会失去如下所示的异样。
ERROR:  update or delete on table "a" violates foreign key constraint "fk_a_id" on table "b"
DETAIL:  Key (id)=(1) is still referenced from table "b".

大表分区

GitLab 通过分区技术来解决可能增长至宏大规模的表格，目标是为了晋升查问性能。

  • 按范畴分区（PARTITION BY RANGE）：这种分区办法依据选定的范畴来分区表格数据。这种策略通常实用于须要对工夫序列数据进行分区的场景。例如，audit_events（审计事件）和 web_hook_logs（Web 钩子日志）表就采纳了这种分区策略。
  • 按列表分区（PARTITION BY LIST）：这种分区办法依据某一列的离散值来对表格数据进行分区。loose_foreign_keys_deleted_records（涣散外键删除记录）表就是采纳这种分区策略的例子。
  • 按哈希分区（PARTITION BY HASH）：这种分区办法通过为每个分区指定一个模数和余数来进行。每个分区将蕴含那些其分区键的哈希值除以指定模数后失去指定余数的行。product_analytics_events_experimental（产品剖析事件试验）表采纳了这种分区策略。
    无关 Postgres 分区的更多信息，请参阅 Postgres 文档。

应用 Trigrams 和 gin_trgm_ops 反对 LIKE 搜寻用例

GitLab 应用 GIN（通用反向索引）索引来执行高效搜寻。

GIN 索引类型是为解决可细分的数据类型而设计的，您心愿搜寻单个组件值（数组元素、文本文档中的词条等）”。- 汤姆 - 莱恩

因为 LIKE 操作反对任意通配符表达式，因而从根本上说很难编制索引。其中一个例子是问题表，您可能想对题目和形容字段进行搜寻。因而，咱们应用 pg_trgm 扩大来创立一个可用于 Trigrams 的索引。

CREATE INDEX index_issues_on_title_trigram ON issues USING gin (title gin_trgm_ops);
CREATE INDEX index_issues_on_description_trigram ON issues USING gin (description gin_trgm_ops);

GIN 索引使搜寻变得高效。让咱们来看看实际操作。
咱们将创立一个简略的表，如下所示。

create table words(id serial primary key, word text not null);

让咱们插入一些数据。我从这个链接中提取了 CSV 格局的英语单词列表。

\copy words(word) from '/Users/xxx/Aword.csv' CSV;
select count(*) from words;

count
-------
11616
(1 row)

咱们将在 word 列上创立一个 btree 索引，稍后咱们将应用 gin 索引来显示其效率。

create index id1 on words using btree (word);

让咱们运行解释打算查问。

EXPLAIN select * from words where word like '%bul%';

QUERY PLAN
-----------------------------------------------------------
Seq Scan on words  (cost=0.00..211.20 rows=1 width=14)
Filter: (word ~~ '%bul%'::text)
(2 rows)

当初，让咱们删除 btree 索引。

drop index id1;

装置 pg_trm 扩大。

CREATE EXTENSION pg_trgm;

创立索引。

create index index_words_on_word_trigram ON words USING gin (word gin_trgm_ops);

当初，运行 Explain。

EXPLAIN select count(*) from words where word like '%bul%';

QUERY PLAN

----------------------------------------------------------------------------------------------------
  Aggregate  (cost=16.02..16.03 rows=1 width=8)
   ->  Bitmap Heap Scan on words  (cost=12.01..16.02 rows=1 width=0)
         Recheck Cond: (word ~~ '%bul%'::text)
          ->  Bitmap Index Scan on index_words_on_word_trigram  (cost=0.00..12.01 rows=1 width=0)
            Index Cond: (word ~~ '%bul%'::text)
(5 rows)

GitLab 还应用 tsvector 反对残缺的全文搜寻。

在主数据存储中进行文本搜寻的劣势在于：

  • 实时索引。创立索引无提早
  • 拜访残缺数据
  • 升高架构的复杂性

应用 jsonb

正如我在前一篇文章中所探讨的，我在 schema 设计中将 json 数据类型用于以下用例：

  • 转储稍后解决的申请数据
  • 反对附加字段
  • 一对多关系，其中多方都没有本人的标识
  • Key Value
  • 更简略的 EAV (Entity-Attribute-Value) 设计

GitLab schema 设计也在多个表中应用 jsonb 数据类型。他们次要将其用于上述列表中的 1 和 2 用例。与纯文本存储相比，应用 jsonb 的劣势在于 Postgres 反对对 jsonb 数据类型进行高效查问。
例如，表 error_tracking_error_events 以 jsonb 数据类型存储无效负载。这是一个转储申请数据的示例，将在前面的用例中进行解决。

CREATE TABLE error_tracking_error_events (
    id bigint NOT NULL,
    payload jsonb DEFAULT '{}'::jsonb NOT NULL,
    // ...
);

你能够应用 JSON schema 来验证 JSON 文档的构造。

另一个例子是下图中的 operations_strategies 表。您不晓得可能会收到多少个参数，因而须要像 jsonb 这样灵便的数据类型。

CREATE TABLE operations_strategies (
    id bigint NOT NULL,
    feature_flag_id bigint NOT NULL,
    name character varying(255) NOT NULL,
    parameters jsonb DEFAULT '{}'::jsonb NOT NULL);

反对附加字段用例如下所示。

CREATE TABLE packages_debian_file_metadata (created_at timestamp with time zone NOT NULL,    updated_at timestamp with time zone NOT NULL,    package_file_id bigint NOT NULL,    file_type smallint NOT NULL,    component text,    architecture text,fields jsonb,);

他们还应用 jsonb 来存储曾经是 JSON 格局的数据。例如，在表 vulnerability_finding_evidences 中，报告数据曾经是 JSON 格局，因而他们将其保留为 jsonb 数据类型。

CREATE TABLE vulnerability_finding_evidences (
    id bigint NOT NULL,
    created_at timestamp with time zone NOT NULL,
    updated_at timestamp with time zone NOT NULL,
    vulnerability_occurrence_id bigint NOT NULL,
    data jsonb DEFAULT '{}'::jsonb NOT NULL);

其余花絮

  • 审计字段（如 updated_at）只用于可批改记录的表中。例如，issues 就有一个 updated_at 列。对于只可附加的不可变日志表（如 audit_events），则没有 updated_at 列，如下代码片段所示。有 updated_at 列的 issues 表。
CREATE TABLE issues (
    id integer NOT NULL,
    title character varying,
    author_id integer,
    project_id integer,
    created_at timestamp without time zone,
    updated_at timestamp without time zone,
    // removed remaining columns and constraints
);

没有 updated_at 列的 audit_events 表。

CREATE TABLE audit_events (
    id bigint NOT NULL,
    author_id integer NOT NULL,
    entity_id integer NOT NULL,
    created_at timestamp without time zone NOT NULL,
    // removed remaining columns and constraints
);
    • 枚举值存储为 smallint 而非 character varying，以节俭空间。惟一的问题是不能更改枚举值的程序。在上面的示例中，reason 和 severity_level 是枚举值。
CREATE TABLE merge_requests_compliance_violations (
    id bigint NOT NULL,
    violating_user_id bigint NOT NULL,
    merge_request_id bigint NOT NULL,
    reason smallint NOT NULL,
    severity_level smallint DEFAULT 0 NOT NULL
);
  • 在少数几个表（如 issues 和 ci_builds）中应用乐观锁定机制，以避免多方同时编辑所导致的抵触。乐观锁定假如数据抵触的状况极少，如果发生冲突，利用会抛出异样并疏忽更新。如果表中存在 lock_version 字段，则 Active Record 会反对乐观锁定，每次更新都会减少 lock_version 列的值。锁定机制可确保两次实例化的记录在第一次也被更新的状况下，最初保留的记录会引发 StaleObjectError 异样。上面显示的 ci_builds 表应用了 lock_version 列。
CREATE TABLE ci_builds (
    status character varying,
    finished_at timestamp without time zone,
    trace text,

      lock_version integer DEFAULT 0,

      // removed columns
    CONSTRAINT check_1e2fbd1b39 CHECK ((lock_version IS NOT NULL))
);
  • 应用 inet 来存储 IP 地址。我不晓得有 inet 类型。他们在 audit_events 和 authentication_events 表中应用了 inet。
CREATE TABLE audit_events (
    id bigint NOT NULL,
    ip_address inet,
    // ……
);

GitLab 并未在所有存储 ip_address 的表中应用 inet。例如，在 ci_runners 和 user_agent_details 表中，他们将其存储为 character varying。我不分明为什么他们没有在所有存储 ip 地址的表中应用雷同的类型。

与将 ip 地址存储为纯文本类型相比，您应该抉择 inet 类型，因为这些类型提供了输出错误处理和专门性能。

让咱们疾速理解一下它的操作。首先，咱们将创立一个蕴含两个字段（id 和 ip_addr）的表。

create table e (id serial primary key, ip_addr inet not null);

咱们能够插入一条无效记录，如下所示。

insert into e(ip_addr) values ('192.168.1.255');

咱们还能够插入带掩码的记录，如下所示。

insert into e(ip_addr) values ('192.168.1.5/24');

这两条记录都将被插入。

select id, abbrev(ip_addr) from e;

id |     abbrev
----+----------------
1 | 192.168.1.255
2 | 8.8.8.8
3 | 192.168.1.5/24
(3 rows)

如果咱们试图保留有效数据，则插入将失败。

insert into e(ip_addr) values ('192.168.1');

ERROR:  invalid input syntax for type inet: "192.168.1"
LINE 1: insert into e(ip_addr) values ('192.168.1');

你能够应用 Postgres 反对的 inet 操作符查看子网是否蕴含 IP 地址，如下所示。

select * from e where ip_addr << inet '192.168.1.1/24';

id |    ip_addr
----+---------------
1 | 192.168.1.255
(1 row)

如果要查看子网是否蕴含或相等，咱们能够执行以下操作。

select * from e where ip_addr <<= inet '192.168.1.1/24';
id |    ip_addr
----+----------------
1 | 192.168.1.255
3 | 192.168.1.5/24
(2 rows)

Postgres 还反对许多其余操作符和函数。你能够在 Postgres 文档中浏览它们。

  • Postgres 的 bytea 数据类型用于存储 SHA、加密令牌、加密密钥、加密明码、指纹等。
  • Postgres 的数组类型用于存储具备多个值的列。

数组应在齐全确定不须要在数组中的我的项目与其余表之间创立任何关系的状况下应用。它应该用于严密耦合的一对多关系。

例如，在下图所示的表中，咱们将 *_ids 存储为数组，而不是以立体形式存储并定义与其余表的关系。咱们不晓得有多少用户和我的项目会被提及，因而创立诸如 mentioned_user_id1 , mentioned_user_id2, mentioned_user_id3 等列会造成节约。

CREATE TABLE alert_management_alert_user_mentions (id bigint NOT NULL,    alert_management_alert_id bigint NOT NULL,    note_id bigint,    mentioned_users_ids integer[],    mentioned_projects_ids integer[],    mentioned_groups_ids integer[]);

Postgres 数组的另一个常见用例是存储 hosts、tags 和 urls 等字段。

CREATE TABLE dast_site_profiles (
    id bigint NOT NULL,
    excluded_urls text[] DEFAULT '{}'::text[] NOT NULL,);

CREATE TABLE alert_management_alerts (
    id bigint NOT NULL,
    hosts text[] DEFAULT '{}'::text[] NOT NULL,);

CREATE TABLE ci_pending_builds (
    id bigint NOT NULL,
    tag_ids integer[] DEFAULT '{}'::integer[],);

总结

通过 GitLab 的数据库 schema 设计，我学到了很多。他们并不是自觉地将雷同的实际利用于所有表设计。每个表都依据其目标、存储的数据类型以及增长速度做出最佳决策。

如果你须要像 GitLab 一样束缚表结构设计的话，能够应用 Bytebase 的 SQL 审核性能，针对包含 Postgres 的各种数据库，它提供了上百条 SQL 审核规定。

参考资料

  • Gitlab schema structure.sql – https://gitlab.com/gitlab-org/gitlab/-/blob/master/db/structu…
  • Issue 29465: Use structure.sql instead of schema.rb – https://gitlab.com/gitlab-org/gitlab/-/issues/29465
  • Choosing Primary Key Type in Postgres – https://shekhargulati.com/2022/06/23/choosing-a-primary-key-t…
  • Github’s Path to 128M public repositories – https://towardsdatascience.com/githubs-path-to-128m-public-re…
  • Postgres Character Types Documentation – https://www.postgresql.org/docs/current/datatype-character.html
  • Difference between text and varchar (character varying) – https://stackoverflow.com/questions/4848964/difference-between-text-and-varchar-character-varying
  • CHAR(x) vs. VARCHAR(x) vs. VARCHAR vs. TEXT – https://www.depesz.com/2010/03/02/charx-vs-varcharx-vs-varcha…

💡 更多资讯，请关注 Bytebase 公号：Bytebase

正文完
数据库
发表至： 数据库
2024-02-23
 0
关于数据库:博睿数据入选中国高科技高成长企业系列榜单
关于数据库:业务数据迁移上云的一些技术思考
关于数据库:一周活动速递｜深入浅出第8期Meetup成都站报名进行中
关于数据库:20000节点云数仓在大型商业银行的实践之路