PostgreSQL磁盘表访问机制及按用户分表的优劣咨询

• PG的最小磁盘IO单元是固定8KB的数据页（块），所有读写操作都不会直接操作单行数据，必须以页为单位加载/写入。每个普通表的数据存在数据目录base路径下、以表OID命名的文件里，单文件超过1GB会自动切分成多个段文件。
• 查询时会优先访问进程共享内存里的shared_buffers缓冲区，如果目标页已经被缓存，直接从内存读取，不会触发磁盘IO；只有缓存未命中时，才会向磁盘发起读请求，把整个8KB页加载到缓冲区后再做后续处理。
• 如果查询走索引，会先读取索引对应的页（索引本身也按8KB页存储），通过索引结构定位到目标行所在的堆表页位置，再加载对应堆页提取数据；如果是全表扫描，就按顺序遍历所有堆表页，逐行判断是否匹配过滤条件。
• 数据修改不会立刻同步刷盘：先修改缓冲区里的页并标记为脏页，后续由后台的checkpoint、bgwriter进程按策略批量把脏页写回磁盘，减少随机IO开销。

除了你已经发现的空间浪费问题，这个方案还有几个非常致命的缺陷：

• 元数据过载：PG中每个表、索引都会在pg_class、pg_attribute等系统表中存储元数据、统计信息。如果用户量达到十万甚至百万级，系统表会严重膨胀，连库内建表、查表、连接初始化这类基础操作都会变得极慢，日常vacuum、备份、版本升级的运维成本会指数级上涨。
• 跨用户查询几乎不可用：只要你需要做跨用户的统计——比如统计某件商品的总销量、某段时间的全站购买趋势，就得把所有用户表拼起来做UNION ALL，不仅写起来繁琐，执行效率也极低，基本没法做复杂的数据分析需求。
• 缓存命中率暴跌：单表场景下，高频访问的用户数据页、索引页会长期驻留在共享缓冲区里，重复查询不用走磁盘IO；但每用户一表的模式下，每个表至少占一个8KB页，海量的冷数据页会快速挤占缓存空间，大部分非热点用户的查询都要触发真实磁盘IO，整体延迟反而更高。
• 维护成本极高：后续只要你需要调整表结构——比如加个购买时间字段、给商品ID加索引、修改字段类型，就得遍历所有用户表重复执行DDL，一旦中间某个表执行失败，就会出现表结构不一致的问题，排查修复的成本极高。

结论先放这：99%的生产场景下，这个方案不仅不会降低查询耗时，反而会比合理设计的单表/分区分表方案慢很多，所谓的查询优势基本是错觉。

• 你觉得单表需要先检索匹配用户行有开销，但实际上这部分开销可以忽略：只要给单表的username字段建普通B树索引，查询某个用户的购买记录时，索引会直接定位到该用户所有行所在的堆页，内存里逐行判断用户名的操作是纳秒级的，100行的过滤耗时根本感知不到。如果再配合按username做聚簇或者原生分区，查询时直接裁剪到对应数据块，连多余的行都不用扫。
• 反而每用户一表会带来额外的性能损耗：每次查询前都要动态拼接表名、校验权限、查系统表获取表结构和元数据，当表的数量大到一定程度，这部分开销比行过滤的开销高几个数量级。
• 你观察到的空间浪费问题还会进一步拖慢性能：每个表占一个8KB页，同样存100万用户的购买记录，每用户一表要占至少8GB的基础空间，而单表存的话，按每条记录100字节算，1亿条记录也就10GB左右，前者的空间浪费率超过70%，备份、扫描、刷盘的IO开销都会跟着涨。
• 唯一可能出现微弱性能优势的极端场景：总用户量只有几百上千，完全没有跨用户查询需求，且所有查询都是拉取单个用户的全量购买记录。但这个优势你完全可以用PG原生的列表分区实现——按username分区，每个分区存对应用户的数据，查询时自动裁剪分区，效果和查独立表完全一致，还不会有元数据爆炸、运维困难的问题，根本没必要用每用户一表的野路子。

内容的提问来源于stack exchange，提问作者Striker