一、数据存储
1、物理存储位置
-- 查询表所在磁盘的位置
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分一页，行数据存在页中
-- 查询表页数、行数
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
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中
图形化分析也有展示
5、查看缓存情况
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;
6、缓存置为失效
参考: PostgreSQL查询shared buffer使用情况和清理方式
7、缓存刷盘策略
写操作先写WAL日志，写成功之后再写内存数据，之后Checkpointer进行数据刷盘，缓存数据即为最新数据。
四、对我们优化查询有什么借鉴
1、重点表数据常驻缓存
参考: PostgreSQL-缓存利器
将重点数据、实时性要求高的数据手动加载至数据库缓存中，减少重点数据的内存淘汰，不稳定IO。同时要考虑刷盘问题，宕机容灾问题。
这同时会牺牲其余不重点功能的性能，需要实际去压测。
2、计算系统和查询系统主从分离
计算服务-写主
报表读服务-读从
- 优势:
1、主从服务因为行为不一样，所以热点数据不一样，隔离开来保证各自缓存命中率更高。 - 其他影响:
1、主从即便有延迟，也是跑数数据没有及时生成，只要延迟不高，影响则不大。
2、系统变复杂，易维护性降低。
3、单测、压测时要排除数据库缓存干扰
排除干扰，或者带上缓存综合去考虑优化方案。
五、其他优化方向
1、PREPARE预加载，PostgreSQL手册-prepare
2、PostgreSQL 列存索引 - 新方式 - 列存
3、PostgreSQL 列存, 混合存储, 列存索引, 向量化存储, 混合索引 - OLTP OLAP OLXP HTAP 混合负载应用
4、介绍PostgreSQL CTE(common table expressions) - with as 简化
参考
1、深入理解Postgres中的cache
2、PostgreSQL物理存储简介
3、PostgreSQL Internals Through Pictures
4、The Internals of PostgreSQL
