在 PostgreSQL 中管理数组数据的架构 数据库博客
PostgreSQL中管理数组数据的架构
关键要点
多种架构：有多种架构可用于在PostgreSQL数据库中保存数组数据，包括以分隔符字符串存储、主从关系表、JSON数据类型和PostgreSQL数组数据类型。性能考量：每种架构的选择将影响应用设计和数据迁移的复杂性，且在性能测试中，各种架构的执行时间存在差异。推荐架构：如果你的业务对象结构固定，推荐使用数组数据类型；如果需要灵活的数据结构，建议使用JSONB数据类型。应用程序常常使用数组数据来表示包含多个值的业务对象。许多客户选择Amazon关系数据库服务Amazon RDS for PostgreSQL或Amazon Aurora PostgreSQL兼容版来存储他们的业务数据数组。
在选择合适的架构时，需要考虑以下几个因素：
你需要在PostgreSQL中创建多少个表？表的定义和数据库约束是什么？选择的架构如何影响你的应用设计？如果业务需要，如何将PostgreSQL表中的记录迁移到目标数据库？在本文中，我们将讨论四种架构及其影响。
架构概述
设计数据库架构以表示一个数组业务对象时，可以考虑以下架构选项：
将数组业务对象保存为分隔字符串创建主从关系表使用PostgreSQL JSON数据类型使用PostgreSQL数组数据类型在本文中，我们将描述支持每个选项所需的表架构，并讨论每个选项可能对应用复杂性和性能的影响。为你提供根据业务需求选择正确架构的指导。
以下示意图显示了来自虚构分销商的用例。该分销商希望根据产品组的历史销售数据创建分析模型。产品组代表在多个地区销售的多种产品，覆盖多个年份。示例ProductCohort1包含在2019、2020和2021年在美国和加拿大销售的三种产品。我们将使用业务对象ProductCohort讨论四种架构。
架构1：分隔字符串
许多应用程序将数组值转换为以逗号分隔的字符串，并使用文本字段存储该分隔字符串。以下SQL创建productcohort表，包含四列：
id 主键products 用于多种产品名称的文本字段regions 用于多种地区的文本字段years 用于多种年份的文本字段sqlCREATE SCHEMA IF NOT EXISTS blogs AUTHORIZATION postgresCREATE TABLE blogsproductcohort ( id int PRIMARY KEY products varchar(100) regions varchar(100) years varchar(30))
以下SQL将示例ProductCohort1数据记录插入productcohort表中：
sqlINSERT INTO blogsproductcohort VALUES (Product1 Product2 Product3 US Canada 2021 2020 2019)
该架构使用简单的productcohort表表示业务对象ProductCohort。如果业务需要，可能需要将productcohort数据库表中的记录复制到数据仓库。在这种情况下，可以基于单个表productcohort满足需求的ETL作业。
使用该表的应用程序需要将多个数组值转换为分隔字符串，反之亦然。应用程序还需要转义数组值中出现的逗号分隔符。
与本文讨论的其他选项相比，该架构可能在搜索和过滤操作时出现扩展性问题。以下SQL查询查找在2020年美国销售的包含产品Product2的productcohort记录：
sqlSELECT id FROM blogsproductcohort WHERE products LIKE Product2 AND regions LIKE US AND years LIKE 2020
根据用户指定的匹配条件的表记录查询通常会导致字符串匹配和全表扫描，这在表大小增长时会导致延迟。
性能测试
我们向表blogsproductcohort插入了100000行，并运行以下选择语句。
查询编号返回行数消耗时间毫秒查询1148查询2255查询3164平均值1356针对查询1的执行计划如下：
Postgres全文搜索 (FTS)找到满足查询的自然语言文档，并可按相关性对查询结果进行排序。你可以通过创建GIN索引加快文档的全文搜索，这样预处理后文档便可以保存索引，为快速搜索提供支持。在创建索引时，预处理步骤将消除像a、an、is、are这样的停用词，使之在搜索时无效。以下代码展示了如何为表创建GIN索引以及如何使用全文搜索查询表。
sqlCREATE INDEX idxproductcohortProducts ON blogsproductcohort USING GIN (totsvector(english Products))CREATE INDEX idxproductcohortRegions ON blogsproductcohort USING GIN (totsvector(english Regions))CREATE INDEX idxproductcohortYears ON blogsproductcohort USING GIN (totsvector(english years))
后续查询及性能
以下是创建GIN索引后的后续查询示例。
sqlSELECT FROM blogsproductcohort3WHERE productsregionsyearsjson @gt{Years [2020]Regions [US]Products [Product1]} 及其他相关查询
在我们的测试中，产品、地区和年份列中的值是通过拼接多个基本数组元素构造的分隔字符串。这些数据字段比常规文本文档简单得多。因此，创建GIN索引以规范化这些数据字段中的单词，在我们的案例中并未提供性能优势。
架构2：主从详细表
传统架构是将地区、年份和产品的数组值存储在详细表中，并将业务对象ProductCohort存储在主表中。主表与详细表之间的关系存储在一组关系表中。以下图展示了七个表的设计，用于表示ProductCohort业务对象。
以下SQL创建product表，使用以下参数：
id 主键text 用于产品名称的文本字段sqlCREATE TABLE IF NOT EXISTS blogsproduct(id int PRIMARY KEY name varchar(60))
我们使用以下SQL语句填充该表：
sqlINSERT INTO blogsproductVALUES (1 Product1) (2 Product2) (3 Product3)
以下SQL创建year表，使用以下参数：
id 主键value 用于年份的整数字段sqlCREATE TABLE IF NOT EXISTS blogsyear(id int PRIMARY KEY value integer)
我们使用以下SQL语句填充该表：
sqlINSERT INTO blogsyearVALUES (1 2021) (2 2020) (3 2019)
以下SQL创建region表，使用以下参数：
id 主键name 用于地区名称的文本字段sqlCREATE TABLE IF NOT EXISTS blogsregion(id int PRIMARY KEY name varchar(60))
我们使用以下SQL语句填充该表：
sqlINSERT INTO blogsregionVALUES (1 US) (2 Canada)
以下SQL创建productcohort2表。id列是主键，用于表示产品组。
sqlCREATE TABLE IF NOT EXISTS blogsproductcohort2(id int PRIMARY KEY)
我们使用以下SQL语句填充该表。你可以扩展此表，以添加更多关于产品组的信息，例如名称、描述等。
sqlINSERT INTO blogsproductcohort2 VALUES (1)
飞鸟加速npv官网cohortproductrelation表描述了productcohort2表和product表中记录之间的关系。cohortproductrelation表中的一条记录包含两个外键值：一个指向productcohort2表中的记录，另一个指向product表中的记录。以下SQL展示了cohortproductrelation表的参数：
productcohortid 指向productcohort2表中主键ID的外键productid 指向product表中主键ID的外键sqlCREATE TABLE blogscohortproductrelation ( productcohortid int NOT NULL productid int NOT NULL CONSTRAINT cohortproductrelationpkey PRIMARY KEY (productcohortid productid))ALTER TABLE blogscohortproductrelation ADD CONSTRAINT cohortfk FOREIGN KEY (productcohortid) REFERENCES blogsproductcohort2(id)ALTER TABLE blogscohortproductrelation ADD CONSTRAINT productfk FOREIGN KEY (productid) REFERENCES blogsproduct(id)
我们使用以下SQL语句填充该表。
sqlINSERT INTO blogscohortproductrelation VALUES (1 1) (1 2) (1 3)
cohortregionrelation表描述了productcohort2表和region表中记录之间的关系。cohortregionrelation表中的一条记录包含两个外键值：分别指向productcohort2表和region表中的记录。以下SQL创建了cohortregionrelation表，使用以下参数：
productcohortid 指向productcohort2表中主键ID的外键regionid 指向region表中主键ID的外键sqlCREATE TABLE blogscohortregionrelation ( productcohortid int NOT NULL regionid int NOT NULL CONSTRAINT cohortregionrelationpk PRIMARY KEY (productcohortid regionid))ALTER TABLE blogscohortregionrelation ADD CONSTRAINT cohortfk FOREIGN KEY (productcohortid) REFERENCES blogsproductcohort2(id)ALTER TABLE blogscohortregionrelation ADD CONSTRAINT regionfk FOREIGN KEY (regionid) REFERENCES blogsregion(id)
我们使用以下SQL语句填充该表。
sqlINSERT INTO blogscohortregionrelationVALUES (1 1) (1 2)
cohortyearrelation表描述了productcohort2表和year表中记录之间的关系。cohortyearrelation表中的一条记录包含两个外键值：指向productcohort2表和year表中的记录的外键。以下SQL创建了cohortyearrelation表，使用以下参数：
productcohortid 指向productcohort2表中主键ID的外键yearid 指向year表中主键ID的外键sqlCREATE TABLE blogscohortyearrelation ( productcohortid int NOT NULL yearid int NOT NULL CONSTRAINT cohortyearrelationpk PRIMARY KEY (productcohortid yearid))ALTER TABLE blogscohortyearrelation ADD CONSTRAINT cohortfkFOREIGN KEY (productcohortid) REFERENCES blogsproductcohort2(id)ALTER TABLE blogscohortyearrelation ADD CONSTRAINT yearfkFOREIGN KEY (yearid) REFERENCES blogsyear(id)
我们使用以下SQL语句填充该表。
sqlINSERT INTO blogscohortyearrelationVALUES (1 1) (1 2) (1 3)
主从详细架构通过七个规范化的表表示业务对象ProductCohort。为了测试查询性能，我们使用从100000个业务对象生成的数据记录填充这些表，并运行以下查询。
查询编号返回行数消耗时间毫秒查询1284查询2188查询3195平均值1389执行计划
查询3的执行计划如下：
为了测试索引性能，我们创建了六个索引，如下SQL语句：
sqlCREATE INDEX productnameidx ON blogsproduct (name)CREATE INDEX cprproductididx ON blogscohortproductrelation (productid)CREATE INDEX regionnameidx ON blogsregion (name)CREATE INDEX yearvalueidx ON blogsyear (value)CREATE INDEX crrregionididx ON blogscohortregionrelation (regionid)CREATE INDEX cyryearididx ON blogscohortyearrelation (yearid)
经过执行计划的审查后，我们发现只有两个索引productnameidx和cprproductididx被使用。因此，我们删除了其余的未使用索引。
sqlDROP INDEX blogsregionnameidxDROP INDEX blogsyearvalueidxDROP INDEX blogscrrregionididxDROP INDEX blogscyryearididx
再次执行查询
在最终确定索引后，我们再次运行三个查询。
查询编号返回行数消耗时间毫秒查询4152查询5157查询6144平均值151此选项看起来比第一选项有更好的性能，但我们可以通过其他数据类型进一步加速查询，如架构选项3和4所描述。
架构选项3：PostgreSQL JSON数据类型
此架构将业务对象ProductCohort存储为JSON数据记录。PostgreSQL为存储JSON数据提供了两种数据类型：json和jsonb。json类型将输入的JSON字符串作为文本存储，而不需要预处理将字符串保存到数据库列中。当采用jsonb类型时，输入文本会转换为分解的二进制格式。jsonb数据类型之上支持索引，这可能会带来性能优势。
以下SQL创建productcohort3表，包含以下参数：
id 主键productsregionsyearsjson 一个jsonb数据字段，用于保存业务对象ProductCohortsqlCREATE TABLE IF NOT EXISTS blogsproductcohort3( id int PRIMARY KEY productsregionsyearsjson jsonb)
以下SQL将示例业务对象ProductCohort1插入表ProductCohort3：
sqlINSERT INTO blogsproductcohort3VALUES (1{Products [Product1 Product2 Product3]Years [2021 2020 2019]Regions [US Canada]})
该架构将业务对象ProductCohort1表示为JSON有效负载，存储在PostgreSQL数据库中。现代数据库和数据仓库支持JSON数据格式。将productcohort3表复制到数据仓库解决方案和创建单表ETL作业一样简单。
使用productcohort3表的应用程序需要在将对象加载到PostgreSQL表时将业务对象序列化为JSON字符串。当应用程序从PostgreSQL表检索数据记录时，还需要将JSON字符串反序列化为业务对象格式。在现代编程语言中，业务对象与JSON之间的转换相对简单。JSON格式还为应用程序添加额外属性提供了灵活性。
JSON是无模式的，应用程序可能会加载意外有效负载，或将不同版本的有效负载加载到同一个数据库表中。维护数据库表的数据完整性是应用程序的责任。
PostgreSQL提供JSON操作符和函数，用于过滤、搜索和处理JSON数据。
以下SQL查询查找在2020年美国销售的包含产品Product1的productcohort3记录：
sqlSELECT FROM blogsproductcohort3WHERE productsregionsyearsjson @gt{Years [2020]
