PostgreSQL temporary table cache in memory?

Question

Context

I want to save some temporary results in some temporary tables. These tables can be reused in several queries, which can happen close in time, but at some point, the evolutionary algorithm that I use may no longer need the old tables and continue to generate new tables. There will be several queries, possibly using these tables at the same time. Only one user performs all of these requests. I don't know if this clarifies everything about sessions, etc., I'm still not sure how this works.

goal

I would like to create temporary tables (if they do not exist yet), save them in memory as much as possible, and if at some point there is not enough memory, delete those that will be (I think they will be used recently) .

<strong> Examples:

The client will fulfill requests for EMA with different parameters and their aggregation with different coefficients, each of which can vary depending on the coefficients used, and therefore the parameters for EMA can be repeated, since they are still in the gene pool and may not be needed after some time . There will be similar queries with a large number of parameters, and the genetic algorithm will find the correct values ​​for the parameters.

Questions

• Is this what "on commit drop" means? I saw descriptions about sessions and transactions, but I do not really understand these concepts. Sorry if the question is stupid.
• If this is not the case, do you know of any simple way to get Postgres? this is?

Bypass

In the worst case, I should be able to evaluate how many tables I can store in memory and try to implement LRU myself, but it will never be as good as what Postgres could do.

Thank you very much.

Answer 1

This is a complex topic and probably one of them for discussion to some extent. I think it’s worth explaining why PostgreSQL does not support this, as well as what you can do instead of the latest versions to fit what you are trying to do.

PostgreSQL has a pretty good approach to caching a variety of datasets for multiple users. In the general case, you do not want the programmer to indicate that the temporary table should be stored in memory if it becomes very large. However, temporary tables are managed completely differently than regular tables:

• Buffered by separate content rather than shared buffers

• Only local visibility and

• Unkind.

This means that usually you do not generate a lot of disk I / O for temporary tables. Tables do not usually clear WAL segments, and they are controlled by local content, so they do not affect the use of a shared buffer. This means that only sometimes data is written to disk and only if necessary to free memory for other (usually more frequent) tasks. You, of course, do not force to write disks and read only disks when something else uses memory.

In the end, you do not need to worry about it. PostgreSQL is already trying to do what you ask to some extent, and temporary tables have much lower disk I / O than standard tables do. This does not make the tables remain in memory, though, and if they become large enough, the pages may expire in the OS cache and, ultimately, on disk. This is an important feature because it ensures that performance is correctly degraded when many people create many large temporary tables.