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.