Skip to main content

PostgreSQL

PostgreSQL cannot read gzip files from a bucket, or Parquet files behind an Iceberg or DuckLake catalog, on its own. You either load delivered records into a table with a jsonb column on a schedule, or read them through an extension that embeds a lake reader. Both work on self-managed PostgreSQL and on Supabase, and the example queries are the same either way.

When to use PostgreSQL​

Use PostgreSQL when your application already runs on it and you want delivered records next to your other tables, for joins, row-level security, APIs and dashboards. A loaded table is only as fresh as the last load. For ad hoc analysis over weeks or months of records, an engine that reads the destination in place, such as DuckDB, is faster and needs no load job. The overview compares the engines.

Options​

OptionReadsNeedsTested here
Load with DuckDB (recommended)Every destinationDuckDB on a machine that runs the scheduleBucket and DuckLake sources
Load with psqlBucket objectsThe AWS CLI and psqlAgainst a local S3-compatible store
Supabase WrappersBucket objects, Iceberg tables, Supabase Analytics BucketsThe wrappers extension, on Supabase or self-hostedAgainst a local Supabase stack
pg_duckdbEvery destinationA self-managed server where you can install extensionsNo

The three loads fill the same two tables, so pick by where you can run code. The DuckDB load runs anywhere you can schedule a command. On Supabase, the wrappers let a schedule inside the database do the same work.

Create the tables​

Create these once, in the database you load into:

CREATE TABLE s2b_records (
delivery_batch_id text NOT NULL,
batch_id text NOT NULL,
received_at timestamptz NOT NULL,
signal text NOT NULL,
resource jsonb,
scope jsonb,
data jsonb NOT NULL
);

CREATE TABLE s2b_loaded_batches (
delivery_batch_id text PRIMARY KEY,
loaded_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX ON s2b_records (signal, received_at);
CREATE INDEX ON s2b_records (delivery_batch_id);
CREATE INDEX ON s2b_records USING gin (data jsonb_path_ops);
  • s2b_records has one row per record, with the envelope fields from the delivery format as columns. resource and scope are NULL for JSON events.
  • s2b_loaded_batches lists the delivery batches already loaded. Each object, and each table commit, has its own delivery_batch_id and its contents never change, so a batch listed here never needs loading again. Every load below adds a batch to this table in the same transaction as its records, so a failed load leaves nothing behind and the next run tries the batch again.
  • The (signal, received_at) index serves the most common filter. The delivery_batch_id index makes it cheap to delete one batch and load it again. The GIN index serves containment filters such as data @> '{"event": "order.paid"}'.

To filter often on one field, add an expression index for it:

CREATE INDEX ON s2b_records ((data->>'event')) WHERE signal = 'event';

Give the loader its own role, with only what a load needs:

CREATE ROLE s2b_loader LOGIN PASSWORD '<password>';
GRANT SELECT, INSERT ON s2b_records, s2b_loaded_batches TO s2b_loader;

Load with DuckDB​

DuckDB reads every Send2Bucket destination, and its postgres extension writes into PostgreSQL. Save this as load_s2b.sql:

INSTALL httpfs; LOAD httpfs; INSTALL postgres; LOAD postgres;

CREATE SECRET s2b_bucket (
TYPE s3, KEY_ID '<access_key_id>', SECRET '<secret_access_key>', REGION '<region>');

ATTACH 'postgresql://s2b_loader:<password>@db.example.com:5432/analytics' AS pg (TYPE postgres);

BEGIN;

CREATE TEMP TABLE new_lines AS
SELECT *
FROM read_json('s3://<bucket>/<prefix>/s2b/stream=*/date=*/hour=*/*.ndjson.gz',
format = 'newline_delimited',
hive_partitioning = true,
columns = {delivery_batch_id: 'VARCHAR', batch_id: 'VARCHAR',
received_at: 'TIMESTAMPTZ', signal: 'VARCHAR',
resource: 'JSON', scope: 'JSON', data: 'JSON'})
WHERE date >= current_date - 2
AND delivery_batch_id NOT IN (SELECT delivery_batch_id FROM pg.public.s2b_loaded_batches);

INSERT INTO pg.public.s2b_loaded_batches
SELECT DISTINCT delivery_batch_id, now() FROM new_lines;

INSERT INTO pg.public.s2b_records
SELECT delivery_batch_id, batch_id, received_at, signal, resource, scope, data
FROM new_lines;

COMMIT;

Run it from cron or any scheduler, as often as you want fresh data:

duckdb < load_s2b.sql
  • DuckDB's JSON columns arrive in PostgreSQL as jsonb, and TIMESTAMPTZ keeps the microseconds of received_at.
  • stream=* skips the test objects that destination checks write to <prefix>/s2b/_verify/.
  • The date filter limits each run to the last few days of folders. Remove it for the first load. date is the UTC day Send2Bucket received the records, so a delivery that was held up for longer than the window, for example while a destination was blocked, lands outside it. After an outage, run once without the filter.
  • Both inserts run in one PostgreSQL transaction. If either fails, nothing is committed, and the next run loads the same batches.
  • If two runs overlap, the second fails on the primary key of s2b_loaded_batches and commits nothing. Its batches are picked up by the next run.
  • DuckDB writes a value for every column of the target table, NULL for the ones you leave out, so a column default does not apply. That is why the load passes now() for loaded_at.

For Amazon S3 with an AWS profile or instance role, use CREATE SECRET s2b_bucket (TYPE s3, PROVIDER credential_chain, REGION '<region>'). For Google Cloud Storage and S3-compatible storage, create the secret as in DuckDB: Connect. The key needs to list and read <prefix>/s2b/.

From a table destination​

For Apache Iceberg, Amazon S3 Tables, Supabase Analytics Buckets and DuckLake, attach the catalog as in DuckDB: Connect and read payload instead of the objects. Replace the CREATE TEMP TABLE and the last INSERT in load_s2b.sql with these, using the name you attached the catalog as (lake for DuckLake, lakehouse for Iceberg):

CREATE TEMP TABLE new_lines AS
SELECT delivery_batch_id, payload::JSON AS e
FROM lake.<namespace>.<table>
WHERE ingested_at >= now() - INTERVAL 2 DAY
AND delivery_batch_id NOT IN (SELECT delivery_batch_id FROM pg.public.s2b_loaded_batches);

INSERT INTO pg.public.s2b_records
SELECT delivery_batch_id,
e->>'$.batch_id',
CAST(e->>'$.received_at' AS TIMESTAMPTZ),
e->>'$.signal',
e->'$.resource',
e->'$.scope',
e->'$.data'
FROM new_lines;

payload holds the same envelope as an object line, so the tables and queries on this page work unchanged. Send2Bucket commits each delivery batch in one commit, so delivery_batch_id marks a complete batch here too. The ingested_at window works like the date filter above.

note

We ran the bucket load and the DuckLake load against a local S3-compatible store and a PostgreSQL catalog, with DuckDB 2.0 and the Supabase PostgreSQL 17 image. The Iceberg, S3 Tables and Supabase Analytics sources use the same DuckDB connections as the DuckDB page and were not loaded here.

Load with psql​

Without DuckDB, the AWS CLI and psql can do the same job one object at a time. Save this as load_object.sql. It loads one decompressed object from standard input:

\set ON_ERROR_STOP on
BEGIN;

CREATE TEMP TABLE s2b_stage (line jsonb) ON COMMIT DROP;

\copy s2b_stage FROM pstdin WITH (FORMAT csv, QUOTE e'\x01', DELIMITER e'\x02')

WITH new_batches AS (
INSERT INTO s2b_loaded_batches (delivery_batch_id)
SELECT DISTINCT line->>'delivery_batch_id' FROM s2b_stage
ON CONFLICT DO NOTHING
RETURNING delivery_batch_id
)
INSERT INTO s2b_records
(delivery_batch_id, batch_id, received_at, signal, resource, scope, data)
SELECT line->>'delivery_batch_id',
line->>'batch_id',
(line->>'received_at')::timestamptz,
line->>'signal',
line->'resource',
line->'scope',
line->'data'
FROM s2b_stage
JOIN new_batches ON new_batches.delivery_batch_id = line->>'delivery_batch_id';

COMMIT;

And this as load_s2b.sh, which loads yesterday's and today's objects that are not loaded yet:

#!/usr/bin/env bash
set -euo pipefail
: "${DATABASE_URL:?}" "${S2B_BUCKET:?}" "${S2B_PREFIX:?}" "${S2B_STREAM_ID:?}"

work=$(mktemp -d); trap 'rm -rf "$work"' EXIT
loaded=$(psql "$DATABASE_URL" -Atc \
"SELECT delivery_batch_id FROM s2b_loaded_batches WHERE loaded_at > now() - interval '3 days'")

for day in "$(date -u -d yesterday +%F)" "$(date -u +%F)"; do
# aws s3 ls exits 1 when nothing matches, so an empty day is not an error.
keys=$(aws s3 ls --recursive \
"s3://$S2B_BUCKET/$S2B_PREFIX/s2b/stream=$S2B_STREAM_ID/date=$day/" \
| awk '$4 ~ /\.ndjson\.gz$/ {print $4}' || true)
for key in $keys; do
id=$(basename "$key" .ndjson.gz)
grep -qxF "$id" <<<"$loaded" && continue
aws s3 cp --quiet "s3://$S2B_BUCKET/$key" "$work/batch.ndjson.gz"
gunzip -c "$work/batch.ndjson.gz" > "$work/batch.ndjson"
psql "$DATABASE_URL" -q -f load_object.sql < "$work/batch.ndjson"
echo "loaded $id"
done
done
  • FORMAT csv with a quote and a delimiter byte that never appear in the JSON passes each line through unchanged. The default text format treats a backslash as an escape character, so \n and \" inside JSON strings are rewritten and PostgreSQL rejects the line as invalid JSON.
  • pstdin is psql's own standard input, here the decompressed file.
  • The object file name is its delivery_batch_id, so the script skips loaded objects without downloading them. ON CONFLICT DO NOTHING also keeps a second, overlapping run from loading a batch twice: it waits for the first run's transaction and then inserts nothing.
  • The script decompresses each object to a file before psql starts. gunzip fails on a cut-off download but still writes the lines it could read, so piping it straight into psql could load part of a batch.
  • date -d yesterday is GNU date. On macOS, use date -u -v-1d +%F.

For Google Cloud Storage, use gcloud storage ls and gcloud storage cp. For S3-compatible storage, add --endpoint-url https://<endpoint> to both aws commands.

note

We ran this script against a local S3-compatible store with the AWS CLI pointed at it through --endpoint-url. It was not run against AWS itself.

Supabase Wrappers​

Supabase Wrappers is a PostgreSQL extension of foreign data wrappers. It is available on every Supabase project and can be installed on self-hosted PostgreSQL. Two of its wrappers read Send2Bucket destinations:

  • The DuckDB wrapper runs DuckDB inside PostgreSQL and reads bucket objects with read_json.
  • The Iceberg wrapper reads Iceberg tables, including Supabase Analytics Buckets.

A foreign table reads the destination on every query. That works for an occasional look, but each query downloads and parses the objects again, so the examples load new batches into s2b_records with a SQL function and query that. Store the keys in Vault for anything beyond a test: each wrapper accepts vault_-prefixed options that take a secret ID instead of the key.

Bucket objects with the DuckDB wrapper​

CREATE EXTENSION IF NOT EXISTS wrappers WITH SCHEMA extensions;

CREATE FOREIGN DATA WRAPPER duckdb_wrapper
HANDLER duckdb_fdw_handler VALIDATOR duckdb_fdw_validator;

CREATE SERVER s2b_bucket FOREIGN DATA WRAPPER duckdb_wrapper OPTIONS (
type 's3',
key_id '<access_key_id>',
secret '<secret_access_key>',
region '<region>'
);

CREATE SCHEMA s2b_raw;

CREATE FOREIGN TABLE s2b_raw.objects (
delivery_batch_id text,
batch_id text,
received_at text,
signal text,
resource text,
scope text,
data text,
date date
) SERVER s2b_bucket OPTIONS (
table $$read_json('s3://<bucket>/<prefix>/s2b/stream=*/date=*/hour=*/*.ndjson.gz',
format = 'newline_delimited', hive_partitioning = true,
hive_types = {'date': DATE, 'hour': INTEGER},
columns = {delivery_batch_id: 'VARCHAR', batch_id: 'VARCHAR', received_at: 'VARCHAR',
signal: 'VARCHAR', resource: 'JSON', scope: 'JSON', data: 'JSON'})$$
);

For S3-compatible storage, add endpoint '<host>' (without https://) and url_style 'path' to the server options.

Then define the load:

CREATE FUNCTION s2b_load(days int DEFAULT 2) RETURNS bigint LANGUAGE plpgsql AS $$
DECLARE
since date := current_date - days;
loaded bigint;
BEGIN
WITH src AS (
SELECT * FROM s2b_raw.objects WHERE date >= since
), new_batches AS (
INSERT INTO s2b_loaded_batches (delivery_batch_id)
SELECT DISTINCT delivery_batch_id FROM src
ON CONFLICT DO NOTHING
RETURNING delivery_batch_id
), ins AS (
INSERT INTO s2b_records (delivery_batch_id, batch_id, received_at, signal, resource, scope, data)
SELECT delivery_batch_id, batch_id, received_at::timestamptz, signal,
resource::jsonb, scope::jsonb, data::jsonb
FROM src JOIN new_batches USING (delivery_batch_id)
RETURNING 1
)
SELECT count(*) INTO loaded FROM ins;
RETURN loaded;
END
$$;

SELECT s2b_load(); -- returns the number of records loaded
  • The filter on date is passed to DuckDB, which then lists only the matching folders. The function is PL/pgSQL so that since reaches the wrapper as a value: the wrapper cannot pass on an expression such as current_date - 2, and reads every folder instead.
  • For the first load, or after an outage, widen the window: SELECT s2b_load(days => 3650).
  • Columns are text and cast in PostgreSQL. With Wrappers 0.6.3, a jsonb foreign column failed with data type is not supported, and a timestamptz column dropped the fraction of a second.
  • To run the load on a schedule inside the database, use pg_cron: SELECT cron.schedule('s2b-load', '*/5 * * * *', 'SELECT s2b_load()');.

Iceberg tables and Analytics Buckets with the Iceberg wrapper​

For a Supabase Analytics Bucket, open the bucket in the Supabase dashboard, select Query with Postgres on the namespace, and name a schema such as s2b_lake. Supabase installs the wrapper and creates a foreign table for each Iceberg table in the namespace, with the four columns from Delivery format.

For any other Iceberg REST catalog, create them yourself:

CREATE EXTENSION IF NOT EXISTS wrappers WITH SCHEMA extensions;

CREATE FOREIGN DATA WRAPPER iceberg_wrapper
HANDLER iceberg_fdw_handler VALIDATOR iceberg_fdw_validator;

CREATE SERVER s2b_iceberg FOREIGN DATA WRAPPER iceberg_wrapper OPTIONS (
aws_access_key_id '<access_key_id>',
aws_secret_access_key '<secret_access_key>',
region_name '<region>',
catalog_uri '<catalog_uri>',
warehouse '<warehouse>',
token '<catalog_token>',
"s3.endpoint" 'https://<s3_endpoint>', -- only for storage outside AWS
"s3.path-style-access" 'true' -- only for stores that need path-style URLs
);

CREATE SCHEMA s2b_lake;
IMPORT FOREIGN SCHEMA "<namespace>" FROM SERVER s2b_iceberg INTO s2b_lake;

Load new batches the same way, reading payload:

CREATE FUNCTION s2b_load_lake() RETURNS bigint LANGUAGE sql AS $$
WITH src AS (
SELECT delivery_batch_id, payload::jsonb AS e FROM s2b_lake.<table>
), new_batches AS (
INSERT INTO s2b_loaded_batches (delivery_batch_id)
SELECT DISTINCT delivery_batch_id FROM src
ON CONFLICT DO NOTHING
RETURNING delivery_batch_id
), ins AS (
INSERT INTO s2b_records (delivery_batch_id, batch_id, received_at, signal, resource, scope, data)
SELECT delivery_batch_id, e->>'batch_id', (e->>'received_at')::timestamptz, e->>'signal',
e->'resource', e->'scope', e->'data'
FROM src JOIN new_batches USING (delivery_batch_id)
RETURNING 1
)
SELECT count(*) FROM ins
$$;

This reads the whole table on every run. Keep it that way for now: with Wrappers 0.6.3, a WHERE on signal_type returned no rows for some column lists, for example SELECT count(payload) ... WHERE signal_type = 'log', while the same scan without the filter was correct. A query that compared a column with a subquery on the same foreign table never returned and ignored cancel requests. Filter the loaded s2b_records instead.

note

We ran both wrappers with Wrappers 0.6.3 in the Supabase PostgreSQL 17 image, against a local S3-compatible store and a local Iceberg REST catalog. The dashboard's Query with Postgres flow, a hosted Analytics Bucket and the pg_cron schedule were not run here. Check your version with SELECT extversion FROM pg_extension WHERE extname = 'wrappers'.

pg_duckdb​

pg_duckdb embeds DuckDB in PostgreSQL, so a query can call DuckDB's read_json, iceberg_scan and DuckLake functions directly. It is not available on Supabase. On a server where you can install it:

CREATE EXTENSION pg_duckdb;

SELECT duckdb.create_simple_secret(
type := 'S3', key_id := '<access_key_id>', secret := '<secret_access_key>', region := '<region>');

SELECT r['signal'] AS signal, count(*) AS records
FROM read_json('s3://<bucket>/<prefix>/s2b/stream=*/date=*/hour=*/*.ndjson.gz') r
GROUP BY r['signal'];

Columns come back through the r['column'] syntax. See the pg_duckdb documentation for its functions and for attaching Iceberg and DuckLake catalogs.

note

This section follows the pg_duckdb documentation. We have not run it ourselves; the Supabase PostgreSQL image we tested does not include pg_duckdb.

DuckLake catalogs in PostgreSQL​

A DuckLake destination keeps its catalog in a PostgreSQL database, but the catalog holds only metadata: snapshots, schemas and the list of Parquet files. The records are in those Parquet files under the lake's data path. Plain SQL on the catalog tables cannot read them. Use the DuckDB load above, pg_duckdb, or query the lake with DuckDB. Do not write to the catalog tables yourself.

Example queries​

These run against s2b_records. ->> returns text, so cast before doing arithmetic.

Records per signal:

SELECT signal, count(*) AS records, min(received_at) AS first, max(received_at) AS last
FROM s2b_records
GROUP BY signal
ORDER BY records DESC;

JSON events by name:

SELECT data->>'event' AS event, count(*) AS events
FROM s2b_records
WHERE signal = 'event'
GROUP BY event
ORDER BY events DESC;
event | events
------------+--------
page.view | 177
order.paid | 53
email.sent | 47
signup | 23

Revenue from order.paid by day. The @> filter uses the GIN index:

SELECT date_trunc('day', received_at) AS day,
count(*) AS orders,
round(sum((data->>'cents')::numeric) / 100, 2) AS revenue
FROM s2b_records
WHERE signal = 'event' AND data @> '{"event": "order.paid"}'
GROUP BY day
ORDER BY day;

Log records by service and severity. service.name is one entry in the resource's attribute list, and a JSON path picks it out:

SELECT jsonb_path_query_first(resource,
'$.attributes[*] ? (@.key == "service.name").value.stringValue') #>> '{}' AS service,
data->>'severityText' AS severity,
count(*) AS logs
FROM s2b_records
WHERE signal = 'log'
GROUP BY service, severity
ORDER BY logs DESC;

#>> '{}' turns the JSON string into text. To read every attribute as a row instead, join jsonb_array_elements(resource->'attributes').

The five slowest spans. Timestamps are nanosecond strings, so cast them to numeric before subtracting:

SELECT data->>'name' AS span,
data->>'traceId' AS trace_id,
round(((data->>'endTimeUnixNano')::numeric
- (data->>'startTimeUnixNano')::numeric) / 1e6, 3) AS duration_ms
FROM s2b_records
WHERE signal = 'span'
ORDER BY duration_ms DESC
LIMIT 5;

To turn a nanosecond timestamp into a timestamptz, use to_timestamp((data->>'startTimeUnixNano')::numeric / 1e9).

Notes​

  • Loads keep records exactly once per delivery batch. Send2Bucket itself delivers at least once, so a client that resent a request has its records twice, in two batches. See Delivery semantics to deduplicate on an ID of your own.
  • OTLP enums are integers: severityNumber for logs and kind for spans. severityText is whatever the sender set, and can be empty.
  • Filter on signal and received_at first so PostgreSQL can use the composite index. A filter such as data->>'plan' = 'pro' reads every row of the signal, unless you add an expression index for it or write it as data @> '{"plan": "pro"}'.
  • PostgreSQL stores rows, not columns. For aggregations over many millions of records, query a table destination such as DuckLake or Apache Iceberg in place with DuckDB, and keep in PostgreSQL only what your application joins against.