Skip to main content

Database Configuration

Fine-tune your PostgreSQL and MariaDB databases for optimal performance.

Viewing Configuration​

  1. Navigate to your database
  2. Click the Configuration tab
  3. View current settings and parameters

Compute Scaling​

Changing Compute Class​

  1. Go to your database
  2. Click Edit
  3. Select a new compute class
  4. Click Save
Downtime

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​

ClassDescriptionBest For
EcoShared resourcesDevelopment and testing
StandardBalanced performanceGeneral production databases
ProductionDedicated resourcesCritical 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​

  1. Go to your database
  2. Click Edit
  3. Increase storage size
  4. 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​

PostgreSQL-Specific

The following parameters apply to PostgreSQL databases.

ParameterDescriptionDefault
max_connectionsMaximum connections100
shared_buffersShared memory for caching data25% of flavor RAM
work_memPer-operation memory for sorts and hashesDerived from RAM and max_connections
maintenance_work_memMemory for maintenance operations (VACUUM, CREATE INDEX)RAM / 16 (capped at 2 GB)
effective_cache_sizePlanner estimate of available cache75% of flavor RAM
max_wal_sizeMax WAL size before checkpoint1GB
min_wal_sizeMin WAL size to retain80MB
wal_levelWAL information levelreplica
track_io_timingMeasure database I/O durationsoff
track_wal_io_timingMeasure WAL write and sync durations (PostgreSQL 14+)off
Automatic tuning

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​

  1. Go to your database
  2. Click the Configuration tab
  3. Adjust the parameter values
  4. Click Apply Changes
Restart Required

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 ClassFlavorApproximate Max Connections
EcoSmall25-50
EcoLarge50-100
StandardSmall100
StandardLarge200-400
ProductionSmall200
ProductionXLarge500+

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​

  1. Go to your database
  2. Click the Metrics tab
  3. Select time range
  4. View charts

Setting Alerts​

  1. Go to Monitoring > Alerts
  2. Create alert for database
  3. Set thresholds (e.g., CPU > 80%)
  4. Configure notifications

Logs​

Viewing Logs​

  1. Go to your database
  2. Click the Logs tab
  3. Filter by severity or search

Log Types​

TypeContent
ErrorDatabase errors and crashes
WarningPotential issues
StatementSlow queries (> 1s)
ConnectionConnect/disconnect events

Security Settings​

Password Management​

Reset a database password (a new password is auto-generated):

  1. Go to database settings
  2. Click Reset Password
  3. Save the new password securely
  4. Update application configurations

User Management​

Manage database users and roles directly from the SailOps UI.

PostgreSQL​

  1. Navigate to your database > Databases & Users tab
  2. Click Create Role to add new roles
  3. Configure access level per database: Superuser, Full (write + schema changes), Write, or Read Only
  4. Use the key icon to reset a role's password
  5. 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​

  1. Navigate to your database > Databases & Users tab
  2. Click Create User to add new users
  3. Configure privilege presets: Read Only, Write, or All Privileges
  4. Use the key icon to reset a user's password
  5. 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)​

  1. Monitor query performance from the Observability tab
  2. Add missing indexes to frequently queried columns
  3. Consider upgrading compute class

Connection Issues​

  1. Verify you're not at connection limit
  2. Check for connection leaks in application
  3. Implement connection pooling
  4. Consider upgrading for more connections

High CPU Usage​

  1. Identify expensive queries
  2. Optimize or add indexes
  3. Check for long-running transactions
  4. Consider upgrading compute class