PostgreSQL Data Connector

PostgreSQL is an advanced open-source relational database management system known for its reliability, extensibility, and support for SQL compliance.

The PostgreSQL Server Data Connector enables federated/accelerated SQL queries on data stored in PostgreSQL databases.

datasets:
  - from: postgres:my_table
    name: my_dataset
    params: ...

Quickstart

Connect to a local PostgreSQL database and accelerate a table for fast local queries:

version: v1
kind: Spicepod
name: pg_demo

datasets:
  - from: postgres:public.customers
    name: customers
    params:
      pg_host: localhost
      pg_port: "5432"
      pg_db: mydb
      pg_user: spice_reader
      pg_pass: ${secrets:PG_PASSWORD}
    acceleration:
      enabled: true

echo "PG_PASSWORD=your_password" > .env

Start Spice and query the data:

spice run
# In another terminal:
spice sql
sql> SELECT count(*) FROM customers;

Configuration

from

The from field takes the form postgres:my_table where my_table is the table identifer in the PostgreSQL server to read from.

The fully-qualified table name (database.schema.table) can also be used in the from field.

datasets:
  - from: postgres:my_database.my_schema.my_table
    name: my_dataset
    params: ...

name

The dataset name. This will be used as the table name within Spice.

Example:

datasets:
  - from: postgres:my_database.my_schema.my_table
    name: cool_dataset
    params: ...

SELECT COUNT(*) FROM cool_dataset;

+----------+
| count(*) |
+----------+
| 6001215  |
+----------+

params

The connection to PostgreSQL can be configured by providing the following params:

Replication parameters

The following parameters configure PostgreSQL logical replication (WAL streaming) when using refresh_mode: changes:

Types

The table below shows the PostgreSQL data types supported, along with the type mapping to Apache Arrow types in Spice.

numeric precision and scale

numeric(p,s) maps to Decimal128(p,s), including negative scales. An omitted scale defaults to zero: numeric(12) becomes Decimal128(12,0).

Unconstrained numeric maps to Decimal128(38,20). Reads fail for values requiring more than 18 integral or 20 fractional digits. An explicit column precision and scale controls this mapping.

Write Support

The PostgreSQL connector supports writing data to PostgreSQL tables using SQL INSERT INTO, UPDATE, and DELETE FROM statements.

To enable writes, set access: read_write on the dataset:

datasets:
  - from: postgres:public.events
    name: events
    access: read_write
    params:
      pg_host: localhost
      pg_port: '5432'
      pg_db: mydb
      pg_user: spice_writer
      pg_pass: ${secrets:PG_PASSWORD}

-- Insert rows
INSERT INTO events (id, name, amount)
VALUES (1, 'Alice', 100.0), (2, 'Bob', 200.0);

-- Update rows
UPDATE events SET amount = 150.0 WHERE id = 1;

-- Delete rows
DELETE FROM events WHERE id = 2;

Write modes with acceleration

When PostgreSQL is used as the federated source for an accelerated dataset, acceleration.write_mode selects how writes propagate between the local accelerator and PostgreSQL:

• write_through (default): writes are sent to PostgreSQL synchronously. The client receives an ACK only after the source commits. The local accelerator is updated via the configured refresh path. Choose this for ACID guarantees.
• write_back: writes are applied to the local accelerator first (fast ACK), then forwarded asynchronously to PostgreSQL. Choose this for write throughput when eventual consistency at the source is acceptable.

acceleration.refresh_mode: changes is supported for access: read_write datasets: writes go to PostgreSQL and the WAL replication stream applies the resulting changes back to the accelerator.

datasets:
  - from: postgres:public.events
    name: events
    access: read_write
    params:
      pg_host: localhost
      pg_port: '5432'
      pg_db: mydb
      pg_user: spice_writer
      pg_pass: ${secrets:PG_PASSWORD}
      # Replication-mode parameters (see Configuration above)
      pg_publication: spice_pub
      pg_replication_slot: spice_slot
    acceleration:
      engine: duckdb
      mode: file
      refresh_mode: changes
      write_mode: write_through # default; use write_back for fast async writes

For more details, see Data Ingestion.

Examples

Connecting using Username/Password

datasets:
  - from: postgres:my_database.my_schema.my_table
    name: my_dataset
    params:
      pg_host: localhost
      pg_port: 5432
      pg_db: my_database
      pg_user: my_user
      pg_pass: ${secrets:my_pg_pass}

Connect using SSL

datasets:
  - from: postgres:my_database.my_schema.my_table
    name: my_dataset
    params:
      pg_host: localhost
      pg_port: 5432
      pg_db: my_database
      pg_user: my_user
      pg_pass: ${secrets:my_pg_pass}
      pg_sslmode: verify-ca
      pg_sslrootcert: ./custom_cert.pem

Separate dataset/accelerator secrets

Specify different secrets for a PostgreSQL source and acceleration:

datasets:
  - from: postgres:my_schema.my_table
    name: my_dataset
    params:
      pg_host: localhost
      pg_port: 5432
      pg_db: my_database
      pg_user: my_user
      pg_pass: ${secrets:pg1_pass}
    acceleration:
      engine: postgres
      params:
        pg_host: localhost
        pg_port: 5433
        pg_db: acceleration
        pg_user: two_user_two_furious
        pg_pass: ${secrets:pg2_pass}

Secrets

Spice integrates with multiple secret stores to help manage sensitive data securely. For detailed information on supported secret stores, refer to the secret stores documentation. Additionally, learn how to use referenced secrets in component parameters by visiting the using referenced secrets guide.

Cookbook

• A cookbook recipe to configure PostgreSQL as a data connector in Spice. Postgres Data Connector
• A cookbook recipe to configure AWS RDS for PostgreSQL as a data connector in Spice. AWS RDS for PostgreSQL
• A cookbook recipe to configure Supabase as a data connector in Spice. Supabase (PostgreSQL Data Connector)
• A cookbook recipe to stream changes from PostgreSQL into Spice with logical replication. PostgreSQL CDC (Logical Replication)