PostgreSQL Wire Protocol

Gnok supports PostgreSQL-compatible clients through its PostgreSQL wire endpoint. It is a Gnok SQL service, not a PostgreSQL server with every PostgreSQL extension.

Connection

Client connectivity is set up by request

PostgreSQL wire access from outside Studio isn't self-service for new organizations yet; use Gnok Studio in the meantime. Email support@gnok.io to request client connectivity. Gnok support provides the endpoint, client package, and credentials for your organization.

Gnok support provides the host, port, database/catalog, username, and authentication method. Use a connection string with certificate verification, for example:

psql "host=pg.example.com port=5433 dbname=my_catalog user=my_user sslmode=verify-full"

pg.example.com, 5433, my_catalog, and my_user are placeholders. Supply the authorized password or token through your client's secure credential mechanism. If your endpoint uses a private CA, configure the provided root certificate as well.

Authentication and TLS

Use the authentication method Gnok support set up for your organization. For an endpoint configured for token authentication, the token occupies the client's password field. Do not change server authentication or disable TLS to resolve a client error.

The Python examples below read the connection string, including sslmode=verify-full, from a variable named GNOK_PG_DSN in your own shell or secret manager. Import os and psycopg2 in your script. Keep credentials in an approved secret mechanism.

See JDBC for Java and BI tools, or connection troubleshooting.

Supported Features

• Full SQL query execution (SELECT, INSERT, UPDATE, DELETE, DDL)
• Prepared statements and parameterized queries ($N placeholders via Extended Query protocol)
• Server-side cursors (DECLARE, FETCH, CLOSE) for paginated result sets
• COPY (SELECT ...) TO STDOUT for tab-delimited data export
• Multi-statement transactions — BEGIN/COMMIT/ROLLBACK with READ COMMITTED (default), REPEATABLE READ, and SERIALIZABLE isolation; statements outside a transaction autocommit as atomic Iceberg snapshots (see Transactions)
• SET search_path for per-connection schema resolution
• Result set streaming
• TLS connections with certificate verification
• pg_catalog and information_schema system views for JDBC DatabaseMetaData and BI tool auto-discovery
• DEALLOCATE / DEALLOCATE ALL for prepared statement cleanup

Extended Query Protocol

Gnok supports the PostgreSQL Extended Query protocol (Parse/Bind/Describe/Execute), which is used by JDBC drivers, ODBC drivers, and most programming language client libraries.

Prepared Statements

Prepare a statement with $1, $2, ... parameter placeholders and execute it with bound values:

-- psql example
PREPARE my_query AS SELECT * FROM orders WHERE customer_id = $1 AND amount > $2;
EXECUTE my_query(42, 100.00);
DEALLOCATE my_query;

In client libraries, prepared statements are used automatically:

# psycopg2 uses the Extended Query protocol by default
cursor.execute(
    "SELECT * FROM orders WHERE customer_id = %s AND amount > %s",
    (42, 100.00)
)

// node-pg sends prepared statements via the Extended Query protocol
const result = await client.query(
  'SELECT * FROM orders WHERE customer_id = $1 AND amount > $2',
  [42, 100.00]
);

The server supports DESCRIBE on both prepared statements and portals, allowing clients to inspect result column types before executing.

Result formats (text and binary)

The server honors the per-column result format codes a client requests in its Bind message. This matters for JDBC: the PostgreSQL JDBC driver switches a reused (cached/pooled) prepared statement to the binary result format for fixed-width types once it crosses its prepareThreshold (~5 executions) — which connection pools such as c3p0 (used by Metabase) routinely trigger. Gnok encodes those columns in binary (bool, int2/int4/int8, float4/float8, numeric, date/time/timestamp, bytea, and text types) so the driver decodes them correctly. The text format remains the default for the simple query protocol and for the first executions of an extended-protocol statement.

Server-Side Cursors

Use DECLARE / FETCH / CLOSE to paginate through large result sets without loading all rows into client memory:

BEGIN;

DECLARE my_cursor CURSOR FOR
  SELECT * FROM my_catalog.my_schema.large_table;

-- Fetch 100 rows at a time
FETCH 100 FROM my_cursor;
FETCH 100 FROM my_cursor;

-- Close the cursor when done
CLOSE my_cursor;

COMMIT;

In Python with psycopg2, use named cursors for automatic server-side cursor behavior:

conn = psycopg2.connect(os.environ["GNOK_PG_DSN"])
cursor = conn.cursor(name="server_cursor")
cursor.itersize = 1000

cursor.execute("SELECT * FROM my_catalog.my_schema.large_table")
for row in cursor:
    process(row)

cursor.close()
conn.close()

COPY TO STDOUT

Export query results in tab-delimited format using COPY ... TO STDOUT:

COPY (SELECT id, name, amount FROM orders WHERE year = 2025) TO STDOUT;

From psql, redirect output to a file:

psql "$GNOK_PG_DSN" \
  -c "COPY (SELECT * FROM my_catalog.my_schema.my_table) TO STDOUT" > output.tsv

In Python:

import sys

conn = psycopg2.connect(os.environ["GNOK_PG_DSN"])
cursor = conn.cursor()
cursor.copy_expert(
    "COPY (SELECT * FROM my_catalog.my_schema.my_table) TO STDOUT WITH CSV HEADER",
    sys.stdout.buffer
)
conn.close()

System Catalog Views

Gnok provides pg_catalog and information_schema views for compatibility with BI tools and JDBC DatabaseMetaData introspection. These views return metadata derived from Iceberg catalogs.

Postgres pg_catalog views

View	Description
pg_catalog.pg_tables	Lists all tables across registered catalogs
pg_catalog.pg_namespace	Lists schemas (namespaces)
pg_catalog.pg_class	Table/view relation metadata
pg_catalog.pg_attribute	Column metadata
pg_catalog.pg_type	Type definitions
pg_catalog.pg_constraint	PRIMARY KEY (contype='p') and FOREIGN KEY (contype='f') constraints, with int2[] conkey/confkey

Key and relationship discovery

BI tools read primary and foreign keys to mark key columns and auto-draw the schema relationship graph. Gnok surfaces both:

• Primary keys — JDBC getPrimaryKeys, information_schema key views, and pg_constraint (contype='p').
• Foreign keys (informational/unenforced — see Foreign Keys) — JDBC getImportedKeys / getExportedKeys and pg_constraint (contype='f'). A Metabase sync, for example, records the relationship and links the tables automatically.

SQL-standard information_schema

The SQL-standard views — tables, columns, schemata, views, policy_references — are documented in the INFORMATION_SCHEMA reference. Every view is per-catalog, so prefix with the catalog name or run USE CATALOG <name> first.

Examples

-- pg_catalog query (used by BI tools internally)
SELECT schemaname, tablename
FROM pg_catalog.pg_tables
WHERE schemaname NOT IN ('pg_catalog', 'information_schema');

-- INFORMATION_SCHEMA — note the catalog prefix
SELECT table_name, row_count
FROM my_catalog.information_schema.tables
WHERE table_schema = 'sales';

Performance: pgwire vs Flight SQL

The PostgreSQL wire protocol is provided for compatibility with the large ecosystem of PostgreSQL tools, BI applications, and client libraries.

For maximum throughput, use Arrow Flight SQL instead. Flight SQL transfers data in Apache Arrow columnar format with zero-copy semantics, which avoids the row-by-row serialization overhead of the PostgreSQL text/binary protocol. Measure throughput with representative queries and result sizes for your client.

	PostgreSQL Wire	Flight SQL
Best for	BI tools, ad-hoc queries, compatibility	High-throughput data transfer, analytics pipelines
Data format	Row-oriented text/binary	Columnar Apache Arrow
Ecosystem	psql, DBeaver, JDBC, ODBC, every language	Arrow Flight clients, custom integrations
Serialization overhead	Per-row encoding	Zero-copy columnar batches

Connection Pooling

Use a pool configuration approved for your client and endpoint. Session state, prepared statements, and server-side cursors belong to a connection; do not assume they survive transaction pooling. Retain TLS verification between your pool and Gnok.

Limitations

The PostgreSQL wire protocol is provided for compatibility. For maximum throughput, use Arrow Flight SQL which supports zero-copy Arrow data transfer.

• COPY ... FROM STDIN is not supported (use COPY INTO for bulk ingestion)
• pg_catalog and information_schema views return metadata derived from Iceberg catalogs, not from a real PostgreSQL instance
• PostgreSQL-specific functions not in Gnok's function catalog will return errors
• Advisory locks, LISTEN/NOTIFY, and DO blocks are not supported