SQL Settings Reference

SQL Settings Reference

Overview

HeliosDB Nano 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 Nano 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 Nano 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

1. Use appropriate timeouts:

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

2. Tune memory for workload:

   -- Analytics: More memory
   SET work_mem = 131072;  -- 128MB
   
   -- OLTP: Less memory, more concurrency
   SET work_mem = 4096;    -- 4MB

3. Choose right isolation level:

   -- Most cases: READ COMMITTED
   SET transaction_isolation = 'READ COMMITTED';
   
   -- Financial transactions: SERIALIZABLE
   SET transaction_isolation = 'SERIALIZABLE';

4. 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;

5. Configure vector indexes:

   -- Production: HNSW
   SET vector_index_type = 'hnsw';
   SET hnsw_ef_construction = 200;
   
   -- Development: Flat (exact)
   SET vector_index_type = 'flat';

See Also

• REPL Command Reference - Interactive shell commands
• Configuration File Reference - Global configuration
• Performance Tuning Guide - Optimization techniques