关于postgresql存储结构的信息

pg数据库数据文件111代表什么

仅供参考

所有数据库对象都有各自的oid(object identifiers),oid是一个无符号的四字节整数，相关对象的oid都存放在相关的system catalog表中，比如数据库的oid和表的oid分别存放在pg_database,pg_class表中。

1、数据库集群-Database cluster

2、数据库-Database

3、表空间-tablespace

数据库在逻辑上分成多个存储单元，称作表空间。表空间用作把逻辑上相关的结构放在一起。数据库逻辑上是由一个或多个表空间组成。

新创建的数据库默认创建下面的表空间：

1)Catalog表空间 存放系统表信息2)System表空间 存放用户数据3)Temp表空间

4、模式-Schema

自动创建的系统模式如下：

1)PG_CATALOG2)PG_LARGEOBJECT3)PG_TOAST4)PG_PARTITION

默认的用户模式PUBLIC。

5、段-segment

6、区-extent

7、块-block

8、数据库对象-Database object

1)模式对象表、索引、序列、大对象、视图、函数、存储过程、触发器、包 … …2)非模式对象用户、数据库

9、数据表-Table

10、索引-Index

11、序列-Sequence

12、视图-View

二、物理存储结构

在执行initdb的时候会初始化一个目录，通常我们都会在系统配置相关的环境变量$PGDATA来表示，初始化完成后，会再这个目录生成相关的子目录以及一些文件。在postgresql中，tablespace的概念并不同于其他关系型数据库，这里一个tablespace对应的都是一个目录。如下图就是PG的物理结构：

e3b8db31b00c2f05d483b06281f4612b.png

1、存储系统主要包括三个部分：

内存中：buffer，MemoryContext；

数据文件，临时文件；

日志文件，日志缓存。

2、文件和目录相关作用描述：

b0bf0a554cc377fae5f8de898df5e43d.png

61b64f793a40b1d2a91e97af7f7c064f.png

3、数据文件结构

3c57d6e6090ee00e4ee3dd1e4ecb3550.png

3.1、页

aefc00e308669659fe45719ea93668ea.png

将数据文件中的空间从逻辑上划分成一个个页面(数据块)。页面是数据库I/O的基本单位，即只能整页读写数据文件, 页面的大小默认是8K。

页面可以分成两种：

1)数据页面：数据页面是用来存储用户数据的。

2)控制页面：控制页面用来管理这些数据页面。

数据库共享缓存中的空间划分也是按页为基本单位, 一个页的大小与数据文件中页的大小一致, 这样便于整页读取数据文件,并放入到数据库Buffer中, 从Buffer写入数据文件也同理，保证了缓存与数据文件结构和内容上的一致性。

3.2、Block(块)

概念上基本等同于Page, 但Block更多用于说明DMS中对数据文件中Page的描述。

例如: 对文件的读写的操作, 文件读写位置的定位, 数据文件空间回收等操作, 单位均是以块进行。

数据块的大小在系统初始化时指定，默认是8K，可以取值4K,8K,16K,32K。

3.3、Extent(区)

把数据文件中8个连续的Page构成的空间称为一个Extent。Extent是数据库进行数据文件空间分配/释放的基本单位。每个表、索引、序列对象都是由若干个区组成。数据文件被创建后，除自动保留部分区作为控制区外，其他区全部处于未分配状态。表、索引、序列对象的所有数据都存放在Extent中，当向这些Extent中插入数据时，若该Extent的所有页面都已占满，系统就会自动在所属表空间的数据文件中寻找一个尚未分配的区，并将其状态修改为数据区。

3.4、控制页面

用于空间管理的控制页面：PFS/GAM/IAM。

用于增量备份的控制页面：DCM。

判断可见性的控制页面：VM。

预留的控制页面：BCM/SGAM。

3.5、PFS

Page Free Space,简称PFS页.

用于记录本数据文件中页面的空间使用情况。对文件中的每个页面，PFS中都有一个“字节”与之对应，该字节记录了该页面的状态。

PFS页前64bytes被预留为页头, 剩下81024-64=8128一共覆盖81288K=64MB空间.

故PFS页每隔8128个页面出现一次, 系统初始化把第一个PFS页放在数据文件的第二个页面位置,即:第1号数据页面, 由此可知,第N个PFS页的位置在8128*N+1.

ed48dc29b411c3c5ef7bc7fe817ef757.png

3.6、GAM

Global Allocation Map，简称GAM页。

功能:记录所在数据文件的Extent的分配情况，GAM页中除GAM头外,剩下空间的每一位(bit)均对应一个Extent的分配情况。若某bit位为1，则表明该bit位所关联的Extent已被分配出去,反之未被分配。

若一个GAM页面大小为8K,则除GAM头(64 bytes)外,一个GAM页面所能覆盖的文件范围是: (81024-64)8(88K),约4GB空间。此外，GAM页每隔881288个页面出现一个，系统要求第一个GAM页出现在文件的第3个页面位置(即:第2个索引位置),由此得知,第N个GAM页的出现位置是: 881288*N+2

8787a745b70fe3df6b2e0df0671574b1.png

3.7、IAM

Index Allocation Map，简称IAM页。

功能：每个IAM页只隶属于一个数据库对象(例如:表),但一个数据库对象可包含多个IAM页,由此可见IAM页与数据库对象的关系是1对1，而数据库对象与IAM页的关系是1对多.

IAM的结构与GAM页类似,除IAM头外,剩下空间的每一位(bit)均对应着一个与IAM相关的Extent。若某bit位为1，则表明该bit位所关联的Extent已被分配给该IAM,反之未被分配。若一个IAM页面大小为8K,则除IAM头(64 bytes)外,一个IAM页面所能覆盖的文件范围是: (81024-64)8(88K),约4GB空间。

但与GAM也不同之处在于:IAM的出现位置不固定,只在在创建数据库对象的时候才分配。

6bc56896143910b2e3b060025dd891eb.png

三、逻辑与物理存储关系

1、逻辑关系存在表空间；

2、表空间存在对应的数据文件中；

新创建的数据库对应的数据文件的名称:

Catalog表空间 – databasename.dbfSystem表空间 – Udatabasename.dbfTemp表空间-- Tdatabasename.dbf

前面加 “U” 前缀代表用户数据表空间，用于保存用户表的数据。

不带 U 代表 是系统表的表空间，用于保存系统表的数据。

U 前缀的数据文件代表的表空间名为PG。

不带U 的数据文件代表的表空间为 CATALOG。

四、数据库文件、表空间、其他文件之间的关系

1、关系图如下：

1e59db43b34dcdd4d6c5cb6b89b2c194.png

说明：

1)每一个数据库具有一个或多个数据文件，用户存放数据库的所有数据。

2)数据库的数据文件有以下特征：

一个数据库文件只能与一个数据库的一个表空间相连。

一个表空间可以由多个数据文件组成。

3)数据库对象与文件关系：

数据库对象放到表空间中。

表空间有多个数据文件。

表空间中有多个数据库对象。

4)数据库对象逻辑上是存储在表空间中，物理上是存储在与表空间相关联的数据文件中。

2、数据库包含的文件种类：

1)数据库文件：data/DB

数据库对象，如：数据库、表，索引，序列等对象。

2)控制文件：data/CTL

用来记录数据库集群的状态信息，如：版本信息、集群所管理的各种文件信息、检查点信息、事务状态信息等。

3)日志文件：data/REDOLOG

记录数据修改操作的日志，用于系统发生故障时进行数据恢复。

4)临时文件：data/DB

存放数据库进行计算的过程中，生成的各种中间对象，如排序运算的外存归并单元。

5)参数文件：data目录下

五、Postgresql 底层存储管理方式：

Postgresql的每个数据库均存放在一个目录中，以db_oid命名，该目录中存放每个表对应的文件，文件名以该数据表对应的relfilenode_oid命名。当表中的数据量足够大，导致表文件的大小大于1GB的时候，postgresql会自动创建新的文件用于存放新插入的数据。新文件的名称为： relfilenode_iod.1, relfilenode_iod.2 等。使用该策略是为了防止在某些文件系统中，最大支持文件尺寸不能大于1GB的情形。

db_oid, relfilenode_oid可以从pg_class系统表中查询得出。

相关问答

Q1: pg查询处理流程

并行查询使用多个后台进程，但后端进程基本上处理连接的客户端发出的所有查询。改后端有五个子系统组成。

解析器生成一个解析树，后续子系统可以从纯文本的 SQL 语句中读取该解析树。

如下面的查询：

解析树是其根节点是定义在 parsenodes.h中的 [SelectStmt](javascript:void(0))结构的树。

SELECT 查询的元素和解析树的相应元素编号相同。例如，(1) 是第一个目标列表的一个项目，它是表的“id”列，(4) 是 WHERE 子句，依此类推。

由于解析器在生成解析树时只检查输入的语法，因此只有在查询中出现语法错误时才会返回错误。

解析器不检查输入查询的语义。例如，即使查询包含不存在的表名，解析器也不会返回错误。语义检查由分析器/分析器完成。

分析器运行由解析器生成的解析树的语义分析并生成查询树。

查询树的根是定义在 parsenodes.h中的 [查询](javascript:void(0))结构；此结构包含其相应查询的元数据，例如此命令的类型（SELECT、INSERT 或其他）和几个叶子；每个叶子形成一个列表或树，并保存各个特定子句的数据。

述查询树简述如下。

重写器是实现 规则系统 的系统，必要时根据存储在 pg_rules系统目录中的规则变换查询树。

PostgreSQL 中的视图 是使用规则系统实现的。当视图由 CREATE VIEW 命令定义时，相应的规则会自动生成并存储在目录中。

假设已经定义了以下视图，并且对应的规则存储在 pg_rules 系统目录中。

当发出包含如下所示视图的查询时，解析器将创建解析树，如图所示。

在这个阶段，重写器将范围表节点处理为子查询的解析树，即对应的视图，存储在 pg_rules 中。

计划器从重写器接收查询树并生成可以由执行器最有效地处理的（查询）计划树。

PostgreSQL 中的计划器是基于纯成本优化的；它不支持基于规则的优化和提示。这个规划器是 RDBMS 中最复杂的子系统

与其他 RDBMS 一样，PostgreSQL 中的 EXPLAIN命令显示计划树本身。 如下所示。

他对应的计划树：

每个计划节点都有执行器需要处理的信息，单表查询的情况下，执行器从计划树的末端到根进行处理。

PostgreSQL 的查询优化是基于成本的。成本是无量纲值，它们不是绝对的绩效指标，而是比较运营相对绩效的指标。成本由 costsize.c 中定义的函数估算。执行器执行的所有操作都有相应的成本函数。例如，顺序扫描和索引扫描的成本分别由 cost_seqscan() 和 cost_index() 估算。

有三种成本，启动成本，执行成本以及总成本。其中总成本 = 启动成本 + 执行成本。

顺序扫描的成本由 cost_seqscan() 函数估算。

其中 seq_page_cost 、 cpu_tuple_cost 和 cpu_operator_cost 在 postgresql.conf 文件中设置，默认值分别为 1.0 、 0.01 和 0.0025 ，Ntuple和Npage分别是该表的所有元组和所有页的编号。

从运行成本估算可以看出，PostgreSQL 假设所有页面都将从存储中读取；也就是说，PostgreSQL 不考虑扫描的页面是否在共享缓冲区中。

虽然 PostgreSQL 支持 一些索引方法 ，例如 BTree、 GiST 、 GIN 和 BRIN ，但索引扫描的成本是使用常见的成本函数估算的：cost_index()。

索引扫描的启动成本是读取索引页以访问目标表中第一个元组的成本，它由以下等式定义：

Hindex是索引树的高度。

索引扫描的运行成本是表和索引的 cpu 成本和 IO（输入/输出）成本之和：

前三个成本定义如下：

其中 cpu_index_tuple_cost 和 random_page_cost 在 postgresql.conf 文件中设置（默认分别为 0.005 和 4.0）； qual_op_cost粗略来说就是指数的评估成本，值为0.0025。选择性选择性是指定WHERE子句对索引的搜索范围的比例；它是一个从 0 到 1 的浮点数

查询谓词的选择率是通过直方图界值与高频值估计的，这些信息都储存在系统目录pg_staticstics中，并可通过pg_stats视图查询。

表中的每一列的高频值都在pg_stats视图的most_common_vals和most_common_freqs中成对存储。

排序路径会在排序操作中被使用。排序操作包括order by、归并连接的预处理操作，以及其他函数。函数cost_sort()用于估计排序操作的代价。如果能在工作内存中放下所有元组，那么排序操作会选用快速排序算法。否则就会创建临时文件，使用文件归并排序算法。

排序路径的启动代价就是对目标表的排序代价，因此代价就是O(Nsort) * Log 2 (Nsort)，这里Nsort就是带排序的元组数。排序路径的运行代价就是读取已经排序好的元组的代价，因此代价就是O（Nsort）。

PostgreSQL中的计划器会执行三个步骤：

访问路径是估算代价时的处理单元。比如顺序扫描、索引扫描、排序，以及各种连接操作都有其对应的路径。访问路径只在计划器创建查询计划树的时候使用。最忌本的访问路径数据结构就是relation.h中定义的path结构体，相当于顺序扫描。所有其他的路径访问都基于该结构。

在创建计划树之前，计划器将线对PlannerInfo中的查询书进行一些预处理。预处理有很多步骤，本节值讨论和单表查询处理相关的主要步骤。

计划器对所有可能的访问路径进行代价估计，然后选择代价最小的那个。

在最后一步中，计划器按照代价最小的路径生成一颗计划树。

计划树的根节点是定义在plannodes.h中的Plannedstmt结构，包含19个字段，其中有4个代表性字段：

计划树包含各式各样的计划节点。PlanNode是所有计划节点的基类，其他计划节点都会包含PlanNode结构。比如顺序扫描节点SeqScanNode包含一个PlanNode和一个整型变量scanrelid。PlanNode包含14个字段，下面是7个代表性字段:

在单表查询的例子中，执行器从计划树中取出计划节点，按照自底向上的顺序进行处理，并调用节点相应的处理函数。

每个计划节点都有相应的函数，用于执行节点对应的操作。这些函数在src/backend/executor目录中。

理解执行器如何工作的最好方式，就是阅读explain命令的输出。

我们可以自底向上阅读explain的结果，来看一看执行器是如何工作的。

第六行：首先，执行器通过nodeSeqscan.c中定义的函数执行顺序扫描操作。

第四行：然后，执行器通过nodeSort.c中定义的函数，对顺序扫描的结果进行排序。

执行器在处理查询时会使用工作内存和临时缓冲区，两者都在内存中分配。如果查询无法在内存中完成，就会用到临时文件。

使用带有Analyze选项的explain，待解释的命令会真正执行，并显示实际结果行数、实际执行时间和实际内存使用量。

在第6行，explain命令显示执行器使用了10000KB的临时文件。临时文件会被临时创建在base/pg_tmp子目录中，并遵循如下命令规则：｛“pgsql_tmp”｝+ ｛创建本文件的postgres进程pid｝.{从0开始的序列号}

比如，临时文件pgsql_tmp8903.5是pid为8903的postgres进程创建的第6个临时文件。

PostgreSQL中支持三种连接操作，分别是嵌套循环连接，归并连接和散列连接。在pg中，嵌套循环连接和归并连接有几种变体。

这三种连接方式都支持pg中所有的连接操作，注入inner join、 left/right outer join、 full outer join等。

循环嵌套连接不需要任何启动代价，因此：start-up cost = 0

运行代价和内外表尺寸的乘积成比例，即run cost是O(Nouter * Ninner)， Nouter和Ninner分别是外表和内表的元组条数。run cost的定义如下：

Couter和Cinner分别是内表和外表顺序扫描的代价。

循环嵌套连接的代价总会被估计，但实际中很少会使用这种连接操作，因为它有几种更高效的变体。

在上面描述的循环嵌套连接中，每当读取一条外表中的元组时，都需要扫描内标中的所有元组。位每条外表元组对内标做全表扫描，这一过程代价高昂，pg支持一种物化嵌套循环连接，可以减少内标全表扫描的代价。

在运行嵌套循环连接之前，执行器会使用临时元组存储模块对内表进行一次扫描，将内表元组加载到工作或临时文件中。在处理内表元组时，临时元组存储比缓冲区管理器更为高效，特别是当所有的元组都能放入工作内存中。

qg内部提供了临时元组存储的模块，可用于各种操作，如五花膘、创建混合散列连接的批次等。该模块包含一系列函数，都在tuplestore.c中。这些函数用于从工作内存或临时文件读写元组。该工作内存还是临时文件取决于待存储元组的总数。

上面显示了执行器要进行的操作，执行器对这些计划节点的处理过程如下：

第7行：执行器使用顺序扫描，物化内部表tbl_b。

第4行：执行器执行嵌套循环连接操作，外表是tbl_a，内表是物化的tbl_b。

如果内表上有索引，且该索引能用于搜索满足连接条件的元组，那么计划器在外外表的每条元组搜索内标中的匹配元组时，会考虑使用索引进行直接搜索，以替代顺序扫描。这种变体叫做索引嵌套循环连接，如下图所示。虽然这种变体叫做“索引嵌套循环连接”，但是谁该算法基本上只需要在外表上循环一次，因此连接操作的执行非常高效。

与嵌套循环连接不同的是，归并连接只能用于自然连接与等值连接。

函数initial_cost_merge_join()和final_cost_merge_join()用于估计归并连接的代价。

归并连接的启动成本是内表与外表排序成本之和，因此其启动成本为：

这里Nouter和Ninner分别是外表和内标的元素条数，而运行代价是O(Nouter + Ninner)。

下图是归并连接的示意图。

如果所有元组都可以存储在内存中，那么排序操作就能在内存中进行，否则就是用临时文件。

第9行：执行器对内表tbl_b进行排序，使用顺序扫描（第11行）。

第6行：执行器对外表tbl_a进行排序，使用顺序扫描(第8行)。

第4行：执行器执行归并连接操作，外表是排序好的tbl_a，内表是排好序的tbl_b。

与嵌套循环连接类似，归并连接还支持物化归并连接，物化内表，使内表扫描更为高效。

下面是物化归并连接的explain结果，很容易发现，与普通归并连接的差异是第9行:Materialize。

与归并连接类似，hash连接只能用于自然连接与等值连接。

PostgreSQL中的散列连接的行为因表的大小而异。如果布标足够小（确切的说，内表大小不超过工作内存的25%），那么hash连接就是简单的两阶段内存hash连接，否则将会使用带倾斜批次的混合hash连接。

内存中的hash连接是在work_mem中处理的，在pg中，散列表区域被称作处理批次。一个批处理批次会有多个散列槽，内部称其为桶，桶的数量由nodeHash.c中定义的ExecChooseHashTableSize()函数所确定。桶的数量是2的整数次幂。

内存散列连接有两个阶段，分别是构建阶段和探测阶段。在构建阶段，内存表中的所有元组都会被插入到处理批次中；在探测阶段每条腕表元组都会与处理批次中的内表元组比较，如果满足连接条件，则将两条元组连接起来。

当内表的元组无法全部存储在工作内表中的单个处理批次时，pg使用带倾斜批次的混合散列连接算法，该算法时混合散列连接诶的一种变体。

在第一个构建和探测阶段postgresql准备多个批次，宇通的数目类似，处理批次的数据由函数ExecChooseHashTableSize()决定，也就是2的整数次幂。工作内存中智慧分配一个处理批次，而其他批次都以临时文件的形式创建。属于这些批次的元组将通过临时元组存储功能被写入到相应的文件中。

为了获取最佳计划树，计划器必须考虑各个索引与各种连接方法之间的所有可能组合。如果表的数量超过某个水平，该过程的代价就会因为组合爆炸而变得非常昂贵，以至于根本不可行。

如果表的数量小于12张，计划器可以使用动态规划来获取最佳计划。

Q2: MySQL与PostgreSQL相比哪个更好

MySQL

MySQL声称自己是最流行的开源数据库。LAMP中的M指的就是MySQL。构建在LAMP上的应用都会使用MySQL，如WordPress、Drupal等大多数php开源程序。MySQL最初是由MySQL AB开发的，然后在2008年以10亿美金的价格卖给了Sun公司，Sun公司又在2010年被Oracle收购。Oracle支持MySQL的多个版本：Standard、Enterprise、Classic、Cluster、Embedded与Community。其中有一些是免费下载的，另外一些则是收费的。其核心代码基于GPL许可，由于MySQL被控制在Oracle，社区担心会对MySQL的开源会有影响，所以开发了一些分支，比如： MariaDB和Percona。

PostgreSQL

PostgreSQL标榜自己是世界上最先进的开源数据库。PostgreSQL的一些粉丝说它能与Oracle相媲美，而且没有那么昂贵的价格和傲慢的客服。最初是1985年在加利福尼亚大学伯克利分校开发的，作为Ingres数据库的后继。PostgreSQL是完全由社区驱动的开源项目。它提供了单个完整功能的版本，而不像MySQL那样提供了多个不同的社区版、商业版与企业版。PostgreSQL基于自由的BSD/MIT许可，组织可以使用、复制、修改和重新分发代码，只需要提供一个版权声明即可。

MySQL与PostgreSQL的对比

MySQL的背后是一个成熟的商业公司，而PostgreSQL的背后是一个庞大的志愿开发组。这使得MySQL的开发过程更为慎重，而PostgreSQL的反应更为迅速。这样的两种背景直接导致了各自固有的优点和缺点。

PostgreSQL相对于MySQL的优势

1)不仅仅是关系型数据库

除了存储正常的数据类型外，还支持存储：

array，不管是一位数组还是多为数组均支持

json(hStore)和jsonb，相比使用text存储接送要高效很多

json和jsonb之间的区别

jsonb和json在更高的层面上看起来几乎是一样的，但在存储实现上是不同的。

json存储完的文本，json列会每次都解析存储的值，它不支持索引，但你可以为查询创建表达式索引。

jsonb存储的二进制格式，避免了重新解析数据结构。它支持索引，这意味着你可以不使用指定的索引就能查询任何路径。

当我们比较写入数据速度时，由于数据存储的方式的原因，jsonb会比json稍微的慢一点。json列会每次都解析存储的值，这意味着键的顺序要和输入的时候一样。但jsonb不同，以二进制格式存储且不保证键的顺序。因此，如果你有软件需要依赖键的顺序，jsonb可能不是你的应用的最佳选择。使用jsonb的优势还在于你可以轻易的整合关系型数据和非关系型数据， PostgreSQL对于mongodb这类的基于文档的数据库是个不小的威胁，毕竟如果一个表中只有一列数据的类型是半结构化的，没有必要为了迁就它而整个表的设计采用schemaless的结构。

2)支持地理信息处理扩展

PostGIS 为PostgreSQL提供了存储空间地理数据的支持，使PostgreSQL成为了一个空间数据库，能够进行空间数据管理、数量测量与几何拓扑分析。在功能上，和MYSQL对比，PostGIS具有下列优势：

O2O业务场景中的LBS业务使用PostgreSQL + PostGIS有无法比拟的优势。

3)可以快速构建REST API

PostgREST 可以方便的为任何 PostgreSQL 数据库提供完全的 RESTful API 服务。

4)支持树状结构

支持R-trees这样可扩展的索引类型，可以更方便地处理一些特殊数据。MySQL 处理树状的设计会很复杂, 而且需要写很多代码, 而 PostgreSQL 可以高效处理树结构。

5)有极其强悍的 SQL 编程能力

支持递归，有非常丰富的统计函数和统计语法支持。

MySQL：支持 CREATE PROCEDURE 和 CREATE FUNCTION 语句。存储过程可以用 SQL 和 C++ 编写。用户定义函数可以用 SQL、C 和 C++ 编写。

PostgreSQL：没有单独的存储过程，都是通过函数实现的。用户定义函数可以用 PL/pgSQL(专用的过程语言)、PL/Tcl、PL/Perl、PL/Python 、SQL 和 C 编写。

6)外部数据源支持

可以把 70 种外部数据源 (包括 Mysql, Oracle, CSV, hadoop …) 当成自己数据库中的表来查询。Postgres有一个针对这一难题的解决方案：一个名为“外部数据封装器(Foreign Data Wrapper，FDW)”的特性。该特性最初由PostgreSQL社区领袖Dave Page四年前根据SQL标准SQL/MED(SQL Management of External Data)开发。FDW提供了一个SQL接口，用于访问远程数据存储中的远程大数据对象，使DBA可以整合来自不相关数据源的数据，将它们存入Postgres数据库中的一个公共模型。这样，DBA就可以访问和操作其它系统管理的数据，就像在本地Postgres表中一样。例如，使用FDW for MongoDB，数据库管理员可以查询来自文档数据库的数据，并使用SQL将它与来自本地Postgres表的数据相关联。借助这种方法，用户可以将数据作为行、列或JSON文档进行查看、排序和分组。他们甚至可以直接从Postgres向源文档数据库写入(插入、更细或删除)数据，就像一个一体的无缝部署。也可以对Hadoop集群或MySQL部署做同样的事。FDW使Postgres可以充当企业的中央联合数据库或“Hub”。

7)没有字符串长度限制

一般关系型数据库的字符串有限定长度8k左右，无限长 TEXT 类型的功能受限，只能作为外部大数据访问。而PostgreSQL的 TEXT 类型可以直接访问，SQL语法内置正则表达式，可以索引，还可以全文检索，或使用xml xpath。MySQL 的各种text字段有不同的限制，要手动区分 small text, middle text, large text… PostgreSQL 没有这个限制，text 能支持各种大小。

8)支持图结构数据存储

没有具体使用过，具体可以自己搜索下。参考链接：

9)支持窗口函数

窗口函数提供跨行相关的当前查询行集执行计算的能力。仅当调用跟着OVER子句的聚集函数，作为窗口函数;否则它们作为常规的聚合函数。窗口也是一种分组，但和 group by 的分组不同。窗口，可以提供分组之外，还可以执行对每个窗口进行计算。可以相像成是group by 后，然后对每个分组进行计算，而不像Group by ，只是单纯地分组。MySQL 不支持 OVER 子句, 而PostgreSQL支持。OVER 子句能简单的解决 “每组取 top 5” 的这类问题。MySQL支持的SQL语法(ANSI SQL标准)的很小一部分。不支持递归查询、通用表表达式(Oracle的with 语句)或者窗口函数(分析函数)。

10)对索引的支持更强

PostgreSQL 的可以使用函数和条件索引，这使得PostgreSQL数据库的调优非常灵活，mysql就没有这个功能，条件索引在web应用中很重要。对于索引类型：

MySQL：取决于存储引擎。MyISAM：BTREE，InnoDB：BTREE。

PostgreSQL：支持 B-树、哈希、R-树和 Gist 索引。

InnoDB的表和索引都是按相同的方式存储。也就是说表都是索引组织表。这一般要求主键不能太长而且插入时的主键最好是按顺序递增，否则对性能有很大影响。PostgreSQL不存在这个问题。

索引类型方面，MySQL取决于存储引擎。MyISAM：BTREE，InnoDB：BTREE。PostgreSQL支持 B-树、哈希、R-树和 Gist 索引。

11)集群支持更好

Mysql Cluster可能与你的想象有较大差异。开源的cluster软件较少。复制(Replication)功能是异步的并且有很大的局限性。例如，它是单线程的(single-threaded)，因此一个处理能力更强的Slave的恢复速度也很难跟上处理能力相对较慢的Master。

PostgreSQL有丰富的开源cluster软件支持。plproxy 可以支持语句级的镜像或分片，slony 可以进行字段级的同步设置，standby 可以构建WAL文件级或流式的读写分离集群，同步频率和集群策略调整方便，操作非常简单。

另外，PostgreSQL的主备复制属于物理复制，相对于MySQL基于binlog的逻辑复制，数据的一致性更加可靠，复制性能更高，对主机性能的影响也更小。对于WEB应用来说，复制的特性很重要，mysql到现在也是异步复制，pgsql可以做到同步，异步，半同步复制。还有mysql的同步是基于binlog复制，类似oracle golden gate,是基于stream的复制，做到同步很困难，这种方式更加适合异地复制，pgsql的复制基于wal，可以做到同步复制。同时，pgsql还提供stream复制。

12)事务隔离做的更好

MySQL 的事务隔离级别 repeatable read 并不能阻止常见的并发更新, 得加锁才可以, 但悲观锁会影响性能, 手动实现乐观锁又复杂. 而 PostgreSQL 的列里有隐藏的乐观锁 version 字段, 默认的 repeatable read 级别就能保证并发更新的正确性, 并且又有乐观锁的性能。

13)对于字符支持更好一些

MySQL 里需要 utf8mb4 才能显示 emoji 的坑, PostgreSQL 没这个坑。

14)对表连接支持较完整

对表连接支持较完整，MySQL只有一种表连接类型:嵌套循环连接(nested-loop),不支持排序-合并连接(sort-merge join)与散列连接(hash join)。PostgreSQL都支持。

15)存储方式支持更大的数据量

PostgreSQL主表采用堆表存放，MySQL采用索引组织表，能够支持比MySQL更大的数据量。

16)时间精度更高

MySQL对于时间、日期、间隔等时间类型没有秒以下级别的存储类型，而PostgreSQL可以精确到秒以下。

17)优化器的功能较完整

MySQL对复杂查询的处理较弱，查询优化器不够成熟，explain看执行计划的结果简单。性能优化工具与度量信息不足。

PostgreSQL很强大的查询优化器，支持很复杂的查询处理。explain返回丰富的信息。提供了一些性能视图，可以方便的看到发生在一个表和索引上的select、delete、update、insert统计信息，也可以看到cache命中率。网上有一个开源的pgstatspack工具。

18)序列支持更好

MySQL 不支持多个表从同一个序列中取 id, 而 PostgreSQL 可以。

19)对子查询支持更好

对子查询的支持。虽然在很多情况下在SQL语句中使用子查询效率低下，而且绝大多数情况下可以使用带条件的多表连接来替代子查询，但是子查询的存在在很多时候仍然不可避免。而且使用子查询的SQL语句与使用带条件的多表连接相比具有更高的程序可读性。几乎任何数据库的子查询 (subquery) 性能都比 MySQL 好。

20)增加列更加简单

MySQL表增加列，基本上是重建表和索引，会花很长时间。PostgreSQL表增加列，只是在数据字典中增加表定义，不会重建表.

MySQL相对于PostgreSQL的优势

1)MySQL比PostgreSQL更流行

流行对于一个商业软件来说，也是一个很重要的指标，流行意味着更多的用户，意味着经受了更多的考验，意味着更好的商业支持、意味着更多、更完善的文档资料。易用，很容易安装。第三方工具，包括可视化工具，让用户能够很容易入门。

2)回滚实现更优

innodb的基于回滚段实现的MVCC机制，相对PG新老数据一起存放的基于XID的MVCC机制，是占优的。新老数据一起存放，需要定时触发VACUUM，会带来多余的IO和数据库对象加锁开销，引起数据库整体的并发能力下降。而且VACUUM清理不及时，还可能会引发数据膨胀。

3)在Windows上运行更可靠

与PostgreSQL相比，MySQL更适宜在Windows环境下运行。MySQL作为一个本地的Windows应用程序运行(在 NT/Win2000/WinXP下，是一个服务)，而PostgreSQL是运行在Cygwin模拟环境下。PostgreSQL在Windows下运行没有MySQL稳定，应该是可以想象的。

4)线程模式相比进程模式的优势

MySQL使用了线程，而PostgreSQL使用的是进程。在不同线程之间的环境转换和访问公用的存储区域显然要比在不同的进程之间要快得多。

进程模式对多CPU利用率比较高。进程模式共享数据需要用到共享内存，而线程模式数据本身就是在进程空间内都是共享的，不同线程访问只需要控制好线程之间的同步。

线程模式对资源消耗比较少。所以MySQL能支持远比PostgreSQL多的更多的连接。但PostgreSQL中有优秀的连接池软件软件，如pgbouncer和pgpool，所以通过连接池也可以支持很多的连接。

5)权限设置上更加完善

MySQL在权限系统上比PostgreSQL某些方面更为完善。PostgreSQL只支持对于每一个用户在一个数据库上或一个数据表上的 INSERT、SELECT和UPDATE/DELETE的授权，而MySQL允许你定义一整套的不同的数据级、表级和列级的权限。对于列级的权限， PostgreSQL可以通过建立视图，并确定视图的权限来弥补。MySQL还允许你指定基于主机的权限，这对于目前的PostgreSQL是无法实现的，但是在很多时候，这是有用的。

6)存储引擎插件化机制

MySQL的存储引擎插件化机制，使得它的应用场景更加广泛，比如除了innodb适合事务处理场景外，myisam适合静态数据的查询场景。

7)适应24/7运行

MySQL可以适应24/7运行。在绝大多数情况下，你不需要为MySQL运行任何清除程序。PostgreSQL目前仍不完全适应24/7运行，这是因为你必须每隔一段时间运行一次VACUUM。

8)更加试用于简单的场景

PostgreSQL只支持堆表，不支持索引组织表，Innodb只支持索引组织表。

索引组织表的优势：表内的数据就是按索引的方式组织，数据是有序的，如果数据都是按主键来访问，那么访问数据比较快。而堆表，按主键访问数据时，是需要先按主键索引找到数据的物理位置。

索引组织表的劣势：索引组织表中上再加其它的索引时，其它的索引记录的数据位置不再是物理位置，而是主键值，所以对于索引组织表来说，主键的值不能太大，否则占用的空间比较大。

对于索引组织表来说，如果每次在中间插入数据，可能会导致索引分裂，索引分裂会大大降低插入的性能。所以对于使用innodb来说，我们一般最好让主键是一个无意义的序列，这样插入每次都发生在最后，以避免这个问题。

由于索引组织表是按一个索引树，一般它访问数据块必须按数据块之间的关系进行访问，而不是按物理块的访问数据的，所以当做全表扫描时要比堆表慢很多，这可能在OLTP中不明显，但在数据仓库的应用中可能是一个问题。

总结

MySQL从一开始就没有打算做所有事情，因而它在功能方面有一定的局限性，并不能满足一些先进应用程序的要求。MySQL对某些功能(例如引用、事务、审计等)的实现方式使得它与其他的关系型数据库相比缺少了一些可靠性。对于简单繁重的读取操作，使用PostgreSQL可能有点小题大做，同时性能也比MySQL这样的同类产品要差。除非你需要绝对的数据完整性，ACID遵从性或者设计复杂，否则PostgreSQL对于简单的场景而言有点多余。

如何你确定只在MySQL和PostgreSQL中进行选择，以下规则总是有效的：

如果你的操作系统是Windows，你应该使用MySQL。

当绝对需要可靠性和数据完整性的时候，PostgreSQL是更好的选择。

如果需要数据库执行定制程序，那么可扩展的PostgreSQL是更好的选择。

你的应用处理的是地理数据，由于R-TREES的存在，你应该使用PostgreSQL。

如果你对数据库并不了十分了解，甚至不知道事务、存储过程等究竟是什么，你应该使用MySQL。

Q3: postgresql 怎么自动创建分区

在数据库日渐庞大的今天，为了方便对数据库数据的管理，比如按时间，按地区去统计一些数据时，基数过于庞大，多有不便。很多商业数据库都提供分区的概念，按不同的维度去存放数据，便于后期的管理，PostgreSQL也不例外。

PostgresSQL分区的意思是把逻辑上的一个大表分割成物理上的几块儿。分区不仅能带来访问速度的提升，关键的是，它能带来管理和维护上的方便。

分区的具体好处是：

某些类型的查询性能可以得到极大提升。

更新的性能也可以得到提升，因为表的每块的索引要比在整个数据集上的索引要小。如果索引不能全部放在内存里，那么在索引上的读和写都会产生更多的磁盘访问。

批量删除可以用简单的删除某个分区来实现。

可以将很少用的数据移动到便宜的、转速慢的存储介质上。

在PG里表分区是通过表继承来实现的，一般都是建立一个主表，里面是空，然后每个分区都去继承它。无论何时，都应保证主表里面是空的。

小表分区不实际，表在多大情况下才考虑分区呢？PostgresSQL官方给出的建议是：当表本身大小超过了机器物理内存的实际大小时（the size of the table should exceed the physical memory of the database server），可以考虑分区。

PG目前（9.2.2）仅支持范围分区和列表分区，尚未支持散列分区。

二、环境

系统环境：CentOS release 6.3 (Final)

PostgreSQL版本：PostgreSQL 9.2.2 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.6 20120305 (Red Hat 4.4.6-4), 64-bit

三、实现分区

3.1 创建主表

david=# create table tbl_partition (

david(# id integer,

david(# name varchar(20),

david(# gender boolean,

david(# join_date date,

david(# dept char(4));

CREATE TABLE

david=#

3.2 创建分区表

david=# create table tbl_partition_201211 (

check ( join_date = DATE '2012-11-01' AND join_date DATE '2012-12-01' )

) INHERITS (tbl_partition);

CREATE TABLE

david=# create table tbl_partition_201212 (

check ( join_date = DATE '2012-12-01' AND join_date DATE '2013-01-01' )

) INHERITS (tbl_partition);

CREATE TABLE

david=# create table tbl_partition_201301 (

check ( join_date = DATE '2013-01-01' AND join_date DATE '2013-02-01' )

) INHERITS (tbl_partition);

CREATE TABLE

david=# create table tbl_partition_201302 (

check ( join_date = DATE '2013-02-01' AND join_date DATE '2013-03-01' )

) INHERITS (tbl_partition);

CREATE TABLE

david=# create table tbl_partition_201303 (

check ( join_date = DATE '2013-03-01' AND join_date DATE '2013-04-01' )

) INHERITS (tbl_partition);

CREATE TABLE

david=# create table tbl_partition_201304 (

check ( join_date = DATE '2013-04-01' AND join_date DATE '2013-05-01' )

) INHERITS (tbl_partition);

CREATE TABLE

david=# create table tbl_partition_201305 (

check ( join_date = DATE '2013-05-01' AND join_date DATE '2013-06-01' )

) INHERITS (tbl_partition);

CREATE TABLE

david=#

3.3 分区键上建索引

david=# create index tbl_partition_201211_joindate on tbl_partition_201211 (join_date);

CREATE INDEX

david=# create index tbl_partition_201212_joindate on tbl_partition_201212 (join_date);

CREATE INDEX

david=# create index tbl_partition_201301_joindate on tbl_partition_201301 (join_date);

CREATE INDEX

david=# create index tbl_partition_201302_joindate on tbl_partition_201302 (join_date);

CREATE INDEX

david=# create index tbl_partition_201303_joindate on tbl_partition_201303 (join_date);

CREATE INDEX

david=# create index tbl_partition_201304_joindate on tbl_partition_201304 (join_date);

CREATE INDEX

david=# create index tbl_partition_201305_joindate on tbl_partition_201305 (join_date);

CREATE INDEX

david=#

对于开发人员来说，希望数据库是透明的，只管 insert into tbl_partition。对于数据插向哪个分区，则希望由DB决定。这点，ORACLE实现了，但是PG不行，需要前期人工处理下。

3.4 创建触发器函数

david=# CREATE OR REPLACE FUNCTION tbl_partition_insert_trigger()

RETURNS TRIGGER AS $$

BEGIN

IF ( NEW.join_date = DATE '2012-11-01' AND

NEW.join_date DATE '2012-12-01' ) THEN

INSERT INTO tbl_partition_201211 VALUES (NEW.*);

ELSIF ( NEW.join_date = DATE '2012-12-01' AND

NEW.join_date DATE '2013-01-01' ) THEN

INSERT INTO tbl_partition_201212 VALUES (NEW.*);

ELSIF ( NEW.join_date = DATE '2013-01-01' AND

NEW.join_date DATE '2013-02-01' ) THEN

INSERT INTO tbl_partition_201301 VALUES (NEW.*);

ELSIF ( NEW.join_date = DATE '2013-02-01' AND

NEW.join_date DATE '2013-03-01' ) THEN

INSERT INTO tbl_partition_201302 VALUES (NEW.*);

ELSIF ( NEW.join_date = DATE '2013-03-01' AND

NEW.join_date DATE '2013-04-01' ) THEN

INSERT INTO tbl_partition_201303 VALUES (NEW.*);

ELSIF ( NEW.join_date = DATE '2013-04-01' AND

NEW.join_date DATE '2013-05-01' ) THEN

INSERT INTO tbl_partition_201304 VALUES (NEW.*);

ELSIF ( NEW.join_date = DATE '2013-05-01' AND

NEW.join_date DATE '2013-06-01' ) THEN

INSERT INTO tbl_partition_201305 VALUES (NEW.*);

ELSE

RAISE EXCEPTION 'Date out of range. Fix the tbl_partition_insert_trigger() function!';

END IF;

RETURN NULL;

END;

$$

LANGUAGE plpgsql;

CREATE FUNCTION

david=#

说明：如果不想丢失数据，上面的ELSE 条件可以改成 INSERT INTO tbl_partition_error_join_date VALUES (NEW.*); 同时需要创建一张结构和tbl_partition 一样的表tbl_partition_error_join_date，这样，错误的join_date 数据就可以插入到这张表中而不是报错了。

附上出处链接：

Q4: SQL 数据库 学习 003 什么是数据库？ 为什么需要数据库？

1. 什么是数据库 如果我们狭义的理解,它就是一个存储数据的仓库。 如果我们广义的理解,...

2. 为什么需要数据库

3. 几乎所有的应用软件的后台都需要数据库。 比如说:银行。张三、李四…将钱存入银行,我们不就需要一个来存储这些信息的库吗,...

4. 数据库存储数据占用的空间小,并且容易持久保存。 比如:同样的数据,如果让你直接...

5. 是不是所有的软件都是用Sql Server? 大多数应用软件,都可能会使用数据...

关于postgresql存储结构和的介绍到此就结束了，不知道你从中找到你需要的信息了吗 ？如果你还想了解更多这方面的信息，记得收藏关注本站。