This section provides a high-level overview of performance tuning for PostgreSQL. It highlights areas where small changes or updates can improve efficiency and stability.

The content is aimed at administrators and developers familiar with general database concepts, but not necessarily PostgreSQL specialists.

1. PostgreSQL Version Upgrades

Upgrading PostgreSQL can improve performance without any configuration changes. For example, in version 17 there were some long-awaited improvements to B-tree enhancements (queries using indexed columns, especially SELECT ... IN (...)  and range queries, may run faster), memory management (for VACUUM and other maintenance operations), and general I/O improvements for large tables and index operations.

Important: Before upgrading, ensure that Flyway supports the PostgreSQL version you plan to use. Using an unsupported version can cause migration failures or runtime instability. Check either Installing DSpace section or Flyway's version and compatibility documentation.

2. Performance Tips from PostgreSQL 'Performance Tips'

Official PostgreSQL documentation includes tuning queries, monitoring statistics, and analyzing execution plans.

Reference: https://www.postgresql.org/docs/current/performance-tips.html

3. Configuration Tuning with pgTune

PgTune is a web-based tool that generates recommended PostgreSQL settings based on your hardware and workload. It generates a ready-to-use postgresql.conf  with parameters such as:

• shared_buffers  – memory for caching table data
• work_mem  – memory per operation for sorting and hashing
• maintenance_work_mem  – memory for indexing and VACUUM

Important: When specyfing the 'Total Memory (RAM)', leave at least 20% for the operating system.

PgTune is available at: https://pgtune.leopard.in.ua/

4. Additional Tools

• pgAdmin – a GUI to monitor server status, view query execution, and inspect performance metrics.
• pgBouncer – a lightweight connection pooler. Reduces the overhead of handling many simultaneous client connections by reusing existing connections. Can also act as a simple load balancer across multiple databases.

Notes

• Regular upgrades and monitoring are often more effective than large-scale tuning without data.
• DSpace backend upgrades can also improve database performance by optimizing queries and schema in the code. To apply these improvements, remember to run the [dspace]/bin/dspace database migrate  script after upgrading.
• Start with default settings or pgTune recommendations, then adjust based on query logs and observed performance.