[PostgreSQL] - 存储结构及缓存shared_buffers

一、数据存储

1、物理存储位置

代码语言：javascript复制

-- 查询表所在磁盘的位置
select pg_relation_filepath('表名')
-- out
base/16393/24804

包含三个文件, 具体介绍请看PostgreSQL目录结构之base目录，Free Space Map and Visibility Map:

24804代表表的OID(object id)

24804 - 数据和索引文件(超过1G每1G划分一段, 命名*.1, *.2)
24804_fsm(free space map): 空闲空间映射，主要用来保持对关系中可用空间的跟踪。
24804_vm(visibility map): 可见性映射, 每一个堆关系都有一个可见性映射（VM）用来跟踪哪些页面只包含已知对所有活动事务可见的元组，它也跟踪哪些页面只包含未被冻结的元组。

注意: 因为一个页是8K，如果一个表的列中可能存储相当大的项，那么该表就会有个与之相关联的TOAST表，它用于存储无法保留在在表行中的域值的线外存储。参考68.2. TOAST。

2、物理存储结构

每个表每8K分一页，行数据存在页中

代码语言：javascript复制

-- 查询表页数、行数
SELECT relpages as 页个数, reltuples as 行个数 FROM pg_class WHERE relname = '表名'

-- 查询占空间大小(排名前20)
SELECT table_schema || '.' || table_name                                                         AS table_full_name,
       pg_size_pretty(pg_total_relation_size('"' || table_schema || '"."' || table_name || '"')) AS size
FROM information_schema.tables
ORDER BY pg_total_relation_size('"' || table_schema || '"."' || table_name || '"') DESC
limit 20;

表中的一个页面包含如下描述的三种数据：

heap tuple(s) ——堆元组本身就是一个记录数据。它们从页面底部开始按顺序堆叠。
line pointer(s) – 一个行指针有 4 个字节长，并保存一个指向每个堆元组的指针。它也称为项目指针。行指针组成一个简单的数组，起到元组索引的作用。每个索引从 1 开始按顺序编号，称为偏移编号。当一个新的元组被添加到页面时，一个新的行指针也被推到数组上以指向新的。
标头数据——由结构PageHeaderData定义的标头数据分配在页面的开头。它长 24 字节，包含有关页面的一般信息。结构的主要变量如下所述。

pd_lsn——这个变量存储了本页最后一次更改写入的 XLOG 记录的 LSN。它是一个 8 字节的无符号整数，与 WAL（Write-Ahead Logging）机制有关。
pd_checksum – 此变量存储此页面的校验和值。
pd_lower, pd_upper – pd_lower 指向行尾指针，pd_upper 指向最新堆元组的开头。
pd_special – 此变量用于索引。在表中的页面中，它指向页面的末尾。（在索引内的页面中，它指向特殊空间的开头，即只有索引持有的数据区域，根据索引类型的种类，如B-tree、GiST、GiN等包含特定的数据。）

二、检索

1、数据扫描方式

左侧为顺序扫描，右侧为b-tree索引扫描

b-tree扫描细节

三、缓存cache

参考: 深入理解Postgres中的cache

1、概述

我们知道，大多数OLTP工作负载是随机的I/O，但是从磁盘获取非常缓慢。为了克服这个问题，和其它现有的数据库系统差不多，Postgres也把数据缓存到RAM(也就是我们说的内存)以提高性能。

2、缓存 shared_buffers

shared_buffers所代表的内存区域可以看成是一个以8KB的block为单位的数组，即最小的分配单位是8KB。这正好是一个page的大小，每个page以page内部的元数据(Page Header)互相区分。这样，当Postgres想要从disk获取(主要是table和index)数据(page)时，他会(根据page的元数据)先搜索shared_buffers，确认该page是否在shared_buffers中，如果存在，则直接命中，返回缓存的数据以避免I/O。如果不存在，Postgres才会通过I/O访问disk获取数据(显然要比从shared_buffers中获取慢得多)。

3、缓存淘汰

以页为单位，cache满的时候，会淘汰不常用的页。淘汰后的数据则进行刷盘，但是一般数据都是通过WAL Checkpointer保证修改的数据刷盘，而不用等到cache满了才进行刷盘。

4、如何观察是否走cache

使用explain时，Shared read表示来自disk，Shared hit则是已经在cache中

图形化分析也有展示

image.png

5、查看缓存情况

代码语言：javascript复制

SELECT
     c.relname,
     pg_size_pretty(count(*) * 8192) as buffered,
     round(100.0 * count(*) / (SELECT setting FROM pg_settings WHERE name='shared_buffers')::integer, 1) AS buffer_percent,
     round(100.0 * count(*) * 8192 / pg_table_size(c.oid), 1) AS percent_of_relation
 FROM pg_class c
 INNER JOIN pg_buffercache b ON b.relfilenode = c.relfilenode
 INNER JOIN pg_database d ON (b.reldatabase = d.oid AND d.datname = current_database())
 GROUP BY c.oid, c.relname
 ORDER BY 3 DESC
 LIMIT 10;