---
title: "Sql Settings"
---

# SQL Settings Reference

## Overview

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

## Table of Contents

- [SET Command](#set-command)
- [SHOW Command](#show-command)
- [RESET Command](#reset-command)
- [Query Execution Settings](#query-execution-settings)
- [Optimizer Settings](#optimizer-settings)
- [Memory Settings](#memory-settings)
- [Transaction Settings](#transaction-settings)
- [Time-Travel Settings](#time-travel-settings)
- [Compression Settings](#compression-settings)
- [Vector Index Settings](#vector-index-settings)
- [Materialized View Settings](#materialized-view-settings)
- [SMFI Settings](#smfi-settings)
- [Display Settings](#display-settings)
- [Read-Only Settings](#read-only-settings)

## SET Command

Set a session variable to a new value.

### Syntax

```sql
SET setting_name = value;
SET setting_name TO value;
```

### Examples

```sql
-- 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

```sql
SHOW setting_name;
SHOW ALL;
```

### Examples

```sql
-- 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

```sql
RESET setting_name;
RESET ALL;
```

### Examples

```sql
-- 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:**
```sql
-- 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:**
```sql
-- 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:**
```sql
-- Enable optimizer
SET optimizer = on;

-- Disable for debugging
SET optimizer = off;
```

### enable_seqscan

Enable sequential scans.

**Type:** Boolean
**Default:** `on`
**Read-Only:** No

**Example:**
```sql
-- Disable sequential scans (force index usage)
SET enable_seqscan = off;
```

### enable_indexscan

Enable index scans.

**Type:** Boolean
**Default:** `on`
**Read-Only:** No

**Example:**
```sql
-- Force sequential scans for testing
SET enable_indexscan = off;
```

### enable_hashjoin

Enable hash join operations.

**Type:** Boolean
**Default:** `on`
**Read-Only:** No

**Example:**
```sql
SET enable_hashjoin = off;
```

### enable_mergejoin

Enable merge join operations.

**Type:** Boolean
**Default:** `on`
**Read-Only:** No

**Example:**
```sql
SET enable_mergejoin = off;
```

### enable_nestloop

Enable nested loop joins.

**Type:** Boolean
**Default:** `on`
**Read-Only:** No

**Example:**
```sql
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:**
```sql
-- 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:**
```sql
-- 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:**
```sql
-- 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:**
```sql
-- 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:**
```sql
-- 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:**
```sql
-- 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:**
```sql
-- 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:**
```sql
-- 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:**
```sql
-- 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:**
```sql
-- 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:**
```sql
-- 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:**
```sql
-- 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:**
```sql
-- 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:**
```sql
-- 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:**
```sql
-- 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:**
```sql
-- 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:**
```sql
-- 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:**
```sql
-- 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:**
```sql
-- 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:**
```sql
-- 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:**
```sql
-- 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:**
```sql
-- 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:**
```sql
-- 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:**
```sql
-- 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:**
```sql
-- 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:**
```sql
SET client_encoding = 'UTF8';
```

### datestyle


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

**Example:**
```sql
SET datestyle = 'ISO, MDY';
SET datestyle = 'SQL, DMY';
SET datestyle = 'Postgres, MDY';
```

### timezone

Session timezone.

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

**Example:**
```sql
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:**
```sql
SHOW server_version;
-- Returns the running HeliosDB Nano version string
```

### server_encoding

Server character encoding.

**Type:** String
**Read-Only:** Yes

**Example:**
```sql
SHOW server_encoding;
-- Output: UTF8
```

### max_connections

Maximum server connections (from config).

**Type:** Integer
**Read-Only:** Yes

**Example:**
```sql
SHOW max_connections;
-- Output: 100
```

### port

Server listen port (from config).

**Type:** Integer
**Read-Only:** Yes

**Example:**
```sql
SHOW port;
-- Output: 5432
```

## Common Usage Patterns

### Performance Tuning

```sql
-- 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

```sql
-- 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

```sql
-- 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

```sql
-- 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

```sql
-- Bulk loads are automatic
-- 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:**
   ```sql
   -- Prevent runaway queries
   SET statement_timeout = 30000; -- 30 seconds
   ```

2. **Tune memory for workload:**
   ```sql
   -- Analytics: More memory
   SET work_mem = 131072; -- 128MB

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

3. **Choose right isolation level:**
   ```sql
   -- Most cases: READ COMMITTED
   SET transaction_isolation = 'READ COMMITTED';

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

4. **Optimize compression:**
   ```sql
   -- 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:**
   ```sql
   -- Production: HNSW
   SET vector_index_type = 'hnsw';
   SET hnsw_ef_construction = 200;

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