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
