PostgreSQL 10.0 preview 功能增强 - SQL执行剩余时间 - 垃圾回收过程可视pg_stat_progress_vacuum
背景
在PostgreSQL中执行SQL时，如果SQL执行时间比较长，总想知道它执行到哪里了，还剩余多少任务，剩余多少时间。
目前PostgreSQL增加了对VACUUM的可视监控(目前涵盖了autovacuum与手工的vacuum命令，但是不涵盖VACUUM FULL)，将来还会加入更多的progress动态视图，方便用户了解LONG SQL或者任务的执行情况。
动态视图解释如下
可以了解每个vacuum worker工作进程在工作过程中，扫描了多少HEAP PAGE, 回收了多少PAGE，index的vacuum关卡次数（每个关卡的大小取决于maintenance_work_mem可以存储的heap tuple数量）。
Table 28.21. pg_stat_progress_vacuum View
| Column | Type | Description |
|---|---|---|
| pid | integer | Process ID of backend. |
| datid | oid | OID of the database to which this backend is connected. |
| datname | name | Name of the database to which this backend is connected. |
| relid | oid | OID of the table being vacuumed. |
| phase | text | Current processing phase of vacuum. See Table 28.22. |
| heap_blks_total | bigint | Total number of heap blocks in the table. This number is reported as of the beginning of the scan; blocks added later will not be (and need not be) visited by this VACUUM. |
| heap_blks_scanned | bigint | Number of heap blocks scanned. Because the visibility map is used to optimize scans, some blocks will be skipped without inspection; skipped blocks are included in this total, so that this number will eventually become equal to heap_blks_total when the vacuum is complete. This counter only advances when the phase is scanning heap. |
| heap_blks_vacuumed | bigint | Number of heap blocks vacuumed. Unless the table has no indexes, this counter only advances when the phase is vacuuming heap. Blocks that contain no dead tuples are skipped, so the counter may sometimes skip forward in large increments. |
| index_vacuum_count | bigint | Number of completed index vacuum cycles. |
| max_dead_tuples | bigint | Number of dead tuples that we can store before needing to perform an index vacuum cycle, based on maintenance_work_mem. |
| num_dead_tuples | bigint | Number of dead tuples collected since the last index vacuum cycle. |
过程术语解释
Table 28.22. VACUUM phases
| Phase | Description |
|---|---|
| initializing | VACUUM is preparing to begin scanning the heap. This phase is expected to be very brief. |
| scanning heap | VACUUM is currently scanning the heap. It will prune and defragment each page if required, and possibly perform freezing activity. The heap_blks_scanned column can be used to monitor the progress of the scan. |
| vacuuming indexes | VACUUM is currently vacuuming the indexes. If a table has any indexes, this will happen at least once per vacuum, after the heap has been completely scanned. It may happen multiple times per vacuum if maintenance_work_mem is insufficient to store the number of dead tuples found. |
| vacuuming heap | VACUUM is currently vacuuming the heap. Vacuuming the heap is distinct from scanning the heap, and occurs after each instance of vacuuming indexes. If heap_blks_scanned is less than heap_blks_total, the system will return to scanning the heap after this phase is completed; otherwise, it will begin cleaning up indexes after this phase is completed. |
| cleaning up indexes | VACUUM is currently cleaning up indexes. This occurs after the heap has been completely scanned and all vacuuming of the indexes and the heap has been completed. |
| truncating heap | VACUUM is currently truncating the heap so as to return empty pages at the end of the relation to the operating system. This occurs after cleaning up indexes. |
| performing final cleanup | VACUUM is performing final cleanup. During this phase, VACUUM will vacuum the free space map, update statistics in pg_class, and report statistics to the statistics collector. When this phase is completed, VACUUM will end. |
这个patch的讨论，详见邮件组，本文末尾URL。
PostgreSQL社区的作风非常严谨，一个patch可能在邮件组中讨论几个月甚至几年，根据大家的意见反复的修正，patch合并到master已经非常成熟，所以PostgreSQL的稳定性也是远近闻名的。
参考
https://www.postgresql.org/docs/devel/static/progress-reporting.html#vacuum-progress-reporting