Update Postgres service configuration

PATCH/v1/organizations/{organizationId}/postgres/{postgresId}/config

This endpoint is in beta. API contract is stable, and no breaking changes are expected in the future.

Update the existing Postgres service and pgBouncer configuration.

Permission

The API key must have the control-plane:postgres-service:manage permission.

Authorizations

Authorizationstringheaderrequired
Use key ID and key secret obtained in ClickHouse Cloud console: https://clickhouse.com/docs/cloud/manage/openapi

Path parameters

organizationIdstringrequired
ID of the organization that owns the Postgres service.

format: uuid
postgresIdstringrequired
ID of the requested Postgres service.

format: uuid

Request bodyJSON

pgConfigobjectrequired
Postgres runtime configuration configuration.

Example: {"max_connections":100}
31 properties
- max_connectionsoptionalstring | integer
  Sets the maximum number of concurrent connections to the database server.
  
  minimum: 1
  Example: 500
- default_transaction_isolationoptionalread committedorrepeatable readorserializable
  Sets the default transaction isolation level for new transactions.
  
  Example: "read committed"
- ssl_min_protocol_versionoptionalTLSv1orTLSv1.1orTLSv1.2orTLSv1.3
  Sets the minimum SSL/TLS protocol version allowed for client connections.
  
  Example: "TLSv1.3"
- maintenance_work_memoptionalstring | integer
  Sets the maximum memory to be used for maintenance operations.
  
  minimum: 64
  Example: "64MB"
- work_memoptionalstring | integer
  Sets the amount of memory Postgres will use for internal operations like sorting and hashing as part of executing a query.
  
  minimum: 64
  Example: "4MB"
- effective_cache_sizeoptionalstring | integer
  Sets the planner's assumption about the total size of data caches.
  
  minimum: 8
  Example: "4GB"
- random_page_costoptionalstring | number
  Sets the planner's estimate of the cost of a non-sequentially-fetched disk page. Lower values (1.1-1.5) are better for SSDs.
  
  minimum: 0
  Example: 1.1
- effective_io_concurrencyoptionalstring | integer
  Number of concurrent disk I/O operations the planner expects. Higher values (100-200) benefit SSDs.
  
  minimum: 0
  Example: 200
- max_worker_processesoptionalstring | integer
  Maximum number of background processes the system can support. Includes parallel query workers, logical replication, and more.
  
  minimum: 0
  Example: 8
- max_parallel_workersoptionalstring | integer
  Maximum number of workers that can be used for parallel operations. Cannot exceed max_worker_processes.
  
  minimum: 0
  Example: 4
- max_parallel_workers_per_gatheroptionalstring | integer
  Maximum number of parallel workers per executor node for parallel queries. Use 0 to disable parallel queries.
  
  minimum: 0
  Example: 2
- max_parallel_maintenance_workersoptionalstring | integer
  Maximum number of parallel workers for maintenance operations like CREATE INDEX and VACUUM.
  
  minimum: 0
  Example: 2
- statement_timeoutoptionalstring | integer
  Abort any statement that runs longer than the specified time. Use 0 to disable.
  
  minimum: 0
  Example: "60s"
- lock_timeoutoptionalstring | integer
  Abort any statement that waits longer than the specified time while attempting to acquire a lock. Use 0 to disable.
  
  minimum: 0
  Example: "10s"
- idle_session_timeoutoptionalstring | integer
  Terminate any session that has been idle for longer than the specified time. Use 0 to disable.
  
  minimum: 0
  Example: "2m"
- idle_in_transaction_session_timeoutoptionalstring | integer
  Terminate any session that has been idle within an open transaction for longer than the specified time. Use 0 to disable.
  
  minimum: 0
  Example: "2h"
- transaction_timeoutoptionalstring | integer
  Terminate any statement that takes more than the specified time, even while active. Use 0 to disable.
  
  minimum: 0
  Example: "120s"
- wal_sender_timeoutoptionalstring | integer
  Terminate replication connections that are inactive for longer than this time. Use 0 to disable.
  
  minimum: 0
  Example: "120m"
- wal_keep_sizeoptionalstring | integer
  Minimum size of past WAL files kept in pg_wal for standby servers. Use 0 to disable.
  
  minimum: 0
  Example: "1GB"
- min_wal_sizeoptionalstring | integer
  Minimum size to shrink the WAL to. WAL files are recycled rather than removed when below this size.
  
  minimum: 32768
  Example: "80MB"
- max_wal_sizeoptionalstring | integer
  Maximum size WAL can grow between checkpoints. Larger values improve write performance but increase crash recovery time.
  
  minimum: 32768
  Example: "5GB"
- max_slot_wal_keep_sizeoptionalstring | integer
  Specifies the maximum size of WAL files that replication slots are allowed to retain. Use -1 for unlimited.
  
  minimum: 0
  Example: "-1"
- wal_compressionoptionalofforonorlz4orzstd
  Compress full-page writes in WAL. Reduces I/O at the cost of CPU. Options vary by PostgreSQL version.
  
  Example: "off"
- autovacuum_max_workersoptionalstring | integer
  Maximum number of autovacuum worker processes that can run at the same time. Workers share a single cost-limit budget, so raising this alone may not speed up vacuuming.
  
  minimum: 1
  Example: 8
- autovacuum_naptimeoptionalstring | integer
  Minimum delay between autovacuum runs. Lower values make autovacuum check for work more frequently.
  
  minimum: 1
  Example: "5s"
- autovacuum_work_memoptionalstring | integer
  Maximum memory each autovacuum worker uses to track dead tuples. Higher values reduce repeated index-vacuum passes on large tables. Use -1 to fall back to maintenance_work_mem.
  
  minimum: 1024
  Example: "64000kB"
- autovacuum_vacuum_scale_factoroptionalstring | number
  Fraction of a table's rows that must change before autovacuum runs. Lower values vacuum large tables more frequently.
  
  minimum: 0
  Example: 0.2
- autovacuum_analyze_scale_factoroptionalstring | number
  Fraction of a table's rows that must change before autovacuum runs ANALYZE to refresh planner statistics.
  
  minimum: 0
  Example: 0.1
- autovacuum_vacuum_insert_scale_factoroptionalstring | number
  Fraction of a table's rows that must be inserted before autovacuum runs. Helps vacuum insert-heavy, rarely-updated tables.
  
  minimum: 0
  Example: 0.2
- autovacuum_vacuum_cost_limitoptionalstring | integer
  Cost-accounting limit shared across all autovacuum workers before they pause. Use -1 to inherit vacuum_cost_limit.
  
  minimum: 1
  Example: "-1"
- autovacuum_vacuum_cost_delayoptionalstring | integer
  Time autovacuum sleeps when the cost limit is reached. Lower values speed up vacuuming at the cost of more I/O.
  
  minimum: 0
  Example: "2ms"
pgBouncerConfigmap ofstringrequired
PgBouncer runtime configuration configuration.

Example: {"default_pool_size":"16"}

Response

JSON

200

Successful response

JSON

statusoptionalnumber
HTTP status code.

Example: 200
requestIdoptionalstring
Unique id assigned to every request. UUIDv4

format: uuid
resultoptionalobject
3 properties
- pgConfigobjectrequired
  Postgres runtime configuration configuration.
  
  Example: {"max_connections":100}
  31 properties
  max_connectionsoptionalstring | integer
  Sets the maximum number of concurrent connections to the database server.
  
  minimum: 1
  Example: 500
  default_transaction_isolationoptionalread committedorrepeatable readorserializable
  Sets the default transaction isolation level for new transactions.
  
  Example: "read committed"
  ssl_min_protocol_versionoptionalTLSv1orTLSv1.1orTLSv1.2orTLSv1.3
  Sets the minimum SSL/TLS protocol version allowed for client connections.
  
  Example: "TLSv1.3"
  maintenance_work_memoptionalstring | integer
  Sets the maximum memory to be used for maintenance operations.
  
  minimum: 64
  Example: "64MB"
  work_memoptionalstring | integer
  Sets the amount of memory Postgres will use for internal operations like sorting and hashing as part of executing a query.
  
  minimum: 64
  Example: "4MB"
  effective_cache_sizeoptionalstring | integer
  Sets the planner's assumption about the total size of data caches.
  
  minimum: 8
  Example: "4GB"
  random_page_costoptionalstring | number
  Sets the planner's estimate of the cost of a non-sequentially-fetched disk page. Lower values (1.1-1.5) are better for SSDs.
  
  minimum: 0
  Example: 1.1
  effective_io_concurrencyoptionalstring | integer
  Number of concurrent disk I/O operations the planner expects. Higher values (100-200) benefit SSDs.
  
  minimum: 0
  Example: 200
  max_worker_processesoptionalstring | integer
  Maximum number of background processes the system can support. Includes parallel query workers, logical replication, and more.
  
  minimum: 0
  Example: 8
  max_parallel_workersoptionalstring | integer
  Maximum number of workers that can be used for parallel operations. Cannot exceed max_worker_processes.
  
  minimum: 0
  Example: 4
  max_parallel_workers_per_gatheroptionalstring | integer
  Maximum number of parallel workers per executor node for parallel queries. Use 0 to disable parallel queries.
  
  minimum: 0
  Example: 2
  max_parallel_maintenance_workersoptionalstring | integer
  Maximum number of parallel workers for maintenance operations like CREATE INDEX and VACUUM.
  
  minimum: 0
  Example: 2
  statement_timeoutoptionalstring | integer
  Abort any statement that runs longer than the specified time. Use 0 to disable.
  
  minimum: 0
  Example: "60s"
  lock_timeoutoptionalstring | integer
  Abort any statement that waits longer than the specified time while attempting to acquire a lock. Use 0 to disable.
  
  minimum: 0
  Example: "10s"
  idle_session_timeoutoptionalstring | integer
  Terminate any session that has been idle for longer than the specified time. Use 0 to disable.
  
  minimum: 0
  Example: "2m"
  idle_in_transaction_session_timeoutoptionalstring | integer
  Terminate any session that has been idle within an open transaction for longer than the specified time. Use 0 to disable.
  
  minimum: 0
  Example: "2h"
  transaction_timeoutoptionalstring | integer
  Terminate any statement that takes more than the specified time, even while active. Use 0 to disable.
  
  minimum: 0
  Example: "120s"
  wal_sender_timeoutoptionalstring | integer
  Terminate replication connections that are inactive for longer than this time. Use 0 to disable.
  
  minimum: 0
  Example: "120m"
  wal_keep_sizeoptionalstring | integer
  Minimum size of past WAL files kept in pg_wal for standby servers. Use 0 to disable.
  
  minimum: 0
  Example: "1GB"
  min_wal_sizeoptionalstring | integer
  Minimum size to shrink the WAL to. WAL files are recycled rather than removed when below this size.
  
  minimum: 32768
  Example: "80MB"
  max_wal_sizeoptionalstring | integer
  Maximum size WAL can grow between checkpoints. Larger values improve write performance but increase crash recovery time.
  
  minimum: 32768
  Example: "5GB"
  max_slot_wal_keep_sizeoptionalstring | integer
  Specifies the maximum size of WAL files that replication slots are allowed to retain. Use -1 for unlimited.
  
  minimum: 0
  Example: "-1"
  wal_compressionoptionalofforonorlz4orzstd
  Compress full-page writes in WAL. Reduces I/O at the cost of CPU. Options vary by PostgreSQL version.
  
  Example: "off"
  autovacuum_max_workersoptionalstring | integer
  Maximum number of autovacuum worker processes that can run at the same time. Workers share a single cost-limit budget, so raising this alone may not speed up vacuuming.
  
  minimum: 1
  Example: 8
  autovacuum_naptimeoptionalstring | integer
  Minimum delay between autovacuum runs. Lower values make autovacuum check for work more frequently.
  
  minimum: 1
  Example: "5s"
  autovacuum_work_memoptionalstring | integer
  Maximum memory each autovacuum worker uses to track dead tuples. Higher values reduce repeated index-vacuum passes on large tables. Use -1 to fall back to maintenance_work_mem.
  
  minimum: 1024
  Example: "64000kB"
  autovacuum_vacuum_scale_factoroptionalstring | number
  Fraction of a table's rows that must change before autovacuum runs. Lower values vacuum large tables more frequently.
  
  minimum: 0
  Example: 0.2
  autovacuum_analyze_scale_factoroptionalstring | number
  Fraction of a table's rows that must change before autovacuum runs ANALYZE to refresh planner statistics.
  
  minimum: 0
  Example: 0.1
  autovacuum_vacuum_insert_scale_factoroptionalstring | number
  Fraction of a table's rows that must be inserted before autovacuum runs. Helps vacuum insert-heavy, rarely-updated tables.
  
  minimum: 0
  Example: 0.2
  autovacuum_vacuum_cost_limitoptionalstring | integer
  Cost-accounting limit shared across all autovacuum workers before they pause. Use -1 to inherit vacuum_cost_limit.
  
  minimum: 1
  Example: "-1"
  autovacuum_vacuum_cost_delayoptionalstring | integer
  Time autovacuum sleeps when the cost limit is reached. Lower values speed up vacuuming at the cost of more I/O.
  
  minimum: 0
  Example: "2ms"
- pgBouncerConfigmap ofstringrequired
  PgBouncer runtime configuration configuration.
  
  Example: {"default_pool_size":"16"}
- messageoptionalstring
  Informational message about the configuration update, such as restart requirements.

Update Postgres service configuration

curl --request PATCH \
  --url https://api.clickhouse.cloud/v1/organizations/497f6eca-6276-4993-bfeb-53cbbbba6f08/postgres/497f6eca-6276-4993-bfeb-53cbbbba6f08/config \
  --header 'Authorization: Basic <token>' \
  --header 'Content-Type: application/json' \
  --data '
{
  "pgConfig": {
    "max_connections": 100
  },
  "pgBouncerConfig": {
    "default_pool_size": "16"
  }
}
'

const url = 'https://api.clickhouse.cloud/v1/organizations/497f6eca-6276-4993-bfeb-53cbbbba6f08/postgres/497f6eca-6276-4993-bfeb-53cbbbba6f08/config';
const options = {
  method: 'PATCH',
  headers: {'Content-Type': 'application/json', Authorization: 'Basic <token>'},
  body: JSON.stringify({pgConfig: {max_connections: 100}, pgBouncerConfig: {default_pool_size: '16'}})
};

fetch(url, options)
  .then(res => res.json())
  .then(json => console.log(json))
  .catch(err => console.error(err));

import requests

url = "https://api.clickhouse.cloud/v1/organizations/497f6eca-6276-4993-bfeb-53cbbbba6f08/postgres/497f6eca-6276-4993-bfeb-53cbbbba6f08/config"

payload = {
    "pgConfig": { "max_connections": 100 },
    "pgBouncerConfig": { "default_pool_size": "16" }
}
headers = {
    "Content-Type": "application/json",
    "Authorization": "Basic <token>"
}

response = requests.patch(url, json=payload, headers=headers)

print(response.text)

Response example

{
  "status": 200,
  "requestId": "d385ab22-0f51-4b97-9ecd-b8ff3fd4fcb6",
  "result": {
    "pgConfig": {
      "max_connections": 100
    },
    "pgBouncerConfig": {
      "default_pool_size": "16"
    },
    "message": "string"
  }
}

{
  "status": 400,
  "error": "string",
  "requestId": "d385ab22-0f51-4b97-9ecd-b8ff3fd4fcb6"
}

{
  "status": 500,
  "error": "string",
  "requestId": "d385ab22-0f51-4b97-9ecd-b8ff3fd4fcb6"
}