Menu
How can I create an in-memory table in PostgreSQL?
374
PostgreSQL allocates memory within memory contexts, which provide a convenient method of managing allocations made in many different places that need to live for differing amounts of time.
The in-memory database defined In-memory databases are purpose-built databases that rely primarily on memory for data storage, in contrast to databases that store data on disk or SSDs. In-memory data stores are designed to enable minimal response times by eliminating the need to access disks.
Unlogged tables is a PostgreSQL feature that can be used effectively to optimize bulk inserts. PostgreSQL uses Write-Ahead Logging (WAL). It provides atomicity and durability, by default. Atomicity, consistency, isolation, and durability make up the ACID properties.
Postgres primarily caches indexes based on how often they're used, and it will not use an index if the stats suggest that it shouldn't -- hence the need to analyze after an import. Giving Postgres plenty of memory will, of course, increase the likelihood it's in memory too, but keep the latter points in mind.
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.
190
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).
21
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
