# 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.

## Basic Configuration

```json
{
  "engines": {
    "postgres": {
      "config": {
        // Optional PostgreSQL-specific settings
      }
    }
  }
}
```

## Cloud Deployment

For cloud deployment configuration (instance sizes, replica counts), see [Cloud Deployment Configuration](cloud-deployment.md#postgresql-enginespostgresdeployment).

## 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](../sqrl-language#type-system):

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

<!--EXTENDED-->

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