PostgreSQL Engine Configuration

PostgreSQL is a realtime database that stores the materialized views and tables generated by your DataSQRL pipeline for low-latency querying.

Configuration Options

No mandatory configuration keys are required. Physical DDL (tables, indexes, views) is produced automatically by the DataSQRL compiler.

Key	Type	Default	Description
partition-ttl-divisor	integer	100	Controls the number of partitions for range-partitioned tables with a TTL (see Partitioning).
partition-premake	integer	4	Number of future partitions pg_partman pre-creates ahead of the current one (p_premake).

Basic Configuration

{
  "engines": {
    "postgres": {
      "partition-ttl-divisor": 100,
      "config": {
        // Optional PostgreSQL-specific settings
      }
    }
  }
}

Partitioning

Tables annotated with partition_key on a timestamp column and a ttl hint are range-partitioned with pg_partman, and expired partitions are dropped automatically. The partition width is derived from the TTL:

• The TTL duration divided by partition-ttl-divisor caps the number of partitions.
• The unit the TTL is declared with sets the minimum width: ttl(14 days) never produces partitions smaller than 1 day, while ttl(336 hours) allows hourly partitions.
• The result is snapped down to the closest calendar-aligned width from: 15 min, 30 min, 1, 2, 4, 6, 8, 12 hours, 1, 2, 4 days, 1, 2, 4, 8, 12 weeks.

For example, ttl(14 days) with the default divisor produces 1-day partitions.

At setup time, pg_partman pre-creates the historical partitions covering the full TTL window (via p_start_partition), so replayed or late-arriving data within the retention period lands in a dated partition rather than the DEFAULT partition. The partition-premake key controls how many future partitions are created ahead of the current one.

Conflict Handling

When a generated PostgreSQL table receives a row that conflicts with an existing row on a primary key or unique constraint, DataSQRL chooses one of the following conflict handling strategies:

Strategy	PostgreSQL behavior	Description
Update	ON CONFLICT (...) DO UPDATE SET ...	The existing row is overwritten with the incoming row.
Timestamp update	ON CONFLICT (...) DO UPDATE SET ... WHERE incoming timestamp > existing timestamp	The existing row is overwritten only when the incoming row has a newer timestamp than the existing row. Otherwise, the conflicting insert is silently skipped, which prevents out-of-order events from rolling back newer state.
Ignore	ON CONFLICT (...) DO NOTHING	The existing row is kept, and the conflicting insert is silently skipped.

DataSQRL applies these strategies automatically based on the generated table type:

• STATE tables use timestamp update with the appropriate timestamp column.
• STREAM tables use ignore.
• Other PostgreSQL tables use update.

Usage Notes

• Database schema is automatically generated from your SQRL script
• Tables, indexes, and views are created based on the compiled data pipeline
• Connection parameters are typically provided via environment variables
• The engine handles both real-time data ingestion and query serving
• Optimized for low-latency reads of materialized data
• Read replicas improve query performance and provide redundancy

Internal Environment Variables

When running pipelines with the DataSQRL run command, the following environment variables are used in the configuration:

• POSTGRES_VERSION
• POSTGRES_HOST
• POSTGRES_PORT
• POSTGRES_DATABASE
• POSTGRES_AUTHORITY
• POSTGRES_JDBC_URL
• POSTGRES_USERNAME
• POSTGRES_PASSWORD