关于PostgreSQL数据的存储,你有必要有所了解

本文涉及的产品
云原生数据库 PolarDB PostgreSQL 版,标准版 2核4GB 50GB
云原生数据库 PolarDB MySQL 版,通用型 2核4GB 50GB
简介: 关于PostgreSQL数据的存储,你有必要有所了解

PostgerSQL对象标识符


OID


OID 是 PostgreSQL 内部用于标识数据库对象(数据库,表**,视图,**存储过程等等)的标识符,用4个字节的无符号整数表示。它是PostgreSQL大部分系统表的主键。

类型oid表示一个对象标识符。 也有多个oid的别名类型:

regproc,regprocedure, regoper, regoperator,regclass, regtype, regrole,regnamespace, regconfig, 和regdictionary


1.jpg


OID的别名类型除了特定的输入和输出例程之外没有别的操作。这些例程可以接受并显示系统对象的符号名,而不是类型oid使用的原始数字值。别名类型使查找对象的OID值变得简单。例如,要检查与一个表course有关的pg_attribute行,你可以写:


SELECT * FROM pg_attribute WHERE attrelid = 'course'::regclass;
复制代码

2.jpg


OID 在系统表中通常是作为隐藏列存在的,它是以整个PostgreSQL数据库实例(Database Cluster)的范围内统一分配。因为只有四个字节,因此,在大型数据库中它并不足以提供数据库范围内的唯一性,甚至在一些大型的表中也无法提供表范围内的唯一性。


OID 在旧版本中还可以用于标识元组,对于没有主键,重复的行,此时 OID 作为唯一 ID,则可以根据它进行删除指定行数据。我们之前创建表时,default_with_oids 默认是关闭的。在老版本中执行 create table 语句时可以指定开启 OID。


create table foo (
    id integer,
    content text
) with oids;
复制代码


不过从 Postgres 12 开始,删除了将 OID 用作表上的可选系统列。将无法再使用:

  • CREATE TABLE … WITH OIDS 命令
  • default_with_oids (boolean) 相容性设定

数据类型OID保留在Postgres 12中。您可以显式创建类型的列OID


XID


事务ID:


  • 由32位组成,这就有可能造成事务ID回卷的问题,具体参考文档
  • 顺序产生,依次递增
  • 没有数据变更,如INSERT、UPDATE、DELETE等操作,在当前会话中,事务ID不会改变


数据库系统中使用的数据类型为 xmin xmax


  • xmin 存储的是产生这个元组的事务ID,可能是insert或者update语句
  • xmax 存储的是删除或者锁定这个元组的XID


简单示例如下:


select id, xmin, xmax from course;
复制代码

3.jpg


当 PostgreSQL的XID 到达40亿,会造成溢出,从而新的XID 为0。而按照 PostgreSQL的MVCC 机制实现,之前的事务就可以看到这个新事务创建的元组,而新事务不能看到之前事务创建的元组,这违反了事务的可见性。具体参考文档


CID


CID 名为命令标识符,PG 每个表都包含一些系统字段,关于 CID 用到的数据类型为 cmax 和 cmin。

  • cmin:插入该元组的命令在插入事务中的命令标识(从0开始累加)
  • cmax:删除该元组的命令在插入事务中的命令标识(从0开始累加)

cmin和cmax用于判断同一个事务内的其他命令导致的行版本变更是否可见。如果一个事务内的所有命令严格顺序执行,那么每个命令总能看到之前该事务内的所有变更,不需要使用命令标识。


简单示例如下:

select id, xmin, xmax,cmin,cmax from course;
复制代码

4.jpg


TID


TID 称为元组标识符(行标识符),一个元组ID是一个(块号,块内元组索引)对,它标识了行在它的表中的物理位置。


简单示例如下:


select ctid,id, xmin, xmax,cmin,cmax from course;
复制代码

5.jpg


了解完上述四大标识符后,我们接着来学习 PostgreSQL 中数据到底是怎么存储的。


PostgreSQL数据存储



关于数据存储,我们都知道数据是存在数据库中的某个数据表中,每条数据记录对应数据表中的某一行,所以我们从上至下来查看各层次结构的数据存储。


PGDATA目录结构



PGDATA 是 PostgreSQL 用来存放所有数据的地方.


关于 PGDATA 的设置,可以先执行下述命令。


postgres=# show data_directory;
       data_directory        
-----------------------------
 /Library/PostgreSQL/12/data
(1 row)
复制代码


接下来我们来看一下 PGDATA 文件夹中有哪些文件,首先打开命令行窗口,然后进入到上述目录。


MacBook-Pro 12 % cd /Library/PostgreSQL/12/data
cd: permission denied: /Library/PostgreSQL/12/data
复制代码


如果遇到上述问题,则执行如下命令,尝试使用 sudo 模拟 postgresql 用户登录:


MacBook-Pro 12 % sudo -u postgres -i
The default interactive shell is now zsh.
To update your account to use zsh, please run `chsh -s /bin/zsh`.
For more details, please visit https://support.apple.com/kb/HT208050.
复制代码


接着执行如下命令:


tree -FL 1 /Library/PostgreSQL/12/data
/Library/PostgreSQL/12/data
├── PG_VERSION
├── base/
├── current_logfiles
├── global/
├── log/
├── pg_commit_ts/
├── pg_dynshmem/
├── pg_hba.conf
├── pg_ident.conf
├── pg_logical/
├── pg_multixact/
├── pg_notify/
├── pg_replslot/
├── pg_serial/
├── pg_snapshots/
├── pg_stat/
├── pg_stat_tmp/
├── pg_subtrans/
├── pg_tblspc/
├── pg_twophase/
├── pg_wal/
├── pg_xact/
├── postgresql.auto.conf
├── postgresql.conf
├── postmaster.opts
└── postmaster.pid
复制代码


介绍几个常见的文件夹:


  • base/:存储 database 数据(除了指定其他表空间的),子目录的名字为该数据库在 pg_database里的 OID。
  • postgresql.conf:postgresql 配置文件


database数据存储


上文提到在 base/ 目录下存放着每个 database 数据,其中文件名我们叫做 dboid。

由于 OID 是系统表的隐藏列,因此查看系统表中数据库对象的OID时,必须在SELECT语句中显式指定。我们进入 postgres 命令行窗口,执行下述命令:


postgres=# select oid,datname from pg_database;
  oid  |  datname  
-------+-----------
 13635 | postgres
     1 | template1
 13634 | template0
 16395 | mydb
 16396 | dvdrental
 16399 | testdb
(6 rows)
select oid,relname from pg_class order by oid;
复制代码


我们可以在 PGDATA 文件夹下的 base 目录下看到 oid


MacBook-Pro:base postgres$ ls /Library/PostgreSQL/12/data/base
1 13634 13635 16395 16396 16399
复制代码


从上述内容可知 postgres 数据库相关的数据存储在 PGDATA/base/13635 目录里面。


table数据存储


上文我们定位到数据库的存储位置,接着我们来定位数据表的位置。


每一张表的数据(大部分)又是放在 $PGDATA/base/{dboid}/{relfilenode} 这个文件里面,relfilenode一般情况下和和tboid一致,但有些情况下也会变化,如TRUNCATEREINDEXCLUSTER以及某些形式的ALTER TABLE


CREATE TABLE public.cities (
  city varchar(80) NOT NULL,
  "location" point NULL,
  CONSTRAINT cities_pkey PRIMARY KEY (city)
);
postgres=# select oid,relfilenode from pg_class where relname = 'cities';
  oid  | relfilenode 
-------+-------------
 16475 |       16475
(1 row)
insert into cities values('北京',null);
insert into cities values('上海',null);
truncate cities ;
postgres=# select oid,relfilenode from pg_class where relname = 'cities';
  oid  | relfilenode 
-------+-------------
 16475 |       16480
(1 row)
SELECT * FROM pg_attribute WHERE attrelid = 'course'::regclass;
复制代码


除了上述 SQL 语句,我们还可以通过系统函数 pg_relation_filepath 来查看指定表的文件存储位置。


postgres=# select pg_relation_filepath('cities');
 pg_relation_filepath 
----------------------
 base/13635/16480
(1 row)
复制代码


当查看 PGDATA/base/13635/ 目录时,会发现 16480 的文件夹,除此之外还会发现有些文件命名为 relfilenode_fsmrelfilenode_vmrelfilenode_init, 关于 16480 通常会有三种文件:16480、16480_fsm、16480_vm,分别是该数据库对应表的数据或索引文件、其对应的空闲空间映射文件、其对应的可见性映射文件。

如果数据文件过大,那么会怎么命名呢?


在表或者索引超过1GB之后,它就被划分成1GB大小的段。 第一个段的文件名和文件节点相同,随后的段被命名为 filenode.1filenode.2等等。这样的安排避免了在某些有文件大小限制的平台上的问题。


postgres=# create table bigdata(id int,name varchar(64));
postgres=# insert into bigdata select generate_series(1,20000000) as key, md5(random()::text);
postgres=# select pg_relation_filepath('bigdata');
 pg_relation_filepath 
----------------------
 base/13635/16486
(1 row)
#切换命令行界面
MacBook-Pro:base postgres$ ls 13635 |grep 16486
16486
16486.1
16486_fsm  
复制代码


元组数据存储


上文我们提到 table 存储时,每个数据文件(堆文件、索引文件)可存储 1G 的容量,每个文件内部又是有若干个固定的页组成。页的默认大小为8192字节(8KB)。单个表文件中的这些页(Page)从0开始进行顺序编号,这些编号也称为“块编号(Block Numbers)”。如果第一页空间已经被数据填满,则 postgres 会立刻重新在文件末尾(即已填满页的后面)添加一个新的空白页,用于继续存储数据,一直持续这个过程,直到当前表文件大小达到 1GB位置。若文件达到1GB,则重新创建一个新的表文件,然后重复上面的这个过程。


每个页的内部又由一个页文件头(Page Header)、若干行指针(Line Pointer)、若干个元组数据(Heaple Tuple)组成。因为每个文件默认大小为 1GB,页大小为 8kb,则每个文件大概有 131072 个页。


首先来看一下页面结构。


6.jpg


其中:


  • page header: 24 字节,存储 page 的基本信息,包括 pd_lsn、pd_checksum、pd_special...


pd_lsn: 存储最近改变该页面的xlog。
pd_checksum:存储页面校验和。
pd_lower,pd_upper:pd_lower指向行指针(line pointer)的尾部,pd_upper指向最后那个元组。
pd_special: 索引页面中使用,它指向特殊空间的开头。
pd_flags:用以设置位标志。
pd_pagesize_version:页面大小及页面版本号。
pd_prune_xid:可删除的旧 XID,如果没有则为零。
复制代码


  • line pointe:行指针,4 bytes,形为 (offset, length) 的二元组,指向相关 tuple
  • heap tuple: 用来存储 row 的数据,注意元组是从页面的尾部向前堆积的,元组和行指针之间的是数据页的空闲空间。
  • 空白处:未申请空间,新的 line point 从其首端申请,新的 tuple 从其尾端申请


因此我们找 row 的数据需要知道哪一个 page,page 的哪一个 item, (page_index, item_index), 通常称它为 CTID(ItemPointer), 我们可以通过下面语句查看每一列的 CTID:


select ctid,* from course;
复制代码


查询结果如下所示:


7.jpg


关于元组结构以及数据变化的详解讲解,可以参考本文


扩展

schema


PostgreSQL 除了默认的 public schema 之外,还有两个比较重的系统 schema:

information_schema 与pg_catalog。


通过查看 pg_catalog.pg_namespace 来查看当前数据库中全部的 schema。


postgres=# select * from pg_catalog.pg_namespace ;
  oid  |      nspname       | nspowner |               nspacl                
-------+--------------------+----------+-------------------------------------
    99 | pg_toast           |       10 | 
 12314 | pg_temp_1          |       10 | 
 12315 | pg_toast_temp_1    |       10 | 
    11 | pg_catalog         |       10 | {postgres=UC/postgres,=U/postgres}
  2200 | public             |       10 | {postgres=UC/postgres,=UC/postgres}
 13335 | information_schema |       10 | {postgres=UC/postgres,=U/postgres}
(6 rows)
复制代码


我们创建的表、视图、索引等默认都在 public 下。


information_schema 是方便用户查看表/视图/函数信息提供的,它大多是视图。


select * from information_schema."tables";
复制代码

8.jpg


pg_catalog 包含系统表和所有内置数据类型、函数、操作符。pg_catalog 下有很多系统表,比如说 pg_classpg_attributepg_authid等,关于这些表的详细介绍可以参考本文



相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
目录
相关文章
|
6月前
|
SQL Oracle 关系型数据库
实时计算 Flink版操作报错之往GREENPLUM 6 写数据,用postgresql-42.2.9.jar 报 ON CONFLICT (uuid) DO UPDATE SET 语法有问题。怎么解决
在使用实时计算Flink版过程中,可能会遇到各种错误,了解这些错误的原因及解决方法对于高效排错至关重要。针对具体问题,查看Flink的日志是关键,它们通常会提供更详细的错误信息和堆栈跟踪,有助于定位问题。此外,Flink社区文档和官方论坛也是寻求帮助的好去处。以下是一些常见的操作报错及其可能的原因与解决策略。
|
6月前
|
关系型数据库 PostgreSQL
PostgreSQL排序字段不唯一导致分页查询结果出现重复数据
PostgreSQL排序字段不唯一导致分页查询结果出现重复数据
123 0
|
关系型数据库 MySQL Linux
TiDB实时同步数据到PostgreSQL(三) ---- 使用pgloader迁移数据
使用PostgreSQL数据迁移神器pgloader从TiDB迁移数据到PostgreSQL,同时说明如何在最新的Rocky Linux 9(CentOS 9 stream也适用)上通过源码编译安装pgloader。
|
5月前
|
消息中间件 Java 关系型数据库
实时计算 Flink版操作报错合集之从 PostgreSQL 读取数据并写入 Kafka 时,遇到 "initial slot snapshot too large" 的错误,该怎么办
在使用实时计算Flink版过程中,可能会遇到各种错误,了解这些错误的原因及解决方法对于高效排错至关重要。针对具体问题,查看Flink的日志是关键,它们通常会提供更详细的错误信息和堆栈跟踪,有助于定位问题。此外,Flink社区文档和官方论坛也是寻求帮助的好去处。以下是一些常见的操作报错及其可能的原因与解决策略。
1008 0
|
5月前
|
DataWorks 安全 关系型数据库
DataWorks产品使用合集之使用Flink CDC读取PostgreSQL数据时如何指定编码格式
DataWorks作为一站式的数据开发与治理平台,提供了从数据采集、清洗、开发、调度、服务化、质量监控到安全管理的全套解决方案,帮助企业构建高效、规范、安全的大数据处理体系。以下是对DataWorks产品使用合集的概述,涵盖数据处理的各个环节。
|
3月前
|
SQL 关系型数据库 MySQL
SQL Server、MySQL、PostgreSQL:主流数据库SQL语法异同比较——深入探讨数据类型、分页查询、表创建与数据插入、函数和索引等关键语法差异,为跨数据库开发提供实用指导
【8月更文挑战第31天】SQL Server、MySQL和PostgreSQL是当今最流行的关系型数据库管理系统,均使用SQL作为查询语言,但在语法和功能实现上存在差异。本文将比较它们在数据类型、分页查询、创建和插入数据以及函数和索引等方面的异同,帮助开发者更好地理解和使用这些数据库。尽管它们共用SQL语言,但每个系统都有独特的语法规则,了解这些差异有助于提升开发效率和项目成功率。
324 0
|
3月前
|
SQL 关系型数据库 HIVE
实时计算 Flink版产品使用问题之如何将PostgreSQL数据实时入库Hive并实现断点续传
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
|
3月前
|
开发框架 关系型数据库 数据库
在 PostgreSQL 中,解决图片二进制数据,由于bytea_output参数问题导致显示不正常的问题。
在 PostgreSQL 中,解决图片二进制数据,由于bytea_output参数问题导致显示不正常的问题。
|
5月前
|
关系型数据库 5G PostgreSQL
postgreSQL 导出数据、导入
postgreSQL 导出数据、导入
54 1
|
6月前
|
SQL 关系型数据库 MySQL
实时计算 Flink版产品使用合集之如何使用PostgreSQL2.4.1从指定时间戳同步数据
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStreamAPI、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。