Parameter HOLDMEM and Additional Disk Block Caches

WARNING!
In QHB 1.5.3 release, this feature is experimental and is not recommended for use in a production installation.

In QHB, to speed up work with tables, it is possible to create them with a special parameter HOLDMEM, which specifies where they will be placed. Three available values are OFF, POSSIBLY, and ONLY.

Syntax:

CREATE TABLE cats (id int) WITH (HOLDMEM = OFF);
-- the same as CREATE TABLE cats (id int);

CREATE TABLE dogs (id int) WITH (HOLDMEM = POSSIBLY);

CREATE UNLOGGED TABLE cows (id int) WITH (HOLDMEM = ONLY);

Note
Note that tables with HOLDMEM = ONLY must be created with the UNLOGGED option.

---

---

HOLDMEM Values

• OFF are normal tables. This parameter can be omitted if you creates a table. It is usually needed when you desire to change a table attribute.

ALTER TABLE dogs SET (HOLDMEM = OFF);

• POSSIBLY are tables that are in memory if possible.

ALTER TABLE dogs SET (HOLDMEM = POSSIBLY);

• ONLY are tables that are always in memory.

ALTER TABLE dogs SET (HOLDMEM = ONLY);

Note
All tables with HOLDMEM = ONLY must be unlogged.
Make the table UNLOGGED in a separate query:

ALTER TABLE dogs set UNLOGGED;

and then

ALTER TABLE dogs SET (HOLDMEM = ONLY);

---

HOLDMEM = POSSIBLY

Tables created with the HOLDMEM = POSSIBLY are intended to store data that requires fast access. They use a separate LRU cache, the simplicity of which allows for high performance when accessing it during a page search. However, the LRU algorithm has fewer configuration parameters than TARQ, so it should be used in special cases, such as working with data that requires frequent access.

---

HOLDMEM = ONLY

Tables created with the HOLDMEM = ONLY are intended to store data that requires the fastest possible access.

However, you need to know some specifics of working with it:

• Since all data is stored in memory, overflowing this buffer will result in an error. Also, in the event of a failure or emergency termination, data cannot and will not be recovered.

• Mechanism TOAST is not supported.

  • When creating a table, the PLAIN storage mode will be forced for all columns.

  • You cannot change the storage attribute of an existing table to HOLDMEM = ONLY if there is a corresponding TOAST table. You must disable TOAST beforehand using the command ALTER TABLE table_name ALTER COLUMN column_name SET STORAGE PLAIN. Note that changing the storage mode will not remove the existing TOAST table. The HOLDMEM = ONLY has no effect on a TOAST table.

  • Setting the storage attribute to HOLDMEM = POSSIBLY or HOLDMEM = OFF after HOLDMEM = ONLY does not automatically enable TOAST support. If such support is needed, it must be manually enabled using the ALTER TABLE table_name ALTER COLUMN column_name SET STORAGE command.

To enable and control the eviction algorithm for tables that are stored in memory if possible, use the parameters in the configuration file (see Section Parameter HOLDMEM Eviction Algorithm Enabling and Control for details).

---

---

Compatibility

Parameter HOLDMEM, as well as the OFF, POSSIBLY, and ONLY values ​​implemented in QHB, is an extension of the SQL standard.

---

---

See Also

ALTER TABLE