Cómo optimizar PostgreSQL 16 con postgresql.conf en Ubuntu

PostgreSQL viene configurado de fábrica para arrancar en máquinas muy modestas: 128 MB de shared_buffers, 4 MB de work_mem y costes del planificador pensados para discos mecánicos. En este tutorial ajustarás PostgreSQL 16 en Ubuntu 24.04 para aprovechar la memoria y el almacenamiento SSD de tu servidor, en un archivo de configuración propio, y activarás pg_stat_statements para localizar las consultas que realmente consumen tiempo.

Requisitos previos

Para seguir esta guía necesitas:

• Un servidor con Ubuntu 24.04 LTS, por ejemplo un VPS de CubePath, con almacenamiento SSD o NVMe.
• PostgreSQL 16 instalado desde los repositorios de Ubuntu (sudo apt install postgresql).
• Un usuario no root con privilegios sudo.
• Una carga de trabajo real o representativa con la que medir.

Los valores de ejemplo corresponden a un servidor con 8 GB de RAM y 4 vCPU dedicado a PostgreSQL. Si el servidor comparte memoria con una aplicación, calcula los porcentajes sobre la RAM que quede libre para la base de datos.

Paso 1: Localizar la configuración y medir el punto de partida

Pregunta a PostgreSQL qué archivo está usando:

sudo -u postgres psql -c "SHOW config_file;"

               config_file
-----------------------------------------
 /etc/postgresql/16/main/postgresql.conf

Anota el estado inicial. La tasa de aciertos de caché indica qué proporción de bloques se leyó de memoria en lugar de disco:

sudo -u postgres psql -c "SELECT datname, round(100.0 * blks_hit / nullif(blks_hit + blks_read, 0), 2) AS cache_hit_pct, temp_files, pg_size_pretty(temp_bytes) AS temp_bytes FROM pg_stat_database WHERE datname NOT LIKE 'template%';"

 datname  | cache_hit_pct | temp_files | temp_bytes
----------+---------------+------------+------------
 postgres |         99.91 |          0 | 0 bytes
 miapp    |         97.40 |       1283 | 6204 MB

En una base de datos OLTP bien dimensionada, cache_hit_pct debería estar por encima del 99 %. Muchos temp_files significan que las ordenaciones y los hashes no caben en work_mem y se escriben en disco.

Comprueba también cuántos checkpoints se lanzan por tiempo y cuántos se fuerzan por volumen de WAL:

sudo -u postgres psql -c "SELECT checkpoints_timed, checkpoints_req FROM pg_stat_bgwriter;"

 checkpoints_timed | checkpoints_req
-------------------+-----------------
               412 |             389

Si checkpoints_req es alto en comparación con checkpoints_timed, max_wal_size se queda corto. En PostgreSQL 17 y posteriores estas columnas están en la vista pg_stat_checkpointer como num_timed y num_requested.

Paso 2: Crear un archivo de ajustes propio

En Ubuntu, postgresql.conf incluye al final todos los archivos .conf del directorio conf.d, y lo que se define allí prevalece sobre lo anterior. Poner tus ajustes en un archivo aparte te permite ver de un vistazo qué has cambiado y evita conflictos al actualizar el paquete.

Comprueba que la inclusión está activa:

grep -n "^include_dir" /etc/postgresql/16/main/postgresql.conf

832:include_dir = 'conf.d'

Crea el archivo:

sudo nano /etc/postgresql/16/main/conf.d/tuning.conf

Lo irás rellenando en los pasos siguientes.

Nota

los parámetros cambiados con ALTER SYSTEM se guardan en postgresql.auto.conf y prevalecen sobre este archivo. Si un ajuste no se aplica, comprueba de dónde sale con SELECT name, setting, sourcefile FROM pg_settings WHERE name = 'shared_buffers';.

Paso 3: Configurar la memoria

Añade a tuning.conf:

# Memoria
shared_buffers = 2GB
effective_cache_size = 6GB
work_mem = 16MB
maintenance_work_mem = 512MB
huge_pages = try

Qué hace cada parámetro y cómo dimensionarlo:

Con work_mem hay que ser prudente: 100 conexiones que ejecuten a la vez consultas con tres ordenaciones pueden usar 100 × 3 × 16 MB = 4,8 GB. Si solo unas pocas consultas de informes necesitan más, súbelo para ellas y no de forma global:

SET work_mem = '256MB';

huge_pages = try usa páginas grandes de memoria si el sistema las tiene reservadas y, si no, arranca con normalidad.

Paso 4: Ajustar el WAL y los checkpoints

Cada checkpoint escribe en disco todas las páginas modificadas. Si ocurren muy a menudo, generan picos de E/S y aumentan el volumen de WAL, porque tras cada checkpoint la primera modificación de cada página se escribe completa en el WAL. Añade:

# WAL y checkpoints
max_wal_size = 4GB
min_wal_size = 1GB
checkpoint_timeout = 15min
checkpoint_completion_target = 0.9
wal_compression = on

• max_wal_size: volumen de WAL que se puede acumular antes de forzar un checkpoint. Por defecto es 1 GB; súbelo hasta que checkpoints_req sea minoritario.
• checkpoint_timeout: intervalo máximo entre checkpoints. 15 minutos reduce la E/S, a cambio de una recuperación algo más larga tras un corte.
• checkpoint_completion_target = 0.9: reparte las escrituras del checkpoint a lo largo del 90 % del intervalo. Es el valor por defecto desde PostgreSQL 14; se incluye para que quede explícito.
• wal_compression: comprime las páginas completas del WAL. Reduce el volumen escrito a cambio de algo de CPU.

No cambies synchronous_commit ni fsync para ganar velocidad: desactivarlos puede suponer perder transacciones confirmadas o corromper la base de datos si el servidor se cae.

Paso 5: Adaptar el planificador a almacenamiento SSD

Los costes por defecto del planificador asumen que una lectura aleatoria es cuatro veces más cara que una secuencial, algo cierto en discos mecánicos pero no en SSD o NVMe. Con esos valores, PostgreSQL tiende a descartar índices perfectamente válidos. Añade:

# Planificador (SSD/NVMe)
random_page_cost = 1.1
effective_io_concurrency = 200

effective_io_concurrency indica cuántas lecturas concurrentes puede atender el almacenamiento; PostgreSQL 16 lo usa en los bitmap heap scans.

Paso 6: Revisar las conexiones

Cada conexión de PostgreSQL es un proceso con su propia memoria. Un max_connections muy alto no aumenta el rendimiento: con más conexiones activas que núcleos, las consultas compiten entre sí.

# Conexiones
max_connections = 100

Si tu aplicación abre muchas conexiones (por ejemplo, varios workers de PHP-FPM o de Node.js), no subas max_connections por encima de unos cientos: coloca delante un pooler como PgBouncer, que reparte cientos de clientes entre unas pocas decenas de conexiones reales.

Paso 7: Afinar autovacuum

Autovacuum elimina las versiones antiguas de las filas que dejan UPDATE y DELETE, y actualiza las estadísticas del planificador. Nunca lo desactives. En tablas grandes, los umbrales por defecto (vaciar cuando cambia el 20 % de la tabla) hacen que se ejecute demasiado tarde y acumule mucho trabajo de una vez. Añade:

# Autovacuum
autovacuum_max_workers = 4
autovacuum_vacuum_scale_factor = 0.05
autovacuum_analyze_scale_factor = 0.02
autovacuum_vacuum_cost_limit = 1000
log_autovacuum_min_duration = 1s

• Los scale_factor bajan del 20 % y 10 % al 5 % y 2 %, así las tablas grandes se procesan antes y en tandas más pequeñas.
• autovacuum_vacuum_cost_limit permite a autovacuum trabajar más rápido; en SSD el valor por defecto es muy conservador.
• log_autovacuum_min_duration registra las ejecuciones que tardan más de un segundo, útil para detectar tablas problemáticas.

Para ver qué tablas acumulan más filas muertas:

sudo -u postgres psql -d miapp -c "SELECT relname, n_live_tup, n_dead_tup, last_autovacuum FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 5;"

Sustituye miapp por el nombre de tu base de datos.

Paso 8: Activar el registro de consultas lentas y pg_stat_statements

pg_stat_statements acumula estadísticas de todas las consultas (número de llamadas, tiempo total y medio), agrupadas por forma. En Ubuntu viene incluida en el paquete del servidor. Añade:

# Registro y estadísticas
shared_preload_libraries = 'pg_stat_statements'
log_min_duration_statement = 1000
log_temp_files = 0
log_lock_waits = on

• log_min_duration_statement = 1000: registra las consultas que tarden más de 1000 ms en /var/log/postgresql/postgresql-16-main.log.
• log_temp_files = 0: registra cada archivo temporal creado, lo que te dice qué consultas necesitan más work_mem.
• log_lock_waits: registra las esperas por bloqueos que superen deadlock_timeout (1 segundo por defecto).

Paso 9: Aplicar los cambios y verificar

El archivo completo para el servidor de ejemplo queda así:

# Memoria
shared_buffers = 2GB
effective_cache_size = 6GB
work_mem = 16MB
maintenance_work_mem = 512MB
huge_pages = try

# WAL y checkpoints
max_wal_size = 4GB
min_wal_size = 1GB
checkpoint_timeout = 15min
checkpoint_completion_target = 0.9
wal_compression = on

# Planificador (SSD/NVMe)
random_page_cost = 1.1
effective_io_concurrency = 200

# Conexiones
max_connections = 100

# Autovacuum
autovacuum_max_workers = 4
autovacuum_vacuum_scale_factor = 0.05
autovacuum_analyze_scale_factor = 0.02
autovacuum_vacuum_cost_limit = 1000
log_autovacuum_min_duration = 1s

# Registro y estadísticas
shared_preload_libraries = 'pg_stat_statements'
log_min_duration_statement = 1000
log_temp_files = 0
log_lock_waits = on

Algunos parámetros (shared_buffers, max_connections, huge_pages, shared_preload_libraries, autovacuum_max_workers) solo se aplican al reiniciar. Reinicia el clúster:

sudo systemctl restart postgresql@16-main
sudo systemctl status postgresql@16-main --no-pager

Si el servicio no arranca, el motivo aparece en el log:

sudo tail -n 30 /var/log/postgresql/postgresql-16-main.log

Comprueba que no queda ningún parámetro pendiente de reinicio y que los valores se han aplicado:

sudo -u postgres psql -c "SELECT name, setting, unit, pending_restart FROM pg_settings WHERE sourcefile LIKE '%tuning.conf' ORDER BY name;"

              name               | setting | unit | pending_restart
---------------------------------+---------+------+-----------------
 autovacuum_analyze_scale_factor | 0.02    |      | f
 ...
 shared_buffers                  | 262144  | 8kB  | f
 work_mem                        | 16384   | kB   | f

shared_buffers se muestra en bloques de 8 kB: 262144 × 8 kB = 2 GB.

Para cambios posteriores que no requieren reinicio (por ejemplo, work_mem o los de autovacuum), basta con recargar la configuración:

sudo systemctl reload postgresql@16-main

Paso 10: Encontrar las consultas más costosas

Crea la extensión en la base de datos de tu aplicación:

sudo -u postgres psql -d miapp -c "CREATE EXTENSION IF NOT EXISTS pg_stat_statements;"

Tras unas horas de tráfico real, lista las consultas que más tiempo total consumen:

sudo -u postgres psql -d miapp -c "SELECT calls, round(total_exec_time) AS total_ms, round(mean_exec_time, 1) AS mean_ms, left(query, 70) AS query FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 5;"

 calls  | total_ms | mean_ms |                                 query
--------+----------+---------+------------------------------------------------------------------------
 184220 |  2210640 |    12.0 | SELECT * FROM orders WHERE customer_id = $1 ORDER BY created_at DESC
   1440 |   403200 |   280.0 | SELECT date_trunc($1, created_at), sum(total) FROM orders GROUP BY 1

Analiza la primera con EXPLAIN (ANALYZE, BUFFERS) para ver dónde gasta el tiempo:

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE customer_id = 42 ORDER BY created_at DESC;

Si el plan muestra un Seq Scan sobre una tabla grande, un índice sobre las columnas del filtro y la ordenación suele resolverlo:

CREATE INDEX CONCURRENTLY idx_orders_customer_created ON orders (customer_id, created_at DESC);

CONCURRENTLY crea el índice sin bloquear las escrituras en la tabla. Después, vuelve a ejecutar el EXPLAIN y confirma que aparece Index Scan using idx_orders_customer_created.

Pasados unos días, repite las consultas del paso 1: la tasa de aciertos de caché debería haber subido, y temp_files y checkpoints_req deberían crecer mucho más despacio.

Solución de problemas

PostgreSQL no arranca con could not map anonymous shared memory. shared_buffers es demasiado grande para la RAM disponible. Redúcelo y vuelve a reiniciar.

Un parámetro no cambia tras reiniciar. Probablemente está definido en postgresql.auto.conf por un ALTER SYSTEM anterior. La consulta de sourcefile del paso 2 te dice de dónde sale; elimínalo con ALTER SYSTEM RESET nombre_parametro;.

El servidor usa swap o el proceso muere por falta de memoria. Revisa work_mem multiplicado por las conexiones activas y reduce work_mem, max_connections o ambos.

ERROR: pg_stat_statements must be loaded via shared_preload_libraries. No has reiniciado después de añadir la librería, o hay otra línea shared_preload_libraries en postgresql.auto.conf que la sobrescribe.

Conclusión

PostgreSQL ya usa la memoria de tu servidor, planifica las consultas teniendo en cuenta que el almacenamiento es SSD, espacia los checkpoints y mantiene las tablas con un autovacuum más activo, y pg_stat_statements te indica qué consultas optimizar. Como siguientes pasos, puedes colocar PgBouncer delante si tienes muchas conexiones, medir el impacto de los cambios con pgbench en un entorno de pruebas, y configurar copias de seguridad continuas con archivado de WAL.