PostgreSQL 什么样的设计，什么样的性能 （设计与性能因果报应）

一个应用系统中的数据库部分，DBA 往往充当了一个救火的角色和一个背锅的角色，并且DBA 尝尝被认为是一个运维的角色，在PG 的应用系统设计中，DB 到底在数据库部分充当什么角色，怎么能饰演好这个角色，其中今天说说设计模式这个DBA 应该扮演好的角色。

下面是一个应用系统中的关于账号和电话信息的设计

设计模式 1

CREATE TABLE public.account

( account_id serial primary key,

login text,

first_name text,

last_name text,

frequent_flyer_id integer,

update_ts timestamp without time zone

);

CREATE TABLE public.phone

( phone_id serial primary key,

account_id integer,

phone text,

phone_type text,

primary_phone boolean,

update_ts timestamp without time zone

);

设计模式2

CREATE TABLE public.account_phone

( account_id serial primary key,

login text,

first_name text,

last_name text,

frequent_flyer_id integer,

home_phone text,

work_phone text,

cell_phone text,

primary_phone text,

update_ts timestamp with time zone

);

两个模式有什么不一样，主要还是基于业务和访问的关系所决定，如果按照单表访问的方式来说，实际上比多表的设计，在访问上是要快的，数据页面装载到内存中，只需要一个IO，而如果使用第一个表的设计，必须将表进行联合查询才能得出相关的数据，而这样必须至少有两个页面进入到内存中，所以

如果从访问速度和物力内存消耗的关系上，第一个设计显然不如第二个设计。

但问题怕返回过来想，如果这个数据表中的电话会在后期进行添加，如home phone, cell phone ,前期数据是没有的，但后期添加了相关的数据，则页面有可能进行页分割，产生页面的碎片，导致访问效率逐渐变低，而此时如果仅仅访问账号的信息，这样的情况下，则性能必然不如第一种设计。

那么现在就有矛盾了，到底第一种设计好，还是第二种设计好，那种会提高查询和插入的性能，这其实是一个很难回答的问题，这里列出几个维度

1 查询频率与查询语句的维度

2 数据写入的维度与性能

3 后续扩展性问题

如果考虑这三个问题，则如果是高频插入数据，并且后续扩展性不进行考虑，或需求中扩展性不大的情况下，第二种方式是好的选择，而如果查询的频率高，后续的电话的添加的内容可能性大，数据的写入频率不高的情况下，则第一种设计的模式更好。

SELECT account_id FROM account WHERE home_phone=’8471234567’ OR work_phone=’8471234567’ OR cell_phone=’8471234567’;

SELECT DISTINCT account_id FROM phone WHERE phone=’8471234567’;

上面两个SQL 如果是经常进行的操作，那么上面的表那种设计更好

1 通过电话找人，这样的情况下，第1个表整体的设计更有利于这样的查询，原因在于如果想要命中所有的索引，需要针对三个条件建立三个索引，这样一个表的索引量的问题就凸显了，所以还是讲表的电话和账号数据分开更好，那么第二个语句如果经常进行，第二种设计更好，因为不在需要跨表的操作，才能获得数据。

所以根据业务和查询的频度，数据插入的频度，来构思一个表的设计是表设计的第一关。

除此之外一个表的设计还有你的系统是偏向OLTP还是OLAP 有关，如果是OLTP 系统，灵活性和访问的便捷性等都是考虑的方向，所以还是需要两个表的审核及模式。但如果是OLAP 的系统，提取数据中宽表的设计更得人心，一次I/O 就可以将数据提取。

那么除此以外还有别的设计的模式可以使用吗，当然目前除了在表设计中，存在的，实体--属性--值 ，还有一种将关键值与提取值分别存放的模式。

CREATE TABLE pass_json ( (pass_id INT primary key, passenger_info JSON);

passenger_info:

{"booking_ref" : "4H5B12", "passenger_no": "2", "first_name" : "MTRIAM", "last_name" : "SRREN", "update_ts" : "2021-04-12T19:45:55.022782-05:00", }

将主键进行设置，同时将值以KEY VALUE的方式，存储到JSON 的数据格式中，这样的方式好的地方是数据的输入更加灵活，乘客的信息可能在每个乘客收集的信息都不十分一致，在这样的情况下，二维表格的方式是对数据处理的一种束缚和空间的浪费，所以可以通过这样的方式来对数据进行处理。通过主键的方式进行数据的提取，并在程序的缓冲中对这些数据进行在处理和改写，在重新写入到数据库中，也是一种应对灵活环境和需求的做法。

显然这样的设计可能需要更多的内存，来将JSON 的数据统统装入到数据库的SHARE BUFFER 中。同时为在JSONB 中添加的索引也是内存消耗的一个大户。

这里小结一下

表的设计在PG中，影响了数据的存储的效率，数据提取的效率，以及运维数据库的效率，此时一个应用系统的表设计对于数据库本身来说是很重要的。

总结我们在一个数据库系统中，需要考虑

1 逻辑业务灵活扩展性，在数据库中的体现和支持

2 支持程序在数据正确性，业务逻辑正确性方的支持方式方法

3 程序设计使用数据库的方式方法，与应用后数据库运维的便捷性和可操作性

所以这里在设计一个应用系统的数据库中，要考虑的问题很多，所以大多数的时间内，设计的数据库表的性能问题被放到了最后，因为在此之前我们需要考虑表设计的灵活性，以及数据的有效性，和正确性等问题（数据去重）。

最后一个设计良好的应用系统是离不开表设计的，但很不幸的是大多数系统因为需求的问题，设计的不合理的情况，OLTP OLAP 不明确等问题，给数据库的表设计带来很各种各样的问题，如不断往一个表中增加字段，或者不断建立依附与主表的子表，如果要获取更多的信息则需要多个表进行复杂的JOIN 操作等。