How to Self-Host BigQuery in PostgreSQL

What does self-hosting BigQuery in PostgreSQL mean?

It means installing the open-source bigquery_fdw extension so your Postgres instance can treat BigQuery datasets as foreign tables. You run SQL from Postgres, while execution happens in BigQuery and results stream back.

Why choose this approach?

You keep Postgres workflows and permissions, avoid exporting data, lower egress costs by streaming only the result set, and freely join BigQuery data with local tables such as Customers or Orders.

How do I install bigquery_fdw?

Build the extension from source or use a package manager (apt install postgresql-16-bigquery-fdw). Then run CREATE EXTENSION bigquery_fdw; inside the target database.

Which server options are required?

Create a foreign server with project_id, dataset_id, and optional location. Map a service-account JSON key with CREATE USER MAPPING so the FDW can authenticate.

How do I import BigQuery tables?

Use IMPORT FOREIGN SCHEMA to pull table definitions into the local public schema. PostgreSQL stores metadata only—data remains in BigQuery until queried.

How can I query BigQuery data?

Query foreign tables exactly like native tables. Joins, filters, and aggregates push down to BigQuery when possible, returning only final rows.

Example join question

SELECT c.name, SUM(oi.quantity * p.price) AS spend FROM Customers c JOIN Orders@bigquery o ON o.customer_id = c.id JOIN OrderItems@bigquery oi ON oi.order_id = o.id JOIN Products p ON p.id = oi.product_id GROUP BY c.name;

What are key performance best practices?

Select only needed columns, push filters early, and avoid CROSS JOINs. Use EXPLAIN VERBOSE to verify that predicates are being pushed down to BigQuery.

What common mistakes should I avoid?

First, storing service-account keys inside SQL scripts—use a secure path or Vault. Second, forgetting to cast Postgres types to match BigQuery, which prevents predicate pushdown.

How do I monitor FDW queries?

Check pg_stat_foreign_server for latency and rows, enable BigQuery audit logs, and log fdw_explain_verbose to understand remote SQL.