---
title: "Dedicated PostgreSQL"
description: "Provision and manage dedicated PostgreSQL instances for your ZeroDB project — isolated compute, direct SQL access, and connection string support."
canonical: "https://docs.ainative.studio/docs/zerodb/postgresql"
last-updated: "2026-10-22T17:48:46.689Z"
---

# Dedicated PostgreSQL

Source: https://docs.ainative.studio/docs/zerodb/postgresql

> Provision and manage dedicated PostgreSQL instances for your ZeroDB project — isolated compute, direct SQL access, and connection string support.

# Dedicated PostgreSQL

Each ZeroDB project can provision a **dedicated PostgreSQL instance** backed by isolated Railway infrastructure. Unlike shared databases, your instance gets its own CPU, memory, and storage — with a direct connection string you can use from any Postgres client.

**Base URL:** `https://api.ainative.studio/api/v1`

:::info Plan requirement
Dedicated PostgreSQL is available on **BASIC, PROFESSIONAL, and ENTERPRISE** plans. FREE accounts cannot provision instances. [Upgrade at ainative.studio/pricing](https://ainative.studio/pricing).
:::

---

## Instance Sizes

| Size | Price | CPU | RAM | Storage |
|------|-------|-----|-----|---------|
| `micro-1` | $5/month | 0.25 vCPU | 256 MB | 1 GB |
| `standard-2` | $10/month | 0.5 vCPU | 512 MB | 5 GB |
| `standard-4` | $25/month | 1 vCPU | 1 GB | 20 GB |
| `performance-8` | $50/month | 2 vCPU | 2 GB | 50 GB |
| `performance-16` | $100/month | 4 vCPU | 4 GB | 100 GB |

---

## Provision an Instance

<APIEndpoint method="POST" path="/api/v1/projects/{project_id}/postgres" auth />

Starts provisioning a dedicated PostgreSQL instance for your project. Provisioning runs in the background — use the status endpoint to poll for completion (~2–3 minutes).

```bash
curl -X POST https://api.ainative.studio/api/v1/projects/$PROJECT_ID/postgres \
  -H "Authorization: Bearer $TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "instance_size": "micro-1",
    "postgres_version": "15"
  }'
```

```python
import requests

response = requests.post(
    f"https://api.ainative.studio/api/v1/projects/{project_id}/postgres",
    headers={"Authorization": f"Bearer {token}"},
    json={
        "instance_size": "micro-1",
        "postgres_version": "15"
    }
)
print(response.json())
```

**Request body:**

| Parameter | Type | Required | Default | Description |
|-----------|------|----------|---------|-------------|
| `instance_size` | `string` | Yes | — | One of: `micro-1`, `standard-2`, `standard-4`, `performance-8`, `performance-16` |
| `postgres_version` | `string` | No | `"15"` | PostgreSQL major version: `"13"`, `"14"`, or `"15"` |

**Response:**

```json
{
  "success": true,
  "message": "PostgreSQL provisioning started",
  "project_id": "fbf6a70c-00c9-4738-ad00-53eb4f79553e",
  "instance_size": "micro-1",
  "postgres_version": "15",
  "estimated_completion_time_minutes": 3,
  "status": "provisioning"
}
```

:::note One instance per project
Each project supports one dedicated PostgreSQL instance. Attempting to provision again on an active project returns a `400` error.
:::

---

## Check Instance Status

<APIEndpoint method="GET" path="/api/v1/projects/{project_id}/postgres" auth />

Poll this endpoint after provisioning to check when the instance is ready.

```bash
curl https://api.ainative.studio/api/v1/projects/$PROJECT_ID/postgres \
  -H "Authorization: Bearer $TOKEN"
```

**Response:**

```json
{
  "exists": true,
  "status": "active",
  "instance_size": "micro-1",
  "postgres_version": "15",
  "database_host": "nozomi.proxy.rlwy.net",
  "database_port": 38632,
  "database_name": "zerodb_14ba393a",
  "database_user": "zerodb_user",
  "monthly_cost_usd": 5.00,
  "provisioning_completed_at": "2026-04-26T02:08:12Z"
}
```

**Status values:**

| Status | Meaning |
|--------|---------|
| `provisioning` | Instance is being created (~2–3 min) |
| `active` | Ready to accept connections |
| `maintenance` | Temporarily unavailable (restart in progress) |
| `error` | Provisioning or runtime failure — contact support |

---

## Get Connection Details

<APIEndpoint method="GET" path="/api/v1/projects/{project_id}/postgres/connection" auth />

Returns the full connection string and individual credentials for use with `psql`, TablePlus, DBeaver, or any application driver.

```bash
curl https://api.ainative.studio/api/v1/projects/$PROJECT_ID/postgres/connection \
  -H "Authorization: Bearer $TOKEN"
```

**Response:**

```json
{
  "connection_string": "postgresql://zerodb_user:your_password@nozomi.proxy.rlwy.net:38632/zerodb_14ba393a",
  "host": "nozomi.proxy.rlwy.net",
  "port": 38632,
  "database": "zerodb_14ba393a",
  "username": "zerodb_user",
  "password": "your_password",
  "ssl_required": true
}
```

**Connect with psql:**

```bash
psql "postgresql://zerodb_user:your_password@nozomi.proxy.rlwy.net:38632/zerodb_14ba393a"
```

**Connect with Python (psycopg2):**

```python
import psycopg2

conn = psycopg2.connect(
    host="nozomi.proxy.rlwy.net",
    port=38632,
    dbname="zerodb_14ba393a",
    user="zerodb_user",
    password="your_password",
    sslmode="require"
)
```

**Connect with SQLAlchemy:**

```python
from sqlalchemy import create_engine

engine = create_engine(
    "postgresql://zerodb_user:your_password@nozomi.proxy.rlwy.net:38632/zerodb_14ba393a",
    connect_args={"sslmode": "require"}
)
```

:::tip Keep credentials secure
Store your connection string in environment variables — never hardcode it. The password is encrypted at rest in ZeroDB.
:::

---

## Rotate Credentials

<APIEndpoint method="POST" path="/api/v1/projects/{project_id}/postgres/rotate" auth />

Generates a new password for your instance. The old password is invalidated immediately — update all active connections.

```bash
curl -X POST https://api.ainative.studio/api/v1/projects/$PROJECT_ID/postgres/rotate \
  -H "Authorization: Bearer $TOKEN"
```

---

## Usage Statistics

<APIEndpoint method="GET" path="/api/v1/projects/{project_id}/postgres/usage" auth />

Returns CPU, memory, storage, and connection metrics for your instance.

```bash
curl https://api.ainative.studio/api/v1/projects/$PROJECT_ID/postgres/usage \
  -H "Authorization: Bearer $TOKEN"
```

**Response:**

```json
{
  "cpu_usage_percent": 4.2,
  "memory_usage_percent": 18.7,
  "storage_usage_gb": 0.3,
  "connection_count": 2,
  "monthly_cost_usd": 5.00,
  "instance_size": "micro-1"
}
```

---

## Query Logs

<APIEndpoint method="GET" path="/api/v1/projects/{project_id}/postgres/logs" auth />

Returns recent SQL query logs with execution time and row counts.

```bash
curl "https://api.ainative.studio/api/v1/projects/$PROJECT_ID/postgres/logs?limit=20" \
  -H "Authorization: Bearer $TOKEN"
```

---

## Restart Instance

<APIEndpoint method="POST" path="/api/v1/projects/{project_id}/postgres/restart" auth />

Restarts your PostgreSQL container. Active connections will be dropped briefly.

```bash
curl -X POST https://api.ainative.studio/api/v1/projects/$PROJECT_ID/postgres/restart \
  -H "Authorization: Bearer $TOKEN"
```

---

## Delete Instance

<APIEndpoint method="DELETE" path="/api/v1/projects/{project_id}/postgres" auth />

Permanently deletes your PostgreSQL instance and all data. **This cannot be undone.**

```bash
curl -X DELETE https://api.ainative.studio/api/v1/projects/$PROJECT_ID/postgres \
  -H "Authorization: Bearer $TOKEN"
```

---

## For AI Agents

- **Poll status** after provisioning — the instance is ready when `status` is `"active"`
- **Use the connection string** directly with any Postgres driver — no ZeroDB API layer required for raw SQL
- **Combine with ZeroDB vectors** for hybrid relational + semantic search — store structured data in Postgres, embeddings in the vector store, query both in parallel
- **Rotate credentials** periodically as a security best practice — automate via a scheduled agent task

---

## Infrastructure

Each dedicated instance is provisioned as an **isolated Railway service** in the `AINative - Customer DBs` workspace. Instances are:

- Fully isolated from other customers
- Accessible via a public TCP proxy (`*.proxy.rlwy.net`)
- Named `zerodb-{short_project_id}-{account}` for dashboard visibility
- Backed by [`timescale/timescaledb-ha`](https://hub.docker.com/r/timescale/timescaledb-ha) (PostgreSQL 15.18 + TimescaleDB 2.27.2)

### Pre-installed Extensions

Every instance ships with 100+ extensions. Key extensions are auto-enabled at provisioning:

| Extension | Version | Use Case |
|-----------|---------|----------|
| **PostGIS** | 3.5 | Geospatial data types, `ST_*` functions, geometry/geography columns |
| **TimescaleDB** | 2.27 | Hypertables, continuous aggregates, compression, real-time analytics |
| **pgvector** | 0.8.1 | Vector embeddings with HNSW and IVFFlat indexes |
| **pg_trgm** | — | Trigram-based fuzzy text search |
| **pg_stat_statements** | — | Query performance monitoring |
| **uuid-ossp** | — | UUID generation |
| **hstore** | — | Key-value storage |
| **pgcrypto** | — | Cryptographic functions |
| **pg_cron** | — | Scheduled jobs inside Postgres |
| **pg_repack** | — | Online table repacking (no locks) |

Extensions are automatically enabled on new instances. For existing instances:

```sql
CREATE EXTENSION IF NOT EXISTS postgis;
CREATE EXTENSION IF NOT EXISTS timescaledb;
CREATE EXTENSION IF NOT EXISTS vector;
```

#### Example — Spatial query ready out of the box

```sql
CREATE TABLE locations (
    id SERIAL PRIMARY KEY,
    name TEXT,
    geom GEOMETRY(Point, 4326)
);

INSERT INTO locations (name, geom) VALUES
    ('HQ', ST_SetSRID(ST_MakePoint(-122.03, 36.97), 4326));

SELECT name, ST_Distance(geom, ST_SetSRID(ST_MakePoint(-122.0, 37.0), 4326))
FROM locations;
```

#### Example — Time-series hypertable

```sql
CREATE TABLE sensor_data (
    time TIMESTAMPTZ NOT NULL,
    sensor_id TEXT,
    value DOUBLE PRECISION
);

SELECT create_hypertable('sensor_data', 'time');
```