PostgreSQL 什么都能存，什么都能塞 --- 你能成熟一点吗？

开头还是介绍一下群，如果感兴趣PolarDB ,MongoDB ,MySQL ,PostgreSQL ,Redis, OceanBase, Sql Server等有问题，有需求都可以加群群内有各大数据库行业大咖，可以解决你的问题。加群请联系 liuaustin3 ，（共2630人左右 1 + 2 + 3 + 4 +5 + 6 + 7 + 8 ）(1 2 3 4 5 6群均已爆满，新人进7群350+，8群，准备9群)

PostgreSQL 迁移用户很简单 ---  我看你的好戏

PostgreSQL  用户胡作非为只能受着 --- 警告他

PostgreSQL  加索引系统OOM 怨我了--- 不怨你怨谁

PostgreSQL  “我怎么就连个数据库都不会建？”   --- 你还真不会！

有人私信我，说没礼貌的那个人，你真愿意搭理他，你这是编的还是真的。我只能说，源于生活，高于生活，脱离生活那是不可能的，但我也不能把人家姓字名谁写出来，那不就的公堂上见了。

今天我们继续“没礼貌”的故事，可能是看见我写的文章了，也觉得不好意思了，“没礼貌”也想有礼貌。最近这个“没礼貌”有问我一个问题，迁移中有一些比较大的字段要迁移到PostgreSQL中，看新的一天，故事又开始了。

（什么叫会了，通过一个学习这个事情，把这个事情体系化的理解，他的上游，下游，为什么要做这个事情，做完影响是什么，出现故障大致解决的步骤流程，方案是什么，不同的场景下灵活运用，而不是会敲一个命令而已，读死书，死读书）

DBA：诶，那个，你有时间吗？

我：哦，我有点忙，你留言吧

DBA：行，那个就挺简单的，我们那边要往PostgreSQL里面写大字段，我记得你说不建议一些特别大的，比如JSON类似的日志类，或大量的分析性的数据，写到PostgreSQL中。我想问问，为什么？

DBA: 喂，麻烦明天能告诉我吗？

我：嗯！

我： 我来说一下这个问题，这里有前提，数据库本身具有的功能，和完全放心去使用这个功能毫无禁忌的去使用是两码事。我们经常搞混一件事，一个数据库能做的事情和一个数据库擅长做的事情，且认为能做就是擅长做。

DBA: 是吗，我其实就一个问题，我这个图片和大量的文字，存在PG上没有什么问题吧！ 原来我们SQL SERVER就那么存的。

我： 那我请问一句，效果如何？

DBA: 我记得我们原来用的IMAGE类型，后面转成VARBINARY(MAX)了，反正不能量特别的大，进行提取，只要大量提取IOPS就挺高的，而且内存也会有的时候因为读取这些数据，导致缓存命中率低的情况发生，会有告警，数据的命中率低于90%。

并且我还查了一下，SQL SERVER varbinary最大支持2G ，为什么PostgreSQL bytea 就支持存储1GB，不是说PostgreSQL能力很强吗？

我：我有一个问题，在一个字段存储1GB 和 在一个字段存储2GB，的区别在哪里，你真正的需求是什么，在一个字段里面存储2GB？？？

DBA: 当然有区别，人家SQL SERVER 能存2GB 你就能存1GB，那不是人家强？？

我： 那你要这样说，我要是说，POSTGRESQL 全模糊查询，可以利用索引，这点是不是POSTGRESQL 要强过SQL SERVER？？？

DBA: 这个？？

我：一个数据库分为基本功能，特殊功能，你分析一个数据库本身，要从你实际的业务来分析，而不是数据库的说明书上有这个功能，你就不管不顾的去使用，那样你早晚会撞到南墙。你刚才说的那个问题，我给你举一个例子，如同告诉客户,SQL SERVER 上高速可以开到每小时800公里，POSTGRESQL 略逊一筹，可以每小时开到400公里，我请问，这对买车的客户，有意义吗？？？

DBA：有意义呀，SQL SERVER 开800公里每小时，比你POSTGRESQL快 ！

我：快400公里，你怎么不飞呢？ 你告诉我那个高速可以开800公里，中国高铁也跑不了那么快，你指望高速给你SQL SERVER 铺水泥铁轨吗？ 这对用户根本没有意义，高速最高的速度是120公里，法定的。

DBA: (ˉ▽￣～) 切~~

我：所有一个数据库的功能差距是在这里吗？ 你什么都想往PostgreSQL里面塞吗？比如你塞入大的数据，PostgreSQL怎么处理呢，这个原理你懂得吗？ 如果你懂得，你还应该来问这个问题吗？

DBA: 这不简单，不就是TOAST ，好多人都写了这个文章，我都看过了，将数据切片，想存多大就存多大。

我: 那我问几个问题

1 TOAST 里面存的数据有索引的数据吗?

 2 TOAST 有好几个字段都比较大的情况下，他们是一个字段一个TOAST文件还是只有一个TOAST文件

 3 怎么寻址TOAST 

4 TOAST 压缩后性能好，还是不压缩的性能好

不是都这么明白吗？ 来说说

DBA: 我懂那些干嘛，我用就完了，我告诉他们什么JOSN,图片，声音，统统的往PG里面存，我们有TOAST。

我：你小心你面包烤糊了。

DBA: 你吓唬我，你就那两瓶子水，切

我：抬杠长能耐，我今天教教你！

1 PostgreSQL Toast 使用是通过单个字段的大小来进行判断是否触发此机制的，比如一个字段，超过了toast_tuple_threshold的阈值就会触发字段里面的值，要进行TOAST的工作流程，默认这个字段的值，大小要超过2KB，才会触发。

2 会使用TOAST的是TEXT,BYTEA,JSONB,JSON，varchar(),char()等在POSTGRESQL上的字段类型。

3 对于一个字段的Toast存储方式有4种

4 如果一个页面里面的存储大小在存储后，超过8KB，则会触发TOAST的优化。

来我做一个事情，你不是会了吗？ 你给我说说为什么我这个字段存不进去

test=# INSERT INTO post_toast (long_text, large_varchar, json_data, other_column)
test-# VALUES 
test-#     ('Short text', 'Short varchar', '{"key": "value"}', 1);
INSERT 0 1
test=# 
test=# 
test=# INSERT INTO post_toast (long_text, large_varchar, json_data, other_column)
test-# VALUES 
test-#     (
test(#         repeat('This is a long text. ', 200),               
test(#         repeat('A large varchar field. ', 200),            
test(#         '{"nested_key": {"sub_key": "large_value"}}',      
test(#         2
test(#     );
ERROR:  row is too big: size 8896, maximum size 8160
test=# 

为什么第二插入的数据报错了？？ 来说说

DBA: 这个上面不是写了吗，你插入的数据大于了8160了呀？

我：不对呀，你不是说一个字段可以存1G吗，怎么我这一行都不能超过8160？

DBA: 你这个POSTGRESQL 安装的不对，有问题？

我：那我现在怎么又行了，可以存储了？？

test=# INSERT INTO post_toast (long_text, large_varchar, json_data, other_column)
VALUES 
    (                                                      
        repeat('This is a long text. ', 200),               -- 约4000字节
        repeat('A large varchar field. ', 200),            -- 约4000字节
        '{"nested_key": {"sub_key": "large_value"}}',      -- JSON结构，足够小不会触发TOAST
        2
    );
INSERT 0 1
test=# 

DBA: 你玩我？ 快告诉我怎么弄的？

我： 来，这个语句就能查出来为什么，你自己好好看看，看明白了吗？

test=# 
test=# SELECT a.attname,
  pg_catalog.format_type(a.atttypid, a.atttypmod),
  (SELECT pg_catalog.pg_get_expr(d.adbin, d.adrelid, true)
   FROM pg_catalog.pg_attrdef d
   WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef),
  a.attnotnull,
  (SELECT c.collname FROM pg_catalog.pg_collation c, pg_catalog.pg_type t
   WHERE c.oid = a.attcollation AND t.oid = a.atttypid AND a.attcollation <> t.typcollation) AS attcollation,
  a.attidentity,
  a.attgenerated,
  a.attstorage,
  a.attcompression AS attcompression,
  CASE WHEN a.attstattarget=-1 THEN NULL ELSE a.attstattarget END AS attstattarget,
  pg_catalog.col_description(a.attrelid, a.attnum)
FROM pg_catalog.pg_attribute a
WHERE a.attrelid = '49302' AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY a.attnum;
    attname    |       format_type       |              pg_get_expr               | attnotnull | attcollation | attidentity | attgenerated | atts
torage | attcompression | attstattarget | col_description 
---------------+-------------------------+----------------------------------------+------------+--------------+-------------+--------------+-----
-------+----------------+---------------+-----------------
 id            | integer                 | nextval('post_toast_id_seq'::regclass) | t          |              |             |              | p   
       |                |               | 
 long_text     | text                    |                                        | f          |              |             |              | e   
       |                |               | 
 large_varchar | character varying(8000) |                                        | f          |              |             |              | e   
       |                |               | 
 json_data     | jsonb                   |                                        | f          |              |             |              | e   
       |                |               | 
 other_column  | integer                 |                                        | f          |              |             |              | p   
       |                |               | 
(5 rows)

DBA: 没有，没看明白？ 怎么弄的，不告诉我，你太坏了！

总结：“君子深造之以道，欲其自得之也。自得之，则居之安，居之安，则资之深，资之深，则取之左右逢其原。”

这里TOAST 有几个问题，我们需要进行深入

1  TOAST的目的是为了保证一行数据可以存在在一个页面内（8KB）,因为一行数据如果存储在两个页面内，是无法进行寻址的。

2 为了保证一行数据一定可以存储在8KB的页面内，对于一些大型的字段，数据等就不能完全存储在数据的页面内。需要将这些数据存储在其他的文件页面内，后续进行数据的调用。

3 数据存在在字段内的存储模式是可以调节的，PG支持4种方式，分别是PLAIN, EXTERNAL,EXTENED,MAIN。只要将字段的存储模式调整到 plain 或者 main ，只要你的字段够大，那么一定会出现上面的报错信息，因为一个行已经超过了一个页面存储他的极限。

所以我们选择的行存储模式，应该在external ,extended 两个钟选择，一般我们都是选择extended,对数据进行压缩后，存储到TOAST的文件中。

4 TOAST 是非常优秀的针对大数据的解决方案

这话的分两头说，普通的数据库产品都要有兼容性，不能说我的字段特别大就无法使用，而要借助其他的数据库产品来进行处理，必须有兼容性的方案。所以TOAST如果在数据库的功能上来说，属于“提供的功能”，如果说擅长的功能，我个人觉得提不上。如果擅长处理大容量的字段的数据库我首推MONGODB （一行数据的容量是16MB为最大值）

5 TOAST 存储数据有什么参数可以进行调节

test=# select name,setting,context,enumvals from pg_settings where name like '%toast%';
           name            | setting | context |  enumvals  
---------------------------+---------+---------+------------
 default_toast_compression | pglz    | user    | {pglz,lz4}
(1 row)

在postgresql 16 中已经支持了LZ4的压缩算法，所以我们可以在安装数据库以及配置时注意配置为LZ4作为默认压缩的算法，这样压缩比会更大，更节省空间。

6 TOAST 可以调节触发TOAST存储的参数

test=#  SELECT (
        SELECT substring(option_value FROM 'toast_tuple_target=([0-9]+)')
        FROM unnest(reloptions) AS option_value
        WHERE option_value LIKE 'toast_tuple_target=%'
    ) AS toast_tuple_target
    FROM pg_class
    WHERE relname = 'post_toast';
 toast_tuple_target 
--------------------
 4096
(1 row)

toast_tuple_target 是一个目标值，是一个表级别的存储参数，TOAST的过程会尽力将行大小压缩在这个目标值之下。

那么这个值的作用是什么，如果你的这个列经常被读取，且你希望每次更少调用TOAST的页面，则可以调大这个值，让存储和调用数据的时候，尽量避免和TOAST打交道。

postgres=# select name,setting from pg_settings where name like '%toast%';;
           name            | setting 
---------------------------+---------
 default_toast_compression | pglz
(1 row)

postgres=# set default_toast_compression = 'lz4';
SET
postgres=# select name,setting from pg_settings where name like '%toast%';;
           name            | setting 
---------------------------+---------
 default_toast_compression | lz4
(1 row)

postgres=# 

问题： 一个表中，大部分的字段都是TEXT，VARCHAR(8000),那么我对toast_tuple_target 应该是调大呢 ，还是调小呢？ 阁下您的选择是什么，为什么？