advice create momery local temporary table

[Rinse Lemstra]

We would appreciate your advice.

In our application, we make intensive use of the construction involving temporary tables:

create memory local temporary table if not exists temp_report as select * from ( .... )

We use this, for instance, in reporting functionality. This allows calculated fields to behave like physical fields, enabling users to create filters based on the actual values found.

We also use this approach to keep complex queries readable and to prevent the query optimizer from making incorrect choices regarding indexes.

However, this construction has the drawback that the size of the database can grow very quickly, which has a dramatic effect on performance. A SHUTDOWN COMPACT resolves the problem completely — the database shrinks significantly in size, and performance improves again.

Unfortunately, SHUTDOWN COMPACT is disruptive in production and is not stable in the current version, see GitHub Issue #4247, which can even lead to data loss.

Ideally, we would like to move the use of these temporary tables completely outside of the regular database — for example, to a temporary database. Moving everything entirely to RAM is not feasible due to the number of users we need to support.

Does anyone have advice?

[Andrei Tokar]

Hello Rinse,

Firs of all, I think that you are mistaken about GitHub Issue #4247. It has nothing to do with SHUTDOWN COMPACT (which does a full database compaction), but rather regular SHUTDOWN (which dose some partial time-limited compaction using totally different algorithm).
As far as your architecture goes, It is not clear whether you need to join such temp table with the rest of the database, or just need filtering/sorting on a table alone? If joins are not needed, you can create temp database on a client side and put it there.