SQL Settings Reference¶

Overview¶

HeliosDB-Lite provides PostgreSQL-compatible SET and SHOW commands for managing session-level and global settings. This document provides comprehensive reference for all available settings.

Table of Contents¶

SET Command
SHOW Command
RESET Command
Query Execution Settings
Optimizer Settings
Memory Settings
Transaction Settings
Time-Travel Settings
Compression Settings
Vector Index Settings
Materialized View Settings
SMFI Settings
Display Settings
Read-Only Settings

SET Command¶

Set a session variable to a new value.

Syntax¶

SET setting_name = value;
SET setting_name TO value;

Examples¶

-- Enable optimizer
SET optimizer = on;

-- Set timeout
SET statement_timeout = 30000;

-- Set memory limit
SET work_mem = 8192;

-- Set transaction isolation
SET transaction_isolation = 'SERIALIZABLE';

SHOW Command¶

Display current value of a setting.

Syntax¶

SHOW setting_name;
SHOW ALL;

Examples¶

-- Show specific setting
SHOW optimizer;

-- Show all settings
SHOW ALL;

-- Show server version
SHOW server_version;

RESET Command¶

Reset a setting to its default value.

Syntax¶

RESET setting_name;
RESET ALL;

Examples¶

-- Reset optimizer to default
RESET optimizer;

-- Reset all settings to defaults
RESET ALL;

Query Execution Settings¶

statement_timeout¶

Maximum execution time for a single statement (milliseconds).

Type: Duration (milliseconds) Default: 0 (unlimited) Read-Only: No

Example:

-- Set 30-second timeout
SET statement_timeout = 30000;

-- Disable timeout
SET statement_timeout = 0;

Use Cases: - Prevent runaway queries in production - Enforce query performance standards - Protect against resource exhaustion

query_timeout¶

Maximum execution time for an entire query (milliseconds).

Type: Duration (milliseconds) Default: 0 (unlimited) Read-Only: No

Example:

-- Set 60-second query timeout
SET query_timeout = 60000;

Optimizer Settings¶

optimizer¶

Enable or disable the query optimizer.

Type: Boolean Default: on Read-Only: No

Example:

-- Enable optimizer
SET optimizer = on;

-- Disable for debugging
SET optimizer = off;

enable_seqscan¶

Enable sequential scans.

Type: Boolean Default: on Read-Only: No

Example:

-- Disable sequential scans (force index usage)
SET enable_seqscan = off;

enable_indexscan¶

Enable index scans.

Type: Boolean Default: on Read-Only: No

Example:

-- Force sequential scans for testing
SET enable_indexscan = off;

enable_hashjoin¶

Enable hash join operations.

Type: Boolean Default: on Read-Only: No

Example:

SET enable_hashjoin = off;

enable_mergejoin¶

Enable merge join operations.

Type: Boolean Default: on Read-Only: No

Example:

SET enable_mergejoin = off;

enable_nestloop¶

Enable nested loop joins.

Type: Boolean Default: on Read-Only: No

Example:

SET enable_nestloop = off;

Memory Settings¶

work_mem¶

Amount of memory to use for query workspace operations (KB).

Type: Integer (kilobytes) Default: 4096 (4MB) Read-Only: No

Example:

-- Allocate 16MB for sorting and hashing
SET work_mem = 16384;

-- Large data warehouse query
SET work_mem = 131072;  -- 128MB

Use Cases: - Sorting operations - Hash table construction - Merge joins - Set operations (UNION, EXCEPT, INTERSECT)

shared_buffers¶

Amount of shared memory for data caching (KB).

Type: Integer (kilobytes) Default: 131072 (128MB) Read-Only: No

Example:

-- Increase cache for read-heavy workload
SET shared_buffers = 524288;  -- 512MB

Transaction Settings¶

transaction_isolation¶

Transaction isolation level.

Type: String Default: 'READ COMMITTED' Read-Only: No Valid Values: - READ UNCOMMITTED (maps to READ COMMITTED) - READ COMMITTED - REPEATABLE READ - SERIALIZABLE

Example:

-- Strictest isolation
SET transaction_isolation = 'SERIALIZABLE';

-- Standard PostgreSQL default
SET transaction_isolation = 'READ COMMITTED';

-- Consistent snapshot
SET transaction_isolation = 'REPEATABLE READ';

Isolation Level Details:

Level	Dirty Reads	Non-Repeatable Reads	Phantom Reads
READ UNCOMMITTED	Possible*	Possible	Possible
READ COMMITTED	Not Possible	Possible	Possible
REPEATABLE READ	Not Possible	Not Possible	Not Possible
SERIALIZABLE	Not Possible	Not Possible	Not Possible

*HeliosDB-Lite maps READ UNCOMMITTED to READ COMMITTED for safety.

transaction_read_only¶

Make transactions read-only.

Type: Boolean Default: off Read-Only: No

Example:

-- Enable read-only mode for reporting
SET transaction_read_only = on;

-- Allow writes
SET transaction_read_only = off;

Time-Travel Settings¶

time_travel_enabled¶

Enable automatic versioning for time-travel queries.

Type: Boolean Default: on Read-Only: No

Example:

-- Enable time-travel
SET time_travel_enabled = on;

-- Disable for write-heavy workloads
SET time_travel_enabled = off;

When Enabled: - All INSERT/UPDATE operations create versioned snapshots - AS OF queries work automatically - Minimal write overhead (snapshot-on-write)

When Disabled: - No automatic versioning - AS OF queries will fail - Faster writes for bulk operations

Compression Settings¶

default_compression¶

Default compression type for new tables.

Type: String Default: 'zstd' Read-Only: No Valid Values: none, zstd, lz4

Example:

-- Use LZ4 for faster compression
SET default_compression = 'lz4';

-- Disable compression
SET default_compression = 'none';

-- Best compression ratio
SET default_compression = 'zstd';

Compression Comparison:

Type	Speed	Ratio	Use Case
none	Fastest	1:1	Temp tables, pre-compressed data
lz4	Very Fast	2-3:1	Hot data, frequent access
zstd	Fast	3-5:1	General purpose (recommended)

compression_level¶

Compression level (1-22 for zstd).

Type: Integer Default: 3 Read-Only: No Valid Range: 1-22

Example:

-- Balanced performance
SET compression_level = 3;

-- Maximum compression
SET compression_level = 19;

-- Fast compression
SET compression_level = 1;

Vector Index Settings¶

vector_index_type¶

Default vector index type.

Type: String Default: 'hnsw' Read-Only: No Valid Values: flat, hnsw, ivf

Example:

-- Use HNSW for fast approximate search
SET vector_index_type = 'hnsw';

-- Use flat index for exact search (small datasets)
SET vector_index_type = 'flat';

-- Use IVF for very large datasets
SET vector_index_type = 'ivf';

Index Type Comparison:

Type	Speed	Recall	Memory	Best For
flat	Slow	100%	Low	<10K vectors, exact search
hnsw	Fast	95-99%	Medium	General purpose
ivf	Very Fast	90-95%	Low	>1M vectors

hnsw_ef_construction¶

HNSW construction parameter (higher = better recall, slower build).

Type: Integer Default: 200 Read-Only: No

Example:

-- High-quality index
SET hnsw_ef_construction = 400;

-- Fast build
SET hnsw_ef_construction = 100;

hnsw_m¶

HNSW M parameter (connections per layer).

Type: Integer Default: 16 Read-Only: No

Example:

-- Better recall (more memory)
SET hnsw_m = 32;

-- Lower memory usage
SET hnsw_m = 8;

Materialized View Settings¶

mv_auto_refresh¶

Enable auto-refresh for new materialized views.

Type: Boolean Default: off Read-Only: No

Example:

-- Enable auto-refresh by default
SET mv_auto_refresh = on;

-- Manual refresh only
SET mv_auto_refresh = off;

mv_max_cpu_percent¶

Maximum CPU percentage for MV refresh.

Type: Integer Default: 15 Read-Only: No Valid Range: 1-100

Example:

-- Allow more CPU for refresh
SET mv_max_cpu_percent = 30;

-- Limit background refresh impact
SET mv_max_cpu_percent = 10;

SMFI Settings¶

Self-Maintaining Filter Index (SMFI) provides automatic storage-level filtering for queries.

smfi_enabled¶

Enable or disable the SMFI system.

Type: Boolean Default: on Read-Only: No

Example:

-- Disable SMFI (not recommended)
SET smfi_enabled = off;

-- Enable SMFI
SET smfi_enabled = on;

smfi_tracking_enabled¶

Enable delta tracking during DML operations.

Type: Boolean Default: on Read-Only: No

Example:

-- Disable tracking for bulk loads
SET smfi_tracking_enabled = off;

-- Re-enable after bulk load
SET smfi_tracking_enabled = on;

smfi_bulk_load_threshold¶

Row count threshold for automatic SMFI suspension during bulk operations.

Type: Integer Default: 10000 Read-Only: No Hot-Reloadable: Yes (no restart required) Valid Range: 1-1000000

When an INSERT operation has >= this many rows, SMFI tracking is automatically suspended for the affected table and resumes after the operation completes.

Example:

-- Check current threshold
SHOW smfi_bulk_load_threshold;

-- Increase for less frequent suspension
SET smfi_bulk_load_threshold = 50000;

-- Decrease for more aggressive suspension
SET smfi_bulk_load_threshold = 1000;

-- Reset to default
RESET smfi_bulk_load_threshold;

smfi_parallel_enabled¶

Enable parallel filter evaluation.

Type: Boolean Default: on Read-Only: No

Example:

-- Force single-threaded filtering
SET smfi_parallel_enabled = off;

-- Enable parallel filtering (default)
SET smfi_parallel_enabled = on;

smfi_max_cpu_percent¶

Maximum CPU percentage for background consolidation.

Type: Integer Default: 15 Read-Only: No Valid Range: 1-100

Example:

-- Allow more CPU for faster consolidation
SET smfi_max_cpu_percent = 25;

-- Minimize impact on production
SET smfi_max_cpu_percent = 10;

smfi_delta_threshold¶

Number of deltas before triggering consolidation.

Type: Integer Default: 1000 Read-Only: No

Example:

-- Consolidate more frequently
SET smfi_delta_threshold = 500;

-- Consolidate less frequently (batch more deltas)
SET smfi_delta_threshold = 5000;

smfi_time_threshold¶

Seconds before triggering time-based consolidation.

Type: Integer Default: 300 Read-Only: No

Example:

-- Consolidate every 10 minutes
SET smfi_time_threshold = 600;

-- Consolidate every minute
SET smfi_time_threshold = 60;

smfi_min_query_frequency¶

Minimum query frequency before creating speculative filter.

Type: Integer Default: 10 Read-Only: No

Example:

-- Create filters faster (lower threshold)
SET smfi_min_query_frequency = 5;

-- More conservative filter creation
SET smfi_min_query_frequency = 50;

smfi_drop_after_days¶

Days of inactivity before dropping unused speculative filter.

Type: Integer Default: 7 Read-Only: No

Example:

-- Keep unused filters longer
SET smfi_drop_after_days = 14;

-- Drop unused filters quickly
SET smfi_drop_after_days = 3;

smfi_max_filters_per_table¶

Maximum speculative filters per table.

Type: Integer Default: 10 Read-Only: No

Example:

-- Allow more auto-created filters
SET smfi_max_filters_per_table = 20;

-- Limit auto-created filters
SET smfi_max_filters_per_table = 5;

smfi_parallel_threshold¶

Minimum rows before enabling parallel filtering.

Type: Integer Default: 10000 Read-Only: No

Example:

-- Parallelize smaller scans
SET smfi_parallel_threshold = 5000;

-- Only parallelize large scans
SET smfi_parallel_threshold = 100000;

smfi_max_workers¶

Maximum parallel workers for filtering.

Type: Integer Default: Number of CPU cores Read-Only: No

Example:

-- Limit to 4 workers
SET smfi_max_workers = 4;

-- Use all available cores
RESET smfi_max_workers;

smfi_bloom_fpr¶

Bloom filter false positive rate.

Type: Float Default: 0.01 (1%) Read-Only: No Valid Range: 0.001-0.1

Example:

-- Lower false positive rate (more accurate, more space)
SET smfi_bloom_fpr = 0.001;

-- Higher false positive rate (less space)
SET smfi_bloom_fpr = 0.05;

Trade-offs:

FPR	Space	Accuracy	Use Case
0.001 (0.1%)	High	Very High	Critical lookups
0.01 (1%)	Medium	High	General purpose (default)
0.05 (5%)	Low	Moderate	Space-constrained

Display Settings¶

client_encoding¶

Client character encoding.

Type: String Default: 'UTF8' Read-Only: No

Example:

SET client_encoding = 'UTF8';

datestyle¶

Date display format.

Type: String Default: 'ISO, MDY' Read-Only: No

Example:

SET datestyle = 'ISO, MDY';
SET datestyle = 'SQL, DMY';
SET datestyle = 'Postgres, MDY';

timezone¶

Session timezone.

Type: String Default: 'UTC' Read-Only: No

Example:

SET timezone = 'UTC';
SET timezone = 'America/New_York';
SET timezone = 'Europe/London';

Read-Only Settings¶

These settings can be viewed with SHOW but cannot be modified.

server_version¶

HeliosDB-Lite version.

Type: String Read-Only: Yes

Example:

SHOW server_version;
-- Output: 2.1.0

server_encoding¶

Server character encoding.

Type: String Read-Only: Yes

Example:

SHOW server_encoding;
-- Output: UTF8

max_connections¶

Maximum server connections (from config).

Type: Integer Read-Only: Yes

Example:

SHOW max_connections;
-- Output: 100

port¶

Server listen port (from config).

Type: Integer Read-Only: Yes

Example:

SHOW port;
-- Output: 5432

Common Usage Patterns¶

Performance Tuning¶

-- For analytics/reporting
SET work_mem = 131072;          -- 128MB
SET shared_buffers = 1048576;   -- 1GB
SET enable_seqscan = on;
SET optimizer = on;

-- For OLTP workload
SET work_mem = 4096;            -- 4MB
SET statement_timeout = 5000;   -- 5 seconds
SET enable_indexscan = on;

Time-Travel Queries¶

-- Enable time-travel
SET time_travel_enabled = on;

-- Query historical data
SELECT * FROM orders
  AS OF TIMESTAMP '2025-11-20 10:00:00'
  WHERE customer_id = 123;

Vector Search Optimization¶

-- Configure vector index
SET vector_index_type = 'hnsw';
SET hnsw_ef_construction = 200;
SET hnsw_m = 16;

-- Create vector table
CREATE TABLE embeddings (
  id SERIAL PRIMARY KEY,
  vec VECTOR(384)
);

Compression Configuration¶

-- Set global compression
SET default_compression = 'zstd';
SET compression_level = 9;

-- Create compressed table
CREATE TABLE logs (
  id BIGSERIAL PRIMARY KEY,
  timestamp TIMESTAMP,
  message TEXT
) WITH COMPRESSION zstd;

SMFI Optimization¶

-- Bulk loads are now automatic (v3.4+)
-- SMFI auto-suspends for operations with 100+ rows:
INSERT INTO large_table VALUES (1,'a'), (2,'b'), ... -- 100+ rows
-- SMFI auto-resumes and schedules rebuild when done

-- For very large imports, manual control is still available:
SET smfi_tracking_enabled = off;
COPY large_table FROM '/data/import.csv';
SET smfi_tracking_enabled = on;
CALL smfi_rebuild_table('large_table');

-- For write-heavy workloads: reduce consolidation frequency
SET smfi_delta_threshold = 5000;
SET smfi_time_threshold = 600;

-- For read-heavy workloads: enable aggressive filtering
SET smfi_parallel_enabled = on;
SET smfi_parallel_threshold = 5000;

-- Check SMFI effectiveness
SELECT * FROM pg_smfi_table_stats();

Best Practices¶

Use appropriate timeouts:

-- Prevent runaway queries
SET statement_timeout = 30000;  -- 30 seconds

Tune memory for workload:

-- Analytics: More memory
SET work_mem = 131072;  -- 128MB

-- OLTP: Less memory, more concurrency
SET work_mem = 4096;    -- 4MB

Choose right isolation level:

-- Most cases: READ COMMITTED
SET transaction_isolation = 'READ COMMITTED';

-- Financial transactions: SERIALIZABLE
SET transaction_isolation = 'SERIALIZABLE';

Optimize compression:

-- Hot data: LZ4 (fast)
SET default_compression = 'lz4';

-- Cold data: Zstd level 9 (best ratio)
SET default_compression = 'zstd';
SET compression_level = 9;

Configure vector indexes:

-- Production: HNSW
SET vector_index_type = 'hnsw';
SET hnsw_ef_construction = 200;

-- Development: Flat (exact)
SET vector_index_type = 'flat';

SQL Settings Reference¶

Overview¶

Table of Contents¶

SET Command¶

Syntax¶

Examples¶

SHOW Command¶

Syntax¶

Examples¶

RESET Command¶

Syntax¶

Examples¶

Query Execution Settings¶

statement_timeout¶

query_timeout¶

Optimizer Settings¶

optimizer¶

enable_seqscan¶

enable_indexscan¶

enable_hashjoin¶

enable_mergejoin¶

enable_nestloop¶

Memory Settings¶

work_mem¶

shared_buffers¶

Transaction Settings¶

transaction_isolation¶

transaction_read_only¶

Time-Travel Settings¶

time_travel_enabled¶

Compression Settings¶

default_compression¶

compression_level¶

Vector Index Settings¶

vector_index_type¶

hnsw_ef_construction¶

hnsw_m¶

Materialized View Settings¶

mv_auto_refresh¶

mv_max_cpu_percent¶

SMFI Settings¶

smfi_enabled¶

smfi_tracking_enabled¶

smfi_bulk_load_threshold¶

smfi_parallel_enabled¶

smfi_max_cpu_percent¶

smfi_delta_threshold¶

smfi_time_threshold¶

smfi_min_query_frequency¶

smfi_drop_after_days¶

smfi_max_filters_per_table¶

smfi_parallel_threshold¶

smfi_max_workers¶

smfi_bloom_fpr¶

Display Settings¶

client_encoding¶

datestyle¶

timezone¶

Read-Only Settings¶

server_version¶

server_encoding¶

max_connections¶

port¶

Common Usage Patterns¶

Performance Tuning¶

Time-Travel Queries¶

Vector Search Optimization¶

Compression Configuration¶

SMFI Optimization¶

Best Practices¶

See Also¶