6

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.

3 Answers 3

5

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:

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

Details in the manual for CREATE TABLE.

2

Database server RAM is always scarce, so it is best reserved for caching stuff that is needed often by many clients, like most accessed rows etc.

Postgres doesn't support RAM-only tables. But if it did, if you had lots of clients creating RAM tables, you'd have to be very careful not to use too much RAM on the server for data that only client uses, while at the same time tons of requests hit other data that could then be pushed out of cache, reducing overall performance.

So, normal or temporary tables are just fine for this. They'll stay in RAM if enough RAM is available, but the server can also write them to disk to free some buffers. If you use non-temporary tables, make sure you create them as UNLOGGED so writes don't go to the WAL. They won't be crash-proof, but that's not important for cache tables.

What you're saying makes sense though: it makes no sense to waste server CPU to do cosmetic work, if possible it is better to do it on the application side, because it is much easier to add webserver CPUs than database CPUs. The database's job is to handle data quickly, without caring too much how the table will be displayed on the application side. Besides, some presentation/cosmetics stuff is much easier to do on the application side.

If you still need to do some data massaging on the database side, you can use a temporary table, but you can also use CTEs, which are faster because no table creation is involved. Like:

WITH query1 AS (get some data)
SELECT something FROM query1

One important exception to the rule is the big slow search query that is then displayed using pagination. Often, the big slow search query will use as much database resources whether it returns one page or ten pages of results. Then it makes a lot of sense to cache the results in order to not redo the query on every page. This also applies to other kinds of slow queries, of course.

You can put that cache in the session, in memcached, or even in a special UNLOGGED table, basicaly anywhere that will be faster to retrieve than doing the big slow query.

0

Answer is a bit late but someone made a in memory postgresql!!

  1. https://github.com/electric-sql/pglite
  2. https://github.com/stackframe-projects/pgmock
  3. https://github.com/oguimbal/pg-mem

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.