Table placed only in RAM, visibility for all
Hello. I have managed to achieve this by creating a new instance and them creating a tablespace on a ramfs mount. I created a materialized view on the ramfs instance and then I rebuild the view every time I start PG there... IMPORTANT: Creating a tablespace consisting of RAM with your usual database might endanger your consistency. Use another instance!
Nathan Clayton commented
What would be really nice would be a table hint to indicate that it should be loaded into memory and kept there once the system loads up without having to create and execute any queries to do so. This would be ideal for dimension tables that are frequently accessed (e.g. date/time dimensions), but you didn't necessarily want to loose upon system shutdown. Maybe another hint to indicate that the table is to be memory-only and not saved to disk.
Something like `create table ... with inmemory` and `create table ... with inmemory nodisk`
Daniel Caldeweyher commented
Just create a tablespace on /dev/shm with a low page cost
Darren Duncan commented
It should be possible to do this both with private table variables and public/shared ones. On the other hand, if Postgres provides the ability to selectively say that some tables are not logged and otherwise hint that they are small or should be kept in RAM where possible, that should meet the goal, perhaps.
In-memory tables - good way to improve perfomance.