How to Fix BigQuery Connection Timeout in PostgreSQL

How do I stop PostgreSQL from timing out when connecting to BigQuery?

Extends or eliminates the default timeout when PostgreSQL connects to Google BigQuery through FDW, JDBC, or the REST API.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

Why does my PostgreSQL–BigQuery connection time out?

BigQuery terminates idle sessions after roughly 10 minutes, while PostgreSQL clients often default to 15–30 seconds for establishing a TCP handshake. Slow networks, large SSL handshakes, or heavy authentication can exceed either limit, triggering a timeout.

How do I increase the timeout when using bigquery_fdw?

Add or update the connection_timeout option on the foreign server. Values are in seconds, and 0 disables the limit. Restart existing sessions to apply the change.

How can I raise the timeout in a JDBC connection string?

Append timeout=NNN (connect) and readTimeout=NNN (I/O) to your jdbc:bigquery:// URI. This works for psql + JDBC, Spring, and DBT runners.

What if the timeout occurs during long SELECTs?

BigQuery limits interactive query execution to 6 hours. Use EXECUTE IMMEDIATE jobs when calling from FDW, or split large aggregations into materialised tables to avoid server-side cancellation.

Best practices for stable connections

Keep connection_timeout > 60 s to cover TLS negotiation.
Use Cloud SQL Auth Proxy to shorten SSL handshakes.
Tune statement_timeout separately; it does not fix connection issues.
Close idle cursors to stay under BigQuery’s 10 min idle cap.

Why How to Fix BigQuery Connection Timeout in PostgreSQL is important

How to Fix BigQuery Connection Timeout in PostgreSQL Example Usage


-- Import BigQuery tables into PostgreSQL schema bq
IMPORT FOREIGN SCHEMA ecommerce
  FROM SERVER bigquery_srv
  INTO bq;

-- Run a cross-system query that joins local and BigQuery data
SELECT c.id, c.name, SUM(oi.quantity) AS items_bought
FROM Customers  c
JOIN Orders     o  ON o.customer_id = c.id
JOIN bq.OrderItems oi ON oi.order_id = o.id
GROUP BY c.id, c.name;

How to Fix BigQuery Connection Timeout in PostgreSQL Syntax


-- Create or alter a BigQuery FDW server with a higher timeout
CREATE SERVER bigquery_srv
  FOREIGN DATA WRAPPER bigquery_fdw
  OPTIONS (
      project      'my-gcp-project',
      dataset      'ecommerce',
      connection_timeout '120'       -- 120-second handshake window
  );

-- Modify an existing server
ALTER SERVER bigquery_srv
  OPTIONS (SET connection_timeout '180');

-- JDBC connection string example
jdbc:bigquery://https://www.googleapis.com/bigquery/v2:443;ProjectId=my-gcp-project;timeout=180;readTimeout=600;

Common Mistakes

Setting <code>statement_timeout</code> instead of <code>connection_timeout</code>: the former limits query execution, not the initial handshake. Fix by altering the foreign server or JDBC URI with the correct timeout parameter.
Using an unlimited timeout (0) in production: this masks network problems and can leave hanging connections. Set a generous but finite value (120-300 s) and monitor failures.

Frequently Asked Questions (FAQs)

Does increasing the timeout slow my queries?

No. The value only defines how long the client waits for the connection or first byte. Normal query speed is unaffected.

Can I change the timeout per session?

Yes. Use SET bigquery.connection_timeout = '90' right after connecting if your FDW exposes a GUC, or supply a different JDBC URL for each job runner.