如何压缩超500GB的PostgreSQL数据库（支持写入）

针对你这个500GB全球GIS PostgreSQL数据库的压缩需求，结合你试过SquashFS但受限于只读特性的情况，我整理了几个兼顾压缩比和可写性的实用方案，都是GIS场景下经过验证的：

1. 利用PostgreSQL原生压缩功能（最推荐，无侵入） #

PostgreSQL本身就支持对大字段（比如GIS的geometry/geography类型）进行高效压缩，完全兼容写入操作，对应用层透明：

• 调整TOAST压缩级别：PostgreSQL默认用pglz压缩大字段，从14版本开始支持更高效的zstd（压缩比和性能平衡最好）。先修改全局默认压缩配置：

  ALTER SYSTEM SET default_toast_compression = 'zstd';
  SELECT pg_reload_conf();
  

  然后对现有GIS表的空间字段应用新压缩，并重写数据生效：

  ALTER TABLE your_gis_table ALTER COLUMN geom SET COMPRESSION 'zstd';
  VACUUM FULL your_gis_table;
  

  这个操作会把表数据重新写入，同时用zstd压缩，对于重复度高的GIS数据，压缩比能达到2-3倍左右（接近你用SquashFS的效果）。
• 分区表+差异化压缩：如果你的GIS数据有明显的冷热区分（比如按区域/时间划分），可以把冷分区设置为高压缩级别，热分区保持默认性能。比如用pg_cron定期对超过1年的分区执行压缩和VACUUM FULL，既节省空间又不影响热数据的写入速度。

2. 采用支持透明可写压缩的文件系统 #

如果原生压缩达不到预期，文件系统层面的透明压缩是个好选择，完全不影响PostgreSQL的写入逻辑：

• ZFS（最优选择）：ZFS的zstd压缩在压缩比和性能之间的平衡非常出色，甚至能接近SquashFS的水平。你只需要把PostgreSQL的数据目录迁移到ZFS数据集上：

  # 创建启用zstd压缩的ZFS数据集
  zfs create -o compression=zstd your_zpool/pg_data
  # 停止PostgreSQL服务，迁移数据到新目录
  systemctl stop postgresql
  mv /var/lib/postgresql/14/main/* /your_zpool/pg_data/
  # 修改postgresql.conf中的data_directory路径，重启服务
  systemctl start postgresql
  

  ZFS还自带快照、增量备份等功能，对本地部署的GIS系统非常友好。
• Btrfs（轻量替代）：如果不想折腾ZFS，Btrfs也支持透明压缩，挂载时加上compress=zstd参数即可：

  mount -o compress=zstd /dev/sdX /mnt/pg_data
  

  性能略逊于ZFS，但配置简单，适合Linux系统。

3. 冷热数据分层存储（兼顾极致压缩与可写） #

既然是全球GIS数据，大概率存在大量不常更新的冷数据（比如几年前的历史地图数据），可以把这部分数据用SquashFS压缩存储（保留高压缩比），热数据放在普通可写存储：

• 先把冷数据从主库导出，用SquashFS压缩成镜像：

  # 导出冷数据到单独的目录
  pg_dump -d your_db -t cold_gis_table > cold_data.sql
  psql -d cold_db -f cold_data.sql
  # 把cold_db的数据目录压缩成SquashFS镜像
  mksquashfs /var/lib/postgresql/14/cold_db cold_db.squashfs -comp zstd
  

• 挂载镜像为只读目录，然后用PostgreSQL的postgres_fdw创建外部表，或者用分区表把冷分区指向这个只读数据库。这样查询时会自动合并冷热数据，热数据依然可以正常写入，冷数据享受SquashFS的高压缩比。

4. 业务层面的GIS数据优化（额外空间节省） #

除了压缩，针对GIS数据的特性做业务层面的优化，能进一步减少体积：

• 简化几何图形：如果业务不需要极高的精度，用PostGIS的ST_Simplify或ST_SimplifyPreserveTopology简化空间数据：

  UPDATE your_gis_table SET geom = ST_SimplifyPreserveTopology(geom, 0.001);
  

  这个操作能大幅减少几何字段的存储体积，而且完全不影响写入。
• 删除冗余数据：清理不需要的历史数据、重复的空间记录，或者把非空间的冗余字段归档到其他存储。

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