PostgreSQL Driver Documentation

This documentation explains the PostgreSQL driver implementation and how connection parameters map to UI fields in the Gigantic application.

Connection Parameters

Technical Details

The PostgreSQL driver implementation uses the pg Node.js library with Pool-based connections.

• Connection pooling is used through the Pool class from pg
• Schema selection is supported and affects query generation
• The driver uses search_path to set the schema context for operations
• Point data types are specially handled in the normalizeRecordPG method
• For large datasets, TABLESAMPLE is used for efficient sampling

When SSH tunneling is enabled, connections are first established through the tunnel before connecting to the PostgreSQL server.

Connection String Format

PostgreSQL connections combine individual UI fields into a connection string:

postgresql://[user[:password]@][host][:port][/database]

The application takes the individual fields from the UI (Host, Port, Database, Username, Password) and combines them into this format for the pg library.

Authentication Options

PostgreSQL supports several authentication types:

SSH Tunnel Support

The PostgreSQL driver supports SSH tunneling for secure access to databases behind firewalls or in private networks. For detailed information on configuring SSH tunnels, please refer to the SSH Tunnelling documentation.

API Endpoints Used

PostgreSQL connections are primarily used in:

• Tap creation (data source discovery)
• Sink creation (data destination for anonymized data)
• Pipeline execution (data extraction and loading)

Large Tables and Resume

This section lists only what is specific to PostgreSQL. How resume, parallel ranges and row estimates work in every driver is explained in Large databases.

Reading

• Tables are read in chunks ordered by the primary key, so a job that stops resumes after the last key it saved. Only the primary key is used, not unique indexes.
• Every primary key column must be an integer (smallint, integer, bigint, serial types) or text-like (char, varchar, text, uuid, inet, enums). A key with a numeric, decimal, real, double precision, money, date/time, bytea, array or JSON column is not used.
• On PostgreSQL 14 or later, a table with no usable primary key is read by ctid page ranges instead. This works only on plain tables: partitioned tables and tables with inheritance children are read with one cursor and start over if the job stops. A model scanned with an older version of Gigantics does not record the kind of table; the job log then says scan the model again to read it by ctid in ranges.
• With snapshotRead: true in Custom Params, each table is first copied to a temporary table and read from there with one cursor. Those reads are not resumed or split.

Parallel Ranges and Row Estimates

• Primary key ranges are cut from a TABLESAMPLE SYSTEM sample of the key. When the sample is too small, a single integer key is split evenly between its minimum and maximum. Tables read by ctid are split into even page windows.
• The row estimate is pg_class.reltuples. A table that was never analyzed has none, and the row count saved in the model is used instead: run ANALYZE on big tables that were loaded recently. When the model is scanned, tables that were never analyzed and take up to 256 MB are counted with count(*).

Loading

• Rows are loaded with COPY ... FROM STDIN. A batch that COPY rejects is written again with INSERT, which reports the failing row; after 3 rejected batches the table goes on with INSERT only. Tables with composite-type columns always use INSERT. COPY is turned on or off for every PostgreSQL sink with jobs.copy in the jobs configuration. A sink with copy: false in its Custom Params always uses INSERT.
• Update and merge loads COPY each batch into a temporary staging table and apply it with INSERT ... ON CONFLICT (primary key) DO UPDATE or UPDATE ... FROM, in the same transaction. A composite primary key is matched on all its columns.
• The truncate option runs TRUNCATE TABLE, and DELETE FROM if that fails.
• Before loading, the job drops the foreign key and check constraints of the tables it loads, and the foreign keys of other tables that point to them. It adds them back when the load ends. Triggers stay enabled.
• Each dropped constraint is saved in gigantics_meta.dropped_constraints, in the same transaction as the drop. If a job dies, the next job on the same database adds the saved constraints back: at its start, or at its end when they belong to tables it loads. Constraints held by a job that is still running are left alone. A constraint that cannot be added back (for example because the loaded data breaks it) stays in that table and is reported in the log of every job until it is fixed.
• If the gigantics_meta schema or table cannot be created, the job logs a warning and keeps the dropped constraints only in memory: a job that dies before its end loses them.

Permissions

For a PostgreSQL sink, the database user needs:

• CREATE on the database, only if a schema the job loads, or gigantics_meta, does not exist yet: the job creates them. A user without it can load when a database administrator has created them, with USAGE on gigantics_meta and SELECT, INSERT, UPDATE and DELETE on gigantics_meta.dropped_constraints (the table is created by the first job run by a user that can).
• TEMPORARY on the database, for the staging tables and the helper functions of the job.
• Ownership of the tables it loads and of the tables with foreign keys pointing to them, to drop and add constraints and to truncate.

Custom Params

The PostgreSQL driver supports additional custom parameters that can be specified in YAML format to fine-tune connection behavior, pooling, SSL settings, and other advanced options.

Connection Parameters

# SSL Configuration
ssl:
  rejectUnauthorized: false
  ca: |
    -----BEGIN CERTIFICATE-----
    # CA certificate content
    -----END CERTIFICATE-----
  cert: |
    -----BEGIN CERTIFICATE-----
    # Client certificate content
    -----END CERTIFICATE-----
  key: |
    -----BEGIN PRIVATE KEY-----
    # Client private key content
    -----END PRIVATE KEY-----

# Advanced connection options
application_name: my-app
client_encoding: utf8
statement_timeout: 5000
query_timeout: 10000
lock_timeout: 3000
idle_in_transaction_session_timeout: 60000
keepAliveInitialDelayMillis: 10000

Pool Configuration

# Pool sizing options
max: 20
min: 5

# Pool timeout options
connectionTimeoutMillis: 2000
idleTimeoutMillis: 30000
maxLifetimeSeconds: 60

# Pool behavior options
allowExitOnIdle: false

Example Configurations

SSL Configuration

ssl:
  rejectUnauthorized: true
  ca: ${file:/path/to/ca-cert.pem}
application_name: gigantic-app

Advanced Configuration with Timeouts

statement_timeout: 30000
query_timeout: 60000
max: 15
min: 2
idleTimeoutMillis: 10000