ClickHouse and PostgreSQL integration

How ClickHouse integration with PostgreSQL works for analytics teams

Analytics teams often need data that lives in PostgreSQL operational databases alongside event and telemetry data already in ClickHouse. The first decision is whether ClickHouse should read that data live from PostgreSQL at query time or hold its own copy. ClickHouse supports two methods for the live path: the PostgreSQL table engine, which creates a table definition in ClickHouse that points at a remote PostgreSQL table, and the postgresql table function, which runs the same kind of access inline within a single query. The table engine suits repeated access to the same table, while the table function suits one-off exploration or ad hoc joins where you do not want a persistent object in the schema.

Both methods are live. Each query that reads through them is sent to the PostgreSQL server, which then does the work and returns rows to ClickHouse. The PostgreSQL server therefore takes the load, shares its connection limits and locks with the application that owns it, and sets the speed of the query. Filters and some other operations may be pushed down to PostgreSQL so fewer rows cross the network, but the scan still runs on the operational database. Before you point dashboards at these methods, confirm with the ClickHouse documentation for your release which operations are pushed down, how connections are pooled and how TLS is configured, and test those answers on a representative table.

Plan the choice before the first dashboard is built. Read live when the PostgreSQL table is small, changes rarely matter to the analysis, and the source team accepts extra read load on a production database. Copy the data into a MergeTree table when queries scan large ranges, run frequently, or need to stay fast when the source is busy or unavailable. Copying also gives you a place to apply retention and backups that your own team controls. Check the access path in a pre-production environment, use a read-only PostgreSQL role for ClickHouse, and record which team owns each copied table.

What you need in place before connecting ClickHouse to PostgreSQL

The PostgreSQL table engine and table function run from every ClickHouse node, so the database, network, credentials and test environment need to be ready before the first query. Each item below is a decision your team owns, and each one is easier to settle before the connection exists than after dashboards depend on it.

Confirmed version pairing

Check the ClickHouse and PostgreSQL versions you plan to run against the ClickHouse PostgreSQL integration documentation, which describes the supported combinations and any feature limits. Then compare your ClickHouse version with the release lines in the ClickHouse security policy, since only the listed lines receive security updates. A version that is out of support or not covered by the integration docs should be upgraded before the connection is designed, not after. Confirm both versions in writing before scoping the work.

Dedicated read-only PostgreSQL role

Create a PostgreSQL role used only by ClickHouse, granted SELECT on the specific schemas and tables it needs and nothing else. Do not reuse an application account or a superuser, because ClickHouse queries run whenever a dashboard refreshes or a user writes SQL, and the credentials are stored in ClickHouse configuration. Name the role for its purpose so audit logs on the PostgreSQL side make the source clear. Plan how the password is rotated and where it is stored.

Network routes and firewall rules

Every ClickHouse node that may run a query against PostgreSQL needs a route to the PostgreSQL listener, not only one coordinator host. Open the PostgreSQL port from each ClickHouse node's address, and nothing wider. Check the path through any load balancer, proxy or VPN, and confirm that the PostgreSQL server's host-based access rules allow the ClickHouse subnet. A query that works from one node and times out from another is usually a routing or firewall gap.

TLS certificates and trust chain

Decide whether traffic between ClickHouse and PostgreSQL is encrypted, and if so which certificate authority signs the PostgreSQL server certificate. The ClickHouse nodes must trust that authority, and the certificate name must match the host the connection uses. Plan who issues and renews the certificate and how a renewal reaches every ClickHouse node. Unencrypted connections across a shared network should be a documented exception, not a default.

Owned PostgreSQL account and connection limits

Your team should own the PostgreSQL account, its password lifecycle and its connection limit. ClickHouse opens connections from every node and query on the table, so a dashboard refresh can hold several sessions at once. Check the PostgreSQL connection ceiling and any pooler in front of it, then set a limit for the ClickHouse role so it cannot exhaust connections for the applications that run the business. Ask a partner to document these settings, but keep the decisions with your database team.

Pre-production environment with representative data

Build a non-production ClickHouse and PostgreSQL pair with data that has the same column types, nullability, row counts and skew as production. Run the queries your analysts and dashboards will use, and compare results and timings with the source. The ClickHouse production guidance recommends this environment for any upgrade, and it applies here too. Without it, type mismatches and slow pushdown usually surface in front of business users rather than in testing.

Configuring the PostgreSQL connection in ClickHouse, step by step

This sequence takes a PostgreSQL source from a read-only role to a verified ClickHouse table on every node, with a row count check at each stage so problems surface before the definition reaches the whole cluster.

Create a read-only PostgreSQL role
A PostgreSQL administrator creates a dedicated login role for ClickHouse, with no write privileges and no ownership of the tables it reads. Grant USAGE on each schema in scope and SELECT on the specific tables the analytics team needs, rather than on everything in the database. Confirm the grants by connecting as the new role with psql and running a SELECT on one table, then attempting an INSERT into the same table, which should be refused. The step is done when the SELECT returns rows and the INSERT fails with a permission error.
Store credentials in a named collection
Define the connection details and the role's password in a named collection on the ClickHouse cluster, so the password does not appear in table definitions, query text or query logs. Create the collection with the same definition on each node, or confirm your deployment tooling distributes it, and restrict who can read it through access control. Check that SHOW CREATE TABLE output for any test table shows the collection name instead of the password. The step is done when the collection exists on every node and the password is absent from the displayed DDL.
Test connectivity with the postgresql table function
From one ClickHouse node, run a query using the postgresql table function against a single known table, pointing it at the named collection. This confirms network reachability, TLS settings if you require them, and that the role can read the table. Start with a query that selects a small set of columns and includes LIMIT, so the test does not pull a large result. The step is done when the query returns the expected column names and types, and the PostgreSQL server log shows the connection from the ClickHouse host.
Create a PostgreSQL table engine table
On the same node, create a table with the PostgreSQL table engine that maps to the source table, and list the columns explicitly with ClickHouse types. Review how PostgreSQL types such as numeric, timestamps and arrays map to ClickHouse types, and decide whether nullable columns should be declared as Nullable. Use the named collection for credentials. The step is done when SELECT count() against the engine table runs without error and SHOW CREATE TABLE confirms the column definitions you chose.
Run a bounded query and compare row counts
Run a bounded query against the engine table, such as a count restricted by a timestamp or key range that you can reproduce exactly on PostgreSQL. Run the same predicate in PostgreSQL and compare the two counts, then compare a sample of aggregate values such as min and max on a key column. Record the query text and results so the comparison can be repeated after changes. The step is done when both systems return identical counts and matching aggregates for the bounded range. Any mismatch should be investigated before continuing, because it usually points to type mapping, time zone handling or a predicate that does not push down as expected.
Roll the definition out to the remaining nodes
Apply the same named collection and the same CREATE TABLE statement to each remaining ClickHouse node, using your standard change process or configuration management. If the cluster uses a Distributed table on top of local tables, confirm which layer should hold the PostgreSQL engine table with your architecture team, because a PostgreSQL source is not replicated by ClickHouse replication. Run the bounded count query from each node and confirm identical results. The step is done when every node returns the same count for the same predicate and the definition is recorded in your change log.

How PostgreSQL column types map to ClickHouse columns

This table lists how common PostgreSQL column types appear in ClickHouse when read through the PostgreSQL table engine or table function. Use it to plan table definitions and query casts before you create external tables. The mapping depends on the engine version in use, so run a test query on your own PostgreSQL and ClickHouse versions before you rely on any row.

PostgreSQL type	ClickHouse type as exposed	Nullability behavior and usual fix
Integer types (smallint, integer, bigint)	Signed integer type of matching width, confirmed by test query	Nullable when the column allows NULL. Cast or COALESCE in the query, or set a default in the copy.
numeric and decimal	Decimal type when precision and scale are declared; unbounded numeric needs checking	Unbounded numeric can produce unexpected precision. Declare precision and scale in the source or cast in the query.
text, varchar and char	String, with Nullable wrapper when the column is nullable	Check how char padding and trailing spaces arrive. Trim in the query if downstream comparisons depend on them.
timestamp without time zone	DateTime64 with the fractional precision of the source column	Values carry no zone. Set the zone explicitly in queries so reports do not shift by server settings.
timestamp with time zone	DateTime64 normalized to a stored instant, confirmed by test query	Confirm how offsets are rendered on your version. Convert to the reporting zone in the query layer.
boolean	Bool, with Nullable wrapper when the column is nullable	Three-valued logic in filters can surprise dashboards. Filter NULL explicitly in views that feed reports.
uuid	UUID	Usually maps cleanly. Confirm the nullable form and that comparisons with string literals behave as expected.
json and jsonb	Expected to arrive as String; confirm on your version	Parse with JSON functions in queries, or extract needed fields into typed columns during the copy.
Arrays (one-dimensional and multidimensional)	Array of the element type, confirmed for each dimension you use	Null elements and dimension handling need a test query. Flatten arrays in the copy if analysts query elements often.
enum types	String or Enum type depending on engine version; confirm before choosing	Copying into a fixed Enum type fails on labels added later. Keep String when the source enum changes often.

What ClickHouse pushes down to PostgreSQL and what it does not

Simple filters and column selection are the part of a PostgreSQL-backed query that can run inside PostgreSQL. When ClickHouse reads from a table through the postgresql table engine or table function, a WHERE clause made of plain comparisons on columns can be sent to PostgreSQL so that only matching rows cross the network, and only the referenced columns are requested. Joins, aggregations, window functions and functions that PostgreSQL does not recognize as equivalent to a ClickHouse function may not be sent down. In those cases ClickHouse can pull back a larger set of rows and evaluate the rest itself, which can turn a query that looks selective into a full table scan over the wire. The exact boundary depends on the ClickHouse version and on the expression, so treat the list of pushed operations as something to verify, not something to assume.

Check the behavior on your own version before building on it. Run EXPLAIN on the query and read which parts of the filter appear in the query sent to PostgreSQL and which remain as ClickHouse-side steps. Compare the result for a predicate that uses a plain column comparison with one that wraps the column in a function, because the second form is the one most often left behind. Confirm that the PostgreSQL side has an index that matches the filter the way you expect, since pushdown only helps when PostgreSQL can use it. Your PostgreSQL DBA should also review the server-side view of these queries, because a pushed filter still runs on the production database.

Test large scans on a copy of the source database, not on the primary, and time them at the data volume you expect in production. A dashboard that issues a query against a live PostgreSQL table every time it refreshes can hold connections and load the source system in ways no one planned for. If a pass through the test shows that most of the data is pulled back for every refresh, that is the signal to copy the data into a MergeTree table instead, which the ClickHouse documentation on table engines describes as the usual route for analytical reads. Record the results so the copy-or-query decision is documented alongside the query design.

When to query PostgreSQL live and when to copy the data into MergeTree

Live querying through the PostgreSQL table engine or the postgresql table function suits small reference tables that change slowly, low query volume, and data that must be current at the moment of the query. Every query reads from PostgreSQL, so the cost lands on the PostgreSQL primary or whichever replica the connection targets. Nothing is stored in ClickHouse, which keeps storage simple but makes each dashboard refresh a load test against your operational database.

PostgreSQL load: each query runs on the source server, so heavy scans compete with application transactions on the primary.
Freshness: results reflect the source at query time, with no refresh schedule to manage.
Storage: nothing is duplicated, so there is no ClickHouse disk growth for the source tables.
Ownership: the main operational task is connection and privilege management, plus confirming which pushdown behavior your version supports.
Concurrency: many simultaneous analyst queries multiply the reads, so confirm connection limits with your database team.

Before choosing this pattern, ask the PostgreSQL owners for a read-only replica or an agreed query budget, and test the slowest dashboard query against realistic data volumes.

Securing the PostgreSQL connection from ClickHouse

A PostgreSQL table engine or table function gives ClickHouse a live path into a production database, so the connection needs the same care as any other service account. The cards below cover the database role, the credential store, transport encryption, network reach, consumer-side limits and review of what the role actually does.

Read-only PostgreSQL role

Create a dedicated PostgreSQL role with SELECT only, granted on the specific schemas and tables ClickHouse needs and nothing else. Do not reuse an application role or a role with write rights, because the PostgreSQL table engine can be pointed at any table that role can read. Confirm that default privileges on new tables do not silently widen the grant as the schema grows.

Credentials in named collections

Hold the PostgreSQL username and password in a named collection instead of writing them into each table definition. Restrict who can read the collection through ClickHouse access control, so analysts who only query tables never see the secret. Confirm that query logs and system tables do not expose the password when statements are run.

TLS between ClickHouse and PostgreSQL

Enable TLS on the connection so credentials and result rows do not cross the network in clear text. Set the PostgreSQL server to require encrypted connections for the ClickHouse role, and confirm which certificate authority ClickHouse trusts for that server. Test the certificate chain from every ClickHouse node, since a node that cannot verify the certificate will fail in a way that is easy to misread as a query error.

Network allow-list from ClickHouse nodes

Restrict the PostgreSQL listener and the host firewall so that only the ClickHouse node addresses can connect, on the PostgreSQL port only. Include every ClickHouse node that can run a query against the table, because a single missed node turns into intermittent failures. Recheck the list whenever nodes are added, replaced or moved to new subnets.

Row and column limits for consumers

Use ClickHouse row policies and column-level grants on the tables that wrap PostgreSQL data, so each analyst or service sees only the rows and fields it needs. Remember that these controls limit what ClickHouse users see, not what the PostgreSQL role can read, so the database role remains the outer boundary. Design these policies with the access model in ClickHouse access control, not as a separate design.

Logging and review of the role

Enable PostgreSQL logging for connections and statements issued by the ClickHouse role, and review them on a schedule. Look for queries that touch tables outside the agreed list, large scans at unexpected hours, and failed authentication attempts. Confirm where these logs are stored and how long they are kept, because they are often the only evidence of what the link read.

Common PostgreSQL integration failures and how to fix them

This table maps the failures teams most often hit when ClickHouse reads PostgreSQL through the table engine or table function, with the likely cause and a first fix for each. Work down the rows in order, because connection and authentication problems hide the later symptoms. Check the ClickHouse server log and the query log first, then confirm the PostgreSQL side with the database's own logs and connection view.

Symptom	Likely cause	Fix
Connection refused or timeouts from some nodes only	Only some ClickHouse hosts can reach the PostgreSQL port, usually because of firewall rules, security groups or a pg_hba.conf entry that covers a subset of addresses	Test the connection from each node with the same host, port and user. Add the missing source addresses to the network policy and to pg_hba.conf, then reload PostgreSQL
Authentication failures after a password rotation	The stored credentials in the table definition, named collection or dictionary still hold the old password, or the new password was not applied to every database role	Update the named collection or table definition with the new secret, confirm the role password in PostgreSQL, and check the ClickHouse server log for the authentication error text
Type mismatch errors when creating or querying a table	A PostgreSQL column type, such as numeric with no precision, arrays or custom types, has no direct ClickHouse equivalent, or the declared column type does not match the source	Declare the column with an explicit ClickHouse type that the source values fit, or cast in a view on the PostgreSQL side. Confirm the mapping in the ClickHouse documentation for the PostgreSQL engine before copying data
Unexpected NULLs in query results	PostgreSQL NULLs map to ClickHouse Nullable values only when the column is declared that way, so non-nullable declarations or defaults can hide or replace missing values	Declare the affected columns as Nullable where the source allows NULL, and treat NULL explicitly in queries. Compare row counts and null counts against a direct PostgreSQL query for a sample date range
Slow queries that scan whole PostgreSQL tables	Filters are not pushed down to PostgreSQL, often because of expressions ClickHouse cannot translate, or because the query joins a live table to a large ClickHouse table	Rewrite filters into simple predicates on source columns, check the pushed-down query in the server log, and copy large or frequently queried tables into a MergeTree table instead of querying them live
PostgreSQL connection limit exhausted	Each ClickHouse query that reads the table opens a connection, so concurrent dashboards, merges or dictionary refreshes can exceed max_connections on the PostgreSQL server	Lower query concurrency for the table, schedule copies outside peak hours, and ask the database team to confirm max_connections and any pooler limits. Route traffic through a connection pooler where the database team runs one
Stale results after the source changes	A copy in MergeTree or a cached result is not refreshed after source updates, and live engines return whatever PostgreSQL holds at query time, so teams confuse the two behaviors	Record which tables are live and which are copies. For copies, define the refresh job, its window and its success check, and compare the latest source timestamp with the copy's maximum timestamp
Errors that appear only on some queries or dashboards	Query-level settings, user permissions or row policies differ between the account that tests the query and the account that runs the dashboard	Run the failing query under the dashboard's user, then compare grants on the PostgreSQL role and any ClickHouse row policies or quotas that apply to that user

Questions teams ask before connecting ClickHouse to PostgreSQL

What drives the cost of connecting ClickHouse to PostgreSQL?

The cost follows the number of PostgreSQL databases, tables and query patterns the integration must cover, plus how much of the design is new work. The main components are discovery of which tables and columns analysts need, configuration of the table engine or table function and the network path, checks of data type mapping, testing against representative queries, and documentation with handover. Most of this is one-time build work. Recurring costs appear when PostgreSQL schemas change, when ClickHouse is upgraded, or when a copy pipeline needs monitoring. Ask any quote to itemize discovery, build, testing, handover and optional support, and to state the PostgreSQL and ClickHouse versions it assumes. Choosing between live queries and copies should happen before pricing, because copy pipelines add scheduling, storage and monitoring work that live queries avoid.

How long does a first connection and a production rollout take?

Duration depends mainly on scope and on how quickly approvals and access arrive. A first connection to one test database with a few tables is a small exercise. A production rollout usually takes longer because it adds several databases or schemas, credential and secret handling, network approvals, TLS review, query testing against realistic workloads, and a plan for schema changes. Other factors include whether the PostgreSQL side is already monitored, how many teams must sign off on access, and whether a pre-production environment exists. DeployOpen agrees timelines in writing as part of a fixed-scope engagement, so ask for a schedule that lists each approval gate and the owner responsible for it.

What does the integration need from our team?

The work needs three kinds of input. First, PostgreSQL access: a dedicated read-only role, visibility into the schemas in scope, and a way to test queries without touching production write paths. Second, network changes: permitted routes and firewall rules from the ClickHouse nodes to the PostgreSQL endpoint, plus a decision on TLS and certificate handling. Third, data owners: people who can confirm which tables, columns and filters may be exposed, how long copied data may be kept, and who approves changes to those rules. Engineers on both the ClickHouse and PostgreSQL sides should also review the design, since mistakes in access scope are easier to prevent than to clean up later.

Can our internal team connect ClickHouse to PostgreSQL alone?

An internal team can do this alone if it has working experience with both ClickHouse and PostgreSQL, time to test properly, and a non-production environment with representative data. The work touches database administration, network security, identity and data modeling, so the team needs people who cover those areas or access to them. Check whether anyone has run ClickHouse in production, who will own the integration after handover, and whether there is capacity for upgrades and schema changes. Many teams start with a short internal proof of concept to confirm the questions above, then decide whether to build the production version themselves or agree a fixed-scope engagement for it.

How do we choose between live queries and copying PostgreSQL data?

Query PostgreSQL live when the data is small, freshness matters more than speed, and few queries touch it. Copy the data into a MergeTree table when analysts run heavy or repeated aggregations, when queries must not add load to the transactional database, or when ClickHouse needs its own sort key and partitioning to perform well. Live access depends on the network path and on the source database's load at query time, while copies depend on a scheduled load process and its monitoring. Confirm in the ClickHouse documentation for your version exactly which filters and aggregations are sent to PostgreSQL before deciding, because that determines how much data moves across the network.

Which PostgreSQL and ClickHouse versions should we plan around?

Plan around the PostgreSQL version your organization already runs, and the ClickHouse version you intend to operate. On the ClickHouse side, the ClickHouse security policy lists versions 26.9, 26.8, 26.7 and 26.3 as supported with security updates, and all other versions as unsupported. Version support for the PostgreSQL table engine and table function, and for the PostgreSQL versions they can read, should be confirmed in the ClickHouse documentation for the exact ClickHouse release you choose. Record both versions in the design document, test the exact pair in a pre-production environment, and reread the changelog before any upgrade, since minor backward-incompatible changes can occur.

What does a fixed-scope engagement include?

A fixed-scope engagement covers the work agreed in writing before it starts: the systems and databases in scope, the deliverables, and the timeline. For this integration, that typically means a design of the access model and network path, configured connections for the agreed tables or queries, tests against representative queries, a runbook for operators, and a handover session. DeployOpen does not publish a price list, so a quote follows from the written scope. Ask that the scope state what is excluded, such as schema redesign, additional databases or ongoing support, and whether ongoing support is offered under a separate retainer.

How DeployOpen scopes and delivers a ClickHouse and PostgreSQL integration

DeployOpen plans, implements, documents and supports self-hosted open-source platforms inside your own infrastructure, including ClickHouse clusters that read or copy PostgreSQL data. Engagements start with a written scope that fixes the tables, query patterns, network paths and deliverables before any build work begins.

How we scope

Inventory the PostgreSQL tables, schemas and column types that ClickHouse must read or copy.
Confirm the PostgreSQL major version, ClickHouse version and supported release line before choosing an engine.
Agree in writing which queries run live and which data is copied into MergeTree tables.

How we build

Create read-only PostgreSQL users and named collections so credentials stay out of query text.
Test pushed-down filters and type mappings against representative data in a pre-production cluster.
Set up scheduled copy jobs with verification counts and clear rules for handling failed loads.

How we hand over

Document each table engine, connection setting, credential location and the owner of every mapping.
Provide runbooks for connection failures, schema drift on the PostgreSQL side and version upgrades.
Run a restore test of the ClickHouse tables that were created during the integration work.

How we support

Offer ongoing support, upgrades and continuous testing as a retainer after the handover.
Review the ClickHouse changelog with your team before each planned upgrade of the cluster.
Confirm with your team which support hours, response expectations and escalation paths apply to the retainer.