To add an in-memory table to your database, create a foreign table on the in_memory server, using the regular CREATE FOREIGN TABLE syntax. By default, a unique B-tree index is built upon the first column, in the ascending order. If required, you can use the INDICES option in the OPTIONS clause to define a different B-tree index structure, as follows:

OPTIONS ( INDICES '[ UNIQUE ] {column [ COLLATE collation ] [ASC | DESC] } [, ... ]' )

where column is the column to include into the B-tree index, and collation is the name of the collation to use for this column. You can specify up to eight arbitrary columns separated by commas, with sorting options defined for each of these columns (COLLATE, ASC/DESC). The UNIQUE declares the created index unique, reiterating the default behavior.

All columns to be used in the B-tree index must be of a type for which the default B-tree operator class is available. For details on operator classes, see Section 11.10.

Examples

Create an in-memory table blog_views to store statistics on blog views based on blog post IDs, with the unique B-tree index built upon the first column, in the ascending order:

CREATE FOREIGN TABLE blog_views
(
    id int8 NOT NULL,
    author text,
    views bigint NOT NULL
) SERVER in_memory
OPTIONS (INDICES 'UNIQUE (id)');

Define the B-tree index on the id and author columns, with the author values sorted in the ascending order using "ru_RU" collation:

CREATE FOREIGN TABLE blog_views
(
    id int8 NOT NULL,
    author text,
    views bigint NOT NULL
) SERVER in_memory
OPTIONS (INDICES '(id, author COLLATE "ru_RU" ASC)');

Once an in-memory table is created, you can run all the main DML operations on this table: SELECT, INSERT, UPDATE, DELETE.

If you use the primary key as the scan qualifier when running queries, a key lookup or range scan is performed. Otherwise, a full index scan is required.

Examples

Fill the blog_views table with initial zero values for initial ten blog posts:

postgres=# INSERT INTO blog_views (SELECT id, 0 FROM generate_series(1, 10) AS id);

Increment the view count for a couple of posts and display the result:

postgres=# UPDATE blog_views SET views = views + 1 WHERE id = 1;
UPDATE 1
postgres=# UPDATE blog_views SET views = views + 1 WHERE id = 1;
UPDATE 2
postgres=# UPDATE blog_views SET views = views + 1 WHERE id = 2;
UPDATE 1
postgres=# SELECT * FROM blog_views WHERE id = 1 OR id = 2;
 id | views
----+-------
  1 |  2
  2 |  1
(2 rows)

Check planning and execution costs for a query that only requires a primary key lookup:

postgres=# EXPLAIN ANALYZE SELECT * FROM blog_views WHERE id = 1;
                       QUERY PLAN
-------------------------------------------------------------------
Foreign Scan on blog_views  (cost=0.02..0.03 rows=1 width=16)
(actual time=0.013..0.014 rows=1 loops=1)
  Pk conds: (id = 1)
Planning time: 0.060 ms
Execution time: 0.035 ms
(4 rows)

Check the costs of calculating the sum of all views, which requires a full index scan:

postgres=# EXPLAIN ANALYZE SELECT SUM(views) FROM blog_views;
                         QUERY PLAN
--------------------------------------------------------------------
Aggregate  (cost=1.62..1.63 rows=1 width=32)
(actual time=0.323..0.323 rows=1 loops=1)
Foreign Scan on blog_views  (cost=0.02..1.30 rows=128 width=8)
(actual time=0.005..0.168 rows=1000 loops=1)
Planning time: 0.113 ms
Execution time: 0.353 ms
(4 rows)

When a row is deleted from an in-memory table, data pages are not freed. To free the pages occupied by in-memory tables, you can:

RESTART IDENTITY, CONTINUE IDENTITY, CASCADE, and RESTRICT options of the TRUNCATE command are not supported by in-memory tables.

In some cases, it can be useful to perform write operations on hot standby servers. For example, suppose you need to collect statistics on queries run on hot standby. Since in-memory tables are writable, you can use them for such purposes.

To set up in-memory tables for write queries on standby, create the required in-memory tables on the primary server as explained in Section F.22.2.1. Once the replication is complete, you can start writing data to these tables on hot standby.

To get statistics on in-memory pages available in your cluster, run the in_memory_page_stats function, which returns the number of all used and free in-memory pages, as well as the total number of pages allocated for in-memory tables. For example:

postgres=# SELECT * FROM in_memory.in_memory_page_stats();
 busy_pages | free_pages | all_pages
------------+------------+-----------
        576 |       7616 |      8192
(1 row)