> ## Documentation Index > Fetch the complete documentation index at: https://docs.goldsky.com/llms.txt > Use this file to discover all available pages before exploring further. # PostgreSQL > Write data to PostgreSQL databases with automatic table creation and upsert support ## Overview Write data to a PostgreSQL database with automatic table creation and upsert support. You have two paths: * **Goldsky-hosted Postgres** (recommended for most users). Goldsky provisions and manages the database for you via [NeonDB](https://neon.tech). No database administration, no networking setup. Available on **Scale** plans and above. * **Bring your own Postgres**. Connect Turbo to a Postgres database you already run (Neon, Supabase, RDS, Cloud SQL, self-hosted, and similar). Pick a path below to get the connection details, then use the same `sinks` configuration for both. Hosted Postgres works the same way for both Mirror and Turbo pipelines. This page is the source of truth for both. [Mirror users](/mirror/sinks/postgres) are welcome here. Goldsky provisions a dedicated, autoscaling Postgres database for you. Creating the database automatically adds it to your **Sinks** and registers a Goldsky secret you can reference from any pipeline. There's no separate `goldsky secret create` step. Hosted Postgres is a **Scale** plan feature (and above). The **Free** plan does not include it. See the [pricing page](/pricing/summary#hosted-databases) for details. Adding a credit card to your account upgrades you to Scale. You can provision a hosted Postgres database from the [Goldsky web app](https://app.goldsky.com) or from the CLI. **From the web app** — two entry points: * **Sinks > New sink > Hosted Postgres**. Provision a standalone database you can reuse across pipelines. * **From the pipeline create flow**. When you're configuring a Postgres sink for a new pipeline, choose "Create a new hosted Postgres database" inline and the dashboard will provision one and wire it into the pipeline. Once the database is ready, the dashboard shows the host, port, user, password, database name, and full connection string. The secret is also added to your secrets list under the name shown in the dashboard. You can return to the **Sinks** section at any time to look up connection info. **From the CLI** — provision a database and register its secret in one step: ```bash theme={null} goldsky hosted-sink create --type postgres ``` The command prints the new secret's **name**, **ID**, and **type**. For security, the connection string is **not** printed by the CLI — look it up in the web app's **Sinks** section if you need the raw credentials. Pass `--name` to choose the secret name (otherwise one is generated as `HOSTED_POSTGRES_`). See the [CLI reference](/reference/cli#hosted-sink) for all options. Use the secret name the dashboard assigned (visible in **Secrets** and on the hosted Postgres detail page) in the `secret_name` field of your Postgres sink. See [Configuration](#configuration) below. No firewall rules, no `use_dedicated_ip`, no role creation needed. You can reuse the same hosted Postgres secret for multiple Postgres sinks and for [dynamic table](/turbo-pipelines/transforms/dynamic-tables) backends in the same pipeline. Use this when you already run Postgres yourself or with a managed provider (Neon, Supabase, AWS RDS / Aurora, Cloud SQL, self-hosted, and similar). Turbo needs a role that can create schemas and write to the target schema: ```sql theme={null} CREATE ROLE goldsky_writer WITH LOGIN PASSWORD 'your_secure_password'; -- Needed even if the target schemas already exist, because the sink -- creates schemas/tables with CREATE ... IF NOT EXISTS on first write. GRANT CREATE ON DATABASE your_database TO goldsky_writer; GRANT USAGE, CREATE ON SCHEMA TO goldsky_writer; ``` * **Public endpoint** (Neon, Supabase, RDS with a public endpoint, and similar): no extra setup beyond a working connection string. SSL is typically required (`?sslmode=require`). * **IP-allowlisted database**: enable [static IPs](/mirror/static-ips) on your account, allowlist Goldsky's egress IPs on your database firewall, and set `use_dedicated_ip: true` at the top level of your pipeline YAML. Static IPs are an enterprise feature. Contact [support@goldsky.com](mailto:support@goldsky.com) to enable it before deploying. * **Supabase direct connections**: direct connection URLs are IPv6-only. Use the **Session Pooler** connection string from your Supabase dashboard, or buy the IPv4 add-on. See [Supabase](#supabase) below for details. ```bash theme={null} goldsky secret create MY_POSTGRES_SECRET ``` When prompted, paste a standard connection string: ``` postgres://goldsky_writer:your_secure_password@db.example.com:5432/your_database?sslmode=require ``` The CLI stores it as a JDBC-style secret (`host`, `port`, `user`, `password`, `databaseName`). Alternatively, pass the full JSON inline: ```bash theme={null} goldsky secret create --name MY_POSTGRES_SECRET --value '{ "type": "jdbc", "protocol": "postgresql", "host": "db.host.com", "port": 5432, "databaseName": "myDatabase", "user": "myUser", "password": "myPassword" }' ``` Use the secret name in the `secret_name` field of your Postgres sink. See [Configuration](#configuration) below. ## Configuration The same sink configuration works for both Goldsky-hosted and bring-your-own Postgres. Only the secret behind `secret_name` differs. ```yaml theme={null} sinks: my_postgres_sink: type: postgres from: my_transform schema: public table: my_table secret_name: MY_POSTGRES_SECRET # or your hosted Postgres secret name primary_key: id # Optional, enables upsert behavior ``` ## Parameters Must be `postgres`. Name of the transform or source to read data from. PostgreSQL schema name (for example, `public`, `analytics`). Created automatically if it doesn't exist. Table name to write to. Created automatically if it doesn't exist, with columns inferred from the upstream Arrow schema. Name of a Goldsky secret holding the Postgres connection details (host, port, user, password, databaseName). For hosted Postgres, use the secret name shown on the hosted database detail page. Comma-separated list of columns to use as the table's primary key. When set, writes become upserts (`INSERT ... ON CONFLICT DO UPDATE`). When omitted, writes are plain inserts and no primary key is added to the auto-created table. Behavior when a row with the same `primary_key` already exists. `update` runs `ON CONFLICT ... DO UPDATE SET`, `nothing` runs `ON CONFLICT ... DO NOTHING`. Only applies when `primary_key` is set. Map of `column: operator` conditions that gate the `DO UPDATE` on an upsert. Each entry becomes `EXCLUDED."" "".""` and all entries are joined with `AND` into a single `WHERE` clause on the `ON CONFLICT ... DO UPDATE SET`. See [Conditional updates with `update_where`](#conditional-updates-with-update_where) below. Only applies when `primary_key` is set and `on_conflict` is `update` (its default); ignored when `on_conflict` is `nothing`. Maximum number of rows to accumulate before flushing to Postgres. Falls back to the engine default when unset. Maximum time to wait before flushing a partial batch, parsed as a [humantime](https://docs.rs/humantime/latest/humantime/fn.parse_duration.html) duration (for example, `"500ms"`, `"1s"`, `"2s"`). Falls back to the engine default when unset. Number of parallel writer tasks. Each task processes a slice of the accumulated batch concurrently. Increase for sink-bound pipelines. ## Behavior * **Auto schema and table creation**: both the schema and the table are created with `CREATE ... IF NOT EXISTS` on first write. Columns are derived from the upstream Arrow schema. * **No automatic schema migrations**: if the table already exists, the engine does not `ALTER TABLE` to add or change columns. Upstream schema changes that add new columns will fail at insert time. Drop or manually migrate the table first. * **Upsert vs. insert**: setting `primary_key` produces `INSERT ... ON CONFLICT () DO UPDATE SET ...`. Without `primary_key`, rows are plain `INSERT`s and no primary key constraint is created. * **Type mapping**: Arrow types are mapped automatically. `Int32` to `INTEGER`, `Int64` to `BIGINT`, `Utf8` to `TEXT`, `Boolean` to `BOOLEAN`, `Decimal(p, s)` to `NUMERIC(p, s)`, structs/lists/maps to `JSONB`. * **256-bit integers (`U256` / `I256`)**: see [256-bit integers](#256-bit-integers-u256--i256) below. * **`UInt64`**: stored as `NUMERIC` (because `BIGINT` is signed and cannot hold the full unsigned range). ## Conditional updates with `update_where` By default, an upsert (`primary_key` set, `on_conflict: update`) overwrites the existing row with every incoming row that shares the same primary key. `update_where` narrows that: the sink only overwrites the existing row when a per-column condition against the incoming row is true. Each entry in the map is `column: operator`. The sink compiles all entries into a single `WHERE` clause on the `DO UPDATE SET`, comparing the incoming value (`EXCLUDED.""`) to the value already in the table (`"
".""`): ```sql theme={null} theme={null} INSERT INTO "public"."my_table" (...) VALUES (...) ON CONFLICT ("id") DO UPDATE SET ... WHERE EXCLUDED."updated_at" > "my_table"."updated_at" ``` Multiple conditions are joined with `AND`. All of them must be true for the update to happen. If the `WHERE` clause is false, Postgres keeps the existing row and the incoming row is discarded. Allowed operators: `=`, `>`, `>=`, `<`, `<=`, `!=`, `<>`. `update_where` is validated at pipeline start: every column named in the map must exist in the upstream schema, and every operator must be in the list above. The pipeline fails to deploy otherwise. ### When to use it * **Ignore late-arriving updates**: gate on a monotonically increasing column like `updated_at` or a block number so an older event never overwrites a newer one. * **Only update on real change**: gate on the value column itself (for example, `status: "!="`) so a rewrite of the same row is a no-op. ### Example Only overwrite a row when the incoming `updated_at` is strictly newer than what's already stored: ```yaml theme={null} theme={null} sinks: users_postgres: type: postgres from: user_updates schema: public table: users secret_name: MY_POSTGRES_SECRET primary_key: id update_where: updated_at: ">" ``` `update_where` only applies to the upsert path (`primary_key` set and `on_conflict: update`, which is the default). It is ignored when `on_conflict` is `nothing`, and has no effect on plain inserts (no `primary_key`). ## 256-bit integers (`U256` / `I256`) EVM-native datasets contain values that exceed any standard SQL integer type. For example, `uint256` on `value`, `balance`, `totalSupply`, raw `wei` amounts, and ERC-20 `amount` columns. Turbo carries these through the pipeline as Arrow `FixedSizeBinary(32)` columns tagged with internal `U256` or `I256` metadata. When the Postgres sink writes one of these columns, it does the following automatically: * **Auto-created tables**: the column is created as `NUMERIC(78, 0)`. `78` digits is the smallest decimal precision that can represent every `U256` value (`2^256 − 1` has 78 decimal digits) and every `I256` value. `0` scale means whole numbers only, no fractional part is ever produced. * **In-flight encoding**: at write time, the sink converts each 32-byte big-endian value to its full decimal string representation (no scientific notation, no truncation) and sends that string in the `INSERT`. Postgres parses the string directly into the `NUMERIC` column. The conversion does not round, does not lose precision, and is independent of any client locale. * **Existing tables with a different type**: if the destination table already exists and the matching column is **not** `NUMERIC(78, 0)` (or a wider `NUMERIC` that can hold the same range), the sink **fails the write** instead of silently coercing or truncating. To migrate, drop the table and let the sink recreate it, or manually `ALTER` the column to `NUMERIC(78, 0)`. ### Querying `NUMERIC(78, 0)` values `NUMERIC(78, 0)` values are returned by Postgres drivers in different ways depending on the language. A few common cases: * **`psql` / generic SQL**: use the value as-is. Casts like `CAST(value AS NUMERIC) / 1e18` work, but mixing `NUMERIC` with floating-point loses precision. Keep arithmetic in `NUMERIC` if you care about exactness. * **Node.js (`pg` library)**: `NUMERIC` is returned as a JavaScript **string** by default to avoid silent precision loss. Convert with `BigInt(row.value)` rather than `Number(row.value)`. * **Python (`psycopg2` / `psycopg3`)**: returned as `decimal.Decimal`, which is full-precision. * **Go (`pgx`)**: use `pgtype.Numeric` or scan into a `*big.Int` / `*big.Float`. Scanning into `int64` will overflow. Do **not** scan a `NUMERIC(78, 0)` column into a 64-bit integer or a double. The value will overflow or lose precision silently in most drivers. ### Pre-converting in a transform If your downstream consumers cannot work with arbitrary-precision decimals, convert before the sink: * **SQL transform**: cast to a smaller type only when you know the value fits. For example, ERC-20 amounts scaled by `decimals` often fit in `DECIMAL(38, 18)`. Out-of-range values will raise an error. * **TypeScript transform**: use `BigInt` for math and return the result as a string. The TypeScript sandbox supports `BigInt` natively. See the [TypeScript transform](/turbo-pipelines/transforms/typescript) docs. ## Tips for backfilling large datasets into PostgreSQL While PostgreSQL offers fast access of data, writing large backfills into PostgreSQL can sometimes be hard to scale. Pipelines are often bottlenecked against sinks. Things to try: ### Avoid indexes on tables until after the backfill Indexes increase the amount of writes needed for each insert. When doing many writes, inserts can slow down significantly if you're hitting resource limits. ### Bigger batch sizes for the inserts The `batch_size` setting controls how many rows are batched into a single insert statement. Depending on the size of the events, you can increase this to help with write performance. `1000` is a good number to start with. The pipeline will collect data until the batch is full, or until `batch_flush_interval` is met. ### Temporarily scale up the database Look at your database stats like CPU and memory to see where the bottlenecks are. Big writes are often not blocked on CPU or RAM, but on network or disk I/O. For Google Cloud SQL, there are I/O burst limits that you can surpass by increasing the amount of CPU. For AWS RDS instances (including Aurora), the network burst limits are documented for each instance. A rule of thumb is to look at the `EBS baseline I/O` performance, since burst credits are easily used up in a backfill scenario. ## Provider-specific notes ### AWS Aurora Postgres When using Aurora for large datasets, use `Aurora I/O optimized`. It charges for more storage but gives you immense savings on I/O credits. If you're streaming the entire chain into your database or have a very active subgraph, these savings can be considerable, and the disk performance is significantly more stable, resulting in a more stable CPU usage pattern. ### Supabase Supabase's direct connection URLs only support IPv6 connections and will not work with our default validation. There are two solutions: 1. Use **Session Pooling**. In the Supabase connection screen, scroll down to see the connection string for the session pooler. This is included in all Supabase plans and works for most people. Sessions will expire and may produce some warning logs in your pipeline logs. These are handled gracefully and no action is needed. No data will be lost due to a session disconnection. 2. Alternatively, buy the IPv4 add-on if session pooling doesn't fit your needs. It can lead to more persistent direct connections. ### Neon Use the connection string from your Neon project dashboard. SSL is required (`?sslmode=require` is included by default in the connection string Neon gives you). ### Self-hosted PostgreSQL Set `listen_addresses = '*'` in `postgresql.conf`, add a `pg_hba.conf` rule for your Goldsky writer role, and ensure your host firewall allows inbound TCP on the Postgres port. ## Pricing Hosted Postgres usage (CPU-hours and storage) is metered hourly and billed under the **Hosted databases** line on the [pricing page](/pricing/summary#hosted-databases). Paused or deleted pipelines transition the database to idle, and you are **not** charged for utilization during idle time (storage is still billed). Bring-your-own Postgres has no Goldsky database charges. You pay your own provider directly. ## Example ```yaml theme={null} sinks: postgres_transfers: type: postgres from: filtered_transfers schema: public table: erc20_transfers secret_name: MY_POSTGRES primary_key: id ``` ## Related topics - [PostgreSQL](/mirror/sinks/postgres.md) - [Pipeline configuration](/mirror/reference/config-file/pipeline.md) - [Mirror vs. Turbo pipelines](/mirror-vs-turbo.md) - [PostgreSQL aggregation](/turbo-pipelines/sinks/postgres-aggregate.md) - [Sync subgraph to postgres](/mirror/guides/sync-subgraph-to-postgres.md)