iris-pgwire

PostgreSQL wire protocol server for InterSystems IRIS. Connects BI tools, Python frameworks, data pipelines, and any PostgreSQL-compatible client to IRIS databases — no IRIS-specific drivers needed.

Connection string: postgresql://user:pass@localhost:5432/USER

Quick Start

Docker (recommended)

git clone https://github.com/intersystems-community/iris-pgwire.git
cd iris-pgwire
docker compose up -d
Test it (PGWire runs on port 5432 inside the IRIS container)
psql -h localhost -p 5432 -U _SYSTEM -d USER -c "SELECT 'Hello from IRIS!'"

Python package

pip install iris-pgwire psycopg[binary]
export IRIS_HOST=localhost IRIS_PORT=1972 

IRIS_USERNAME=_SYSTEM IRIS_PASSWORD=SYS IRIS_NAMESPACE=USER
python -m iris_pgwire.server

First query

import psycopg
with psycopg.connect("host=localhost port=5432 dbname=USER user=_SYSTEM password=SYS") as conn:
with conn.cursor() as cur:
cur.execute("SELECT COUNT(*) FROM MyTable")
print(cur.fetchone()[0])

Verified Client Compatibility

Tested against real IRIS instances via the wire protocol:

ORMs: SQLAlchemy, Prisma, Drizzle, Sequelize, Hibernate
BI tools: Apache Superset, Metabase, Grafana (standard PostgreSQL driver)

See Client Compatibility Guide for setup examples.

Key Features

pgvector operators — <=> (cosine), <#> (dot product), <-> (L2) auto-translate to IRIS VECTOR_COSINE/VECTOR_DOT_PRODUCT. HNSW indexes give 5× speedup at 100K+ vectors. See Vector Operations.

DDL compatibility — Automatic public ↔ SQLUser schema mapping; strips fillfactor, GENERATED columns, USING btree, IF NOT EXISTS guards, and other PostgreSQL-specific DDL so ORM migrations run cleanly. See DDL Compatibility.

SQL translation — RETURNING emulation, ON CONFLICT, boolean literals, pg_catalog → INFORMATION_SCHEMA rewrites, JSON operators (-> / ->> → JSON_EXTRACT), parameterized queries.

Authentication — SCRAM-SHA-256, OAuth 2.0 (RFC 6749), IRIS Wallet credentials.

Dual backend — Embedded Python (irispython, lowest latency) or external DBAPI (standard TCP connection). Selectable via IRIS_BACKEND env var.

COPY protocol — Bulk load via COPY … FROM STDIN (~600 rows/sec on DBAPI path).

Usage Examples

Parameterized queries (psycopg3)

import psycopg
with psycopg.connect("host=localhost port=5432 dbname=USER user=_SYSTEM password=SYS") as conn:
with conn.cursor() as cur:
cur.execute("SELECT * FROM MyTable WHERE id = %s", (42,))
row = cur.fetchone()

Vector similarity search

query_vector = [0.1, 0.2, 0.3]
with conn.cursor() as cur:
    cur.execute("""
        SELECT id, embedding <=> %s::vector AS score
        FROM vectors
        ORDER BY score
        LIMIT 5
    """, (query_vector,))
    results = cur.fetchall()

Async SQLAlchemy

from sqlalchemy.ext.asyncio import create_async_engine, AsyncSession, async_sessionmaker
from sqlalchemy import text
engine = create_async_engine("postgresql+psycopg://localhost:5432/USER")
SessionLocal = async_sessionmaker(engine, class_=AsyncSession)
async def query():
async with SessionLocal() as session:
result = await session.execute(text("SELECT * FROM MyTable"))
return result.fetchall()

COPY bulk load

with conn.cursor() as cur:
    with cur.copy("COPY MyTable (col1, col2) FROM STDIN") as copy:
        for row in data:
            copy.write_row(row)

Documentation

Development

# Install dependencies
uv sync --frozen
Run unit + contract tests (no IRIS needed)
pytest tests/unit/ tests/contract/ -v
Run with live IRIS (container must be up)
docker compose up -d
PGWIRE_BACKEND_TYPE=dbapi PGWIRE_POOL_SIZE=1 pytest tests/ -v
Code quality check
python -m iris_pgwire.quality

Test coverage: 92% (5349 tests)
Code quality: black (formatter), ruff (linter), bandit (security)

Known Limitations

See https://github.com/intersystems-community/iris-pgwire/blob/main/KNOWN_LIMITATIONS.md for the full list. Key items:

• IRIS Community Edition: 5-user connection limit — use PGWIRE_POOL_SIZE=1 for dev
• No native SSL termination — use nginx/HAProxy in front for TLS
• Kerberos/GSSAPI auth wiring deferred (OAuth 2.0 is the recommended enterprise auth)
• INFORMATION_SCHEMA only, no pg_catalog tables (translated automatically)

Contributing

git clone https://github.com/intersystems-community/iris-pgwire.git
cd iris-pgwire
uv sync --frozen
docker compose up -d
pytest tests/unit/ tests/contract/ -v

Open an issue or PR on GitHub.

Links

• InterSystems IRIS
• IRIS Documentation
• PostgreSQL Protocol Spec
• pgvector

MIT License — see https://github.com/intersystems-community/iris-pgwire/blob/main/LICENSE