In my PostgreSQL database, I have a table with a partition structure for a one-month time interval. In the 10th month, the data size of my partition doubled compared to other months. enter image description here
When I checked my dead rows, I observed that there are "20,488,757" dead rows. enter image description here
Config Parameters;
max_connections = 4096
- Memory -
max_prepared_transactions = 4096
work_mem = 2MB
maintenance_work_mem = 3GB
- Asynchronous Behavior -
effective_io_concurrency = 200
max_parallel_workers = 2
- Checkpoints -
min_wal_size = 2GB
max_wal_size = 3GB
checkpoint_completion_target = 0.9
- Planner Cost Constants
effective_cache_size = 48GB
- Sending Servers -
max_standby_archive_delay = 300s
max_standby_streaming_delay = 300s
- Planner Method Configuration -
random_page_cost = 1.1
- Query and Index Statistics Collector -
track_activity_query_size = 8192
In the 9th month, a prepared transaction was executed, and the value of the "max_prepared_transaction" parameter in the config file was set to 4096. I researched prepared transactions and found this article: https://www.cybertec-postgresql.com/en/prepared-transactions/
Based on the section titled "Problems caused by prepared transactions" in this article, when I checked the transactions in the database, I found the following: enter image description here
It seems that a prepared transaction was executed on September 24, 2023. Could these prepared transactions be the reason why Autovacuum cannot delete dead rows? How can I understand this? How can I solve the problem?
Yes, a prepared transaction will block VACUUM progress just like any other transaction. You should remove it with ROLLBACK PREPARED. See this article for details.
If your application cannot keep track of prepared transactions, it shouldn't be using them. It would also be a good idea to add some monitoring for prepared transactions, since they can bring your database down.
