QuestDB Ingestion at Millions of Rows/Second: ILP vs PGWire vs Kafka

Once QuestDB is up and producers are running flat out, the choice that decides whether you ship or struggle is not SQL - it is the ingestion path. Pick the wrong one and you fight backpressure, duplicated rows, or random latency spikes. Pick the right one and QuestDB sustains millions of rows per second with predictable behavior. Here is how the three paths compare and when to use each.

The Short Version

• ILP (InfluxDB Line Protocol) is the default for high-throughput writes. It is built for speed and is the path QuestDB recommends for ingestion.
• PGWire (Postgres protocol) is mainly for querying. It works for small/occasional inserts, but it is not the recommended path for heavy ingest.
• Kafka → QuestDB via the official Kafka Connect sink is the sweet spot for streaming pipelines and CDC, and is recommended for most teams.

Architecture Choices

What each path looks like

• ILP (HTTP/TCP) - producers write directly to QuestDB using the ILP clients. Default ports: HTTP 9000, TCP 9009. HTTP is preferred for error feedback and retries.
• PGWire (port 8812) - reuse existing Postgres drivers for queries; inserts work but are not optimized for firehose ingestion.
• Kafka Connect sink - Kafka topics → QuestDB Kafka Connector (uses ILP under the hood) → QuestDB. Recommended for most users; easy CDC with Debezium.

Comparison

Dimension	ILP (HTTP/TCP)	PGWire	Kafka Connect Sink
Primary use	High-throughput ingestion	Querying (light writes)	Streaming ingestion / CDC
Throughput	Highest (designed for writes)	Lower	High; scales with Connect
Ordering	Per-producer; late/out-of-order handled by table design	Same	Topic partition order; good for CDC
Error handling	HTTP has clear feedback; TCP is minimal	Driver-level	Built-in retries/offsets
Operational effort	Low	Low (but not for heavy ingest)	Moderate (Connect ops)
Typical pick	Sensors, metrics, tick data	Ad-hoc inserts, admin tools	Event streams, CDC, microservices

QuestDB's official guidance: ILP is the fastest way to insert data, and PGWire is primarily recommended for querying.

Examples

A) ILP via Python (HTTP, port 9000)

# pip install -U questdb[dataframe]
from questdb.ingress import Sender, TimestampNanos

conf = "http::addr=localhost:9000;"
with Sender.from_conf(conf) as sender:
    sender.row(
        "trades",
        symbols={"symbol": "BTC-USD", "side": "buy"},
        columns={"price": 39269.98, "amount": 0.001},
        at=TimestampNanos.now()
    )
    sender.flush()

QuestDB's official Python client implements ILP and positions it as the fastest insert path.

Notes:

• Use HTTP unless you have a reason to choose TCP; HTTP gives better error reporting and retries.
• Default ILP ports: 9000 (HTTP), 9009 (TCP).

B) PGWire for light writes (and queries)

-- Connect with any PostgreSQL driver to port 8812
-- Example insert (okay for small volumes)
INSERT INTO device_heartbeat (device_id, status, ts)
VALUES ('gw-17', 'ok', now());

Use PGWire mainly to query QuestDB; it is compatible with typical Postgres clients. For heavy ingest, prefer ILP. Note the timestamp handling nuance: PGWire transmits TIMESTAMP WITHOUT TIME ZONE.

C) Kafka → QuestDB with the official sink

Create questdb-connector.properties:

name=questdb-sink
connector.class=io.questdb.kafka.QuestDBSinkConnector
client.conf.string=http::addr=localhost:9000;
topics=example-topic
table=example_table

# converters (JSON example)
key.converter=org.apache.kafka.connect.storage.StringConverter
value.converter=org.apache.kafka.connect.json.JsonConverter
value.converter.schemas.enable=false

Start Kafka Connect and you are ingesting. The connector uses ILP and integrates with Debezium for CDC.

How to Choose

Pick ILP if you control producers, want maximum throughput, and need simple direct writes (metrics, IoT, ticks).

Pick Kafka Connect if you already have Kafka and need durable streaming, backpressure handling, transformations, or CDC via Debezium. QuestDB's connector is first-party and the docs label it recommended for most people.

Pick PGWire if you are query-centric and only do small administrative inserts (migrations, quick scripts). For sustained ingest, switch to ILP.

What "Millions/sec" Actually Means

QuestDB's docs state that each primary node can sustain up to ~5 million rows/sec in TSBS benchmarks (hardware and schema dependent). That is why the ingestion path matters so much.

Best Practices

1. Use WAL tables + dedup for idempotent ingest. Turn on deduplication and set correct UPSERT KEYS so retries or replays do not create duplicates.
2. Choose ILP/HTTP unless you truly need TCP. HTTP brings error feedback and simpler ops; TCP is mainly for legacy/compat.
3. Mind ports and security. Expose only what you need: 9000 (HTTP/REST/ILP), 8812 (PGWire), 9009 (ILP/TCP) - lock down with firewalls/TLS where appropriate.
4. Keep Kafka Connect configs boring. Start with JSON, then move to Avro/Protobuf if needed; let Connect handle retries and offsets. The QuestDB sink uses ILP under the hood.
5. Design timestamps and partitions first. Even perfect ingestion chokes on a poor schema. Set the designated timestamp and partitions appropriately before pushing load.
6. Watch write amplification and out-of-order data. High out-of-order rates can degrade throughput.

Common Pitfalls

• Using PGWire for the firehose. It works until it does not. Switch heavy writes to ILP.
• Skipping UPSERT KEYS with dedup. Dedup without proper keys will not prevent duplicates.
• TCP ILP with Kafka Connect. The connector docs advise HTTP; TCP lacks delivery guarantees for this path.
• Timezone confusion over PGWire. Timestamps are transmitted as TIMESTAMP WITHOUT TIME ZONE; configure clients to treat them as UTC.

Takeaways

Use ILP for raw speed, Kafka Connect for streaming and CDC, and PGWire for querying (and only light inserts). That mapping alone prevents most production pain.

Quick checklist:

• Ingestion path chosen: ILP | Kafka | PGWire
• WAL + dedup + UPSERT KEYS configured
• Designated timestamp and partitions set
• Ports and TLS locked down
• Dashboards validated under load