The 1C platform manages temporary tables in PostgreSQL by reusing them within a database connection to save resources. When a query creates a temporary table, the platform checks if a table with the same structure already exists. If it does, the system skips the creation step and reuses the existing table.
The function SELECT FASTTRUNCATE is used to clear temporary tables for subsequent reuse. It performs an exclusive lock on the table, truncates its data, and updates statistics if the table was previously populated. The platform invokes this function automatically when a query finishes or when a temporary table manager is closed.
Analysis of a production ERP database with a size of 700 GB revealed that the platform creates nearly 5 million temporary tables per day. Research shows that 32% of these tables are empty at the time of the cleanup call. The execution of SELECT FASTTRUNCATE on empty tables creates unnecessary overhead.
In one case study, migrating a database to PostgreSQL resulted in a 70% performance degradation compared to MS SQL. The bottleneck was identified as the frequent execution of SELECT FASTTRUNCATE on empty temporary tables. Optimization was achieved by preventing the execution of queries that would result in empty temporary tables.
Monitoring tools like Tantor show that SELECT FASTTRUNCATE can consume up to 12.9% of total CPU time for specific queries. The primary time-consuming operation within the function is the heap_truncate process. Experts suggest that modifying the function to skip truncation for already empty tables could improve performance.
