How to Adjust ClickHouse Connection Timeout in PostgreSQL

How do I change the ClickHouse connection timeout when querying from PostgreSQL?

The ClickHouse connection timeout sets how long a client (e.g., clickhouse_fdw in PostgreSQL) waits to establish a connection before aborting.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

Why change the ClickHouse connection timeout?

Slow networks, VPN hops, or overloaded ClickHouse nodes can delay hand-shakes. Raising the timeout prevents false failures, while lowering it surfaces real issues quickly.

What is the default timeout?

The native clickhouse-client defaults to 10 000 ms (10 s). Most drivers, including clickhouse_fdw and the HTTP interface, inherit the same default unless overridden.

How do I set timeout when creating a foreign server?

PostgreSQL CREATE SERVER

Pass connect_timeout in the OPTIONS clause. The value is in seconds.

How do I modify the timeout later?

Use ALTER SERVER to replace the option without dropping objects. Afterwards, reconnect sessions or run DISCARD ALL.

How can I verify the current timeout?

Query pg_foreign_server or run \des+ in psql. Look for the connect_timeout key.

What about other drivers?

• Native client: --connect_timeout
• https: add timeout=5 URL param
• JDBC: socket_timeout=5000 (ms)

Best practices for production

• Keep timeouts symmetrical across read & write paths.
• Log failed attempts with duration to tune later.
• Combine with statement_timeout in PostgreSQL to guard long-running queries.

Why How to Adjust ClickHouse Connection Timeout in PostgreSQL is important

How to Adjust ClickHouse Connection Timeout in PostgreSQL Example Usage


-- Quick smoke test that respects the 5-second timeout
SELECT id, name, email
FROM customers
WHERE created_at > now() - INTERVAL '30 days'
LIMIT 50;

How to Adjust ClickHouse Connection Timeout in PostgreSQL Syntax


-- Create a ClickHouse foreign server with explicit 5-second timeout
CREATE SERVER ch_srv
  FOREIGN DATA WRAPPER clickhouse_fdw
  OPTIONS (
    host 'clickhouse.local',
    port '8123',
    dbname 'ecom',
    connect_timeout '5'  -- seconds
  );

-- Map a foreign table in the familiar ecommerce schema
CREATE FOREIGN TABLE customers (
  id         UInt64,
  name       String,
  email      String,
  created_at DateTime
) SERVER ch_srv OPTIONS (table_name 'customers');

Common Mistakes

Using milliseconds in clickhouse_fdw. The option expects seconds. Supplying 5000 produces a 5 000-second wait, not 5 s. Always use integer seconds.
Altering the server but keeping old sessions alive. PostgreSQL caches FDW connections per session. Disconnect or run DISCARD ALL to pick up new settings.