How to effectively set up autovacuum in Postgres for 1C

ALTER TABLE _accumrg16920 SET ( autovacuum_enabled = on, autovacuum_vacuum_scale_factor = 0, autovacuum_vacuum_threshold = 10000, autovacuum_analyze_scale_factor =0 , autovacuum_analyze_threshold = 10000 );

ALTER TABLE _accumrgtn17037 SET (autovacuum_enabled = on, autovacuum_vacuum_scale_factor = 0, autovacuum_vacuum_threshold = 2000, autovacuum_analyze_scale_factor =0 , autovacuum_analyze_threshold = 2000 );

Here I’m ignoring the scale_factor so that it doesn’t depend on the percentage, which I wrote about above.

Threshold picked up experimentally, running a synthetic test. This does not mean that the autovacuum will run every 10000 or 2000 records, it will run according to autovacuum_naptime (I have 20 seconds), and work if the vacuum threshold is exceeded = base vacuum threshold + fraction factor to clean up * number of tuples

where the base cleanup threshold is the autovacuum_vacuum_threshold value, the proportion factor is the autovacuum_vacuum_scale_factor, and the number of tuples is pg_class.reltuples .

In fact, there are even more details that can be read in the documentation. PostgreSQL: Documentation: 15:25.1. Scheduled Cleanup : Postgres Professional . You can increase the number of auto-cleaning processes, and various other tuning, but all this will lead to CPU loading and, as a result, premature exhaustion of the resources of one Instance. Let me remind you that in Postgres there is no way to scale Instance across multiple servers as in Oracle Real application cluster.

And now you can look at the result:

Autovacuum is triggered – there are no dead tuples, statistics are updated

The top DML statements have changed, it again has Fast truncate and Analyze temporary tables. Why 1C does analyze on temporary tables is still a rhetorical question.

All other DML statements went down the list, total_time also began to improve

CPU usage decreases

It’s so simple, on a single operation. Synthetic test, synthetic statistics.

How to focus on business logic when around …

For garbage collection, it is easy to forget that all development in 1C is done for the sake of implementing business logic, which is programmed in 1C at the level of 1C language / libraries (actually ORM). Current affairs become important and urgent precisely because of such cases with garbage. And the study of the full stack is a more forced measure, since the consequences of misunderstanding are visible even on a weak synthetic test. There are thousands of tables in 1C, and even if you set the parameters in a couple of dozen of the largest, this is also work.

Perhaps there are some alternative means in Postgres that take care of this, who knows?

Now the hype topic is GhatGTP and other AI topics, where is it in garbage collection? There is a place for beautiful algorithms, but the capitalist economy is arranged differently – more functionality, more consumers, more money. “The economy must be economical” – died along with the USSR. This can only be stopped by the high cost of DBMS support and specialists who understand how to make the DBMS work efficiently. System administrators and 1C programmers are not the first in the food chain, so the arrival of AI in the DBMS will have to wait. In the meantime, even for a vacuum cleaner robot, you must clean up the room so that it is comfortable for him. See you soon on our telegram channel t.me/Chat1CUnlimited new materials are waiting in line when they are written.