Use the ClickHouse OpenAPI to programmatically
control your Managed Postgres services just like ClickHouse services. The
same API also exposes a Prometheus endpoint for scraping service metrics.
Already familiar with OpenAPI? Get your API keys and jump right to the
Managed Postgres API reference. Otherwise, follow along for a
quick run-through.
API Keys
Using the ClickHouse OpenAPI requires authentication; see API keys for how
to create them. Then use them via basic auth credentials like so:
Organization ID
Next you’ll need your organization ID.
- Select your organization name in the lower left corner of the console.
- Select Organization details.
- Hit the copy icon to the right of Organization ID to copy it directly
to your clipboard.
Now can use it in your requests, like so:
Now you’ve made your first Postgres API request: list API above lists all of
the Postgres servers in your organization. The output should be something
like:
CRUD
Let’s explore the lifecycle of a Postgres service.
Create
First, create a new one
using the create API. It requires the following properties in the JSON body
of the request:
name: Name of the new Postgres serviceprovider: Name of the cloud providerregion: Region within the provider’s network in which to deploy the
servicesize: The VM size
See the create API docs for the possible values for these properties. In
addition, let’s specify Postgres 18 rather than the default, 17:
Now use this data to create a new instance; note that it requires the content
type header:
On success, it will create a new instance and return information about it,
including connection data:
Read
Use the id from the response to fetch the service again:
The output will be similar to the JSON returned for creation, but keep an eye
on the state; when it changes to running, the server is ready:
Now you can use the connectionString property to connect, for example via
psql:
Type \q to exit psql.
Update
The patch API supports updating a subset of the properties of a Managed
Postgres service via RFC 7396 JSON Merge Patch. Tags may be of particular
interest for complex deployments; simply send them alone in the request:
The returned data should include the new tags:
The OpenAPI provides additional endpoints to update properties not supported
by the patch API. For example, to update the Postgres configuration,
use the config API:
The output will show the updated configuration as well as a message describing
the consequences of the change:
Delete
Use the delete API to delete a Postgres service.
Deleting a Postgres service completely removes the service and all of its
data. Be sure you have a backup or have promoted a replica to primary before
deleting a service.
Monitoring
Two Prometheus-compatible endpoints expose CPU, memory, I/O, connection, and
transaction metrics for Managed Postgres services: one returns metrics for
every service in the organization, the other for a single service. See the
Prometheus endpoint page for setup and the metrics reference for the full
list of metrics.
Query insights
The per-statement telemetry behind the Query Insights tab in the cloud
console is also available programmatically. Two endpoints expose the slowest
query patterns on a service: one lists every pattern ranked by impact, the
other returns a single pattern with its recent executions.
List slow query patterns
The slow patterns API returns aggregate metrics for the slowest query
patterns observed over a time window. The window is required — pass
from_date and to_date as RFC 3339 timestamps:
Results default to the costliest patterns first, sorted by total_duration
descending. Sort by a different counter with sort_by (for example
p99_duration, call_count, or total_wal_bytes) and flip the direction
with sort_order. Narrow the set with the db_name, db_user,
db_operation, and app filters, and page through it with limit and
offset.
Each result is one normalized pattern, with literals stripped out and
durations reported in microseconds:
The queryId is a signed 64-bit hash of the normalized statement, so it’s
often negative. Pass it back verbatim — leading - and all — to fetch a
single pattern.
Get a slow query pattern
Pass a queryId from the list response to the slow pattern API to get that
pattern’s aggregate metrics alongside its most recent individual executions.
The db_name, db_user, and db_operation that identify the pattern are
required:
The response carries the same aggregate as the list endpoint under
aggregate, plus a recentExecutions array. Each execution includes the
full per-execution counters — shared and temp block I/O, CPU user and system
time, parallel workers, JIT, and WAL — the same counters the
detail flyout breaks down in the console:
The example trims both objects for brevity; the API returns the complete
counter set documented under per-execution counters. 
