我继承了一个系统的维护，有一个大得离谱的TOAST表。 对于大约 400 MB 的表和索引大小，Toast 为 631GB。
clientportal=# SELECT oid, table_schema, table_name,
total_bytes,
pg_size_pretty(total_bytes) AS total
, pg_size_pretty(index_bytes) AS INDEX
, pg_size_pretty(toast_bytes) AS toast
, pg_size_pretty(table_bytes) AS TABLE
FROM (
SELECT *, total_bytes-index_bytes-COALESCE(toast_bytes,0) AS table_bytes FROM (
SELECT c.oid,nspname AS table_schema, relname AS TABLE_NAME
, c.reltuples AS row_estimate
, pg_total_relation_size(c.oid) AS total_bytes
, pg_indexes_size(c.oid) AS index_bytes
, pg_total_relation_size(reltoastrelid) AS toast_bytes
FROM pg_class c
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE relkind = 'r'
) a
) a order by total_bytes desc;
oid | table_schema | table_name | total_bytes | total | index | toast | table
---------+--------------------+------------------------------------------+--------------+------------+------------+------------+------------
17202 | public | phones | 678297559040 | 632 GB | 119 MB | 631 GB | 280 MB
使用对所有表执行pg_size_pretty(sum(pg_column_size($columnName)))的查询，我看不出哪一列可能使用那么多。 所以我可能遇到了该功能的限制或误解：
-[ RECORD 1 ]---------+-----------
access_info | 153 kB
activation_date | 1291 kB
autocreate | 835 kB
btn_id | 3409 kB
callerid | 5954 kB
calling_firstname | 1 bytes
calling_lastname | 1 bytes
connectiontype_id | 1 bytes
created_at | 6985 kB
did_supplier_id | 17 kB
e911_notes | 274 kB
e911_tested | 70 bytes
e911_tested_by_id |
e911_tested_date |
email | 2431 kB
extension | 2020 kB
firstname | 152 kB
foreign_pid | 16 kB
foreign_userid | 10 kB
human_validation_date | 176 bytes
huntgroup | 873 kB
id | 3492 kB
incoming_toll | 873 kB
ipaddress | 1 bytes
lastname | 4406 bytes
line_of_business | 305 kB
local_rate_plan_id | 6264 bytes
national_rate_plan_id |
notes | 114 MB
phone_mac_addr | 5041 kB
phone_number | 9656 kB
phone_type_id |
rate_center_id | 4 bytes
service_type | 3786 bytes
sip_password | 22 MB
sip_username | 8919 kB
site_id | 3480 kB
termination_date | 2217 kB
updated_at | 6985 kB
virtualdid | 873 kB
voicemail_number | 1268 kB
voicemailportal | 873 kB
world_rate_plan_id |
我怀疑sip_password或notes列正在将该行发送到 toast。 这并不能证明我希望将这些列设置为 null 会减少 TOAST 的使用，但经过研究，我假设这不会发生或需要VACCUM FULL
- 有没有办法确定哪列或哪行正在使用那么多 TOAST 空间？
- 将某些文本列设置为 null 后，我必须运行什么来减小 TOAST 大小？
更新：如果d+重要(但它不能完全回答这个问题(：
clientportal=# d+ phones
Table "public.phones"
Column | Type | Modifiers | Storage | Stats target | Description
-----------------------+-----------------------------+-----------------------------------------------------+----------+--------------+-------------
id | integer | not null default nextval('phones_id_seq'::regclass) | plain | |
created_at | timestamp without time zone | | plain | |
updated_at | timestamp without time zone | | plain | |
site_id | integer | | plain | |
btn_id | integer | | plain | |
phone_number | character varying(255) | | extended | |
extension | character varying(255) | | extended | |
voicemail_number | character varying(255) | | extended | |
access_info | character varying(255) | | extended | |
notes | text | | extended | |
activation_date | date | | plain | |
termination_date | date | | plain | |
phone_mac_addr | character varying(255) | | extended | |
phone_type_id | integer | | plain | |
sip_username | character varying(255) | | extended | |
e911_notes | character varying(255) | | extended | |
e911_tested | boolean | | plain | |
e911_tested_by_id | integer | | plain | |
e911_tested_date | date | | plain | |
did_supplier_id | integer | | plain | |
email | character varying(255) | | extended | |
ipaddress | character varying(255) | | extended | |
connectiontype_id | character varying(255) | | extended | |
sip_password | text | | extended | |
virtualdid | boolean | default false | plain | |
voicemailportal | boolean | default false | plain | |
callerid | character varying(255) | default NULL::character varying | extended | |
autocreate | boolean | default false | plain | |
huntgroup | boolean | default false | plain | |
firstname | text | | extended | |
lastname | text | | extended | |
calling_firstname | text | | extended | |
calling_lastname | text | | extended | |
foreign_userId | text | | extended | |
local_rate_plan_id | integer | | plain | |
national_rate_plan_id | integer | | plain | |
world_rate_plan_id | integer | | plain | |
foreign_pid | integer | | plain | |
rate_center_id | integer | | plain | |
human_validation_date | timestamp without time zone | | plain | |
service_type | character varying | | extended | |
line_of_business | character varying | | extended | |
incoming_toll | boolean | default false | plain | |
Indexes:
"phones_pkey" PRIMARY KEY, btree (id)
"index_phones_on_id" UNIQUE, btree (id)
"index_phones_on_btnid" btree (btn_id)
"index_phones_on_extension" btree (extension)
"index_phones_on_foreign_pid" btree (foreign_pid)
pg_column_size不测量烤注值的大小，只测量吐司指针的大小。
但是，您可以通过将其输出与通过使用length()可以获得的实际列大小进行比较来利用这一点。
它可能是varchar(255)列，但主要嫌疑人是text列和character varying列(没有长度(。
如果你没有发现任何这种方式，吐司桌上也有膨胀的可能性。使用 pgstattuple 扩展来衡量这一点。