19

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

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Handsome Nerd
  • 17,114
  • 22
  • 95
  • 173

3 Answers3

20

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.

Andrew Lazarus
  • 18,205
  • 3
  • 35
  • 53
  • According to https://www.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!!! – Sebastien Sep 18 '19 at 14:50
  • Oh, it's dangerous. I wonder if it is safer to use a Memory Mapped file if on an OS where this is supported. – Andrew Lazarus Sep 18 '19 at 16:12
  • 1
    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? – Sebastien Sep 20 '19 at 07:43
12

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).

Pablo Santa Cruz
  • 176,835
  • 32
  • 241
  • 292
  • Thanks a lot for quick answer. – Handsome Nerd Oct 16 '11 at 17:17
  • 17
    Except that "global" is just noise in PostgreSQL. Possibly useful for compatibility with other RDBMS. I [quote the manual](http://www.postgresql.org/docs/9.1/interactive/sql-createtable.html#SQL-CREATETABLE-COMPATIBILITY): `Optionally, GLOBAL or LOCAL can be written before TEMPORARY or TEMP. This makes no difference in PostgreSQL, ...` – Erwin Brandstetter Oct 16 '11 at 17:33
  • Do you know if there is a setting for temporary tables to limit how much of it goes to memory? – tothphu Jun 29 '12 at 04:32
5

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").

yzorg
  • 4,224
  • 3
  • 39
  • 57
  • @PabloSantaCruz If you fork your answer to allow us to "vote" on `UNLOGGED` I'd be happy to delete this duplicate answer. – yzorg Jul 19 '18 at 16:05