Is it possible to have a temporary, on-the-fly PostgreSQL table which only ever lives in RAM?

I know that PostgreSQL automatically uses RAM only for tables that are small enough. But I'm not just talking about the data inside the table, but about the entire table itself.

Basically, I've (poorly) re-implemented various database functionality in PHP arrays, for example when I fetch data from an API and want to sort it or "massage" it before displaying it in my control panel. In such a situation, it makes no sense (at least to me) to have an actual database table around which is only ever used for this, with temporary data. It would be much better if I could on-the-fly create a table which I fill up in RAM, sort and then fetch records from with normal PG SQL queries.

Is this a thing? It feels stupid that I have array structures and various functions that try to mimic SQL's "SORT BY".

Naturally, I'm not talking about executing CREATE TABLE, add the data to it, and then return it again to PHP, and then DROP TABLE. That would be ridiculously bad for performance.

If there is no way to do this, I'll just accept it, but it's something which I've often thought would make perfect sense.

You might be missing the obvious candidate: a TEMPORARY TABLE.

It lives in memory if enough RAM has been allocated with the temp_buffers setting. Only the current session can see it, no WAL is written for it (which is the main reason why it's faster) and it is dropped at the end of the session automatically.

If it must live past the current session, consider an UNLOGGED TABLE instead.

Be aware that temp tables are not covered by autovacuum. See:

• Are regular VACUUM ANALYZE still recommended under 9.1?

But there are still entries in regular system catalogs, so it's not 100 % "in RAM".

Details in the manual for CREATE TABLE.

StackExchange Database Administrators Q#285076, answer score: 4