Database Configuration

Documenso uses PostgreSQL as its primary database. This guide covers database configuration, optimization, and best practices.

Database Requirements

PostgreSQL Version: 14 or higher (15+ recommended)
Extensions: None required (all features use standard PostgreSQL)
Minimum Resources:
- 1 GB RAM
- 10 GB disk space (plus document storage if using database storage)
Recommended Resources:
- 4+ GB RAM
- 50+ GB SSD storage

Connection Configuration

NEXT_PRIVATE_DATABASE_URL

string

required

PostgreSQL connection string for the application. This should point to a connection pooler for production.Format: postgres://user:password@host:port/database

NEXT_PRIVATE_DATABASE_URL="postgres://documenso:password@localhost:5432/documenso"

NEXT_PRIVATE_DIRECT_DATABASE_URL

string

required

Direct PostgreSQL connection string for migrations and operations that don’t work with connection poolers.

NEXT_PRIVATE_DIRECT_DATABASE_URL="postgres://documenso:password@localhost:5432/documenso"

When using services like Supabase or connection poolers (PgBouncer), you’ll have separate URLs for pooled and direct connections.

Connection String Formats

postgres://username:password@hostname:port/database

Example:

postgres://documenso:mypassword@localhost:5432/documenso

postgres://username:password@hostname:port/database?sslmode=require

Example:

postgres://documenso:mypassword@db.example.com:5432/documenso?sslmode=require

postgres://postgres.xxxxx:password@aws-0-region.pooler.supabase.com:6543/postgres

Use the “Transaction” mode connection string from Supabase dashboard.

postgres://postgres.xxxxx:password@aws-0-region.pooler.supabase.com:5432/postgres

Use the “Session” mode connection string from Supabase dashboard.

postgres://username:password@ep-xxx.region.aws.neon.tech/database?sslmode=require

postgres://postgres:password@containers-us-west-xxx.railway.app:6543/railway

Database Setup

Creating the Database

# Connect to PostgreSQL as superuser
psql -U postgres

# Create database and user
CREATE DATABASE documenso;
CREATE USER documenso WITH PASSWORD 'your-secure-password';
GRANT ALL PRIVILEGES ON DATABASE documenso TO documenso;

# Connect to the documenso database
\c documenso

# Grant schema permissions (PostgreSQL 15+)
GRANT ALL ON SCHEMA public TO documenso;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO documenso;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO documenso;

Running Migrations

Documenso uses Prisma for database migrations.

# Install dependencies
npm install

# Run migrations
npm run prisma:migrate:deploy

Always use NEXT_PRIVATE_DIRECT_DATABASE_URL for running migrations, not the pooled connection.

Connection Pooling

For production deployments, use connection pooling to handle concurrent connections efficiently.

Using PgBouncer

Docker Compose
PgBouncer Config

docker-compose.yml

version: '3.8'
services:
  postgres:
    image: postgres:15-alpine
    environment:
      POSTGRES_DB: documenso
      POSTGRES_USER: documenso
      POSTGRES_PASSWORD: password
    volumes:
      - postgres-data:/var/lib/postgresql/data
  
  pgbouncer:
    image: edoburu/pgbouncer:latest
    environment:
      DATABASE_URL: postgres://documenso:password@postgres:5432/documenso
      POOL_MODE: transaction
      MAX_CLIENT_CONN: 1000
      DEFAULT_POOL_SIZE: 25
    ports:
      - "6432:5432"
    depends_on:
      - postgres
  
  documenso:
    image: documenso/documenso:latest
    environment:
      # Use PgBouncer for pooled connections
      NEXT_PRIVATE_DATABASE_URL: postgres://documenso:password@pgbouncer:5432/documenso
      # Direct connection for migrations
      NEXT_PRIVATE_DIRECT_DATABASE_URL: postgres://documenso:password@postgres:5432/documenso
    depends_on:
      - pgbouncer

volumes:
  postgres-data:

pgbouncer.ini

[databases]
documenso = host=postgres port=5432 dbname=documenso

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 25
reserve_pool_size = 5
reserve_pool_timeout = 3
server_lifetime = 3600
server_idle_timeout = 600

userlist.txt

"documenso" "md5password_hash_here"

Supabase Connection Pooling

Supabase provides built-in connection pooling:

.env

# Transaction mode (pooled) - use for application
NEXT_PRIVATE_DATABASE_URL="postgres://postgres.xxxxx:[YOUR-PASSWORD]@aws-0-region.pooler.supabase.com:6543/postgres"

# Session mode (direct) - use for migrations
NEXT_PRIVATE_DIRECT_DATABASE_URL="postgres://postgres.xxxxx:[YOUR-PASSWORD]@aws-0-region.pooler.supabase.com:5432/postgres"

Performance Optimization

PostgreSQL Configuration

Optimize PostgreSQL settings in postgresql.conf:

postgresql.conf

# Memory settings (adjust based on available RAM)
shared_buffers = 256MB              # 25% of total RAM
effective_cache_size = 1GB          # 50-75% of total RAM
maintenance_work_mem = 64MB
work_mem = 16MB

# Connection settings
max_connections = 100

# Performance tuning
random_page_cost = 1.1              # For SSD storage
effective_io_concurrency = 200      # For SSD storage

# Write-ahead log
wal_buffers = 16MB
checkpoint_completion_target = 0.9

# Query planner
default_statistics_target = 100

These values are for a server with 4GB RAM. Adjust based on your available resources.

Indexing Strategy

Documenso’s migrations include necessary indexes. Monitor query performance:

-- Find missing indexes
SELECT
  schemaname,
  tablename,
  attname,
  n_distinct,
  correlation
FROM pg_stats
WHERE schemaname = 'public'
ORDER BY n_distinct DESC;

-- Check index usage
SELECT
  schemaname,
  tablename,
  indexname,
  idx_scan,
  idx_tup_read,
  idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC;

Vacuum and Analyze

Enable autovacuum and configure it appropriately:

postgresql.conf

autovacuum = on
autovacuum_max_workers = 3
autovacuum_naptime = 1min
autovacuum_vacuum_threshold = 50
autovacuum_analyze_threshold = 50

Manual vacuum for maintenance:

-- Vacuum all tables
VACUUM ANALYZE;

-- Vacuum specific table
VACUUM ANALYZE "Document";

Backup and Recovery

Automated Backups with pg_dump

backup.sh

#!/bin/bash

# Configuration
BACKUP_DIR="/backups/postgres"
DATABASE="documenso"
USER="documenso"
HOST="localhost"
PORT="5432"
RETENTION_DAYS=7

# Create backup directory
mkdir -p "$BACKUP_DIR"

# Generate backup filename with timestamp
BACKUP_FILE="$BACKUP_DIR/documenso_$(date +%Y%m%d_%H%M%S).sql.gz"

# Perform backup
pg_dump -h "$HOST" -p "$PORT" -U "$USER" -Fc "$DATABASE" | gzip > "$BACKUP_FILE"

# Delete old backups
find "$BACKUP_DIR" -name "documenso_*.sql.gz" -mtime +$RETENTION_DAYS -delete

echo "Backup completed: $BACKUP_FILE"

Schedule with cron:

# Daily backup at 2 AM
0 2 * * * /path/to/backup.sh

Restore from Backup

# Restore from compressed backup
gunzip -c backup.sql.gz | psql -U documenso -d documenso

# Or restore from custom format
pg_restore -U documenso -d documenso backup.dump

Point-in-Time Recovery (PITR)

Enable WAL archiving for point-in-time recovery:

postgresql.conf

wal_level = replica
archive_mode = on
archive_command = 'test ! -f /archive/%f && cp %p /archive/%f'
max_wal_senders = 3

Monitoring

Database Size

-- Total database size
SELECT pg_size_pretty(pg_database_size('documenso'));

-- Table sizes
SELECT
  schemaname,
  tablename,
  pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

Active Connections

-- Current connections
SELECT
  count(*),
  state
FROM pg_stat_activity
WHERE datname = 'documenso'
GROUP BY state;

-- Long-running queries
SELECT
  pid,
  now() - pg_stat_activity.query_start AS duration,
  query,
  state
FROM pg_stat_activity
WHERE (now() - pg_stat_activity.query_start) > interval '5 minutes'
AND state = 'active';

Performance Metrics

-- Cache hit ratio (should be > 95%)
SELECT
  sum(heap_blks_read) as heap_read,
  sum(heap_blks_hit) as heap_hit,
  sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) as cache_hit_ratio
FROM pg_statio_user_tables;

-- Slow queries
SELECT
  query,
  calls,
  total_exec_time,
  mean_exec_time,
  max_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

Enable pg_stat_statements extension for query statistics:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Managed PostgreSQL Providers

Supabase

Pros:

Free tier available
Built-in connection pooling
Automatic backups
Real-time capabilities

Setup:

Create a project at supabase.com
Get connection strings from Settings > Database
Use Transaction mode for NEXT_PRIVATE_DATABASE_URL
Use Session mode for NEXT_PRIVATE_DIRECT_DATABASE_URL

Neon

Pros:

Serverless PostgreSQL
Instant branching
Autoscaling
Free tier

Setup:

Create a project at neon.tech
Get the connection string
Use the same connection string for both URLs (Neon handles pooling automatically)

Railway

Pros:

Simple setup
Integrated with app deployment
Automatic backups

Setup:

Create PostgreSQL service in Railway
Get connection strings from service variables
Use DATABASE_URL for both configurations

Amazon RDS

Pros:

Fully managed
Multi-AZ deployments
Automated backups
High availability

Setup:

Create RDS PostgreSQL instance
Configure security groups
Enable automated backups
Use endpoint URL in connection strings

Migration from SQLite or Other Databases

Documenso only supports PostgreSQL. Migration from other databases requires manual data conversion.

If migrating from another database:

Export data from the source database
Set up PostgreSQL
Run Documenso migrations
Transform and import data
Verify data integrity
Update connection strings

Troubleshooting

”too many connections” error

Cause: Exceeded max_connections limit. Solution:

Implement connection pooling (PgBouncer)
Increase max_connections in PostgreSQL
Investigate connection leaks in the application

-- Check current connections
SELECT count(*) FROM pg_stat_activity;

-- Terminate idle connections
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = 'documenso'
AND state = 'idle'
AND state_change < now() - interval '1 hour';

Slow queries

Cause: Missing indexes, inefficient queries, or inadequate resources. Solution:

Enable query logging:

postgresql.conf

log_min_duration_statement = 1000  # Log queries taking > 1s

Analyze slow queries:

EXPLAIN ANALYZE SELECT * FROM "Document" WHERE "userId" = 123;

Add indexes if needed
Increase resources (RAM, CPU)

Database bloat

Cause: Dead tuples from updates/deletes not being cleaned up. Solution:

-- Check table bloat
SELECT
  schemaname,
  tablename,
  pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size,
  n_dead_tup,
  n_live_tup,
  round(n_dead_tup::float / NULLIF(n_live_tup, 0), 4) AS dead_ratio
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

-- Full vacuum (locks table)
VACUUM FULL "Document";

Connection pool exhaustion

Cause: All connections in use, new connections blocked. Solution:

Increase pool size in PgBouncer
Optimize queries to reduce connection time
Implement request queuing in the application

Security Best Practices

Use Strong Passwords

Generate secure passwords for database users:

openssl rand -base64 32

Enable SSL/TLS

Require SSL connections in postgresql.conf:

ssl = on
ssl_cert_file = '/path/to/server.crt'
ssl_key_file = '/path/to/server.key'

Connect with SSL:

postgres://user:pass@host:5432/db?sslmode=require

Restrict Network Access

Configure pg_hba.conf to limit connections:

# Allow only from application server
host    documenso    documenso    10.0.1.0/24    md5

# Deny all others
host    all         all          0.0.0.0/0      reject

Use Separate User for Application

Don’t use the postgres superuser for the application:

CREATE USER documenso WITH PASSWORD 'secure-password';
GRANT CONNECT ON DATABASE documenso TO documenso;
GRANT USAGE ON SCHEMA public TO documenso;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO documenso;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO documenso;

Regular Security Updates

Keep PostgreSQL updated with the latest security patches.

Encrypt Backups

Encrypt backup files:

pg_dump documenso | gzip | gpg -e -r your@email.com > backup.sql.gz.gpg

Database Configuration

Database Requirements

Connection Configuration

Connection String Formats

Database Setup

Creating the Database

Running Migrations

Connection Pooling

Using PgBouncer

Supabase Connection Pooling

Performance Optimization

PostgreSQL Configuration

Indexing Strategy

Vacuum and Analyze

Backup and Recovery

Automated Backups with pg_dump

Restore from Backup

Point-in-Time Recovery (PITR)

Monitoring

Database Size

Active Connections

Performance Metrics

Managed PostgreSQL Providers

Supabase

Neon

Railway

Amazon RDS

Migration from SQLite or Other Databases

Troubleshooting

”too many connections” error

Slow queries

Database bloat

Connection pool exhaustion

Security Best Practices

Next Steps

Background Jobs

Environment Variables

​Database Requirements

​Connection Configuration

​Connection String Formats

​Database Setup

​Creating the Database

​Running Migrations

​Connection Pooling

​Using PgBouncer

​Supabase Connection Pooling

​Performance Optimization

​PostgreSQL Configuration

​Indexing Strategy

​Vacuum and Analyze

​Backup and Recovery

​Automated Backups with pg_dump

​Restore from Backup

​Point-in-Time Recovery (PITR)

​Monitoring

​Database Size

​Active Connections

​Performance Metrics

​Managed PostgreSQL Providers

​Supabase

​Neon

​Railway

​Amazon RDS

​Migration from SQLite or Other Databases

​Troubleshooting

​”too many connections” error

​Slow queries

​Database bloat

​Connection pool exhaustion

​Security Best Practices

​Next Steps

Background Jobs

Environment Variables

Database Requirements

Connection Configuration

Connection String Formats

Database Setup

Creating the Database

Running Migrations

Connection Pooling

Using PgBouncer

Supabase Connection Pooling

Performance Optimization

PostgreSQL Configuration

Indexing Strategy

Vacuum and Analyze

Backup and Recovery

Automated Backups with pg_dump

Restore from Backup

Point-in-Time Recovery (PITR)

Monitoring

Database Size

Active Connections

Performance Metrics

Managed PostgreSQL Providers

Supabase

Neon

Railway

Amazon RDS

Migration from SQLite or Other Databases

Troubleshooting

”too many connections” error

Slow queries

Database bloat

Connection pool exhaustion

Security Best Practices

Next Steps