Pragmas

PRAGMA statements are issued like regular SQL statements. They read information about the server and its databases, or change settings that influence the subsequent execution or behavior of the engine.

PRAGMA statements that assign a value to an option can also be issued using the SET statement and the value of an option can be retrieved using SELECT current_setting(option_name).

For SereneDB's built in configuration options, see the Configuration Reference.

This page contains the supported PRAGMA settings.

Metadata

Schema Information

List all databases:

SQL

PRAGMA database_list;

List all tables:

SQL

PRAGMA show_tables;

List all tables, with extra information, similarly to DESCRIBE:

SQL

PRAGMA show_tables_expanded;

To list all functions:

SQL

PRAGMA functions;

For queries targeting non-existing schemas, SereneDB generates "did you mean..." style error messages. When there are thousands of attached databases, these errors can take a long time to generate. To limit the number of schemas SereneDB looks through, use the catalog_error_max_schemas option:

SQL

SET catalog_error_max_schemas = 10;

Table Information

Get info for a specific table:

SQL

PRAGMA table_info('table_name');
CALL pragma_table_info('table_name');

 cid | name | type    | notnull | dflt_value | pk
-----+------+---------+---------+------------+----
   0 | id   | INTEGER | f       | NULL       | f
   1 | name | VARCHAR | f       | NULL       | f

 cid | name | type    | notnull | dflt_value | pk
-----+------+---------+---------+------------+----
   0 | id   | INTEGER | f       | NULL       | f
   1 | name | VARCHAR | f       | NULL       | f

table_info returns information about the columns of the table with name table_name. The exact format of the table returned is given below:

SQL logic test

cid INTEGER,        -- cid of the columnname VARCHAR,       -- name of the columntype VARCHAR,       -- type of the columnnotnull BOOLEAN,    -- if the column is marked as NOT NULLdflt_value VARCHAR, -- default value of the column, or NULL if not specifiedpk BOOLEAN          -- part of the primary key or not

Database Size

Get the file and memory size of each database:

SQL

PRAGMA database_size;
CALL pragma_database_size();

database_size returns information about the file and memory size of each database. A database whose size cannot be read, such as an attached PostgreSQL database whose server is unreachable, is left out, and the other databases are still listed. The column types of the returned results are given below:

SQL

database_name VARCHAR, -- database namedatabase_size VARCHAR, -- total block count times the block sizeblock_size BIGINT,     -- database block sizetotal_blocks BIGINT,   -- total blocks in the databaseused_blocks BIGINT,    -- used blocks in the databasefree_blocks BIGINT,    -- free blocks in the databasewal_size VARCHAR,      -- write ahead log sizememory_usage VARCHAR,  -- memory used by the database buffer managermemory_limit VARCHAR   -- maximum memory allowed for the database

Storage Information

To get storage information:

SQL

PRAGMA storage_info('table_name');
CALL pragma_storage_info('table_name');

row_group_id	column_name	column_id	column_path	segment_id	segment_type	start	count	compression	stats	has_updates	persistent	block_id	block_offset	additional_block_ids

row_group_id	column_name	column_id	column_path	segment_id	segment_type	start	count	compression	stats	has_updates	persistent	block_id	block_offset	additional_block_ids

This call returns the following information for the given table:

Name	Type	Description
row_group_id	BIGINT
column_name	VARCHAR
column_id	BIGINT
column_path	VARCHAR
segment_id	BIGINT
segment_type	VARCHAR
start	BIGINT	The start row id of this chunk
count	BIGINT	The amount of entries in this storage chunk
compression	VARCHAR	Compression type used for this column
stats	VARIANT
has_updates	BOOLEAN
persistent	BOOLEAN	false if temporary table
block_id	BIGINT	Empty unless persistent
block_offset	BIGINT	Empty unless persistent
segment_info	VARCHAR	Only from pragma_storage_info('<table>', include_segment_info := true)
additional_block_ids	BIGINT[]	Empty unless persistent

Show Databases

The following statement is equivalent to the SHOW DATABASES statement:

SQL

PRAGMA show_databases;

Resource Management

Memory Limit

Set the memory limit for the buffer manager:

SQL

SET memory_limit = '1GB';

Attention

The specified memory limit is only applied to the buffer manager. For most queries, the buffer manager handles the majority of the data processed. However, certain in-memory data structures such as vectors and query results are allocated outside of the buffer manager. Additionally, aggregate functions with complex state (e.g., list, mode, quantile, string_agg, and approx functions) use memory outside of the buffer manager. Therefore, the actual memory consumption can be higher than the specified memory limit.

Threads

Cap the threads the queries of the current session run on:

SQL

SET threads = 4;

Collations

List all available collations:

SQL logic test

PRAGMA collations;

Set the default collation to one of the available ones:

SQL logic test

SET default_collation = 'nocase';

Default Ordering for NULLs

Set the default ordering for NULLs to be either NULLS_FIRST, NULLS_LAST, NULLS_FIRST_ON_ASC_LAST_ON_DESC or NULLS_LAST_ON_ASC_FIRST_ON_DESC:

SQL logic test

SET default_null_order = 'NULLS_FIRST';
SET default_null_order = 'NULLS_LAST_ON_ASC_FIRST_ON_DESC';

Set the default result set ordering direction to ASCENDING or DESCENDING:

SQL logic test

SET default_order = 'ASCENDING';
SET default_order = 'DESCENDING';

Ordering by Non-Integer Literals

By default, ordering by non-integer literals is not allowed:

SQL

SELECT 42 ORDER BY 'hello world';

db error: ERROR: ORDER BY non-integer literal has no effect.
* SET order_by_non_integer_literal=true to allow this behavior.

To allow this behavior, use the order_by_non_integer_literal option:

SQL logic test

SET order_by_non_integer_literal = true;

Information on SereneDB

Version

PRAGMA version shows the version of the query engine library built into SereneDB, not the SereneDB release. SELECT version() returns the release, for example PostgreSQL 18.3 (SereneDB 26.10.0).

SQL

PRAGMA version;
CALL pragma_version();

 library_version | source_id | codename
-----------------+-----------+----------
 (varies)        | (varies)  | (varies)

 library_version | source_id | codename
-----------------+-----------+----------
 (varies)        | (varies)  | (varies)

Platform

platform returns an identifier for the platform the current SereneDB executable has been compiled for, linux_amd64 or linux_arm64:

SQL

PRAGMA platform;
CALL pragma_platform();

 platform
----------
 (varies)

 platform
----------
 (varies)

User Agent

The following statement returns the user agent information, duckdb/<engine library version>(<platform>) cpp:

SQL

PRAGMA user_agent;

 user_agent
------------
 (varies)

Metadata Information

The following statement returns information on the metadata store (block_id, total_blocks, free_blocks, and free_list):

SQL

PRAGMA metadata_info;

 block_id | total_blocks | free_blocks | free_list
----------+--------------+-------------+-----------
        0 |           64 |           2 |         1

Progress Bar

A progress bar is printed to the standard output of the serened process, not to the client. Every session tracks the progress of its queries, and none prints it by default.

Track the progress of running queries:

SQL

PRAGMA enable_progress_bar;

Success

Print a progress bar for long queries:

SQL

PRAGMA enable_print_progress_bar;

Success

Stop tracking the progress of running queries:

SQL

PRAGMA disable_progress_bar;

Success

Stop printing the progress bar:

SQL

PRAGMA disable_print_progress_bar;

Success

EXPLAIN Output

The output of EXPLAIN can be configured to show only the physical plan.

The default configuration of EXPLAIN:

SQL

SET explain_output = 'physical_only';

To only show the optimized query plan:

SQL

SET explain_output = 'optimized_only';

To show all query plans:

SQL

SET explain_output = 'all';

Profiling

Enable Profiling

The following query enables profiling with the default format, query_tree. Independent of the format, enable_profiling is mandatory to enable profiling.

SQL

PRAGMA enable_profiling;
PRAGMA enable_profile;

Success

Success

Profiling Coverage

By default, the profiling coverage is set to SELECT. SELECT runs the profiler for each operator in the physical plan of a SELECT statement.

SQL

SET profiling_coverage = 'SELECT';

By default, the profiler does not emit profiling information for other statement types (INSERT INTO, ATTACH, etc.). To run the profiler for all statement types, change this setting to ALL.

SQL

SET profiling_coverage = 'ALL';

Profiling Format

The format of enable_profiling can be specified as query_tree, query_tree_optimizer, json, text, html, graphviz, yaml, mermaid, compact or no_output. Each format prints its output to the configured output, except no_output.

The default format is query_tree. It prints the physical query plan and the metrics of each operator in the tree.

SQL

SET enable_profiling = 'query_tree';

Alternatively, json returns the physical query plan as JSON:

SQL

SET enable_profiling = 'json';

To return the physical query plan, including optimizer and planner metrics:

SQL

SET enable_profiling = 'query_tree_optimizer';

Database drivers and other applications can also access profiling information through API calls, in which case users can disable any other output. Even though the parameter reads no_output, it is essential to note that this only affects printing to the configurable output. When accessing profiling information through API calls, it is still crucial to enable profiling:

SQL

SET enable_profiling = 'no_output';

Profiling Output

Without profiling_output, the profile is printed to the standard error of the serened process, and the client receives nothing; EXPLAIN ANALYZE returns a profile to the client instead. To write the profile to a file on the server, set profiling_output to its path:

Attention

The file contents will be overwritten for every newly issued query. Hence, the file will only contain the profiling information of the last run query:

SQL

SET profiling_output = '/path/to/file.json';
SET profile_output = '/path/to/file.json';

Disable Profiling

To disable profiling:

SQL

PRAGMA disable_profiling;
PRAGMA disable_profile;

Success

Success

Query Optimization

Optimizer

To disable the query optimizer:

SQL

PRAGMA disable_optimizer;

Success

To enable the query optimizer:

SQL

PRAGMA enable_optimizer;

Success

Selectively Disabling Optimizers

The disabled_optimizers option allows selectively disabling optimization steps. For example, to disable filter_pushdown and statistics_propagation, run:

SQL

SET disabled_optimizers = 'filter_pushdown,statistics_propagation';

The available optimizations can be queried using the sdb_optimizers() table function.

To re-enable the optimizers, run:

SQL

SET disabled_optimizers = '';

Attention

The disabled_optimizers option should only be used for debugging performance issues and should be avoided in production.

Logging

Log the text of every statement the session runs to a file on the server:

SQL

SET log_query_path = '/var/log/serenedb/queries.log';

Disable query logging:

SQL

SET log_query_path = '';

Checkpointing

Compression

During checkpointing, the existing column data + any new changes get compressed. There exist a couple of pragmas to influence which compression functions are considered.

Force Compression

Prefer using this compression method over any other method if possible:

SQL

PRAGMA force_compression = 'bitpacking';

Disabled Compression Methods

Avoid using any of the listed compression methods from the comma separated list:

SQL

PRAGMA disabled_compression_methods = 'fsst,rle';

Force Checkpoint

When CHECKPOINT is called when no changes are made, force a checkpoint regardless:

SQL

PRAGMA force_checkpoint;

Success

Checkpoint on Shutdown

Run a CHECKPOINT on successful shutdown and delete the WAL, to leave only a single database file behind:

SQL

PRAGMA enable_checkpoint_on_shutdown;

Success

Don't run a CHECKPOINT on shutdown:

SQL

PRAGMA disable_checkpoint_on_shutdown;

Success

Temp Directory for Spilling Data to Disk

By default, the server spills to disk in the tmp directory of its data directory (<datadir>/tmp). To change this, use:

SQL

SET temp_directory = '/path/to/temp_dir.tmp/';

Returning Errors as JSON

The errors_as_json setting makes the serened shell report errors as raw JSON instead of a formatted message, which is easier to process programmatically. For certain errors it includes extra, decomposed fields:

SQL

SET errors_as_json = true;

With the setting enabled, a failing query in the shell prints a JSON object such as:

console

{"exception_type":"Catalog","exception_message":"Table with name nonexistent_tbl does not exist!","type":"Table","name":"nonexistent_tbl","error_subtype":"MISSING_ENTRY"}

Over the PostgreSQL wire protocol (for example via psql or any PostgreSQL driver), errors are always returned as standard PostgreSQL error messages, so errors_as_json has no visible effect on the client.

IEEE Floating-Point Operation Semantics

A floating-point division by zero fails with a Division by zero error, as in PostgreSQL; with error_on_division_by_zero off, it returns NULL:

SQL

SELECT 1.0 / 0.0 AS a;

db error: ERROR: Division by zero in expression (1 // 0). Use TRY(...) to return NULL for this expression, or SET error_on_division_by_zero=false to return NULL for all divisions by zero.

To follow IEEE floating-point operation semantics instead, run:

SQL

SET ieee_floating_point_ops = true;

Then a floating-point division by zero returns Infinity, NaN or -Infinity:

SQL

SELECT    1.0 / 0.0 AS a,    0.0 / 0.0 AS b,    -1.0 / 0.0 AS c;

 a        | b   | c
----------+-----+-----------
 Infinity | NaN | -Infinity

Query Verification (for Development)

The following PRAGMAs are mostly used for development and internal testing.

Enable force parallel query processing:

SQL

PRAGMA verify_parallelism;

Success

Disable force parallel query processing:

SQL

PRAGMA disable_verify_parallelism;

Success

Block Sizes

When persisting a database to disk, SereneDB writes to a dedicated file containing a list of blocks holding the data. In the case of a file that only holds very little data, e.g., a small table, the default block size of 256 kB might not be ideal. Therefore, SereneDB's storage format supports different block sizes.

There are a few constraints on possible block size values.

• Must be a power of two.
• Must be greater or equal to 16384 (16 kB).
• Must be lesser or equal to 262144 (256 kB).

You can set the default block size for all new SereneDB files created by an instance like so:

SQL

SET default_block_size = '16384';

A database can also be created with its own block size, see CREATE DATABASE.