Menu
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.
722
You can't run Pg in-process, in-memoryPostgreSQL is implemented in C and compiled to platform code. Unlike H2 or Derby you can't just load the jar and fire it up as a throwaway in-memory DB. Unlike SQLite, which is also written in C and compiled to platform code, PostgreSQL can't be loaded in-process either.
Postgres manages a “Shared Buffer Cache”, which it allocates and uses internally to keep data and indexes in memory. This is usually configured to be about 25% of total system memory for a server running a dedicated Postgres instance, such as all Heroku Postgres instances.
General Compatibility Notes. In some cases (obviously) MySQL is just too different from PostgreSQL to allow re-implementation of features. This usually happens when a MySQL feature would require changing the PostgreSQL SQL grammar.
Now, MySQL has InnoDB engine (with modern form of joins like other databases) and lot of arguments for using MySQL in-memory tables are obsolete. Against old MySQL Postgres has own buffers and doesn't bypass file system caches, so all RAM is available for your data and you have to do nothing.
Many of Postgres developers are looking for In-memory database or table implementation in PostgreSQL. But the truth is, This is not possible in PostgreSQL, and it doesn’t offer any in memory database or engine like SQL Server, MySQL. I read many different articles, and everyone is saying the same thing.
So you can create in-memory tables in specialized database and you can work with these tables from Postgres via foreign tables. MySQL memory tables was necessary when there was only MyISAM engine, because this engine has very primitive work with IO and MySQL had not own buffers.
Against old MySQL Postgres has own buffers and doesn't bypass file system caches, so all RAM is available for your data and you have to do nothing. Ten years ago we had to use MySQL inmemory engine to have good enough performance.
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).
137
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
