Skip to main content

Database Configuration

Configure PostgreSQL and MongoDB for Bellamy Book.

Self-host kit (pre-built images): If you use the self-host kit, databases are configured in the single .env file (connection strings, passwords). Schema and seed run automatically when you start the stack (docker compose up -d). No manual migration commands are required. The sections below are for manual installation or advanced tuning.

PostgreSQL Configuration​

Connection String Format​

Host=localhost;Port=5432;Database=bellamybook;Username=bellamyuser;Password=your-password

Performance Tuning​

Edit /etc/postgresql/12/main/postgresql.conf:

# Memory settings
shared_buffers = 256MB
effective_cache_size = 1GB
maintenance_work_mem = 64MB
work_mem = 16MB

# Query planner
random_page_cost = 1.1
effective_io_concurrency = 200

# Connections
max_connections = 100

Create Indexes​

-- Posts indexes
CREATE INDEX idx_posts_author_created ON posts(author_id, created_at DESC);
CREATE INDEX idx_posts_privacy_created ON posts(privacy, created_at DESC);

-- Comments indexes
CREATE INDEX idx_comments_post_created ON comments(post_id, created_at DESC);
CREATE INDEX idx_comments_author ON comments(author_id);

-- Reactions indexes
CREATE INDEX idx_reactions_target ON reactions(target_id, target_type);
CREATE INDEX idx_reactions_user ON reactions(user_id);

Backup Configuration​

Set up automated backups:

# Add to crontab
0 2 * * * pg_dump -U bellamyuser bellamybook > /backups/postgres_$(date +\%Y\%m\%d).sql

MongoDB Configuration​

Connection String Format​

mongodb://username:password@localhost:27017/bellamybook?authSource=admin

Performance Tuning​

Edit /etc/mongod.conf:

storage:
  wiredTiger:
    engineConfig:
      cacheSizeGB: 2
    collectionConfig:
      blockCompressor: snappy
    indexConfig:
      prefixCompression: true

operationProfiling:
  slowOpThresholdMs: 100
  mode: slowOp

Create Indexes​

// In MongoDB shell
use bellamybook

// Stories indexes
db.stories.createIndex({ "userId": 1, "createdAt": -1 })
db.stories.createIndex({ "expiresAt": 1 }, { expireAfterSeconds: 0 })

// Notifications indexes
db.notifications.createIndex({ "userId": 1, "createdAt": -1 })
db.notifications.createIndex({ "userId": 1, "read": 1 })

Backup Configuration​

# Add to crontab
0 3 * * * mongodump --uri="mongodb://username:password@localhost:27017/bellamybook" --out=/backups/mongo_$(date +\%Y\%m\%d)

Redis Configuration​

Performance Tuning​

Edit /etc/redis/redis.conf:

# Memory
maxmemory 2gb
maxmemory-policy allkeys-lru

# Persistence
save 900 1
save 300 10
save 60 10000

# Performance
tcp-backlog 511
timeout 0
tcp-keepalive 300

Cache Strategy​

Configure cache TTLs:

// User cache: 30 minutes
await cache.SetAsync($"user:{userId}", user, TimeSpan.FromMinutes(30));

// Post cache: 10 minutes
await cache.SetAsync($"post:{postId}", post, TimeSpan.FromMinutes(10));

// Feed cache: 5 minutes
await cache.SetAsync($"feed:{userId}", feed, TimeSpan.FromMinutes(5));

Database Monitoring​

PostgreSQL Monitoring​

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

-- Check slow queries
SELECT * FROM pg_stat_statements 
ORDER BY total_time DESC 
LIMIT 10;

MongoDB Monitoring​

// Check database stats
db.stats()

// Check collection stats
db.posts.stats()

// Check index usage
db.posts.aggregate([{ $indexStats: {} }])

High Availability​

PostgreSQL Replication​

Set up streaming replication:

# Master postgresql.conf
wal_level = replica
max_wal_senders = 3
max_replication_slots = 3

# Standby recovery.conf
standby_mode = 'on'
primary_conninfo = 'host=master-host port=5432 user=replicator'

MongoDB Replica Set​

// Initialize replica set
rs.initiate({
  _id: "rs0",
  members: [
    { _id: 0, host: "mongo1:27017" },
    { _id: 1, host: "mongo2:27017" },
    { _id: 2, host: "mongo3:27017" }
  ]
})

Next Steps​