Thanks for the advice. I tried to remove all indexes and constraints from the table - it did not help. I have a copy of the data (before truncate) - I can test any hypothesis
On Tue, 2025-05-20 at 16:48 +0300, Антон Глушаков wrote: > I encountered a very strange behavior. > For any query (even a simple count(*) to one specific table (a small 30MB table with 3 indexes, > without any specific data types - everything is standard out of the box vanilla Postgres) - > the query hangs dead. Waited more than 24 hours - the query did not complete). > > > Similarly, the vacuum process to the table hangs. > Only Kill -9 with a full restart helps > > I get a backtrace, from it - I then examined the pg_multixact directory, which at the time of > the problem had swelled to 900MB and had several thousand files. > I excluded long and inactive transactions, as well as prepared statements. > > The workaround in the end was this - truncate the table (it was successful), then vacuum freeze > each DB, and after that the files from pg_multixact disappeared. > > What could it be? vacuum\freeze\mulitxact settings are default. > At the same time, the value pg_database.datminmxid=1 > Could the problem with the hang be related to the many old files in pg_multixact ? (judging by the backtrace - yes)
I can't say for certain, but I have seen cases like that where index corruption sent processes into an endless loop. Next time you could try to rebuild the indexes.
