Configuration

Configuration Reference

pgvis configuration is layered (later sources override earlier):

1. Defaults — sensible production defaults from Config::default()
2. TOML config file — specified via --config flag or PGVIS_CONFIG env var
3. Environment variables — prefixed with PGVIS_, lowercase field names

Environment Variables

Variable	Purpose	Default
PGVIS_DSN	Database connection string	required
PGVIS_BIND	Server bind address	0.0.0.0:3000
PGVIS_CONFIG	Path to TOML config file	none
PGVIS_SCHEMAS	Schemas to expose (comma-separated)	public
PGVIS_JWT_SECRET	JWT verification secret	none (anonymous only)
PGVIS_JWT_ALGO	JWT algorithm	HS256
PGVIS_ANON_ROLE	Database role for unauthenticated requests	none
PGVIS_ROLE_CLAIM_KEY	JWT claim key for role	role
PGVIS_MAX_ROWS	Maximum rows returned per request	unlimited
PGVIS_STATEMENT_TIMEOUT_MS	SQL statement timeout (ms)	30000
PGVIS_POOL_SIZE	Pool max connections	16
PGVIS_POOL_TIMEOUT_MS	Pool checkout timeout (ms)	5000
PGVIS_POOL_CREATE_TIMEOUT_MS	Pool create connection timeout (ms)	5000
PGVIS_POOL_RECYCLE_TIMEOUT_MS	Pool recycle/validate timeout (ms)	5000
PGVIS_POOL_KEEPALIVES	TCP keepalive on connections	true
PGVIS_POOL_KEEPALIVES_IDLE_SECS	Keepalive idle time (seconds)	60
PGVIS_POOL_CONNECT_TIMEOUT_SECS	TCP connect timeout (seconds)	10
PGVIS_POOL_RECYCLING_METHOD	Connection recycling method	Fast
PGVIS_READ_ONLY	Reject all mutations	false
PGVIS_AGGREGATES_ENABLED	Allow aggregate functions in select	false
PGVIS_PLAN_ENABLED	Allow EXPLAIN plan requests	false
PGVIS_TX_ALLOW_OVERRIDE	Allow Prefer: tx=rollback	false
PGVIS_TX_ROLLBACK_ALL	Force rollback on all transactions	false
PGVIS_PRE_REQUEST	Pre-request function name	none
PGVIS_OPENAPI_TITLE	OpenAPI document title	none
PGVIS_OPENAPI_SERVER_URL	OpenAPI server URL override	none
PGVIS_REPLICA_DSNS	Read replica DSNs (comma-separated)	none
PGVIS_MAX_REPLICATION_LAG_BYTES	Replica lag threshold in bytes	10485760 (10 MB)
PGVIS_HEALTH_CHECK_INTERVAL_MS	Replica health check interval (ms)	5000
PGVIS_CACHE_ENABLED	Enable the in-memory read cache	false
PGVIS_CACHE_TTL	Cache entry TTL in seconds	60
PGVIS_CACHE_MAX_ENTRIES	Max cache entries (LRU capacity)	10000
PGVIS_CACHE_LISTS	Also cache list queries (not just PK lookups)	false
RUST_LOG	Log level filter	pgvis=info,tower_http=info

TOML Config File

Create a pgvis.toml file and reference it with --config pgvis.toml or PGVIS_CONFIG=pgvis.toml:

# Schema selection
schemas = ["public", "api"]
extra_search_path = ["extensions"]

# Authentication
jwt_secret = "your-secret-key-here"
jwt_algo = "HS256"
anon_role = "web_anon"
role_claim_key = "role"

# Feature gates
aggregates_enabled = true
plan_enabled = false
tx_allow_override = true
tx_rollback_all = false
read_only = false

# Query limits
max_rows = 1000
statement_timeout_ms = 30000

# Connection pool
[pool]
size = 16
timeout_ms = 5000
create_timeout_ms = 5000
recycle_timeout_ms = 5000
keepalives = true
keepalives_idle_secs = 60
connect_timeout_secs = 10
recycling_method = "Fast"

# Hooks
pre_request = "auth.check_request"

# OpenAPI
openapi_title = "My API"
openapi_server_url = "https://api.example.com"
openapi_mode = "IgnorePrivileges"

# Routing
[routing]
prefix = "api"
schema_in_path = true
default_schema = "public"
mcp_separator = "/"

# Read replicas (Postgres only)
[replica]
replica_dsns = [
  "postgres://replica1:5432/mydb",
  "postgres://replica2:5432/mydb",
]
max_replication_lag_bytes = 10485760  # 10 MB
health_check_interval_ms = 5000
primary_reads = true

# In-memory read cache (off by default)
[cache]
enabled = true
ttl_seconds = 60
max_entries = 10000
cache_lists = false

---

Configuration Fields

Schema Selection

Field	Type	Default	Description
schemas	string[]	["public"]	Which schemas to expose as API endpoints. Tables, views, and functions in these schemas become REST routes and MCP tools.
extra_search_path	string[]	[]	Additional schemas for type/function resolution (not exposed as endpoints).

Authentication

Field	Type	Default	Description
jwt_secret	string?	null	JWT secret for token verification. If absent, JWT verification is disabled. Can be a symmetric secret or path to a public key.
jwt_algo	enum	HS256	JWT signing algorithm. One of: HS256, HS384, HS512, RS256, EdDSA.
anon_role	string?	null	The database role used for unauthenticated requests. On Postgres, this role’s permissions define anonymous access.
role_claim_key	string	"role"	The JWT claim key that specifies the database role. The value of this claim becomes the SET LOCAL role value.

How JWT auth works

1. Client sends Authorization: Bearer <token> header
2. pgvis verifies the token against jwt_secret using jwt_algo
3. The role from the token’s role_claim_key claim is used to SET LOCAL role
4. All JWT claims are available as current_setting('request.jwt.claims') in SQL
5. This integrates with PostgreSQL Row-Level Security (RLS)

If no valid JWT is present, the anon_role is used (or the connection’s default role if anon_role is not set).

Feature Gates

Field	Type	Default	Description
aggregates_enabled	bool	false	Whether aggregate functions (sum, avg, count, min, max) are allowed in select. Disabled by default because aggregates can be expensive without proper indexes.
plan_enabled	bool	false	Whether clients can request Accept: application/vnd.pgrst.plan+json to get the query execution plan.
tx_allow_override	bool	false	Whether Prefer: tx=rollback is honoured. When false, the preference is silently ignored.
tx_rollback_all	bool	false	Force rollback of ALL transactions regardless of client preference. Useful for testing/staging environments.
read_only	bool	false	Reject all mutations (POST/PATCH/DELETE on tables, VOLATILE RPC calls). MCP tool catalogues omit write tools entirely.

Query Limits

Field	Type	Default	Description
max_rows	u64?	null (unlimited)	Maximum number of rows returned per request. Acts as a server-side cap on limit. When a client requests more rows than this (or no limit), the response is capped.
statement_timeout_ms	u64?	30000 (30s)	SQL statement timeout in milliseconds. Applied to every query. Prevents runaway queries. Set to null or 0 to disable.

Connection Pool

Pool settings live under the [pool] table in TOML (or as flat PGVIS_POOL_* env vars). These settings apply to both the primary pool and all replica pools.

Field	Type	Default	Description
pool.size	u32	16	Maximum number of database connections in the pool. Each concurrent request holds one connection for its transaction duration. For replicas, each replica pool gets this many connections independently.
pool.timeout_ms	u64	5000 (5s)	Pool checkout timeout. If all connections are busy, new requests wait up to this duration before returning 503 Service Unavailable. Set to 0 for no timeout (not recommended).
pool.create_timeout_ms	u64	5000 (5s)	Timeout for creating a new connection. Bounds how long establishing a new TCP connection + TLS handshake + Postgres auth can take. Set to 0 for no timeout.
pool.recycle_timeout_ms	u64	5000 (5s)	Timeout for recycling/validating an existing connection. When a pooled connection is checked out, the recycling query must complete within this time or the connection is discarded. Set to 0 for no timeout.
pool.keepalives	bool	true	Whether TCP keepalive is enabled on connections. Critical for connections through NATs, firewalls, or load balancers that silently drop idle TCP connections.
pool.keepalives_idle_secs	u64	60 (1 min)	How long a connection must be idle before the first keepalive probe is sent. Lower values detect dead connections faster but generate more traffic. Only effective when keepalives = true.
pool.connect_timeout_secs	u64	10	Maximum time (seconds) to wait for a TCP connection to be established with the Postgres server. Independent of the pool checkout timeout. Set to 0 for no timeout.
pool.recycling_method	enum	Fast	How connections are validated when returned to the pool. Fast: check is_closed() only (cheapest). Verified: execute an empty query to ensure the connection responds (one round-trip). Clean: run DISCARD-like statements for pristine session state (most thorough).

Recycling methods explained

Method	Overhead	Use when
Fast	None (in-process check)	Low-latency is critical; Postgres/network is stable
Verified	1 round-trip	Connections cross unreliable networks or NATs
Clean	Several round-trips	Untrusted extensions or session state leaks between requests

Tuning recommendations

• Behind a load balancer/NAT: Set keepalives = true and keepalives_idle_secs below the LB idle timeout (often 60–300 s).
• Serverless/burst traffic: Lower create_timeout_ms (e.g. 3000) so cold-start pool growth doesn’t block the first requests too long.
• High-availability: Use recycling_method = "Verified" to detect dead connections immediately on checkout rather than failing the first query.

Read Replicas (Postgres only)

Field	Type	Default	Description
replica.replica_dsns	string[]	[]	DSNs of read replicas. When non-empty, enables lag-aware read routing and load balancing across replicas.
replica.max_replication_lag_bytes	u64	10485760 (10 MB)	Maximum acceptable replication lag in bytes. Replicas exceeding this are excluded from the read pool. Set to 0 to disable lag checking.
replica.health_check_interval_ms	u64	5000 (5s)	How often the health monitor checks replica lag and connectivity.
replica.primary_reads	bool	true	Whether the primary also serves read queries. When false, reads go exclusively to replicas (primary handles only writes). Falls back to primary if all replicas are unhealthy.

How replica routing works

1. Writes (POST, PATCH, PUT, DELETE on tables) always go to the primary
2. Reads (GET on tables/views, read-only RPC) are distributed round-robin across healthy replicas (and the primary if primary_reads = true)
3. A background health monitor checks each replica’s replication lag every health_check_interval_ms
4. Replicas whose WAL receive position is more than max_replication_lag_bytes behind the primary are temporarily excluded
5. If all replicas are unhealthy, reads fall back to the primary automatically

Failover behaviour

pgvis does not handle promotion. External tools (Patroni, pg_auto_failover, DNS/VIP) handle that. When the primary becomes unreachable:

• Active connections fail with a pool error (503 to the client)
• Once the external tool updates DNS/VIP, the next pool checkout resolves to the new primary
• No manual restart required — deadpool-postgres evicts broken connections automatically

Data Cache

An optional in-memory cache for read responses. Off by default — when disabled it allocates nothing and adds no per-request cost.

Field	Type	Default	Description
cache.enabled	bool	false	Master switch. When true, an in-memory response cache is created at startup.
cache.ttl_seconds	u64	60	How long a cached entry lives before it expires and the next read re-queries the database. Lower = fresher data; higher = less DB load.
cache.max_entries	u64	10000	Maximum entries held. When full, least-recently-used entries are evicted.
cache.cache_lists	bool	false	Whether to cache list/collection queries in addition to primary-key lookups. See the caveats below before enabling.

What gets cached

• PK lookups (every primary-key column filtered with eq) are cached whenever the cache is enabled.
• List queries are cached only when cache_lists = true.
• Queries with embeds are never cached.

The cache key hashes the request’s JWT claims together with the rendered SQL and parameters, prefixed by the database role. This means each entry is unique per (role, identity, query shape):

• Two users who share a role but differ in identity (e.g. different sub) get separate entries — one user’s row-level-security-filtered rows are never served to another.
• The same primary key requested with a different select, an extra filter, a different order, or different pagination gets a separate entry — the cache never returns a response of the wrong shape.

Invalidation

Any write clears the entire cache:

• A mutation (POST/PATCH/PUT/DELETE) on any table, and
• A volatile RPC call (a function that may modify data).

Clearing wholesale is deliberate: a write can cascade to other tables through foreign keys or triggers, so evicting only the directly-targeted table could leave stale rows elsewhere. The ttl_seconds window is the upper bound on staleness for everything else (for example, a data-modifying function mislabeled STABLE/IMMUTABLE in the catalog, which won’t trigger invalidation).

Caveats — read before enabling

• Highly personalized data caches poorly. Because claims are part of the key, per-user data produces a distinct entry per user and gets little benefit. The cache shines on PK lookups against shared, role-gated data.
• List cardinality. High-variety list traffic produces many distinct keys, yielding low hit rates and high memory churn. This is why cache_lists is off by default.
• Write-heavy multi-table workloads. Since every write clears the whole cache, frequent writes erase the working set before it pays off. The cache targets read-heavy workloads.

Monitoring

GET /pgvis/cache (a fixed path, not under your configured prefix) returns the current settings plus hit/miss/invalidation counters and the hit rate:

{
  "settings": { "enabled": true, "ttl_seconds": 60, "max_entries": 10000, "cache_lists": false },
  "stats":    { "hits": 1280, "misses": 240, "invalidations": 12, "entries": 305, "hit_rate": 84.21 }
}

The endpoint is always available; stats is null when caching is disabled.

Hooks

Field	Type	Default	Description
pre_request	string?	null	Fully-qualified function name to call before every query. Called after JWT verification and role switching. Can raise exceptions to abort the request (e.g., rate limiting, audit logging).

OpenAPI

Field	Type	Default	Description
openapi_title	string?	null	Title in the OpenAPI document’s info section.
openapi_server_url	string?	null	Server URL override for the OpenAPI spec (for proxied deployments).
openapi_mode	enum	IgnorePrivileges	Controls spec generation. IgnorePrivileges: show everything. FollowPrivileges: filter based on role. Disabled: disable the endpoint entirely.

Routing

Field	Type	Default	Description
routing.prefix	string	"api"	URL prefix for all API routes. Leading/trailing slashes stripped. Set to "" for PostgREST-compatible flat routes.
routing.schema_in_path	bool	true	Whether the schema name appears in the URL path. When true: /{prefix}/{schema}/{table}. When false: /{prefix}/{table} with schema from header.
routing.default_schema	string	"public"	The default schema when schema_in_path = false and no profile header is sent. Must be one of the schemas listed in schemas.
routing.mcp_separator	char	'/'	Separator in MCP tool names. / maps to hierarchical discovery. . is an alternative for flat tool lists.

---

Routing Mode Examples

Full path (recommended for multi-schema)

[routing]
prefix = "api"
schema_in_path = true

Routes:

GET /api/public/users
GET /api/analytics/events
POST /api/public/rpc/search

Prefixed flat (single schema)

[routing]
prefix = "api"
schema_in_path = false
default_schema = "public"

Routes:

GET /api/users
POST /api/rpc/search

PostgREST-compatible (drop-in replacement)

[routing]
prefix = ""
schema_in_path = false
default_schema = "public"

Routes:

GET /users
POST /rpc/search

---

CLI Flags

CLI flags override config file and environment variable values.

pgvis serve

Flag	Env	Description
--bind <ADDR>	PGVIS_BIND	Bind address (default: 0.0.0.0:3000)
--schema <NAME>	PGVIS_SCHEMAS	Schemas to expose (repeatable or comma-separated)
--replica-dsn <DSN>	PGVIS_REPLICA_DSNS	Read replica DSNs (repeatable or comma-separated)
--cache-enabled	PGVIS_CACHE_ENABLED	Enable the in-memory read cache
--cache-ttl <SECS>	PGVIS_CACHE_TTL	Cache entry TTL in seconds
--cache-max-entries <N>	PGVIS_CACHE_MAX_ENTRIES	Max cache entries (LRU capacity)
--cache-lists	PGVIS_CACHE_LISTS	Also cache list queries (not just PK lookups)
--mcp-http	—	Also serve MCP at /mcp via Streamable HTTP

pgvis mcp

Flag	Env	Description
--schema <NAME>	PGVIS_SCHEMAS	Schemas to expose
--read-only	—	Expose only read tools (no create/update/delete)

Global options

Flag	Env	Description
--dsn <DSN>	PGVIS_DSN	Database connection string (required)
--config <FILE>	PGVIS_CONFIG	Path to TOML config file

---

Configuration Priority

When the same setting is specified in multiple places, the highest-priority source wins:

CLI flags  >  Environment variables  >  TOML config file  >  Defaults

Example:

# Config file sets max_rows = 1000
# Env var overrides: PGVIS_MAX_ROWS=500
# Result: max_rows = 500
PGVIS_MAX_ROWS=500 pgvis --config pgvis.toml serve

---

Pub/Sub

Field	Type	Default	Description
pubsub.enabled	bool	false	Enable the pub/sub subsystem
pubsub.channel_prefix	string	"pgvis:"	Prefix added to Postgres LISTEN/NOTIFY channel names
pubsub.max_payload_bytes	integer	7500	Maximum message payload size
pubsub.buffer_capacity	integer	256	In-process broadcast buffer per channel
pubsub.allowed_channels	string[]	[]	Channel allowlist (empty = allow all). Supports globs: chat.*

TOML example:

[pubsub]
enabled = true
channel_prefix = "myapp:"
max_payload_bytes = 7500
allowed_channels = ["chat.*", "notifications", "events.*"]

Environment variables:

Variable	Maps to
PGVIS_PUBSUB_ENABLED	pubsub.enabled
PGVIS_PUBSUB_CHANNEL_PREFIX	pubsub.channel_prefix

See the Pub/Sub guide for full usage details.

---

PostgREST Compatibility Reference

PostgREST config	pgvis equivalent
db-schemas	schemas
db-extra-search-path	extra_search_path
db-anon-role	anon_role
jwt-secret	jwt_secret
jwt-role-claim-key	role_claim_key
db-aggregates-enabled	aggregates_enabled
db-plan-enabled	plan_enabled
db-tx-allow-override	tx_allow_override
db-tx-rollback-all	tx_rollback_all
max-rows	max_rows
db-pre-request	pre_request
openapi-server-proxy-uri	openapi_server_url
openapi-mode	openapi_mode