> ## Documentation Index
> Fetch the complete documentation index at: https://moridb.sh/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# PostgreSQL

> PostgreSQL engine — pgwire v3, AST-based classification, full feature reference.

## Overview

| Property          | Value                                                            |
| ----------------- | ---------------------------------------------------------------- |
| Engine ID         | `postgres`                                                       |
| Wire Protocol     | pgwire v3                                                        |
| Classifier        | AST-based (`pg_query_go` — PostgreSQL's actual parser internals) |
| Shadow            | Docker `postgres:<version>` (version-matched)                    |
| Extended Protocol | Full Parse/Bind/Describe/Execute/Close/Sync support              |

PostgreSQL is the reference implementation. All 12 routing strategies, the complete merged read pipeline, full write hydration, DDL tracking with schema registry, transaction management with savepoints, proxy-layer FK enforcement, and the four-layer write guard are fully implemented.

**Connection Parameters:**

| Field      | Description                                      | Default       |
| ---------- | ------------------------------------------------ | ------------- |
| `host`     | Database host                                    | —             |
| `port`     | Database port                                    | `5432`        |
| `user`     | Username                                         | —             |
| `password` | Password                                         | —             |
| `database` | Database name                                    | —             |
| `ssl_mode` | `disable`, `require`, `verify-ca`, `verify-full` | `verify-full` |

```bash theme={null}
mori init --from "postgres://user:pass@host:5432/mydb?sslmode=require"
```

## Engine-Specific Features

* **AST-based classification** via `pg_query_go` — PostgreSQL's actual parser compiled to Go. Every SQL statement is parsed into a full AST for precise classification with feature flags (IsJoin, HasAggregate, HasSetOp, IsComplexRead, HasReturning, HasOnConflict, HasWindowFunc, HasDistinct, HasCursor, etc.).
* **Extended query protocol** — Full Parse/Bind/Describe/Execute/Close/Sync support with parameter type resolution, binary format decoding, and statement caching.
* **RETURNING clause** — Used on INSERT/UPDATE/DELETE to extract exact PKs for precise per-row delta tracking.
* **`ctid` for PK-less tables** — Uses PostgreSQL's system column as row identity for deduplication when no user-defined PK exists.
* **Extension replication** — Auto-detects extensions from prod (`pg_extension`) and replicates them to the shadow container. Auto-installs missing extensions via `apt-get` when `CREATE EXTENSION` fails.
* **Read-replica conflict handling** — Automatic retry with exponential backoff on SQLSTATE 40001 (recovery conflict) during initialization.
* **Custom shadow images** — Use `--image` flag to specify a custom Docker image for the shadow container (e.g., for proprietary extensions).

## Known Limitations

* Unsupported operations: NOTIFY, COPY, LOCK TABLE, DO \$\$, CALL, EXPLAIN ANALYZE.
* Most extensions should work (tries to install on start) but there are some that might not work if the extension name isn't standard (anything not in the official PostgreSQL apt repo). Special support is provided out-of-the-box for pgvector and postgis. If your extension doesn't fall into either of those categories, then you can use the `--image` flag to specify a custom Docker image that has the extension installed.
* Functions aren't supported yet because it's really hard to determine if the function is non-mutating or not.
* CTE updates/deletes/upserts might produce incorrect subsequent merged read results.
* Performance: Rare, but some *extremely* complex JOINs over large tables might be slow either because (a) the prod data needed is too large or (b) the query is too complex and the safety mechanism decides to materialize the result into a temporary table locally. If this happens, try passing in `--max-rows` during `mori start`, which will cap the number of rows pulled from prod.