In-memory tables / acceleration

Oracle

-- Keep the table in memory with Database In-Memory:
ALTER TABLE orders INMEMORY;
-- Memory size (init parameter):
ALTER SYSTEM SET inmemory_size = 4G SCOPE=SPFILE;

Oracle Database In-Memory speeds up analytics by keeping frequently queried tables in memory in columnar format (separate license). For OLTP, the Buffer Cache and the KEEP pool also keep frequently accessed blocks in memory. The in-memory solution preserves durability through the row format on disk.

PostgreSQL

-- Fast table without durability (writes no WAL):
CREATE UNLOGGED TABLE temp_calc (id int, data text);
-- Session-private temporary table (tends to stay in memory):
CREATE TEMP TABLE session_data (id int) ON COMMIT DELETE ROWS;

PostgreSQL offers no dedicated in-memory table engine; an UNLOGGED table skips WAL writes to speed up writing (its contents are lost on a crash; not durable). TEMP tables live for the session and are kept in memory where possible via temp_buffers. Hot data is cached in shared_buffers.

MySQL

-- Table using the MEMORY (HEAP) storage engine:
CREATE TABLE cache_table (
  id INT PRIMARY KEY,
  data VARCHAR(100)
) ENGINE=MEMORY;

The MySQL MEMORY (formerly HEAP) storage engine keeps table data entirely in RAM; it is very fast, but data is lost when the server restarts (the structure remains). It does not support BLOB/TEXT and uses a fixed row size. For durable hot data, the InnoDB buffer pool is preferred.

SQL Server

-- In-Memory OLTP: memory-optimized table
CREATE TABLE dbo.session_state (
  id INT NOT NULL PRIMARY KEY NONCLUSTERED,
  data NVARCHAR(100)
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

SQL Server In-Memory OLTP (Hekaton) provides highly concurrent OLTP with memory-optimized tables (MEMORY_OPTIMIZED=ON) and latch-free structures. DURABILITY=SCHEMA_AND_DATA keeps the data durable; SCHEMA_ONLY preserves only the schema. Requires a memory-optimized filegroup.

MongoDB

-- Hot data is kept in the WiredTiger cache automatically.
-- Set the cache size (default ~50% of RAM):
// mongod --wiredTigerCacheSizeGB 8
// or in the configuration: storage.wiredTiger.engineConfig.cacheSizeGB

MongoDB has no separate in-memory table type (the old In-Memory Storage Engine was Enterprise-only and has been deprecated). The default WiredTiger engine improves read speed by keeping frequently accessed data and indexes in its memory cache; the OS file cache also contributes.

Redis

# Redis already runs entirely in memory; nothing extra is needed.
# Persistence and memory policy settings:
CONFIG SET maxmemory 4gb
CONFIG SET maxmemory-policy allkeys-lru

Redis keeps all data in RAM; by design this is the source of its speed. Persistence is provided by RDB (snapshots) and/or AOF (command log). The maxmemory limit and the eviction policy (LRU/LFU/random) determine behavior when memory is full.

Elasticsearch

-- Not applicable: there is no fully in-memory table mode.
# Place hot data on fast nodes with a hot/cold architecture:
PUT sale/_settings
{ "index.routing.allocation.require.data": "hot" }

Elasticsearch stores data on disk (Lucene segments); it offers no dedicated in-memory table engine. For speed it relies on the OS file cache (page cache), doc_values and a hot-warm-cold architecture. Sufficient RAM and heap settings are critical for performance.