Session settings

Session settings in CrateDB allow users to configure behavior and preferences for the duration of the current client session. These settings do not affect other sessions or users unless configured at the role level.

---

Table of Contents

• Usage

• Viewing Current Settings

• Persisting Settings Across Sessions

• Supported Session Settings

  • Modifiable Settings

  • Read-only Settings

---

Usage

To modify a session setting temporarily (for the current session only), use the SET command:

cr> SET search_path TO myschema, doc;
SET OK, 0 rows affected (... sec)

💡 You can also use SET LOCAL to limit the setting change to the duration of a single transaction block.

---

Viewing Current Settings

To retrieve the current value of a setting, use:

SHOW search_path;

Alternatively, you can use the current_setting() scalar function:

SELECT current_setting('search_path');

---

Persisting Settings Across Sessions

To permanently override default values for a specific user or role, use:

ALTER ROLE your_user_name SET search_path TO myschema, doc;

🔍 All active settings for the current session can be viewed in the sys.sessions system table.

---

Supported Session Settings

Modifiable Settings

Setting	Default	Description
search_path	pg_catalog, doc	List of schemas to search for unqualified relation names. CrateDB checks them in order.
application_name	null	Optional name to identify the application connecting to CrateDB. Often set by clients automatically.
statement_timeout	0	Maximum duration for any statement (in ms). A value of 0 means no timeout.
memory.operation_limit	0	Max memory (in bytes) allowed per operation. 0 means unlimited (only global circuit breaker applies).
enable_hashjoin	true	Enables evaluation of JOIN using Hash Join instead of Nested Loops. Experimental feature.
error_on_unknown_object_key	true	Throws an error on unknown keys in dynamic objects; set to false to return null instead.
datestyle	ISO	Format for displaying date/time. Only ISO is supported; other formats will raise an error.
optimizer	true	Enables/disables specific optimizer rules. Must be set using the full rule name.
optimizer_eliminate_cross_join	true	Enables/disables the rule that eliminates unnecessary cross joins.

Examples

• Set a statement timeout to 50 seconds:

  cr> SET LOCAL statement_timeout = '50000ms';

• Limit memory usage of a single operation to 1GB:

  cr> SET LOCAL "memory.operation_limit" = '1073741824';

• Disable a specific optimizer rule:

  cr> SET optimizer_rewrite_collect_to_get = false;

---

Read-only Settings

These are informational settings that cannot be modified during a session. They are available for compatibility or introspection.

Setting	Default	Description
max_index_keys	32	Maximum number of index keys. No effect in CrateDB. For compatibility only.
max_identifier_length	255	Maximum length (in bytes) of identifiers.
server_version_num	100500	Emulated PostgreSQL server version (numeric).
server_version	10.5	Emulated PostgreSQL server version (string).
standard_conforming_strings	on	Treats '...' strings as literal, with backslashes taken literally.

---

Notes & Compatibility

• search_path mirrors the PostgreSQL behavior and always includes pg_catalog unless already explicitly defined.

• datestyle, max_index_keys, and similar PostgreSQL compatibility settings are mostly placeholders in CrateDB and should not be relied upon for behavior customization.

• Experimental settings like enable_hashjoin and memory.operation_limit are powerful but should be used with caution.