Tune key PostgreSQL settings like shared_buffers (25% of RAM), effective_cache_size (50–75% of RAM), work_mem (start at 16MB), maintenance_work_mem (e.g., 1GB), wal_buffers (16MB), max_wal_size (2GB), checkpoint_completion_target (0.9), and random_page_cost (1.1 for SSDs), then reload PostgreSQL. 2. Optimize the Linux system by using XFS or ext4 with noatime,barrier=1, setting the I/O scheduler to deadline or none for SSDs, tuning vm.dirty_ratio (15) and vm.dirty_background_ratio (5), and optionally enabling huge pages. 3. Maintain database health by enabling autovacuum with adjusted scale factors, indexing frequently queried columns without over-indexing, updating statistics, reindexing bloated tables, and partitioning large tables. 4. Monitor performance using pg_stat_statements to identify slow queries, enable slow query logging (log_min_duration_statement = 1000), and use external tools like pgBadger or Grafana for visualization, then iterate based on observed workload to achieve optimal performance.

Optimizing a PostgreSQL database on a Linux server isn't about one magic setting—it's a combination of configuration tuning, system-level adjustments, and good database design. Here’s a practical guide focused on real-world improvements you can implement today.

1. Tune Key PostgreSQL Configuration Settings

The main configuration file, usually postgresql.conf, holds the most impactful tuning knobs. Focus on these parameters:

• shared_buffers: This sets how much memory PostgreSQL uses for caching data. A common recommendation is 25% of total system RAM. For a 16GB server, set:

  

  shared_buffers = 4GB

• effective_cache_size: Tells the query planner how much memory is available for disk caching (by OS and PostgreSQL). It doesn’t allocate memory but influences query plans. Set it to about 50–75% of total RAM:

  effective_cache_size = 12GB

• work_mem: Controls memory per sorting or hashing operation. Too high can cause memory overuse with many concurrent queries. Start with:

  

  work_mem = 16MB

  Increase only if you have complex sorts and sufficient RAM.

• maintenance_work_mem: Used for vacuuming, index creation, etc. Can be much higher:

  maintenance_work_mem = 1GB

• wal_buffers: Should typically be 1/32nd of shared_buffers, but 16MB is often sufficient:

  wal_buffers = 16MB

• checkpoint_segments / max_wal_size (depends on version): Increase to reduce I/O spikes from frequent checkpoints:

  max_wal_size = 2GB

• checkpoint_completion_target: Spread checkpoint writes over time to reduce I/O bursts:

  checkpoint_completion_target = 0.9

• random_page_cost: If using SSDs, lower this to reflect faster random access:

  random_page_cost = 1.1

After changes, reload or restart PostgreSQL:

sudo systemctl reload postgresql

2. Optimize the Linux System for PostgreSQL

PostgreSQL runs on Linux, so system-level settings matter.

• File System: Use XFS or ext4 with appropriate mount options. For example:

  /dev/sdX /var/lib/postgresql xfs noatime,barrier=1 0 0

  noatime reduces disk writes; barrier=1 ensures WAL integrity.

• I/O Scheduler: For SSDs, use deadline or none (for NVMe). Check current:

  cat /sys/block/sda/queue/scheduler

  Set temporarily:

  echo deadline | sudo tee /sys/block/sda/queue/scheduler

• vm.dirty_ratio and vm.dirty_background_ratio: Control how aggressively the kernel flushes dirty pages. High values can cause I/O stalls. Tune down if you see write spikes:

  vm.dirty_ratio = 15
  vm.dirty_background_ratio = 5

  Apply via /etc/sysctl.conf and reload:

  sudo sysctl -p

• Huge Pages (optional): Can improve performance with large shared_buffers. Enable in postgresql.conf:

  huge_pages = on

  And ensure kernel supports it (vm.nr_hugepages in sysctl).

3. Maintain Database Health

Even the best config can't fix a bloated or poorly indexed database.

• Vacuum regularly: Especially autovacuum. Ensure it's enabled:

  autovacuum = on

  Tune for busy tables:

  autovacuum_vacuum_scale_factor = 0.05
  autovacuum_analyze_scale_factor = 0.02

• Index wisely: Use EXPLAIN (ANALYZE, BUFFERS) to find slow queries. Add indexes on frequently filtered or joined columns. But avoid over-indexing—each index slows down writes.

• Update statistics: Ensure ANALYZE runs regularly so the planner makes good decisions.

• Reindex occasionally: Indexes can bloat. Schedule periodic reindexing during maintenance:

  REINDEX TABLE my_large_table;

• Partition large tables: If you have tables over tens of millions of rows, consider partitioning by date or ID to improve query speed and maintenance.

4. Monitor and Iterate

Use tools to spot bottlenecks:

• pg_stat_statements: Enable to track slow queries:

  shared_preload_libraries = 'pg_stat_statements'
  pg_stat_statements.track = all

  Then:

  SELECT query, calls, total_time FROM pg_stat_statements ORDER BY total_time DESC LIMIT 10;

• Logging: Turn on slow query logging:

  log_min_duration_statement = 1000  # Log queries > 1s
  log_statement = 'none'
  log_temp_files = 0  # Log all temp file usage

• External tools: Use pgBadger, Prometheus Grafana, or pgAdmin to visualize performance over time.

Tuning PostgreSQL is iterative. Start with configuration and system settings, then refine based on actual workload. Most gains come from fixing slow queries and ensuring proper maintenance—not just cranking up memory settings.

Basically: configure wisely, maintain consistently, and monitor constantly.

The above is the detailed content of Optimizing a PostgreSQL Database on a Linux Server. For more information, please follow other related articles on the PHP Chinese website!