KDB knowledge

Settings - SET, SHOW ALL, ALTER SYSTEM

One list of the server's settings behind pg_settings, SHOW ALL, SHOW PARAMETERS and GET /api/settings; SET for a session, ALTER SYSTEM and pg_reload_conf() for the server.

KDB keeps one list of its settings. The same list is behind:

WhereShows
SELECT * FROM pg_settingsPostgreSQL's view: name, setting, unit, context, source, ...
SHOW ALLname, value (as SHOW writes it: 4MB, 5s) and description
SHOW PARAMETERSQuestDB's view: property path, environment variable, value, where it came from
SELECT * FROM kdb_settingsall of the above in one table, to filter with WHERE
GET /api/settingsthe list under "parameters", with the server's values
SHOW work_mem, current_setting('work_mem')one setting, for this session

Each setting says what KDB does with it (the use column of kdb_settings):

useMeaning
appliedKDB acts on it (statement_timeout, work_mem, enable_hashjoin, ...)
shownkept and shown for clients, but KDB works the same either way (DateStyle, search_path)
ignoredaccepted so PostgreSQL scripts run, with a NOTICE (shared_buffers in a session, enable_seqscan, jit, ...)

Change a setting for this session

Syntax:

SET name = value          SET name TO value
SET LOCAL name = value    until COMMIT or ROLLBACK
RESET name                RESET ALL
SHOW name                 SHOW ALL

A value can have a unit, as in PostgreSQL: SET work_mem = '64MB', SET statement_timeout = '5s'. SHOW gives it back the same way. A bad value gives 22023, an unknown name on SHOW gives 42704.

The context column says when a setting may change:

contextChanges with
userSET in any session
superuserSET by an admin (log_statement)
sighupALTER SYSTEM and SELECT pg_reload_conf()
postmastera restart: a kdb serve flag, kdb.conf or an environment variable (55P02 on SET)
internalnever (server_version, block_size)

Over HTTP, a SET lasts for the rest of the same request (and for the whole session when you use one: sessions).

Example: Set and read a setting

SET work_mem = '64MB';
SHOW work_mem;
SELECT name, setting, unit, source FROM pg_settings WHERE name IN ('work_mem', 'enable_hashjoin');

Result:

SET work_mem = '64MB'

work_mem is 64MB for this session

SHOW work_mem

work_mem (STRING)
64MB

(1 row)

SELECT name, setting, unit, source FROM pg_settings WHERE name IN ('work_mem', 'enable_hashjoin')

name (STRING)setting (STRING)unit (STRING)source (STRING)
enable_hashjoinonNULLdefault
work_mem65536kBsession

(2 rows)

The planner settings KDB applies

SettingDefaultWhat it does
work_mem0how much a GROUP BY keeps in memory before its groups go to temporary files. 0 = half of the statement's working memory. Never more than three quarters of it
max_parallel_workers_per_gatherthe server's threadsthreads one statement's scans and aggregates may use, its own included. 0 or 1 = one thread
enable_hashjoinonoff: equality joins compare every pair of rows (a nested loop) instead of building a hash
enable_nestlooponoff: a join does not look each row's match up by key or index; it builds a hash
enable_indexscanonoff: no secondary index finds rows; the table is scanned. A full-text search keeps its index
log_statementnoneddl, mod or all: statements go to the server's log as LOG: statement: ... (admins only)
plan_cache_modeautoforce_custom_plan: this session's statements are parsed every time; they neither use nor fill the plan cache. auto and force_generic_plan use it

The switches are there to compare answers and times, as in PostgreSQL. They are read once per statement, never per row.

Memory settings set at start

These change only with a restart (postmaster): a kdb serve flag or an environment variable.

SettingDefaultFlag / variableWhat it does
memory_limitnone--memory-limit, KDB_MEMORY_LIMITone limit for everything the server holds
shared_buffers512 MB--memory, KDB_MEMORYtable data kept loaded
txn_spill_mb64--txn-spill-mb, KDB_TXN_SPILL_MBa transaction's log records past this many MB (or 1/16 of memory_limit) go to a temporary file until COMMIT. 0 = never. See big transactions

Example: Read the memory settings

SELECT name, setting, unit, context FROM pg_settings WHERE name IN ('memory_limit', 'txn_spill_mb') ORDER BY name;

Result:

name (STRING)setting (STRING)unit (STRING)context (STRING)
memory_limit0kBpostmaster
txn_spill_mb64MBpostmaster

(2 rows)

The plan cache

An app sends the same small statement again and again: a key lookup, a price, a session check. Parsing it can take as long as running it. So the server keeps the parse of a statement it has seen, by its exact text, and the next run of the same text skips the parse.

WhatHow
Who uses itevery client: POST /exec, GET /exec and the PostgreSQL port's simple query (prepared statements keep their own parse per connection)
What is keptone SELECT the first time; one INSERT ... VALUES, UPDATE or DELETE when the same text comes a second time. Not SELECT ... FOR UPDATE, a WITH that changes rows, or a statement that names another database
Same statement, other valuesan INSERT ... VALUES, UPDATE, DELETE or CALL is also kept by its shape - the text with its numbers and 'strings' left out - the first time, per connection (or HTTP worker). The next text of that shape, INSERT INTO t VALUES (1018, 'open') after (1017, 'paid'), runs the kept parse with its own values put in: the same tree a parse would make, without the parse. Only when every value is a plain value; a LIMIT, a cast's precision, a SIMILAR TO pattern or a GROUP BY keeps the text parsed every time
Per databaseeach database has its own entries, even for the same text
After a schema changeCREATE, ALTER or DROP of a table: the next run parses the text again
Sizeup to 2,048 texts of at most 2 KB each; when full, one not used lately goes
Planned on each runwhich index, which engine and which partitions are worked out again every time, with the values of that run

The answers never change: only the time to parse is saved. GET /api/stats shows it under "plan_cache":

FieldMeaning
hitsruns that skipped the parse
missesstatements it could keep that had to be parsed: the first time, or after a schema change
entriesparses kept now
capacitytexts it keeps at most

To leave it out for one session, as PostgreSQL does for its own plan cache:

Example: Parse every statement of this session

SET plan_cache_mode = force_custom_plan;
SHOW plan_cache_mode;

Result:

SET plan_cache_mode = 'force_custom_plan'

plan_cache_mode is force_custom_plan for this session

SHOW plan_cache_mode

plan_cache_mode (STRING)
force_custom_plan

(1 row)

Change a setting for the server

Syntax:

ALTER SYSTEM SET name = value
ALTER SYSTEM RESET name
ALTER SYSTEM RESET ALL
SELECT pg_reload_conf();

ALTER SYSTEM saves the value in kdb.conf, under "system". It is in use after SELECT pg_reload_conf() (or the next start) for every new statement of every session that did not SET it itself. Only a server admin may run either. ALTER SYSTEM cannot run inside BEGIN ... COMMIT.

ALTER SYSTEM SET work_mem = '256MB';
ALTER SYSTEM SET log_statement = 'ddl';
SELECT pg_reload_conf();       -- true

kdb.conf then holds:

{
  "data_dir": "/data",
  "system": {"log_statement": "ddl", "work_mem": "262144"}
}

Also fixed on 2026-09-26