Skip to content
Specific Docs
Esc
↑↓navigate↵open⌘Jpreview
On this page

Postgres

Managed PostgreSQL databases, locally and in production.

Managed PostgreSQL database instances, powered by Neon in production. One line in specific.hcl gives you a local database during specific dev and a managed production database on deploy.

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 CREATE EXTENSION vector; Vector similarity search.
pg_search CREATE EXTENSION pg_search; Full-text search with BM25 ranking, powered by ParadeDB.
PostGIS CREATE EXTENSION postgis; Geospatial queries and indexing.
pg_trgm CREATE EXTENSION pg_trgm; Trigram-based fuzzy text matching and LIKE/ILIKE indexes.
ltree CREATE EXTENSION ltree; Hierarchical structures: categories, org charts, taxonomies.
btree_gist CREATE EXTENSION btree_gist; B-tree types in GiST indexes: exclusion constraints (for example, no overlapping bookings) and mixed-type multicolumn indexes.
unaccent CREATE EXTENSION unaccent; Accent-insensitive text search (strips diacritics).
pgcrypto CREATE EXTENSION pgcrypto; Cryptographic functions: digests, HMAC, bcrypt password hashing, PGP encryption, random UUIDs and bytes.
uuid-ossp CREATE EXTENSION "uuid-ossp"; uuid_generate_v1() through uuid_generate_v5() for schemas written against them. New code can use the built-in gen_random_uuid() instead.
pgRouting CREATE EXTENSION pgrouting CASCADE; Routing and network analysis (requires PostGIS, installed by CASCADE).

With Reshape, qualify extension functions, types, and operators in application SQL using their owning schema, for example public.similarity(...), ::public.vector, and OPERATOR(public.<=>). Keep application table names unqualified and preserve Specific’s managed search_path. The Reshape guide covers missing-function/type/operator errors.

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.

# 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:

# 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 instead. You can also browse data and manage the database from the dashboard.

Schema migrations

For zero-downtime schema migrations, enable Reshape:

postgres "main" {
  reshape {
    enabled = true
  }
}

See Reshape migrations 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.

Storage limits

Each plan includes a database storage limit, listed on the pricing page. It applies to each database branch separately, so every database and every preview branch gets the same limit. The database’s metrics page in the dashboard shows its size against the limit. To stay under it, drop or truncate data, or upgrade the plan from the organization’s billing settings in the dashboard.

An organization whose databases already hold more than a plan allows cannot switch to that plan; the billing page names the project that is over.

Real-time sync

Postgres changes can be streamed to clients in real time. See Real-time sync.

Preview environments

In preview environments, databases are branched copy-on-write from the parent environment, so previews start with real data instantly.

External access credentials

Open your database’s Access tab in the dashboard to create a named credential. Choose Read-only for analytics tools and AI connectors, or Full access for administrative tools. Existing credentials retain full access. Passwords are shown once when created or rotated; revoking a credential prevents new connections. Rotation and revocation apply across the primary and all replicas. Existing sessions may stay connected until the client reconnects.

Read-only credentials receive SELECT privileges and respect row-level security. Tables and schemas created by Specific’s service role or direct migration connection are included automatically. Use the direct connection for migrations; objects created by other custom roles need appropriate grants. As with any Postgres role, explicit grants to PUBLIC and publicly executable SECURITY DEFINER functions can provide additional access; review those when exposing a database to external tools.

Read replicas

In Scale → Read replicas, create a named replica for analytics and reporting. Each replica has its own compute range and scales to zero after five minutes without activity. It shares the database’s data and may briefly lag behind recent writes. Select Connect to open a new credential dialog with the replica selected. Access lists all credentials with their permissions. Choose the connection target when creating a credential or viewing its connection details. Replica connections only offer read-only credentials. These credentials also work on the primary with the same read-only permissions.

Replica compute is included in the database’s existing compute charge on the usage page and invoices. All compute uses that database’s rate, including the higher rate when real-time sync is enabled; the creation dialog shows the current rate. Shared storage is charged once. There is no separate per-replica billing breakdown.

Replicas survive redeploys. Preview environments start without replicas. Deleting a replica disconnects its clients but retains the data and access credentials. Deleting the database or environment removes its replicas. Replica configuration is managed in the dashboard rather than in specific.hcl.

Was this page helpful?