How to Clickhouse SaaS pricing in PostgreSQL

What is the CLICKHOUSE_SAAS_PRICING() function?

CLICKHOUSE_SAAS_PRICING() is a wrapper around the public ClickHouse Cloud pricing API, exposed to Postgres through an HTTP extension or FDW.It returns current pricing by region, commitment level, and currency.

Why query pricing from Postgres?

Keeping pricing data in SQL lets engineers join costs to existing usage metrics, run “what-if” scenarios, and present budgets in familiar dashboards without switching tools.

How do I enable the extension?

Install an HTTP FDW such as postgres_fdw_http, then create the foreign table clickhouse_saas_pricing pointing at https://api.clickhouse.cloud/pricing.

What is the exact syntax?

Use the SELECT-LATERAL form or the convenience SQL function documented below.

Syntax breakdown

SELECT * FROM CLICKHOUSE_SAAS_PRICING(region TEXT DEFAULT 'us-east', commitment TEXT DEFAULT 'on-demand', currency TEXT DEFAULT 'USD');

• region – pricing region (e.g., 'us-east', 'eu-west')
• commitment – 'on-demand' or '1-year'
• currency – three-letter ISO code

How do I estimate storage costs for an Orders table?

Multiply the per-GB price returned by the function with the table size from pg_total_relation_size.Join on region and currency as needed.

Example: monthly cost of Orders & OrderItems

The query below fetches the on-demand price and calculates the monthly storage cost for the Orders and OrderItems tables.

WITH ch_price AS (
SELECT price_per_gb
FROM CLICKHOUSE_SAAS_PRICING('us-east', 'on-demand', 'USD')
WHERE service = 'storage')
SELECT relname AS table,
pg_size_pretty(size) AS current_size,
ROUND(size/1e9*cp.price_per_gb, 2) AS usd_monthly
FROM (
SELECT c.relname,
pg_total_relation_size(c.oid)::NUMERIC AS size
FROM pg_class c
WHERE relname IN ('orders','orderitems')) t
CROSS JOIN ch_price cp;


Best practices

Refresh pricing nightly

Create a materialized view that refreshes once per day to avoid API rate limits.

Store historical prices

Insert each nightly snapshot into a clickhouse_pricing_history table to analyze trends.

Common mistakes

1.Forgetting currency conversion

ClickHouse returns prices in USD by default. Convert to local currency when presenting to finance.

2. Hard-coding region

Teams often assume us-east. Always parameterize region to avoid under-budgeting EU clusters.

See also

• pg_stat_user_tables for data sizes
• pg_partman for partitioning to reduce hot data costs

.