Database Tuning for Self-Hosted Apps
Default database settings are conservative. A few configuration changes can dramatically improve performance for self-hosted applications.
Why Default Settings Are Slow
Database defaults are designed to work on any hardware, including a Raspberry Pi. If your server has 4 GB RAM, your database is probably using 128 MB of it.
PostgreSQL Tuning
shared_buffers
Default: 128 MB. Set to 25% of total RAM.
4 GB server: shared_buffers = 1GB
effective_cache_size
Default: 4 GB. Set to 50-75% of total RAM.
4 GB server: effective_cache_size = 3GB
work_mem
Memory for query operations (sorts, joins). Default: 4 MB.
Set based on expected concurrent queries: total_ram / (max_connections * 2)
maintenance_work_mem
Memory for maintenance operations (VACUUM, CREATE INDEX). Default: 64 MB.
Set to 10% of RAM: 256 MB-512 MB
wal_buffers
Default: varies. Set to 64 MB for most workloads.
max_connections
Default: 100. This is usually too many. Each connection uses ~10 MB of RAM.
Use connection pooling (PgBouncer) and set max_connections to 20-50.
MySQL/MariaDB Tuning
innodb_buffer_pool_size
Set to 50-70% of total RAM. This is the single most important MySQL setting.
4 GB server: innodb_buffer_pool_size = 2G
innodb_log_file_size
Default: 48 MB. Set to 256 MB-1 GB for write-heavy workloads.
max_connections
Same advice as PostgreSQL: lower it and use connection pooling.
Connection Pooling
Don't let every app connection go directly to the database. Use a connection pooler:
Connection poolers maintain a smaller pool of actual database connections and multiplex application requests across them.
Monitoring
Tune, then measure:
On TinyPod, shared databases are pre-tuned for the server's available resources.