> ## Documentation Index
> Fetch the complete documentation index at: https://docs.specific.dev/llms.txt
> Use this file to discover all available pages before exploring further.

# Postgres

> Managed PostgreSQL databases, locally and in production.

Managed PostgreSQL database instances, powered by [Neon](https://neon.tech) in production. One line in `specific.hcl` gives you a local database during `specific dev` and a managed production database on deploy.

```hcl specific.hcl theme={null}
service "api" {
  build = build.api
  command = "./api"

  endpoint {
    public = true
  }

  env = {
    DATABASE_URL = postgres.main.url
  }
}

postgres "main" {}
```

## Connection attributes

Reference these in `env` blocks:

| Attribute     | Description                                                                                                                               |
| ------------- | ----------------------------------------------------------------------------------------------------------------------------------------- |
| `url`         | Full connection string (for example, `postgres://user:pass@host:5432/dbname`).                                                            |
| `host`        | Database host.                                                                                                                            |
| `port`        | Database port.                                                                                                                            |
| `user`        | Database user.                                                                                                                            |
| `password`    | Database password.                                                                                                                        |
| `name`        | Database name.                                                                                                                            |
| `direct_url`  | Non-pooled connection string, bypassing the connection pooler. Use when you need a direct connection (for example, Prisma's `directUrl`). |
| `direct_host` | Non-pooled database host.                                                                                                                 |

## Extensions

The following PostgreSQL extensions are available in both development and production. Enable each with `CREATE EXTENSION` in a migration:

| Extension                                                                        | Enable with                           | Use for                                                                  |
| -------------------------------------------------------------------------------- | ------------------------------------- | ------------------------------------------------------------------------ |
| [pgvector](https://github.com/pgvector/pgvector)                                 | `CREATE EXTENSION vector;`            | Vector similarity search.                                                |
| [pg\_search](https://docs.paradedb.com/documentation/getting-started/quickstart) | `CREATE EXTENSION pg_search;`         | Full-text search with BM25 ranking, powered by ParadeDB.                 |
| [PostGIS](https://postgis.net/documentation/)                                    | `CREATE EXTENSION postgis;`           | Geospatial queries and indexing.                                         |
| [pg\_trgm](https://www.postgresql.org/docs/current/pgtrgm.html)                  | `CREATE EXTENSION pg_trgm;`           | Trigram-based fuzzy text matching and `LIKE`/`ILIKE` indexes.            |
| [ltree](https://www.postgresql.org/docs/current/ltree.html)                      | `CREATE EXTENSION ltree;`             | Hierarchical structures: categories, org charts, taxonomies.             |
| [pgRouting](https://docs.pgrouting.org/)                                         | `CREATE EXTENSION pgrouting CASCADE;` | Routing and network analysis (requires PostGIS, installed by `CASCADE`). |

For graph-shaped workloads, use `ltree` for hierarchies and `pgrouting` for weighted networks and shortest-path queries. Apache AGE and Cypher queries are not supported.

## Querying the database

### Local development

Use `specific psql` to connect to a development database. The database is started automatically if it isn't running.

```bash theme={null}
# Interactive session
specific psql main

# Run a single query
specific psql main -- -c "SELECT * FROM users LIMIT 5"

# List tables
specific psql main -- -c "\dt"
```

### Production

Use `specific query --db <name>` to run read-only Postgres SQL against a deployed environment's database:

```bash theme={null}
# Query the production "main" database
specific query --db main "SELECT * FROM users LIMIT 5"

# Target a specific environment with machine-readable output
specific query --db main --environment staging --format jsonl \
  "SELECT id, email FROM users ORDER BY created_at DESC LIMIT 20"
```

Production queries are **read-only**: `INSERT`, `UPDATE`, `DELETE`, and DDL are rejected (the query runs inside a read-only transaction). Without `--db`, `specific query` targets [observability data](/guides/observability) instead. You can also browse data and manage the database from the [dashboard](https://dashboard.specific.dev).

## Schema migrations

For zero-downtime schema migrations, enable Reshape:

```hcl specific.hcl theme={null}
postgres "main" {
  reshape {
    enabled = true
  }
}
```

See [Reshape migrations](/guides/reshape) for how migrations are written and managed. If you prefer your ORM's migration tooling instead, run it via a `pre_deploy` hook; run `specific docs integrations/prisma` or `specific docs integrations/drizzle` for setup guidance.

## Real-time sync

Postgres changes can be streamed to clients in real time. See [Real-time sync](/guides/sync).

## Preview environments

In [preview environments](/guides/previews), databases are branched copy-on-write from the parent environment, so previews start with real data instantly.