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

# PostgreSQL

> Write typed tables to PostgreSQL

`connectors/postgres/sink` (registered as `postgres`, **beta**) loads each
resource into its own typed PostgreSQL table. Native column types are derived
from the source schema, and primary keys and `NOT NULL` constraints are
preserved. It supports snapshot, incremental, and CDC pipelines against the
same destination tables.

## Configuration

| Field | Scope | Default | Description |
| - | - | - | - |
| `connection_method` | Connection | `fields` | `fields` builds the DSN from the fields below. `url` uses `dsn` directly. |
| `dsn` | Connection | — | PostgreSQL connection URL. Required when `connection_method` is `url`. Secret. |
| `host` | Connection | — | Server hostname. Required in `fields` mode. |
| `port` | Connection | `5432` | Server port |
| `username` | Connection | — | Required in `fields` mode |
| `password` | Connection | — | Required in `fields` mode. Secret. |
| `database_name` | Connection | — | Database to connect to. Required in `fields` mode. |
| `ssl_mode` | Connection | `prefer` | TLS verification: `disable`, `allow`, `prefer`, `require`, `verify-ca`, `verify-full` |
| `schema` | Pipeline | `public` | Destination schema |
| `mode` | Pipeline | `typed` | Destination table mode. `typed` is the only value. |

A `schema` left empty is defaulted by the server to the normalized source
connection name. `Open` sizes a pgx pool to at least the run's snapshot
parallelism and runs `CREATE SCHEMA IF NOT EXISTS` for the destination schema.

## Supported write modes

The sink advertises `full_replace`, `full_append`, `full_upsert`,
`incremental_upsert`, `cdc_append`, and `cdc_merge`. See
[replication modes](/pages/guides/concepts/replication-modes) for how the
planner picks one. They bind to three write behaviors:

* `full_replace` replaces the destination; `full_append` inserts without
  removing existing rows.
* `full_upsert` and `incremental_upsert` insert with
  `ON CONFLICT (pk) DO UPDATE` (`DO NOTHING` when the row is PK-only), so
  re-runs and retries are idempotent.
* `cdc_append` retains every change as a history row. `cdc_merge` maintains
  current state: inserts and updates upsert, and deletes delete.

## Table management

Before extraction starts, `EnsureSchema` runs per resource:

1. `CREATE TABLE IF NOT EXISTS` with columns from the source schema, a primary
   key from the source's key fields, and `NOT NULL` from field nullability.
2. `TRUNCATE` — only for replace. Append preserves existing rows; upsert and
   merge preserve resumable state.
3. `ADD COLUMN IF NOT EXISTS` for each column, so new source fields appear on
   existing tables. Evolution is add-only. Type changes, dropped columns, and
   nullability changes are not applied. An incompatible type change fails later
   when a write cannot cast the value.

### Type mapping

| Logical type | PostgreSQL type |
| - | - |
| `bool` | `boolean` |
| `int16` / `int32` / `int64` | `smallint` / `integer` / `bigint` |
| `float32` / `float64` | `real` / `double precision` |
| `decimal` | source-native type if provided, else `numeric` |
| `string` | `text` |
| `bytes` | `bytea` |
| `date` / `time` | `date` / `time` |
| `timestamp` / `timestamptz` | `timestamp` / `timestamptz` |
| `json` | `jsonb` |
| `uuid` | `uuid` |
| `array` | source-native type if provided, else `text[]` |
| unknown | source-native type if provided, else `text` |

## Write mechanics and atomicity

Rows are encoded into PostgreSQL's binary `COPY` format directly from the Arrow
columns. Replace and append batches copy into the destination table. Upserts
copy into a per-connection temporary table and merge into the destination with
`ON CONFLICT`, inside one transaction.

CDC merge batches are split into consecutive delete and non-delete ranges and
applied in arrival order inside **one transaction per batch**. Each range is
copied to a temporary row or key table before its upsert or delete statement. A
merge against a keyless resource is an error.

Before sending a COPY payload, the sink verifies a CRC embedded in its encoded
form. It returns that checksum as `EncodedCRC` alongside the Arrow batch
checksum, so the engine records both the in-memory handoff and PostgreSQL
serialization boundaries. This is not a query that reads committed rows back
from the table.

Batches commit as they are applied (autocommit), so partial data is visible in
the destination mid-run. Delivery is at-least-once, and upsert and merge modes
are idempotent through the primary key. `Commit` closes the pool. There is no
end-of-run atomic swap.

## Failure behavior

On a failed run, `Abort` truncates replace tables. Append tables retain rows
already written, so retrying a non-checkpointed append as a new run can produce
duplicates. Upsert and merge tables are preserved because retries converge
through the primary key; resumable extraction failures skip `Abort` entirely.


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.