Database Parameter Groups

Database parameter groups allow you to customize database configuration settings for your managed PostgreSQL, MySQL, and MariaDB instances. This guide covers parameter management, common configurations, and best practices.

Overview

Parameter groups contain database engine configuration values that control:

  • Performance: Buffer sizes, cache settings, query optimization
  • Connection Handling: Connection limits, timeouts
  • Logging: Query logging, error reporting
  • Replication: Replication settings, binary log configuration
  • Security: Authentication methods, SSL/TLS requirements

How Parameter Groups Work

Default Parameter Groups

Each database engine version has a default parameter group:

  • Optimized Defaults: Pre-configured for general use
  • Cannot Modify: Default groups are read-only
  • Starting Point: Use as template for custom groups

Custom Parameter Groups

Create custom parameter groups for specific workloads:

  • Multiple Databases: Apply the same configuration to multiple databases
  • Cross-Version: Custom parameter groups stay compatible across engine versions
  • Modify Anytime: Change parameters without recreating the group
  • Revert: Restore to default settings if needed

When you attach a group, the dropdown automatically filters to compatible groups: default (system) groups are version-scoped and match your instance's engine version, while custom groups remain usable across versions.

Creating a Parameter Group

Via Dashboard

  1. Navigate to Databases > Parameter Groups
  2. Click Create Parameter Group
  3. Configure:
    • Name: Descriptive name (e.g., "production-mysql-8")
    • Engine: PostgreSQL, MySQL, or MariaDB
    • Version: Specific engine version
    • Base Group: Default or existing custom group
  4. Click Create

Copying an Existing Group

Clone an existing parameter group:

  1. Navigate to Parameter Groups
  2. Select group to copy
  3. Click Actions > Copy
  4. Provide new name
  5. Modify parameters as needed

Parameter groups can also be created, read, updated, and deleted programmatically through the public API.

Applying Parameter Groups

To New Database

During database creation:

  1. Navigate to Create Database
  2. Under Advanced Options
  3. Select Custom Parameter Group
  4. Choose your parameter group
  5. Complete database creation

To Existing Database

Attach an existing parameter group to a database:

  1. Navigate to your database
  2. Click Edit
  3. Under Configuration, choose a group from Parameter group
  4. Save your changes
  5. Restart the database if any of the new group's settings require it

Note: Some parameters require database restart to take effect.

From the Instance's Parameters Tab

Cache, database, and queue instances that support it also have a Parameters tab on the instance page, for changing a handful of settings on that one instance:

  1. Navigate to your database and open the Parameters tab
  2. Click Edit parameters
  3. Enter a New parameter group name. Saving creates a new parameter group for this instance only — other instances keep their current group
  4. For each setting to change, make sure Override engine default is on and enter the new value
  5. Choose how to apply the change:
    • Without restart: applies immediately to the running instance, for settings PostgreSQL can reload
    • Restart instance: for settings that need a restart; this interrupts connections and asks for confirmation before proceeding
  6. Save

The tab only exposes a subset of PostgreSQL settings; everything else is still managed through a full parameter group as described above. Values shown as Current are the saved configuration.

For PostgreSQL, the settings editable without a restart from this tab are:

  • log_min_duration_statement: milliseconds; -1 disables slow-statement logging
  • log_statement: none, ddl, mod, or all
  • log_duration
  • random_page_cost
  • effective_io_concurrency
  • default_statistics_target
  • checkpoint_completion_target
  • statement_timeout: milliseconds; 0 disables it
  • lock_timeout: milliseconds; 0 disables it
  • idle_in_transaction_session_timeout: milliseconds
  • ssl_min_protocol_version: TLSv1.3 (default) or TLSv1.2, for clients that cannot use TLS 1.3 (see below)

Common PostgreSQL Parameters

Performance Parameters

shared_buffers

Controls memory for caching data. On Managed PostgreSQL, the platform sizes this from your instance plan; parameter group and per-instance overrides are ignored.

work_mem

Memory for sort operations and hash tables:

Text
work_mem = 64MB

Recommendations:

  • OLTP workloads: 32-64 MB
  • Analytics workloads: 128-256 MB
  • Complex queries: Up to 1 GB

effective_cache_size

Estimates the cache available to the query planner. On Managed PostgreSQL, the platform sizes this from your instance plan; parameter group and per-instance overrides are ignored.

max_connections

Limits concurrent connections. On Managed PostgreSQL, the platform sizes this from your instance plan; parameter group and per-instance overrides are ignored. Use connection pooling to reduce the database connections your application needs.

Query Optimization Parameters

random_page_cost

Cost of random disk I/O:

Text
random_page_cost = 1.1

Recommendation: 1.1 for SSD storage (default is 4.0 for spinning disks)

effective_io_concurrency

Concurrent I/O operations:

Text
effective_io_concurrency = 200

Recommendation: 200 for NVMe SSDs

Logging Parameters

log_statement

Log SQL statements:

Text
log_statement = 'none'  # none, ddl, mod, all

Options:

  • none: Don't log statements (production default)
  • ddl: Log CREATE, ALTER, DROP statements
  • mod: Log DDL + INSERT, UPDATE, DELETE
  • all: Log all statements (development only)

log_min_duration_statement

Log slow queries:

Text
log_min_duration_statement = 1000  # milliseconds

Recommendation: 1000-5000ms for production. -1 disables slow-statement logging.

log_line_prefix

Format for log lines:

Text
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '

log_error_verbosity and log_min_error_statement

PostgreSQL still logs the text of a failing statement even when log_statement is none, unless log_min_error_statement is raised (for example to panic). log_error_verbosity = terse drops the DETAIL line from error log entries, which can otherwise contain column values, for example Key (email)=(…) already exists. Both are set through a custom parameter group — they are not available in the instance's Parameters tab.

WAL and Archiving Parameters

Managed PostgreSQL lets you tune selected write-ahead log (WAL) and archiving settings in custom parameter groups or per-instance configuration through the API:

  • archive_timeout: 60 seconds to 1 hour, inclusive. The default is 5 minutes; set 60s to switch partly filled, active WAL segments for archiving at least once a minute.
  • checkpoint_timeout: 30 seconds to 1 day.
  • max_wal_size and min_wal_size: At least 32MB.
  • wal_keep_size: 0 or a non-negative size.
  • max_slot_wal_keep_size: -1 for no limit, or a non-negative size such as 512MB or 2GB.
  • wal_compression: on, off, pglz, lz4, or zstd.
  • synchronous_commit: on, off, local, remote_write, or remote_apply.

PostgreSQL unit spelling is case-sensitive. Use values such as 60s, 5min, 1h, 512MB, or 2GB. For wal_compression and synchronous_commit, PostgreSQL's boolean spellings such as true, false, yes, no, 1, and 0 are also accepted.

Duration units are us, ms, s, min, h, and d; size units are B, kB, MB, GB, and TB. Numbers without units use seconds for the two duration settings and megabytes for the four size settings. The bounds above apply after unit conversion.

Some WAL, recovery, and platform safety settings are managed by DanubeData and ignored if present in a parameter group or per-instance configuration:

  • wal_level
  • wal_log_hints
  • full_page_writes
  • huge_pages
  • archive_mode
  • archive_command
  • restore_command
  • wal_sender_timeout
  • wal_receiver_timeout
  • tcp_user_timeout

The platform keeps wal_level at logical to support logical replication. Values such as wal_level = replica inherited from an existing group are ignored.

Plan-sized parameters (shared_buffers, effective_cache_size, max_connections, max_parallel_workers, and max_parallel_workers_per_gather), operator-managed paths and recovery settings, log collection destinations and rotation, TLS certificates, ciphers and the maximum TLS version, and compute_query_id are also platform-managed. Customer overrides are ignored.

ssl_min_protocol_version

The oldest TLS version a client may connect with. Managed PostgreSQL defaults to TLSv1.3. Set it to TLSv1.2 if a client cannot negotiate TLS 1.3 and fails the handshake before authentication with an error such as bad protocol version or unsupported protocol; Prisma's query engine on macOS and Windows is a common example. Clients that support TLS 1.3 keep using it either way. Older versions are not available. The change applies with a configuration reload, without a restart, from the instance's Parameters tab or a custom parameter group.

Replication Connections

max_wal_senders

Maximum concurrent replication connections:

Text
max_wal_senders = 10

Recommendation: Number of replicas + 2

Common MySQL/MariaDB Parameters

Performance Parameters

innodb_buffer_pool_size

InnoDB buffer pool size:

Text
innodb_buffer_pool_size = 8589934592  # 8 GB in bytes

Recommendations:

  • Dedicated database: 70-80% of RAM
  • Shared server: 50-60% of RAM

max_connections

Maximum concurrent connections:

Text
max_connections = 250

Recommendations:

  • Small instances: 100-250
  • Medium instances: 250-500
  • Large instances: 500-1000

query_cache_size

Query cache size (MySQL 5.7, MariaDB):

Text
query_cache_size = 268435456  # 256 MB

Note: Query cache is removed in MySQL 8.0+

tmp_table_size

Maximum size of internal in-memory tables:

Text
tmp_table_size = 67108864  # 64 MB

max_heap_table_size

Maximum size for MEMORY tables:

Text
max_heap_table_size = 67108864  # 64 MB

Recommendation: Set equal to tmp_table_size

InnoDB Parameters

innodb_log_file_size

InnoDB redo log file size:

Text
innodb_log_file_size = 512M

Recommendations:

  • Light writes: 256-512 MB
  • Heavy writes: 1-2 GB
  • Very heavy writes: 4 GB

innodb_flush_log_at_trx_commit

InnoDB log flush behavior:

Text
innodb_flush_log_at_trx_commit = 1

Options:

  • 0: Write and flush once per second (fastest, least safe)
  • 1: Write and flush at each commit (safest, default)
  • 2: Write at commit, flush once per second (compromise)

innodb_file_per_table

Separate file for each table:

Text
innodb_file_per_table = ON

Recommendation: Always ON (default in modern versions)

Binary Log Parameters

log_bin

Enable binary logging:

Text
log_bin = ON

Note: Required for replication and point-in-time recovery

binlog_format

Binary log format:

Text
binlog_format = ROW

Options:

  • ROW: Most reliable for replication (recommended)
  • STATEMENT: Smaller logs but less reliable
  • MIXED: Automatic selection

expire_logs_days

Binary log retention:

Text
expire_logs_days = 7

Recommendation: 7-14 days

Replication Parameters

server_id

Unique server identifier:

Text
server_id = 1

Note: Automatically configured for managed databases

read_only

Make replica read-only:

Text
read_only = ON

Note: Automatically set on replicas

Parameter Validation

When Parameter Group Changes Apply

For PostgreSQL, the dashboard and API validate the WAL and archiving settings listed above when you save a parameter group or database configuration. Other PostgreSQL settings may still be accepted by the parameter group editor; if a value is invalid, PostgreSQL or the database operator may reject it when the database is updated.

When you save a PostgreSQL parameter group, an update is queued for each running managed PostgreSQL database using it only if every changed setting can apply on reload. PostgreSQL reloads these settings without a restart. Stopped databases are skipped and are not started by a group save.

If any changed setting needs a restart, or if a setting is unknown, all changes from that save wait for each database's next update. The save confirmation lists the settings requiring a restart. Changes to platform-managed parameters are ignored.

For MySQL and MariaDB, parameter group changes apply the next time each database is updated.

The update API reports propagation.applied_to (the number of running databases queued for an update) and propagation.deferred_parameters (the changed settings requiring a restart).

Parameter Limits

The PostgreSQL WAL and archiving bounds above are validated when saving a parameter group or per-instance configuration. Invalid values are rejected with a validation error. Other engine-specific values are not covered by this validation; check your engine version's documentation before changing them.

Monitoring Parameter Impact

Performance Metrics

After changing parameters, monitor:

  • Query Performance: Check slow query log
  • CPU Usage: Watch for increased CPU usage
  • Memory Usage: Ensure no OOM issues
  • Connection Count: Verify connection limits are adequate
  • Cache Hit Rate: Monitor buffer cache effectiveness

PostgreSQL Monitoring

SQL
-- Buffer cache hit rate (should be > 99%)
SELECT 
    sum(blks_hit)::float / (sum(blks_hit) + sum(blks_read)) as cache_hit_ratio
FROM pg_stat_database;

-- Current connections
SELECT count(*) FROM pg_stat_activity;

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

MySQL/MariaDB Monitoring

SQL
-- Buffer pool hit rate (should be > 99%)
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';

-- Current connections
SHOW STATUS LIKE 'Threads_connected';

-- Max connections reached
SHOW STATUS LIKE 'Max_used_connections';

-- Temporary tables created on disk
SHOW GLOBAL STATUS LIKE 'Created_tmp_disk_tables';

Use Case Examples

OLTP Workload (High Concurrency)

INI
# PostgreSQL
shared_buffers = 8GB
work_mem = 32MB
max_connections = 500
effective_cache_size = 24GB
random_page_cost = 1.1
INI
# MySQL/MariaDB
innodb_buffer_pool_size = 8G
max_connections = 500
innodb_flush_log_at_trx_commit = 1
tmp_table_size = 64M
max_heap_table_size = 64M

Analytics Workload (Complex Queries)

INI
# PostgreSQL
shared_buffers = 16GB
work_mem = 256MB
max_connections = 100
effective_cache_size = 48GB
random_page_cost = 1.1
INI
# MySQL/MariaDB
innodb_buffer_pool_size = 32G
max_connections = 100
tmp_table_size = 512M
max_heap_table_size = 512M
sort_buffer_size = 8M
read_rnd_buffer_size = 8M

Development/Testing

INI
# PostgreSQL
log_statement = 'all'
log_min_duration_statement = 0
log_connections = on
log_disconnections = on
INI
# MySQL/MariaDB
general_log = ON
slow_query_log = ON
long_query_time = 0
log_queries_not_using_indexes = ON

Write-Heavy Workload

INI
# PostgreSQL
shared_buffers = 12GB
wal_buffers = 16MB
checkpoint_completion_target = 0.9
max_wal_size = 4GB
INI
# MySQL/MariaDB
innodb_buffer_pool_size = 16G
innodb_log_file_size = 2G
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT

Best Practices

Parameter Tuning

  1. Start with Defaults: Default parameters work well for most workloads
  2. Change One at a Time: Modify one parameter, test, measure impact
  3. Monitor After Changes: Watch performance metrics for 24-48 hours
  4. Document Changes: Keep track of what and why you changed
  5. Test in Staging: Try parameter changes in non-production first
  6. Avoid Extremes: Don't max out all parameters
  7. Consider Hardware: Parameters should match instance resources

Common Mistakes to Avoid

  • Over-allocating Memory: Don't allocate 100% of RAM to database
  • Too Many Connections: Use connection pooling instead of increasing limits
  • Disabling Logging: Keep essential logging for troubleshooting
  • Unsafe Settings: Don't sacrifice durability for marginal performance gains
  • Ignoring Defaults: Defaults are tuned; only change when necessary

Performance Optimization Process

  1. Identify Bottleneck: Use monitoring to find actual bottleneck
  2. Research Parameter: Understand what parameter controls bottleneck
  3. Calculate Value: Determine appropriate value based on workload
  4. Test Change: Apply in staging environment
  5. Measure Impact: Compare before/after metrics
  6. Deploy to Production: Apply if improvement is significant
  7. Monitor Continuously: Watch for unexpected side effects

Troubleshooting

Database Won't Start After Parameter Change

Cause: Invalid parameter value

Solution:

  1. Revert to previous parameter group
  2. Review parameter value
  3. Check parameter limits for your instance size
  4. Apply corrected parameter group

Performance Degraded After Change

Cause: Inappropriate parameter value

Solution:

  1. Revert to previous parameter group
  2. Review workload characteristics
  3. Research appropriate values
  4. Test with smaller incremental changes

Parameter Change Not Taking Effect

Cause: Parameter requires restart

Solution:

  1. Check if parameter is static (requires restart)
  2. Restart database from dashboard
  3. Verify parameter value after restart