There is a three-day vacuum freeze on a partition table with wait_event = BufferPin, no transactions (active or idle) older than it that are not blocked, but at least one select query is executing at all times related of this partition table.
This behaviour happens with some partitions, but not always. Running simply vacuum works because it does not wait for BufferPin.
I just expect to see vacuum freeze wait_event = BufferPin if there is any old transaction related to the table, am I wrong?
Is there a wait to figure out which session vacuum freeze to wait for?
Below is a list of all sessions at a particular moment in time; at this point, there are only Select queries with query starts older than the vacuum freeze, and no cursor, transaction block, or zombie sessions.
SELECT pg.query_start, now() - query_start AS duration, pg.pid pg_pid, pg.state, pg.wait_event, backend_type ,
pg.wait_event_type, pg_blocking_pids(pid) as blocked_by,SPLIT_PART(trim(pg.query),' ', 1)
FROM pg_stat_activity pg order by now() - query_start desc;
query_start | duration | pg_pid | state | wait_event | backend_type | wait_event_type | blocked_by | split_part
----------------------------------------------+----------------------------+-----------+--------+------------------------------+--------------------------------------+-----------------------+------------+------------
| | 249 | | AutoVacuumMain | autovacuum launcher | Activity | {} |
| | 252 | | LogicalLauncherMain | logical replication launcher | Activity | {} |
| | 247 | | BgWriterMain | background writer | Activity | {} |
| | 250 | | ArchiverMain | archiver | Activity | {} |
| | 246 | | CheckpointWriteDelay | checkpointer | Timeout | {} |
| | 248 | | WalWriterMain | walwriter | Activity | {} |
2024-01-27 23:10:54.159577+00 | 3 days 18:55:48. | 271039 | active| BufferPin | client backend | BufferPin | {} | vacuum
2024-01-30 21:33:50.262265+00 | 20:32:52.490309 | 558810 | idle | ClientRead | client backend | Client | {} | SELECT
2024-01-31 16:53:17.695929+00 | 01:13:25.056645 | 641053 | idle | ClientRead | client backend | Client | {} | SELECT
2024-01-31 18:04:48.929097+00 | 00:01:53.823477 | 645812 | active | | client backend | | {} | SELECT
2024-01-31 18:05:02.221272+00 | 00:01:40.531302 | 645814 | active | | client backend | | {} | SELECT
2024-01-31 18:05:04.927131+00 | 00:01:37.825443 | 645925 | active | | client backend | | {} | SELECT
2024-01-31 18:05:12.088875+00 | 00:01:30.663699 | 645109 | active | | client backend | | {} | SELECT
2024-01-31 18:05:30.202305+00 | 00:01:12.550269 | 645695 | active | | client backend | | {} | SELECT
2024-01-31 18:05:47.872601+00 | 00:00:54.879973 | 645924 | active | | client backend | | {} | SELECT
2024-01-31 18:06:16.425286+00 | 00:00:26.327288 | 645593 | active | | client backend | | {} | SELECT
2024-01-31 18:06:18.286905+00 | 00:00:24.465669 | 646174 | idle | ClientRead | client backend | Client | {} | select
2024-01-31 18:06:24.878488+00 | 00:00:17.874086 | 644993 | active | | client backend | | {} | SELECT
2024-01-31 18:06:32.560233+00 | 00:00:10.192341 | 645590 | active | | client backend | | {} | SELECT
2024-01-31 18:06:34.068919+00 | 00:00:08.683655 | 646175 | idle | ClientRead | client backend | Client | {} | select
2024-01-31 18:06:36.159063+00 | 00:00:06.593511 | 645926 | active | | client backend | | {} | SELECT
2024-01-31 18:06:40.002643+00 | 00:00:02.749931 | 351 | idle | ClientRead | client backend | Client | {} | SELECT
2024-01-31 18:06:42.5886+00 | 00:00:00.163974 | 645212 | active | | client backend | | {} | WITH
2024-01-31 18:06:42.717906+00 | 00:00:00.034668 | 645207 | idle | ClientRead | client backend | Client | {} | SELECT
2024-01-31 18:06:42.721052+00 | 00:00:00.031522 | 645817 | idle | | client backend | | {} | SELECT
2024-01-31 18:06:42.723673+00 | 00:00:00.028901 | 644916 | idle | ClientRead | client backend | Client | {} | SELECT
2024-01-31 18:06:42.725967+00 | 00:00:00.026607 | 645815 | idle | ClientRead | client backend | Client | {} | (SELECT
2024-01-31 18:06:42.732666+00 | 00:00:00.019908 | 644752 | idle | ClientRead | client backend | Client | {} | SELECT
2024-01-31 18:06:42.733953+00 | 00:00:00.018621 | 645697 | idle | ClientRead | client backend | Client | {} | SELECT
2024-01-31 18:06:42.735012+00 | 00:00:00.017562 | 645193 | idle | ClientRead | client backend | Client | {} | SELECT
2024-01-31 18:06:42.735088+00 | 00:00:00.017486 | 645927 | idle | ClientRead | client backend | Client | {} | SELECT
2024-01-31 18:06:42.736222+00 | 00:00:00.016352 | 645221 | idle | ClientRead | client backend | Client | {} | SELECT
2024-01-31 18:06:42.741013+00 | 00:00:00.011561 | 645813 | active | | client backend | | {} | SELECT
2024-01-31 18:06:42.745033+00 | 00:00:00.007541 | 645238 | idle | ClientRead | client backend | Client | {} | (SELECT
2024-01-31 18:06:42.747161+00 | 00:00:00.005413 | 645596 | idle | ClientRead | client backend | Client | {} | SELECT
2024-01-31 18:06:42.747703+00 | 00:00:00.004871 | 645232 | idle | ClientRead | client backend | Client | {} | SELECT
2024-01-31 18:06:42.749182+00 | 00:00:00.003392 | 645820 | active | | client backend | | {} | SELECT
2024-01-31 18:06:42.750896+00 | 00:00:00.001678 | 645594 | idle | ClientRead | client backend | Client | {} | SELECT
2024-01-31 18:06:42.752015+00 | 00:00:00.000559 | 645620 | idle | ClientRead | client backend | Client | {} | SELECT
2024-01-31 18:06:42.752574+00 | 00:00:00 | 642199 | active | | client backend | | {} | SELECT
(40 rows)
SELECT * FROM pgstattuple('pg_catalog.pg_proc');
table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent
-----------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+--------------
851968 | 3450 | 799839 | 93.88 | 26 | 11602 | 1.36 | 10960 | 1.29
SELECT * FROM pgstattuple('[table_name]');
table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent
-----------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+--------------
922165248 | 11679432 | 805880808 | 87.39 | 0 | 0 | 0 | 31376484 | 3.4
SELECT * FROM pgstatindex('[index_name_01]');
version | tree_level | index_size | root_block_no | internal_pages | leaf_pages | empty_pages | deleted_pages | avg_leaf_density | leaf_fragmentation
---------+------------+------------+---------------+----------------+------------+-------------+---------------+------------------+--------------------
4 | 2 | 262348800 | 412 | 113 | 31911 | 0 | 0 | 90.09 | 0
SELECT * FROM pgstatindex('[index_name_02]');
version | tree_level | index_size | root_block_no | internal_pages | leaf_pages | empty_pages | deleted_pages | avg_leaf_density | leaf_fragmentation
---------+------------+------------+---------------+----------------+------------+-------------+---------------+------------------+--------------------
4 | 2 | 381714432 | 351 | 226 | 46369 | 0 | 0 | 86.9 | 5.67
