Postgres configuration parameters are settings that affect the behavior of the database. These settings are typically adjusted in postgresql.conf, and play a role in optimizing performance, reliability, and resource utilization.
There are over 300 parameters that can be set. In cloud deployments, default values for these parameters are typically configured for general use cases, but may not be optimal for specific workloads. For example, a high-read use case like social media or e-commerce may benefit from different settings than a high-read and high-write use case like a messaging system. Users can modify these settings to better suit their specific needs.
While cloud providers like Ubicloud, Google Cloud SQL, Timescale, and Amazon RDS all offer Postgres as a managed service, they come with some differences in pre-configured settings. In this article, we highlight some of the significant differences in default Postgres parameter settings among these major cloud providers. We explain the purpose of each parameter and the implications of these differences for users.
1. vacuum_cost_page_miss
- Ubicloud, GCP CloudSQL, Timescale: 2
- AWS RDS: 5
- Importance: This parameter controls the cost assigned to vacuuming a page that is not in cache. A higher value means that vacuum operations will be more expensive and thus may run less frequently, potentially leading to longer table bloat but reduced I/O load.
- Implication: AWS RDS is set to 5, leading to less frequent vacuum operations, potentially reducing I/O but risking more table bloat over time.
2. max_prepared_transactions
- Ubicloud, GCP CloudSQL, AWS RDS: 0
- Timescale: 100
- Importance: This setting controls the number of transactions that can be prepared for two-phase commit. It is crucial for applications that use distributed transactions.
- Implication: Timescale is configured to support prepared transactions, which is essential for complex, distributed applications. Other providers set this to 0, indicating no support for prepared transactions, which could limit certain advanced transactional capabilities.
3. hot_standby
- Ubicloud, GCP CloudSQL, Timescale: On
- AWS RDS: Off
- Importance: This parameter allows the server to run in read-only mode while receiving changes from the primary server. It is crucial for high availability and load balancing.
- Implication: AWS RDS having this off means it cannot support hot standby without a restart to change the parameter first, potentially reducing its utility for read-heavy workloads that benefit from read replicas.
4. max_wal_senders
- Ubicloud, GCP CloudSQL, Timescale: 10
- AWS RDS: 35
- Importance: This setting determines the number of simultaneously running WAL sender processes, which are used for replication.
- Implication: AWS RDS supports a higher number of WAL senders, enabling more replicas or higher replication throughput, which is beneficial for scaling out read operations.
5. log_rotation_size
- Ubicloud, Timescale, AWS RDS: 10240
- GCP CloudSQL: 0
- Importance: This parameter defines the maximum size of log files before they are rotated. Proper log rotation is crucial for managing disk space and ensuring log files are manageable.
- Implication: GCP CloudSQL set to 0 may indicate no log rotation based on size, potentially leading to very large log files, which can be difficult to manage and analyze.
6. autovacuum_freeze_max_age
- Ubicloud, GCP CloudSQL, AWS RDS: 200000000
- Timescale: 1000000000
- Importance: This parameter sets the maximum age of a table row before it must be vacuumed to prevent transaction ID wraparound.
- Implication: Timescale has a higher threshold, potentially delaying necessary vacuum operations and risking transaction ID wraparound if not managed correctly, but reducing the frequency of autovacuum operations.
7. logging_collector
- Ubicloud, AWS RDS: On
- GCP CloudSQL, Timescale: Off
- Importance: This parameter enables the collection of logs, which is crucial for diagnosing issues and auditing.
- Implication: GCP CloudSQL and Timescale having this off might limit logging capabilities, making troubleshooting and auditing more challenging.
8. max_wal_size
- Ubicloud: 5120
- GCP CloudSQL: 1504
- Timescale: 3072
- AWS RDS: 6144
- Importance: This parameter sets the maximum size of WAL files before a checkpoint is forced. It affects database performance and recovery time.
- Implication: AWS RDS with the largest max WAL size can handle more transactions between checkpoints, potentially improving performance but increasing the time required for recovery. On the other hand, smaller values (like in GCP CloudSQL) may be more suitable for read-heavy workloads or systems with limited storage, as they reduce the disk space required for WAL files.
9. autovacuum_work_mem
- Ubicloud, GCP CloudSQL: -1
- Timescale: 16384
- AWS RDS: 238967
- Importance: This setting defines the memory available for autovacuum operations, influencing their efficiency and performance.
- Implication: AWS RDS with a higher value means more memory is allocated for vacuuming, which can speed up vacuum operations and reduce their impact on performance.
10. track_activity_query_size
- Ubicloud, GCP CloudSQL, Timescale: 1024
- AWS RDS: 4096
- Importance: This parameter controls the size of the query string tracked in
pg_stat_activity. - Implication: AWS RDS allows longer queries to be tracked, which can be useful for debugging and performance monitoring.
11. wal_buffers
- Ubicloud, GCP CloudSQL, Timescale: 2048
- AWS RDS: 7467
- Importance: This parameter sets the amount of shared memory used for WAL data. Larger buffers can improve performance by reducing the frequency of writes to disk.
- Implication: AWS RDS with a larger buffer size can improve performance for write-intensive applications by reducing the number of I/O operations.