Skip to main content

PostgreSQL Extractor

The PostgreSQL Extractor allows Gojjam to pull data postgres using server-side cursors and batching to ensure that even multi-gigabyte tables can be moved without overwhelming the engine's memory.

Configuration​

To use the Postgres extractor, define a source in gojjam_ingest_sources.yml with type: postgres.

Basic Usage​

sources:
  - name: "operational_db"
    type: "postgres"
    host: "prod-db.example.com"
    port: 5432
    database: "orders_db"
    username: "gojjam_user"
    password: "secure_password"
    schema: "sales" # Optional: defaults to 'public'

How it Works​

1. Server-Side Batching (fetchmany)​

Unlike standard connectors that try to load the entire result set into memory, Gojjam uses an Iterative Fetch Strategy:

  • It establishes a connection via psycopg2.
  • It executes your custom extraction SQL.
  • It fetches records in batches of 2,000 rows at a time.
  • Each batch is converted to an Arrow table and yielded immediately to the sink.

2. Schema Awareness​

The extractor automatically handles PostgreSQL search paths. If you specify a schema in your config, Gojjam executes SET search_path TO ... before running your query. This allows you to write cleaner SQL without constantly prefixing table names.

SQL-Native Extraction​

Because Gojjam is SQL-first, you don't just "sync a table"—you write an Extraction Model. Place this in ingest/operational_db.sql:

-- Select only what you need to reduce network I/O
SELECT
    order_id,
    customer_id,
    total_amount,
    order_status,
    created_at
FROM orders
WHERE created_at >= '2024-01-01' -- Filter data at the source
  AND order_status = 'completed'