--- name: puffgres description: Puffgres docs — Postgres→turbopuffer logical replication. Use when writing puffgres configs/transforms or running the puffgres CLI. --- # Puffgres — Agent Guide The complete Puffgres documentation flattened into one file for coding agents (generated from the docs site — do not edit by hand). Puffgres is a logical-replication service that keeps Postgres entities mirrored in turbopuffer. Configs link a Postgres table to a turbopuffer namespace via an immutable TypeScript transform. The CLI is `puffgres` (`init`, `new`, `check`, `apply`, `remove`, `run`). The full handbook follows. --- # Why puffgres? Postgres is one of the most common relational databases in the world. turbopuffer is a company building a fast database optimized for search. In particular, its strength is in dealing with vectors. Vector embeddings are a technique we use to search semantically, rather than just what is in the text; for example, searching "dog" on regular search wouldn't surface results for "puppy" or "canine," where semantic search would. Storing vectors, and finding results that are "near" (i.e. doing distance calculations) are very different load patterns than those generally found in relational databases. You can store vectors directly in Postgres using an extension called [pgvector]() but it has considerable [negative performance tradeoffs](https://alex-jacobs.com/posts/the-case-against-pgvector/): indexes and filtering aren't very performant, and the extra memory use degrades regular reads and writes. As such, many people use a relational database for most transactional workloads, and also keep their vectors in a separate database specialized for vector workloads, like Pinecone, Weaviate, or turbopuffer. The challenge in these cases becomes keeping the two databases in sync. There's a few approaches, all of which we thought were inadequate: - **Two database calls at each place data is changed**. For example, if you add a new row in Postgres, add a new row in the vector database. The same could happen for updates and deletes. The problem here is that we have no guarantees that both calls will succeed. If the Postgres call succeeds and vector call fails, or vice versa, the two will be permanently out of sync. - **Durable job queue for vector calls**. Another way we could do this would be to handle all calls in Postgres, make row changes, and, in a transaction, add the parallel modification to vectors into a durable job queue. If that queue lived in Postgres, the row change and the job to modify the vector could live in a transaction, meaning we'd have good guarantees they'd both succeed or both failed, and we could retry the vector updates if they had temporarily failed. The challenge here is one of later developer hygeine; developers would need to know, everytime they were modifying a given table, that they needed to have the corresponding call. It would be easy for a new developer, or developer months down the line to forget about an equivalent vector, and then the two databases would again be out of sync. - **Updates in data pipeline** We could have a separate table that tracks when a vector was updated, and, everytime our data pipeline runs, upsert any rows where the vector was updated before the row was updated. This was our initial solution, and it is poor becuase (a) it lower bounds the time to update vectors to the frequency of the data pipeline, in our case several minutes (b) requires janky logic to handle deletes and (c) is super inefficient and requires a full table scan even for minor updates each time. Instead of this, we designed puffgres to be a logical replication service. This is based on Martin Kleppman's talk [Turning the database inside out](https://martin.kleppmann.com/2015/03/04/turning-the-database-inside-out.html) about how all data is either source of truth or derived, and we generally should have derived sources replicate changes on source of truth. In essence, as a developer you only need to modify rows in Postgres, define mappings between Postgres tables and turbopuffer namespaces once, and then puffgres will listen to changes (on the Postgres write-ahead-log) and propagate them to turbopuffer. ``` ╔═ puffgres ═════════════════════╗ ╔═ turbopuffer ══════╗ ╔═ Postgres ══╗ ║ ║░ ║ ║░ ║ ║░ ║ ┏━ configs ━━━━━━━━━━━━━━━━━━┓ ║░ ║ ┏━ namespaces ━━━┓ ║░ ║ ┏━ WAL ━━━┓ ║░ row changes ║ ┃ page ──▶ page_chunks ┃ ║░ ║ ┃ page_chunks ┃ ║░ ║ ┃▞▞▞▞▞▞▞▞▞┃ ║░ ░ ░ ░ ░ ──▶║ ┃ person ──▶ person_names ┃ ║░──API──▶║ ┃ person_names ┃ ║░ ║ ┗━━━━━━━━━┛ ║░ ║ ┃ document ──▶ document_text ┃ ║░ ║ ┃ document_text ┃ ║░ ║ ║░ ║ ┗━━━━━━━━━━━━━━━━━━━━━━━━━━━━┛ ║░ ║ ┗━━━━━━━━━━━━━━━━┛ ║░ ╚═════════════╝░ ║ ║░ ║ ║░ ░░░░░░░░░░░░░░░ ╚════════════════════════════════╝░ ╚════════════════════╝░ ░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░ ░░░░░░░░░░░░░░░░░░░░░░ ``` --- # Programming model Puffgres chooses what WAL events to route and what to send to turbopuffer based on user-defined"configs" and corresponding transformations. These are based on two principles: - **Be simple**. Transformations should simply take in a set of Postgres rows, and output a set of turbopuffer rows. Batching, backfills, streaming, retries, routing, interrupts, etc are handled by the core puffgres process. - **Be maximally expressive**. We found that we needed lots of flexibility in what we would transform to turbopuffer; for example, we'd sometimes need to tokenize before embedding, combine fields into the text we entered, filter, etc. Transforms are defined as Typescript files that can access any Node package and make any arbitrary set of transformations. We also autogenerate a TS schema based on the underlying Postgres schema that lets us have some typing on our input rows. Each config is three files that fit together like this: ``` ╔═ Postgres ═══╗ ┌─ config.toml ─────┐ ┌─ transform.ts ────┐ ╔═ turbopuffer ═══╗ ║ ║░ │ │ │ │ ║ ║░ ║ page ║░────▶│ which table │─────▶│ rows ──▶ docs │─────▶║ page_chunks ║░ ║ ║░ │ ──▶ namespace │ │ any TypeScript │ ║ ║░ ╚══════════════╝░ │ │ │ │ ╚═════════════════╝░ ░░░░░░░░░░░░░░░░ └───────────────────┘ └─────────┬─────────┘ ░░░░░░░░░░░░░░░░░░░ │ input rows typed by schema.ts (autogenerated) ``` ### Config A config is a one-to-one mapping from a Postgres table to a turbopuffer namespace: ``` ┌─ config.toml ──────────┐ ╔═ Postgres ══╗ │ │ ╔═ turbopuffer ══╗ ║ ║░ │ source.table │ ║ ║░ ║ page ║░───────▶│ = "page" │───────▶║ page_chunks ║░ ║ ║░ │ namespace │ ║ ║░ ╚═════════════╝░ │ = "page_chunks" │ ╚════════════════╝░ ░░░░░░░░░░░░░░░ │ │ ░░░░░░░░░░░░░░░░░░ └────────────────────────┘ ``` ### Schema A schema is an autogenerated TypeScript representation of the Postgres table, so transforms get typed input rows: ``` ┌─ schema.ts ──────────────────────┐ ╔═ Postgres ═══════════╗ │ │ ║ ┌─ page ─────────┐ ║░ │ // autogenerated — do not edit │ ║ │ id uuid │ ║░ puffgres generate │ columns = [ │ ║ │ title text │ ║░─────────────────────▶│ { name: "id", "string" }, │ ║ │ body text │ ║░ │ { name: "title", "string" }, │ ║ └────────────────┘ ║░ │ { name: "body", "string" }, │ ╚══════════════════════╝░ │ ] │ ░░░░░░░░░░░░░░░░░░░░░░░░ └──────────────────────────────────┘ ``` ### Transform A transform is defined in TypeScript and run by a long-running process; puffgres streams events in and reads actions back over JSONL: ``` ╔═ puffgres core ══╗ ╔═ transform.ts ════════════════╗ ║ ║░ Event[] JSONL ║ ║░ ║ batching ║░──────────stdin──────────▶║ long-running TS process ║░ ║ retries ║░ ║ Event[] ──▶ Action[] ║░ ║ routing ║░◀─────────stdout──────────║ any TypeScript, any package ║░ ║ ║░ Action[] JSONL ║ ║░ ╚══════════════════╝░ ╚═══════════════════════════════╝░ ░░░░░░░░░░░░░░░░░░░░ ░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░ ``` We build configs to be immutable, sort of like database migrations. This is because we didn't want a transform to change in the middle of a backfill, leaving a namespace with some rows transformed with old logic and some transforms transformed with new ones. As such, the correct way to change a transform is to "tombstone" the old one and create another one. ### Tombstoning and recreating ``` ┌─ 0701_page ─────────┐ ┌─ 0701_page ─────────┐ ┌─ 0715_page ─────────┐ │ page │ tombstone │▞▞▞▞▞▞▞▞▞▞▞▞▞▞▞▞▞▞▞▞▞│ recreate │ page │ │ ──▶ page_chunks │ ─────────▶│▞▞▞▞ tombstoned ▞▞▞▞▞│ ─────────▶│ ──▶ page_chunks_v2 │ └──────────┬──────────┘ └─────────────────────┘ └──────────┬──────────┘ │ ✗ │ backfill ▼ ▼ ╔═ page_chunks ═══════╗ ╔═ page_chunks ═══════╗ ╔═ page_chunks_v2 ════╗ ║ ■■■■■■■■■■■■■■■■■■■ ║░ ║ ■■■■■■■■■■■■■■■■■■■ ║░ ║ ■■■■■■■░░░░░░░░░░░░ ║░ ╚═════════════════════╝░ ╚═════════════════════╝░ ╚═════════════════════╝░ ░░░░░░░░░░░░░░░░░░░░░░░ ░░░░░░░░░░░░░░░░░░░░░░░ ░░░░░░░░░░░░░░░░░░░░░░░ ■ synced ░ pending backfill ▞ tombstoned — no more writes ``` ### Batching and retries Changes stream out of Postgres continuously; rather than make a network call per change, puffgres buffers them and writes once per batch. puffgres will also batch really large operations into smaller ones. ```text changes from Postgres ● ● ● ● ● ● ● ● ● ● └────────────┬─────────────┘ collect until batch_size (100) │ ▼ ┌───────────┐ one write │ batch │ ──────────────▶ turbopuffer ✓ └───────────┘ │ ▲ │ fail └────────── retry ◀───────────┘ up to max_retries (5), then ──▶ dead letter queue ``` ### Dead letter queue When a batch fails after `max_retries`, it goes to the dead letter queue (DLQ) so the stream isn't blocked. The DLQ is replayed on a timer, off to the side of the live stream: ```text every dlq_replay_interval (10s) │ ▼ pull up to dlq_replay_batch_size (50) entries │ ▼ retry write ──── ok ──▶ turbopuffer ✓ (removed from DLQ) │ │ fail × dlq_max_retries (5) ▼ marked permanent — kept dlq_permanent_max_age_hours (72h) for inspection, then pruned ``` All of these knobs are configurable — see [advanced options](./advanced-options.md). --- # Getting started ## Install You can use our install script to pull everything. Under the hood, this verifies you have the Rust toolchain, builds from source, and adds `puffgres` to your PATH. ```bash curl -fsSL https://raw.githubusercontent.com/a24films/puffgres/main/install.sh | sh ``` ## Use with coding agents The docs are published as one file at , and you can install it as a skill using one of the scripts below. ```bash # Claude Code mkdir -p ~/.claude/skills/puffgres && curl -fsSL https://a24films.github.io/puffgres/AGENTS.md -o ~/.claude/skills/puffgres/SKILL.md ``` ```bash # Codex mkdir -p ~/.codex/skills/puffgres && curl -fsSL https://a24films.github.io/puffgres/AGENTS.md -o ~/.codex/skills/puffgres/SKILL.md ``` ## Setting Up a Project Navigate to the root level of your repo and run `puffgres init`. This will generate a `puffgres/` folder, complete with a Dockerfile and initial setup. It will also ask you to navigate to where your environment variables are; to start, you only need a `TURBOPUFFER_API_KEY` and `DATABASE_URL`. You'll need to enable logical replication on the database, but the setup wizard will also run you through this proces. There's many more knobs you can turn; see the [advanced options](./advanced-options.md) section for further configurations. The generated `puffgres.toml` is the main configuration file for your project. It controls both runtime behavior and environment variable loading. See the [Advanced options](./advanced-options.md) section for a full reference. ## Creating your first config Run `puffgres new` to run through the config wizard. It should let you select a Postgres table, pick an embedding provider and what fields you want, and set the destination turbopuffer namespace. This will generate a bunch of files, but they are effectively "staged". You are welcome to change the config or the transform (schema is autogenerated) to write arbitrary logic, use other packages, etc. In effect, these files are "staged". In order to "apply" them and actually get puffgres to listen to changes, run `puffgres apply`. You can then see the run loop by using `puffgres run`. ## Debugging Before you apply a config, you may watn to test that a transform works with some sample data. If you run `puffgres check --name` it will give you sample output. If you've run database migrations on a Postgres table you are listening to, you will also need to run `puffgres generate` to regenerate the TS schema. You can also run `puffgres debug`, which spins up a small service to look at the contents of a turbopuffer namespace, or to view the live progress of the WAL. --- ## State Puffgres retains its own state, independent of restarts of the service, in a separate schema called `puffgres` on the Postgres table. It holds things such as: hashed versions of the configs and transforms (to ensure they don't change), streaming checkpoints (so we know where on the WAL to begin listening), backfill progress, and a "dead letter" queue of rows that failed that we will want to retry in the future. We have a model of branching off of our prod database for user dev databases. Because of this setup, databases mirroring main will also pull the up-to-date puffgres state, and only resume streaming from the most recent point. ## Deploys In our deploy pipeline, we have a `puffgres apply` that takes new, committed files and applies them to state and then we run the `puffgres run` loop. It's no problem if we have some gap where the puffgres streaming loop isn't running, because it is designed to handle interrupts and eventual consistency. We provide the Dockerfile that we use. In essence, you should commit the generated `puffgres` folder that comes from `puffgres init` inside of your project, and developers should be able to add configs at will / change configuration. The puffgres service itself will pull from our canonical repo, and run based on the files in your repo. ## CI Because puffgres' typed schema used for transforms is reliant on Postgres column order, we neeed to re-generate schema whenever there is a database migration on a table we're listening to. `puffgres check` will generate schemas for all of your watched tables, and compare them to the generated schemas, throwing an error if they are out of sync. ## Observability We support OpenTelemetry for tracing and metrics. This works great with Sentry's Logs product, which is how we monitor the service. To do this, you just need to set `OTEL_EXPORTER_OTLP_ENDPOINT` and `OTEL_EXPORTER_OTLP_HEADERS`. See [Advanced options](./advanced-options.md#environment-variables) section for more. --- # Advanced options puffgres is configured in two places: - **`puffgres.toml`** — this is a commmited file that defines runtime behavior (batch sizes, retries, timeouts) and which `.env` files to load. - **Environment variables** — secrets and connection details (database URL, turbopuffer key), loaded from the config above. ## `puffgres.toml` This lives at the root of your puffgres project and is created with `puffgres init`. Every field except `environment_files` is optional and has a sensible default. It will look something like this ```toml environment_files = ["./.env", "../.env", "../.env.development"] batch_size = 100 max_retries = 5 # Dead letter queue dlq_replay_interval = 10 dlq_replay_batch_size = 50 dlq_max_retries = 5 dlq_permanent_max_age_hours = 72 # Advanced (uncomment to override defaults) # max_transaction_events = 1000000 # sub_batch_size = 1000 # transform_timeout_secs = 30 # maintenance_interval_secs = 600 # tls_unclean_close_level = "error" ``` ### `environment_files` **Required.** List of `.env` file paths to load, relative to the `puffgres.toml` location. Earlier files take priority over later ones. Shell environment variables take highest precedence over all files. ### `batch_size` Number of replication events to collect before flushing a batch to turbopuffer. Default: **100**. This is a deliberately low cap so that it doesn't break for users setting this up as a test with low-rate-limit embedding providers. See [the programming model](./programming-model.md#batching-and-retries) for how batching works. ### `max_retries` Number of times to retry a failed batch before sending it to the dead letter queue. Default: **5**. ### Dead letter queue When a batch fails after `max_retries`, it goes to the dead letter queue (DLQ) so the stream isn't blocked. These knobs control how the DLQ is drained and pruned. You probably don't need to change these and can use the defaults. See [the programming model](./programming-model.md#dead-letter-queue) for how the DLQ works. #### `dlq_replay_interval` How often (in seconds) to replay retryable entries from the dead letter queue. Default: **10**. #### `dlq_replay_batch_size` Maximum number of dead letter queue entries to replay per interval. Default: **50**. #### `dlq_max_retries` Number of times to retry a dead letter queue entry before marking it as permanently failed / unretryable. Default: **5**. #### `dlq_permanent_max_age_hours` How long (in hours) to keep permanently-failed dead letter queue entries before discarding them. Default: **72**. ### Large transactions #### `max_transaction_events` Maximum number of events allowed in a single Postgres transaction. This is a way to deal with transactions that might exaust the memory of the puffgres process all at once. Transactions exceeding this limit are skipped and logged. Has no effect when `sub_batch_size` is set, and we break them up. Default: **1,000,000**. #### `sub_batch_size` When set, large transactions are streamed in sub-batches of this size instead of buffering the entire transaction in memory. The pipeline processes chunks as they arrive, giving natural backpressure. We don't commit until all of them. Unset by default (entire transaction is buffered). Obviously this interrupts some of our consistency goals, so use with caution. ### `transform_timeout_secs` How long puffgres waits for a single `transform.ts` batch response before killing and respawning the worker process. Default: **30** seconds. ### `maintenance_interval_secs` How often (in seconds) puffgres runs background maintenance — currently pruning stale permanent DLQ entries past `dlq_permanent_max_age_hours`. Default: **600**. ### `tls_unclean_close_level` Logging level for unclean TLS shutdowns (missing `close_notify`). Supported values: `error`, `warn`, `silent`. Default: **error**. This was throwing a bunch of unecessary errors for us in Sentry, that weren't severe, so we swapped to `warn`. ## Environment variables ### `DATABASE_URL` Non-pooled URL for your Postgres database. Pooled connections cannot handle logical replication! ```sh DATABASE_URL="postgresql://user:pass@host:5432/db" ``` ### `TURBOPUFFER_API_KEY` ```sh TURBOPUFFER_API_KEY="tpuf_abc123..." ``` ### `TURBOPUFFER_NAMESPACE_PREFIX` Prefix for all turbopuffer namespaces. If set to `PUFFGRES_PRODUCTION` and you create a namespace called `page`, it saves as `PUFFGRES_PRODUCTION_page`. ```sh TURBOPUFFER_NAMESPACE_PREFIX="PUFFGRES_PRODUCTION" ``` ### `PUFFGRES_STATE_SCHEMA` Postgres schema (in the same database as `DATABASE_URL`) where puffgres keeps its own state — applied configs, replication checkpoints, backfill cursors, and the DLQ. Defaults to `puffgres`. The schema is created on first run; the `DATABASE_URL` role needs `CREATE SCHEMA` and DML privileges on it (or you can pre-create the schema and grant DML only). ```sh PUFFGRES_STATE_SCHEMA="puffgres" ``` Because state lives in the source database, point in time restores will roll the backfill cursors back and should work. ### `OTEL_EXPORTER_OTLP_ENDPOINT` OpenTelemetry endpoint, if you want observability. We use Sentry for this and it works well. ```sh OTEL_EXPORTER_OTLP_ENDPOINT="https://a123.ingest.us.sentry.io/api/1234/integration/otlp" ``` ### `OTEL_EXPORTER_OTLP_HEADERS` Headers for the OTLP exporter. ```sh OTEL_EXPORTER_OTLP_HEADERS="x-sentry-auth=sentry sentry_key=a123" ``` #### Sentry alerting If you export OTLP to Sentry, keep connection-failure events at warning level in puffgres and set the alert threshold in Sentry. A practical starting point is an issue alert for `connection failed, reconnecting` when it happens more than once in one hour. ### Other environment variables You may also want to set environment variables you use in transformations, i.e. `ZEROENTROPY_API_KEY` or `BASETEN_API_KEY` for embeddings. --- # Command reference NOTE: All commands must be run from within the user's generated `puffgres` directory (the folder created after init), or one level up, in order to work. ## `puffgres init` Create directory structure (in `puffgres/` folder) with everything needed for initial setup. ## `puffgres new --name ` Create a new config / transform. Runs an interactive wizard by default. Pass `--non-interactive` to build the config from flags instead — the codepath scripts and agents should use: ```sh puffgres new --name film \ --table internal_film \ --namespace internal_film_title \ --id-column id \ --id-type string \ --provider zeroentropy \ --embed-column title \ --non-interactive ``` Flags: - `--name` — what to call the config and name the folder - `--table` — which Postgres table we reference (defaults to name) - `--namespace` — which turbopuffer namespace this will land in (defaults to name) - `--id-column` — which column represents our id (defaults to `id`) - `--provider` — the embedding provider we'd use (`none`, `zeroentropy`, `baseten`, `cloudflare`) - `--embed-column` — which column to embed The id type is always auto-detected from the table. ## `puffgres apply` Apply configs on the file system into state, so that replication will begin for a new config/transform pair. Once you do this, configs / transforms are set (+ their hashes are stored in state) and will throw an error if you try to change them. ## `puffgres check [--name ]` Regenerate `schema.ts` from the live database and validate configs against it. Makes sure referenced tables exist, the id column has a unique index, id types are compatible, each transform runs successfully on a sample row, and schemas are correct. Safe to run before `apply` to check, and we run this in CI. ## `puffgres remove` Permanently remove config(s): deletes the turbopuffer namespace, the on-disk config directory, and all state (checkpoints, backfill progress, DLQ). Use `--name ` to remove a specific config, `--last` to remove the most recently applied one, or `--all` to remove every applied config. `--force` skips the confirmation prompt (use with `--all`). This should only be used in local dev / when testing, not in production. ## `puffgres reset` Nuke the whole project back to a clean slate. Drops the `puffgres` replication slot (and the `puffgres_debug` debug slot), drops the `puffgres` publication, drops the state schema, and deletes the local project directory. turbopuffer namespaces are left untouched — use `puffgres remove --all` first if you also want those gone. We also use this for debugging / puffgres dev. This will also work if the state database is in a weird state, functioning as a total reset. Run `puffgres init` to start over afterwards. ## `puffgres tombstone [--name ]` Creates a `tombstone.toml` file in a config directory so the CDC loop ignores it. Effectively a "soft delete" of a namespace. Rather than changing an active config, you should make a new one and tombstone the old one. Without `--name`, puffgres interactively prompts you to pick from the applied configs that aren't tombstoned yet. Pass `--name` to skip the prompt (for scripts and agents). ## `puffgres generate` (Re)generate typed `schema.ts` files. If you have a Postgres migration on a table you are watching, you need to run this so that transforms access the correct columns. ## `puffgres run` Start the replication pipeline. Runs a preflight validation of the applied configs first (failing fast on misconfiguration), then backfill, then the CDC loop. ## `puffgres debug` Launches lightweight web UI (on port 3333 by default) to inspect namespaces / view replication stream. --- # Contributing We are excited about PRs! We are not principally opposed to LLM-generated PRs, and made heavy use of them during development, but such PRs they will be held to the same standard as traditional contributions, and we reserve the right to close low quality PRs at will.