【Postgre】如何查找DB（模式/表）最后更新的日期和时间（分析和清理是什么？）

3 年 ago
清, 扬
1 minute

简要概述

在PostgreSQL中，我们将介绍如何查找某个DB模式或表的最后更新时间。

执行方法

PostgreSQL提供了一个名为pg_stat_user_tables的系统视图，用于提供表的统计信息。使用这个视图，可以查看表的最后更新时间。

以以下形式执行SQL：
pg_stat_user_tables是PostgreSQL内部的系统目录视图之一，包含统计信息。
将相关的表放入relname中。

SELECT relname, last_autoanalyze, last_autovacuum, last_analyze, last_vacuum
FROM pg_stat_user_tables
WHERE relname = 'sample';

结果，结果如下。

image.png

如果要在整个架构中执行，请执行以下操作。

SELECT schemaname, relname, last_autoanalyze, last_autovacuum, last_analyze, last_vacuum
FROM pg_stat_all_tables
WHERE schemaname = 'public'

通过这个，你可以在每张表中知道最后更新的日期和时间。

自動解析和自動吸塵是什麼？

以上描述中，记录了针对表格进行的最近一次自动分析和自动VACUUM等操作的日期和时间，分别保存在名为last_autoanalyze和last_autovacuum的列中。

那么，什么是自动解析和自动吸尘？

PostgreSQL VACUUM及ANALYZE最佳实践技巧

以上内容是有关英文网站的详细描述。

PostgreSQL中有两个重要的功能，自动解析和自动清理，它们都是为了保持数据库的性能而存在的。

    • 自動解析：PostgreSQLがテーブルの統計情報（statistics)を自動的に更新するプロセス

統計情報とは：テーブル内のデータの分布や値の範囲などを示すもので、クエリプランナーがクエリの最適な実行計画を決定する際に使用される(=ベストなクエリプランのために使われる、ってことですね)
テーブル内のデータが一定の変更量を超えた場合にトリガーされる

一个”分析”操作就如其名所示 – 它分析数据库表的内容，并收集每个表每列数值的分布统计信息。PostgreSQL查询引擎使用这些统计信息来找到最佳的查询计划。随着在数据库中插入、删除和更新行，列统计信息也会改变。”分析”操作 – 无论是由数据库管理员手动运行还是在自动清理后由PostgreSQL自动运行 – 确保统计信息是最新的。

    • 自動バキューム：データベース内の不要なデータや行を削除し、データベースの容量を最適化するプロセス

データベース内の行の更新や削除により、不要なDead Rows（dead tuples）が残ることがある。これは、PostgreSQLがMVCC（Multi-Version Concurrency Control）と呼ばれる仕組みを使用しており、削除された行も実際には物理的に削除されずに残るため。データベースが不必要に肥大化し、パフォーマンスが低下する可能性があるため、自動バキュームによってクリーンアップを行う。
PostgreSQLは古い行を物理的にテーブルから削除するのではなく、それに”marker”のようなものを付けてクエリがその行を返さないようにしている
トランザクションIDの整理も行う。長期間にわたって実行されるトランザクションが増えるとデータベースのパフォーマンスに影響を及ぼす可能性があるため、不要な古いトランザクションIDに関連する情報を削除してデータベースの効率を向上させる。
データベース内で一定のしきい値を超えた場合にトリガーされる

在表中使用真空来恢复被“死元组”占用的空间。当记录被删除或更新时（先删除再插入），就会创建一个死元组。PostgreSQL并不会将旧行从表中物理删除，而是在其上放置一个“标记”，以防查询返回该行。当进行真空处理时，这些死元组所占用的空间会被标记为可供其他元组重用的空间。

还有一种方法是确定每个特定的阈值来启动。
根据先前的网站上的最佳实践，“Fine-tune Autovacuum Threshold”也提到了自定义阈值的重要性。

获取ANALYZE阈值信息的SQL查询

SELECT name, setting
FROM pg_settings
WHERE name = 'autovacuum_analyze_scale_factor'
   OR name = 'autovacuum_analyze_threshold';
image.png

如果表格的行数超过10%， 或者行数超过50行的情况。

获取自动吸尘器门限信息的SQL查询。

SELECT name, setting
FROM pg_settings
WHERE name = 'autovacuum_vacuum_scale_factor'
   OR name = 'autovacuum_vacuum_threshold';
image.png

如果表格的行数超过20%或者超过50行

bannerAds