Latitude.sh allows you to customize PostgreSQL and connection pooler parameters to optimize your database for your specific workload. You can configure these parameters from the Parameters tab in your database dashboard.
PostgreSQL parameters
The following PostgreSQL parameters can be customized based on your database plan.
Connection parameters
| Parameter | Description |
|---|
| Max Connections | Maximum number of concurrent database connections. Higher values allow more simultaneous clients but consume more memory. |
| TCP Keepalives Idle | Seconds of inactivity before sending a TCP keepalive. 0 to use OS default. |
| TCP Keepalives Interval | Seconds between TCP keepalive retransmits. 0 to use OS default. |
| TCP Keepalives Count | Maximum TCP keepalive retransmits before considering connection dead. |
Memory parameters
| Parameter | Description |
|---|
| Shared Buffers | Memory allocated for PostgreSQL’s shared buffer cache. This is where frequently accessed data is cached. Recommended: 25% of available RAM. |
| Work Memory | Memory used per operation for sorting and hash tables. Be careful with high values as this is multiplied by concurrent operations. |
| Maintenance Work Memory | Memory allocated for maintenance operations like VACUUM, CREATE INDEX, and ALTER TABLE. |
| Effective Cache Size | The planner’s estimate of available memory for disk caching. This helps PostgreSQL choose better query plans. |
| Temp File Limit | Maximum disk space for temporary files per session. -1 for unlimited. |
Timeout parameters
| Parameter | Description |
|---|
| Idle Transaction Timeout | Time before idle transactions are automatically terminated. Prevents connections from being held indefinitely. |
| Idle Session Timeout | Time before idle sessions are terminated. 0 to disable. |
| Statement Timeout | Maximum time a query can run before being cancelled. Useful for preventing runaway queries. Set to 0 to disable. |
| Lock Timeout | Maximum time to wait for a lock. Prevents queries from waiting indefinitely for locked resources. Set to 0 to disable. |
WAL & Checkpoint parameters
| Parameter | Description |
|---|
| Max WAL Size | Maximum size the WAL can grow to between checkpoints. |
| Min WAL Size | Minimum size of WAL files to retain. |
| WAL Buffers | Shared memory used for WAL data not yet written to disk. |
| Checkpoint Timeout | Maximum time between automatic WAL checkpoints. |
| Checkpoint Completion Target | Target completion fraction of checkpoint. Higher values spread I/O over time. |
Autovacuum parameters
| Parameter | Description |
|---|
| Vacuum Scale Factor | Fraction of table size to add to vacuum threshold. |
| Analyze Scale Factor | Fraction of table size to add to analyze threshold. |
| Autovacuum Naptime | Time between autovacuum runs. |
Connection pooler (PgBouncer)
PgBouncer is a connection pooler that sits between your application and PostgreSQL. It efficiently manages database connections, reducing overhead and improving performance.
Pool settings
| Parameter | Description |
|---|
| Pool Mode | How connections are assigned to clients. Transaction mode (recommended) releases connections after each transaction. Session mode keeps connections for the session lifetime. Statement mode releases after each statement. |
| Default Pool Size | Number of server connections maintained per user/database pair. |
| Min Pool Size | Minimum server connections to keep open per pool. |
| Reserve Pool Size | Additional connections for reserve pool when default is exhausted. |
Connection limits
| Parameter | Description |
|---|
| Max Client Connections | Maximum number of client connections the pooler accepts. |
| Max DB Connections | Maximum connections allowed per database. |
Timeouts
| Parameter | Description |
|---|
| Query Timeout (s) | Maximum time a query can run. 0 to disable. |
| Query Wait Timeout (s) | Maximum time a client can wait for a server connection. |
| Server Idle Timeout (s) | Time a server connection can be idle before being closed. |
| Client Idle Timeout (s) | Time a client connection can be idle. 0 to disable. |
| Server Connect Timeout (s) | Maximum time to establish a server connection. |
Recommended defaults
Default values are optimized for each database plan to provide stable operation within your plan’s resources. You have full control over parameter values, but we recommend staying close to these defaults unless you have specific requirements.
| Parameter | Default |
|---|
| Max Connections | 100 |
| Shared Buffers | 128MB |
| Work Memory | 16MB |
| Maintenance Work Memory | 64MB |
| Effective Cache Size | 1GB |
| Pool Size | 20 |
| Max Client Conn | 100 |
| Parameter | Default |
|---|
| Max Connections | 200 |
| Shared Buffers | 256MB |
| Work Memory | 32MB |
| Maintenance Work Memory | 128MB |
| Effective Cache Size | 2GB |
| Pool Size | 30 |
| Max Client Conn | 200 |
| Parameter | Default |
|---|
| Max Connections | 1000 |
| Shared Buffers | 512MB |
| Work Memory | 128MB |
| Maintenance Work Memory | 256MB |
| Effective Cache Size | 4GB |
| Pool Size | 50 |
| Max Client Conn | 500 |
Values significantly outside recommended ranges will be highlighted in the
dashboard. While you can set any value, extreme settings may impact database
stability or performance.
Configuring parameters
- Go to Databases and select your database.
- Click the Parameters tab.
- Click Manage next to PostgreSQL or Pooler parameters.
- Click on any parameter row to expand it and view the input field and allowed range.
- Adjust the value as needed. A warning indicator appears if the value is outside the recommended range.
- Click Restore defaults to reset all parameters to plan defaults, or Apply to save your changes.
Parameter changes trigger a rolling restart of your database pods. This
ensures the new configuration is applied safely with minimal downtime.
Best practices
- Start with defaults: The default values are tuned for typical workloads. Only adjust if you have specific requirements.
- Monitor after changes: Watch your database metrics after making parameter changes to ensure the desired effect.
- Work Memory caution: High
work_mem values can cause memory issues since this is allocated per operation. Calculate: work_mem × max_connections × average_operations.
- Use connection pooling: For applications with many short-lived connections, the connection pooler significantly reduces overhead.
- Transaction mode: Use transaction pool mode unless your application requires session-level features like prepared statements or advisory locks.
