PostgreSQL equivalent of MySQL memory tables?

Question

Does PostgreSQL have an equivalent of MySQL memory tables?

These MySQL memory tables can persist across sessions (i.e., different from temporary tables which drop at the end of the session). I haven't been able to find anything with PostgreSQL that can do the same.

possible duplicate of [in-memory table in PostgreSQL](http://stackoverflow.com/questions/7785419/in-memory-table-in-postgresql) — fancyPants, Jun 11 '13 at 08:44
I'd say this is not a duplicate. A MySQL memory table refers to something more specific than a table that is merely created on a RAM disk: http://dev.mysql.com/doc/refman/5.5/en/memory-storage-engine.html — Elliot B., Jul 29 '13 at 19:42

score 41 · Accepted Answer · edited Jul 31 '12 at 01:40

41

No, at the moment they don't exist in PostgreSQL. If you truly need a memory table you can create a RAM disk, add a tablespace for it, and create tables on it.

If you only need the temporary table that is visible between different sessions, you can use an UNLOGGED table. These are not true memory tables but they'll behave surprisingly similarly when the table data is significantly smaller than the system RAM.

Global temporary tables would be another option but are not supported in PostgreSQL as of 9.2 (see comments).

edited Jul 31 '12 at 01:40

Craig Ringer

259,831
56
584
684

answered Jul 30 '12 at 21:24

aleroot

66,082
27
164
205

5

We've been talking about adding global temporary tables in 9.3 as a new feature. In existing releases the keyword is accepted, but ignored. The closest you can come in PostgreSQL is an `UNLOGGED` table. http://www.postgresql.org/docs/current/interactive/sql-createtable.html – kgrittn Jul 30 '12 at 22:15
Oh, i didn't know it is ignored now ... Thank you, i thought was already working. Thanks . – aleroot Jul 30 '12 at 22:26
1

Updated answer to reflect @kgrittn's comments. Hope that's OK. Added some docs links too. – Craig Ringer Jul 31 '12 at 01:42
1

Also it looks like Postgresql [v10 will provide][1] Pluggable storage engine (columnar, in-memory, etc) [1]: https://www.infoq.com/news/2017/04/postgresql-10-features – MegaTux Apr 24 '17 at 19:40
1

The [docs](https://www.postgresql.org/docs/11/manage-ag-tablespaces.html) say that you should NOT use transient storage for a tablespace... _"The location must not be on removable or transient storage, as the cluster might fail to function if the tablespace is missing or lost."_ – user9645 Apr 16 '19 at 18:44
Isn't that feature added yet? On SQL Server, Microsoft even added a *persisted* in-memory table implementation called "in-memory OLTP"! – user2173353 Sep 02 '20 at 10:44

score 18 · Answer 2 · edited Mar 16 '20 at 20:29

Answering a four year old question but since it comes on top of google search results even now.

There is no built in way to cache a full table in memory, but there is an extension that can do this.

In Memory Column Store is a library that acts as a drop in extension and also as a columnar storage and execution engine. You can refer here for the documentation. There is a load function that you can use to load the entire table into memory.

The advantage is the table is stored inside postgres shared_buffers, so when executing a query postgres immediately senses that the pages are in memory and fetches from there.

The downside is that shared_buffers is not really designed to operate in such a way and instabilities might occur (usually it doesn't), but you can probably have this in a secondary cluster/machine with this configuration just to be safe.

All other usual caveats about postgres and shared_buffers still apply.

PostgreSQL equivalent of MySQL memory tables?

2 Answers2

Linked