postgresql 如何检查 TOAST 是否正在处理 postgres 中的特定表

How to check if TOAST is working on a particular table in postgres

提问by jindal

I have a table that contains two text fields which hold a lot of text. For some reason our table have started growing exponentially. I suspect that TOAST (compression for text fields in postgres) is not working automatically. In our table definition we have not defined any clause to force compression of these fields. Is there a way to check if compression is working on that table or not?

我有一个包含两个文本字段的表格，其中包含大量文本。出于某种原因，我们的表开始呈指数增长。我怀疑 TOAST（postgres 中文本字段的压缩）不能自动工作。在我们的表定义中，我们没有定义任何子句来强制压缩这些字段。有没有办法检查压缩是否在该表上工作？

采纳答案by Mike Sherrill 'Cat Recall'

From the docs. . .

从文档。. .

If any of the columns of a table are TOAST-able, the table will have an associated TOAST table, whose OID is stored in the table's pg_class.reltoastrelid entry. Out-of-line TOASTed values are kept in the TOAST table, as described in more detail below.

如果表的任何列是 TOAST-able，该表将有一个关联的 TOAST 表，其 OID 存储在表的 pg_class.reltoastrelid 条目中。外部 TOAST 值保存在 TOAST 表中，如下文更详细的描述。

So you can determine whether a TOAST table exists by querying the pg_class system catalog. This should get you close to what you're looking for.

所以你可以通过查询pg_class 系统目录来确定一个 TOAST 表是否存在。这应该让你接近你正在寻找的东西。

select t1.oid, t1.relname, t1.relkind, t2.relkind, t2.relpages, t2.reltuples
from pg_class t1
inner join pg_class t2
on t1.reltoastrelid = t2.oid
where t1.relkind = 'r'
  and t2.relkind = 't';

In psql, you can use \d+. I'll use the pg_class system catalog as an example; you'd use your own table name.

在 psql 中，您可以使用\d+. 我将使用 pg_class 系统目录作为示例；你会使用你自己的表名。

sandbox=# \d+ pg_class
     Column     |   Type    | Modifiers | Storage  | Stats target | Description 
----------------+-----------+-----------+----------+--------------+-------------
 relname        | name      | not null  | plain    |              | 
 relnamespace   | oid       | not null  | plain    |              | 
 [snip]
 relacl         | aclitem[] |           | extended |              | 
 reloptions     | text[]    |           | extended |              | 

Where Storage is 'extended', PostgreSQL will try to reduce row size by compressing first, then by storing data out of line. Where Storage is 'main' (not shown), PostgreSQL will try to compress.

在“扩展”存储的情况下，PostgreSQL 将尝试通过首先压缩，然后通过存储外行数据来减少行大小。当 Storage 是 'main'（未显示）时，PostgreSQL 将尝试压缩。

In your particular case, you might find it useful to monitor changes in size over time. You can use this query, and save the results for later analysis.

在您的特定情况下，您可能会发现监控大小随时间的变化很有用。您可以使用此查询，并保存结果以供以后分析。

select table_catalog, table_schema, table_name, 
       pg_total_relation_size(table_catalog || '.' || table_schema|| '.' || table_name) as pg_total_relation_size,
       pg_relation_size(table_catalog || '.' || table_schema|| '.' || table_name) as pg_relation_size,
       pg_table_size(table_catalog || '.' || table_schema|| '.' || table_name) as pg_table_size
from information_schema.tables

PostgreSQL admin functionshas details about what each function includes in its calculations.

PostgreSQL 管理函数有关于每个函数在其计算中包含的内容的详细信息。

回答by Ramfjord

This is old, but I've recently had some success with a similar issue. ANALYZE VERBOSE revealed that a couple of our tables had grown to > 1 page of disk per tuple, and EXPLAIN ANALYZE revealed that sequential scans were taking up to 30 seconds on a table of 27K rows. Estimates of the number of active rows were getting further and further off.

这是旧的，但我最近在类似问题上取得了一些成功。ANALYZE VERBOSE 显示我们的几个表已经增长到每个元组超过 1 页的磁盘，并且 EXPLAIN ANALYZE 显示顺序扫描在 27K 行的表上花费了多达 30 秒。活动行数的估计越来越远。

After much searching, I learned that rows can only be vacuumed if there is no transaction that has been open since they were updated. This table was rewritten every 3 minutes, and there was a connection that was "idle in transaction" that was 3 days old. You can do the math.

经过多次搜索，我了解到只有在更新后没有打开的事务的情况下才能清空行。这张表每 3 分钟被重写一次，并且有一个“事务空闲”的连接是 3 天前的。你可以算一算。

In this case, we had to

在这种情况下，我们不得不

1. kill the connection with the open transaction
2. reconnectto the database. Unfortunately the maximum transaction ID for rows that can be vacuumed is currently (as of 9.3) stored in the connection, so vacuum full will not work.
3. VACUUUM FULL your table (this will take out an ACCESS EXCLUSIVE lock, which will block everything including reads. You may want to run VACUUM first (non-blocking), to speed up the time VACUUM FULL takes).

1. 终止与打开事务的连接
2. 重新连接到数据库。不幸的是，可以清空的行的最大事务 ID 当前（从 9.3 开始）存储在连接中，因此真空已满将不起作用。
3. VACUUUM FULL 您的表（这将取出一个 ACCESS EXCLUSIVE 锁，这将阻止包括读取在内的所有内容。您可能希望首先运行 VACUUM（非阻塞），以加快 VACUUM FULL 所需的时间）。

This may not have been your problem, but if you would like to see if tables are affected in your own database, I wrote a query to order tables by the average number of tuples stored in a page of disk. Tables with large rows should be at the top - ANALYZE VERBOSE should give you an idea of the ratio of dead to live tuples in these tables. Valid for 9.3- this will probably require some minor tweaks for other versions:

这可能不是您的问题，但如果您想查看您自己的数据库中的表是否受到影响，我编写了一个查询，以按存储在磁盘页面中的平均元组数对表进行排序。大行的表应该在顶部 - ANALYZE VERBOSE 应该让您了解这些表中死元组与活元组的比率。 适用于 9.3- 这可能需要对其他版本进行一些小的调整：

SELECT rolname AS owner,
       nspname AS schemaname
     , relname AS tablename
     , relpages, reltuples, (reltuples::FLOAT / relpages::FLOAT) AS tuples_per_page
FROM pg_class
JOIN pg_namespace ON relnamespace = pg_namespace.oid
JOIN pg_roles     ON relowner     = pg_roles.oid
WHERE relkind = 'r' AND relpages > 20 AND reltuples > 1000
  AND nspname != 'pg_catalog'
ORDER BY tuples_per_page;

回答by rogerdpack

You can see what queries psql runs by starting it with the -Eparameter, then running normal commands:

您可以通过使用-E参数启动 psql ，然后运行正常命令来查看 psql 运行的查询：

In this instance, the one that came down to was, first lookup your table's oid:

在这种情况下，归结为首先查找表的 oid：

SELECT c.oid,
  n.nspname,
  c.relname
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname ~ '^(YOUR_TABLE_NAME_HERE)$'
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 2, 3;

then it executes this to lookup more stats about it:

然后它执行此操作以查找有关它的更多统计信息：

SELECT a.attname,                                                                                                                                          pg_catalog.format_type(a.atttypid, a.atttypmod),
  (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)
   FROM pg_catalog.pg_attrdef d
   WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef),
  a.attnotnull, a.attnum,
  (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,
  NULL AS indexdef,
  NULL AS attfdwoptions,
  a.attstorage,
  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 = '57692' AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY a.attnum;

x.attrstorage is what you care about pis PLAIN, xis EXTENDED I wager.

x.attrstorage 是你关心的p是 PLAIN，x是 EXTENDED 我打赌。

回答by Denis de Bernardy

If vacuuming rhe table strips it to 19GB from 80GB, what you're likely seeing is MVCC at work: dead rows take up space until vacuumed or re-used.

如果清空表将其从 80GB 剥离到 19GB，您可能会看到 MVCC 在起作用：死行占用空间，直到被清空或重新使用。

http://wiki.postgresql.org/wiki/MVCC

http://wiki.postgresql.org/wiki/MVCC