Database Configuration
Fine-tune your PostgreSQL and MariaDB databases for optimal performance.
Viewing Configuration
- Navigate to your database
- Click the Configuration tab
- View current settings and parameters
Compute Scaling
Changing Compute Class
- Go to your database
- Click Edit
- Select a new compute class
- Click Save
Compute changes require a brief restart (~30 seconds). Plan for a maintenance window in production.
When to Scale Up
Consider upgrading when:
- CPU utilization consistently > 80%
- Memory usage approaching limits
- Connection count near maximum
- Query performance degrading
Compute Class and Flavor
| Class | Description | Best For |
|---|---|---|
| Eco | Shared resources | Development and testing |
| Standard | Balanced performance | General production databases |
| Production | Dedicated resources | Critical databases |
Within each compute class, select a flavor (Small, Medium, Large, XLarge) that defines the CPU and memory allocation. See PostgreSQL Overview for details.
Connection limits scale with the selected flavor and compute class.
Storage Management
Increasing Storage
- Go to your database
- Click Edit
- Increase storage size
- Click Save
Storage increases apply immediately without downtime.
Monitoring Storage
Monitor storage usage from the Observability tab or the database Overview tab.
Set up alerts when storage reaches 80% capacity.
PostgreSQL Parameters
Viewing Parameters
View current parameter values in the Configuration tab of your database.
Key Parameters
The following parameters apply to PostgreSQL databases.
| Parameter | Description | Default |
|---|---|---|
max_connections | Maximum connections | 100 |
shared_buffers | Shared memory for caching data | 25% of flavor RAM |
work_mem | Per-operation memory for sorts and hashes | Derived from RAM and max_connections |
maintenance_work_mem | Memory for maintenance operations (VACUUM, CREATE INDEX) | RAM / 16 (capped at 2 GB) |
effective_cache_size | Planner estimate of available cache | 75% of flavor RAM |
max_wal_size | Max WAL size before checkpoint | 1GB |
min_wal_size | Min WAL size to retain | 80MB |
wal_level | WAL information level | replica |
track_io_timing | Measure database I/O durations | off |
track_wal_io_timing | Measure WAL write and sync durations (PostgreSQL 14+) | off |
SailOps automatically sizes shared_buffers, work_mem, maintenance_work_mem, and effective_cache_size based on the selected flavor's RAM and the configured max_connections. When you change the flavor, these parameters are recalculated — unless you have overridden them manually, in which case your custom values are preserved.
Modifying Parameters
- Go to your database
- Click the Configuration tab
- Adjust the parameter values
- Click Apply Changes
Some parameters require a database restart to take effect. The UI will indicate which changes need a restart.
I/O Timing
In Database Parameters, use Track data I/O timing and Track WAL I/O timing to diagnose persistence performance. These settings apply globally to the instance, including TimescaleDB databases, and take effect without a restart. Timing adds measurement overhead; enable it when you need these diagnostics.
The Public API accepts the same settings in the config object when creating or updating a PostgreSQL service. For example, send this body to PUT /api/public/api/v1/services/postgresql/{service}:
{
"config": {
"track_io_timing": true,
"track_wal_io_timing": true
}
}
Omitting a timing setting preserves its current value on update. Send false to disable it. track_wal_io_timing requires PostgreSQL 14 or later. Use SailOps configuration rather than ALTER SYSTEM, which is disabled on managed instances.
Verify the effective settings with:
SELECT name, setting, pending_restart
FROM pg_settings
WHERE name IN ('track_io_timing', 'track_wal_io_timing');
Connection Management
Connection Limits
Connection limits depend on your compute class and allocated resources. More resources support more connections.
| Compute Class | Flavor | Approximate Max Connections |
|---|---|---|
| Eco | Small | 25-50 |
| Eco | Large | 50-100 |
| Standard | Small | 100 |
| Standard | Large | 200-400 |
| Production | Small | 200 |
| Production | XLarge | 500+ |
The max_connections parameter is configurable (1-1000, default: 100).
Monitoring Connections
Monitor active connections from the Observability tab, which shows real-time connection metrics.
Connection Pooling
For applications with many connections, use connection pooling:
- PgBouncer (built into many frameworks)
- pgpool-II (advanced use cases)
- Application-level pooling (most Node.js, Python, Go drivers)
Query Performance
Query Statistics
Query performance can be monitored from the Observability tab. For detailed query analysis, enable the pg_stat_statements extension from the Extensions page.
Query Analysis
Use EXPLAIN ANALYZE:
EXPLAIN ANALYZE
SELECT * FROM users WHERE email = '[email protected]';
Look for:
- Sequential scans on large tables
- High row estimates vs actual
- Sort operations
Index Optimization
Create indexes for frequently queried columns:
-- Single column index
CREATE INDEX idx_users_email ON users(email);
-- Composite index
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at);
-- Partial index
CREATE INDEX idx_orders_pending ON orders(id)
WHERE status = 'pending';
Maintenance Tasks
VACUUM and ANALYZE
PostgreSQL automatically handles maintenance operations like VACUUM and ANALYZE. These run in the background without manual intervention.
Monitoring
Built-in Metrics
SailOps monitors:
- CPU utilization
- Memory usage
- Storage usage
- Connection count
- Query performance
- Replication lag (if HA enabled)
Viewing Metrics
- Go to your database
- Click the Metrics tab
- Select time range
- View charts
Setting Alerts
- Go to Monitoring > Alerts
- Create alert for database
- Set thresholds (e.g., CPU > 80%)
- Configure notifications
Logs
Viewing Logs
- Go to your database
- Click the Logs tab
- Filter by severity or search
Log Types
| Type | Content |
|---|---|
| Error | Database errors and crashes |
| Warning | Potential issues |
| Statement | Slow queries (> 1s) |
| Connection | Connect/disconnect events |
Security Settings
Password Management
Reset a database password (a new password is auto-generated):
- Go to database settings
- Click Reset Password
- Save the new password securely
- Update application configurations
User Management
Manage database users and roles directly from the SailOps UI.
PostgreSQL
- Navigate to your database > Databases & Users tab
- Click Create Role to add new roles
- Configure access level per database: Superuser, Full (write + schema changes), Write, or Read Only
- Use the key icon to reset a role's password
- Use the trash icon to delete a role
Databases and roles that belong to a dependent service are listed with a read-only badge and cannot be edited or deleted from this tab.
MariaDB
- Navigate to your database > Databases & Users tab
- Click Create User to add new users
- Configure privilege presets: Read Only, Write, or All Privileges
- Use the key icon to reset a user's password
- Use the trash icon to delete a user
Schemas and users that belong to a dependent service are listed with a read-only badge and cannot be edited or deleted from this tab.
MariaDB Configuration
MariaDB databases can also be configured from the database settings in the SailOps UI.
Key Settings
- Public Access: Toggle public access on or off from the database settings. When enabled, configure the IP allowlist with allowed CIDR ranges and an optional note per entry.
- Galera Cluster Configuration: The number of nodes in a Galera cluster is set at creation time. To change the node count, you must recreate the database.
- Storage and Compute: Storage and compute class can be adjusted the same way as PostgreSQL databases.
Troubleshooting
Slow Queries (PostgreSQL)
- Monitor query performance from the Observability tab
- Add missing indexes to frequently queried columns
- Consider upgrading compute class
Connection Issues
- Verify you're not at connection limit
- Check for connection leaks in application
- Implement connection pooling
- Consider upgrading for more connections
High CPU Usage
- Identify expensive queries
- Optimize or add indexes
- Check for long-running transactions
- Consider upgrading compute class