In-memory table in PostgreSQL

Question

How can I create an in-memory table in PostgreSQL?

Dross

Answer 1

Create a RAM disk using software appropriate to your OS. Use CREATE TABLESPACE to create a DB cluster on the RAM disk. When you create your table, use the TABLESPACE clause. Obviously, your RAM tables will not persist across system reboots unless you save the RAM disk.

Rhinehart

According to 2ndquadrant.com/en/blog/postgresql-no-tablespaces-on-ramdisks this is a dangerous thing to do as it may prevent recovery of other tablespaces as replay of the Write Ahead Log could fail!!! – Kashakashden

Oh, it's dangerous. I wonder if it is safer to use a Memory Mapped file if on an OS where this is supported. – Rhinehart

Maybe combining ramdisk tablespace with unlogged table would avoid the issue as in theory WAL should not contain anything for this table so the replay could not fail? – Kashakashden

Answer 2

Well, it's not technically a in memory table, but, you can create a global temporary table:

create global temporary table foo (a char(1));

It's not guaranteed that it will remain in memory the whole time, but it probably will (unless is a huge table).

You can also consider PostgreSQL 9.1's unlogged tables, which will give you better performance at the cost of not being able to be part of transactions (their write operations are not maintained in WAL).

Versus

Thanks a lot for quick answer. – Dross

Except that "global" is just noise in PostgreSQL. Possibly useful for compatibility with other RDBMS. I quote the manual: Optionally, GLOBAL or LOCAL can be written before TEMPORARY or TEMP. This makes no difference in PostgreSQL, ... – Sensate

Do you know if there is a setting for temporary tables to limit how much of it goes to memory? – Ase

Answer 3

You can also consider PostgreSQL 9.1's unlogged tables, which will give you better performance at the cost of not being able to be part of transactions (their write operations are not maintained in WAL).

from @PabloSantaCruz

sample from https://www.compose.com/articles/faster-performance-with-unlogged-tables-in-postgresql/

CREATE UNLOGGED TABLE "EUR/USD_ticks"  
(
  dt timestamp without time zone NOT NULL,
  bid numeric NOT NULL,
  ask numeric NOT NULL,
  bid_vol numeric,
  ask_vol numeric,
  CONSTRAINT "EUR/USD_ticks_pkey" PRIMARY KEY (dt)
)

I plan to try this, and thought it deserved it's own answer for users to vote on this option (i.e. the "long tail").

Scarlatina

@PabloSantaCruz If you fork your answer to allow us to "vote" on UNLOGGED I'd be happy to delete this duplicate answer. – Scarlatina