Tune VPS performance: swap, PostgreSQL and the DB pool

Speed up a 16GB Ubuntu VPS that runs many Docker containers next to PostgreSQL: add swap to keep the OOM Killer away, tune PostgreSQL for more RAM and SSD storage, then raise the app's connection pool.

Updated: Sep 21, 20269 min read

PostgreSQL VPS Performance Ubuntu

On this page

Quick reference
Check the system before tuning
Add swap (no downtime)
Tune PostgreSQL (needs a restart of about 3-5 seconds)
Part 1: Add swap - keep the OOM Killer away
Why add swap?
Step 1.1 - Check the current swap
Step 1.2 - Create another 4GB of swap
Step 1.3 - Make the swap mount on reboot
Step 1.4 - Confirm the swap has grown
Part 2: Tune PostgreSQL - 2-5x faster
Why is a default PostgreSQL slow?
Config changes (for a 16GB RAM server)
Step 2.1 - Find the config file path
Step 2.2 - Back up the old config
Step 2.3 - Run the script that edits the config
Step 2.4 - Check that the config is correct
Step 2.5 - Restart PostgreSQL
Step 2.6 - Confirm PostgreSQL is healthy
Part 3: Raise the DB connection pool in the app
Why raise the pool?
Make the change (on your local machine, not the VPS)
Troubleshooting and notes
Roll back the PostgreSQL config
Remove the swap if needed
PostgreSQL does not start after the config change
Check the slow query log
Useful tools

This guide tunes an Ubuntu VPS that runs many Docker containers alongside PostgreSQL. It has three parts: add swap to avoid OOM kills, tune the PostgreSQL config for a 16GB RAM server, and raise the DB connection pool in the application. Use it when:

The VPS feels slow and swap is almost exhausted
PostgreSQL still runs on its default config (only 128MB of shared_buffers)
The app regularly hits "too many connections" errors or timeouts

Paths in this guide assume PostgreSQL 16 - change 16 to match your version. The config values are sized for a VPS with 16GB RAM + SSD.

Quick reference

If you already know what you are doing, these three blocks are all you need. Each step is explained in the sections below.

Check the system before tuning

echo "===== SYSTEM INFO =====" && \
echo "--- RAM ---" && free -h && \
echo "--- DISK ---" && df -h / && \
echo "===== POSTGRESQL =====" && \
sudo -u postgres psql -c "SHOW shared_buffers;" && \
sudo -u postgres psql -c "SHOW effective_cache_size;" && \
sudo -u postgres psql -c "SHOW work_mem;" && \
sudo -u postgres psql -c "SHOW max_connections;" && \
sudo -u postgres psql -c "SELECT count(*) as active_connections FROM pg_stat_activity;"

Add swap (no downtime)

sudo fallocate -l 4G /swapfile2 && sudo chmod 600 /swapfile2 && \
sudo mkswap /swapfile2 && sudo swapon /swapfile2 && \
echo '/swapfile2 none swap sw 0 0' | sudo tee -a /etc/fstab

Tune PostgreSQL (needs a restart of about 3-5 seconds)

PG_CONF="/etc/postgresql/16/main/postgresql.conf"
sudo cp $PG_CONF ${PG_CONF}.bak.$(date +%Y%m%d)
sudo sed -i "s/^#*shared_buffers\s*=.*/shared_buffers = 512MB/" $PG_CONF
sudo sed -i "s/^#*effective_cache_size\s*=.*/effective_cache_size = 6GB/" $PG_CONF
sudo sed -i "s/^#*work_mem\s*=.*/work_mem = 8MB/" $PG_CONF
sudo sed -i "s/^#*maintenance_work_mem\s*=.*/maintenance_work_mem = 128MB/" $PG_CONF
sudo sed -i "s/^#*wal_buffers\s*=.*/wal_buffers = 16MB/" $PG_CONF
sudo sed -i "s/^#*checkpoint_completion_target\s*=.*/checkpoint_completion_target = 0.9/" $PG_CONF
sudo sed -i "s/^#*random_page_cost\s*=.*/random_page_cost = 1.1/" $PG_CONF
sudo sed -i "s/^#*effective_io_concurrency\s*=.*/effective_io_concurrency = 200/" $PG_CONF
sudo sed -i "s/^#*log_min_duration_statement\s*=.*/log_min_duration_statement = 1000/" $PG_CONF
sudo systemctl restart postgresql

Part 1: Add swap - keep the OOM Killer away

Why add swap?

Swap is disk space the system uses as backup RAM. When physical RAM runs out, Linux moves rarely used data from RAM to swap to free up memory.

If both RAM and swap run out, Linux triggers the OOM Killer (Out of Memory Killer), which automatically kills the processes using the most RAM - quite possibly PostgreSQL or one of your Docker containers.

On a 16GB RAM VPS running about 30 Docker containers, the default 2GB of swap is not enough. Raise it to at least 6GB.

Step 1.1 - Check the current swap

echo "=== CURRENT SWAP ===" && swapon --show && free -h | grep Swap

The output shows how much swap exists and how much is in use. If used is close to total, add more right away.

Step 1.2 - Create another 4GB of swap

# Create a 4GB file to use as swap
sudo fallocate -l 4G /swapfile2

# Let only root read/write it (security)
sudo chmod 600 /swapfile2

# Format the file as swap
sudo mkswap /swapfile2

# Turn the swap on immediately (no restart needed)
sudo swapon /swapfile2

What each command does:

Command	Effect
`fallocate -l 4G`	Creates an empty 4GB file on disk
`chmod 600`	Only root can read/write the file (avoids a security hole)
`mkswap`	Marks the file as a swap area (formats it)
`swapon`	Activates it right away, no reboot needed

Step 1.3 - Make the swap mount on reboot

echo '/swapfile2 none swap sw 0 0' | sudo tee -a /etc/fstab

/etc/fstab lists the partitions that are mounted automatically at boot. Skip this step and the swap disappears the next time the VPS reboots.

Step 1.4 - Confirm the swap has grown

echo "=== SWAP AFTER RESIZE ===" && swapon --show && free -h | grep Swap

Expected result: total swap of about 6GB (2GB old + 4GB new).

Part 2: Tune PostgreSQL - 2-5x faster

Why is a default PostgreSQL slow?

PostgreSQL ships with a very conservative default config (it uses only 128MB of RAM) so it can run on any server, even one with 512MB RAM. On a 16GB RAM VPS, tuning the config gives you:

Queries 2-5x faster because far more data is cached in RAM
Faster sorts/joins thanks to more memory per query
Faster VACUUM, so tables do not bloat

Config changes (for a 16GB RAM server)

Parameter	Default	Set to	Why
`shared_buffers`	128 MB (too low)	512 MB	Caches data in RAM. Rule of thumb: 25-40% of the RAM reserved for PG
`effective_cache_size`	4 GB	6 GB	Tells PG how much RAM is available for caching overall (OS + PG)
`work_mem`	4 MB	8 MB	RAM per sort/hash join operation. Careful: every connection gets its own
`maintenance_work_mem`	64 MB	128 MB	RAM for VACUUM, CREATE INDEX. Only one runs at a time, so it is safe to raise
`wal_buffers`	-1 (auto)	16 MB	Buffer for the Write-Ahead Log. 16MB is the best practice
`checkpoint_completion_target`	0.5	0.9	Spreads checkpoint I/O out evenly, avoids spikes
`random_page_cost`	4.0	1.1	For SSD. Makes PG prefer an Index Scan over a Seq Scan
`effective_io_concurrency`	1	200	For SSD. Lets PG read many pages concurrently
`log_min_duration_statement`	-1 (off)	1000	Logs queries that run longer than 1 second. Helps find slow queries

These values are tuned for a VPS with 16GB RAM + SSD. If your server is different, use PGTune to recalculate them.

Step 2.1 - Find the config file path

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

It is usually /etc/postgresql/16/main/postgresql.conf (change 16 to your PG version).

Step 2.2 - Back up the old config

# CHANGE the path if your PG version is not 16
sudo cp /etc/postgresql/16/main/postgresql.conf /etc/postgresql/16/main/postgresql.conf.bak.$(date +%Y%m%d)

Always back up before editing. If something goes wrong you can roll back immediately (see "Roll back the PostgreSQL config" near the end of this guide).

Step 2.3 - Run the script that edits the config

Copy the whole block below into your terminal:

# CHANGE the path if your PG version is not 16
PG_CONF="/etc/postgresql/16/main/postgresql.conf"

# Main memory
sudo sed -i "s/^#*shared_buffers\s*=.*/shared_buffers = 512MB/" $PG_CONF
sudo sed -i "s/^#*effective_cache_size\s*=.*/effective_cache_size = 6GB/" $PG_CONF
sudo sed -i "s/^#*work_mem\s*=.*/work_mem = 8MB/" $PG_CONF
sudo sed -i "s/^#*maintenance_work_mem\s*=.*/maintenance_work_mem = 128MB/" $PG_CONF

# Write-Ahead Log
sudo sed -i "s/^#*wal_buffers\s*=.*/wal_buffers = 16MB/" $PG_CONF
sudo sed -i "s/^#*checkpoint_completion_target\s*=.*/checkpoint_completion_target = 0.9/" $PG_CONF

# SSD tuning
sudo sed -i "s/^#*random_page_cost\s*=.*/random_page_cost = 1.1/" $PG_CONF
sudo sed -i "s/^#*effective_io_concurrency\s*=.*/effective_io_concurrency = 200/" $PG_CONF

# Logging
sudo sed -i "s/^#*log_min_duration_statement\s*=.*/log_min_duration_statement = 1000/" $PG_CONF

echo "Config updated!"

sed -i finds and replaces text in place inside a file. The pattern s/^#*param\s*=.*/param = value/ works like this:

^#* - drops the leading # if the line is commented out
\s*=.* - matches the = old_value part
The whole line is replaced with the new value

Step 2.4 - Check that the config is correct

PG_CONF="/etc/postgresql/16/main/postgresql.conf"
echo "=== NEW CONFIG ===" && \
grep -E "^(shared_buffers|effective_cache_size|work_mem|maintenance_work_mem|wal_buffers|checkpoint_completion_target|random_page_cost|effective_io_concurrency|log_min_duration_statement)" $PG_CONF

Expected result:

shared_buffers = 512MB
effective_cache_size = 6GB
work_mem = 8MB
maintenance_work_mem = 128MB
wal_buffers = 16MB
checkpoint_completion_target = 0.9
random_page_cost = 1.1
effective_io_concurrency = 200
log_min_duration_statement = 1000

Step 2.5 - Restart PostgreSQL

sudo systemctl restart postgresql

The restart drops every DB connection for about 3-5 seconds. Do it when traffic is at its lowest (for example 2-4 AM).

Step 2.6 - Confirm PostgreSQL is healthy

echo "=== PG STATUS ===" && \
sudo systemctl status postgresql --no-pager | head -5 && \
echo "" && \
echo "=== CONFIG VERIFY ===" && \
sudo -u postgres psql -c "SHOW shared_buffers;" && \
sudo -u postgres psql -c "SHOW effective_cache_size;" && \
sudo -u postgres psql -c "SHOW work_mem;" && \
sudo -u postgres psql -c "SHOW maintenance_work_mem;" && \
echo "" && \
echo "=== CONNECTIONS ===" && \
sudo -u postgres psql -c "SELECT count(*) as active_connections FROM pg_stat_activity;" && \
echo "" && \
echo "=== MEMORY AFTER RESTART ===" && \
free -h

Check that shared_buffers shows 512MB and the PG status is active (running).

Part 3: Raise the DB connection pool in the app

Why raise the pool?

Every request from the app needs a connection to PostgreSQL. A connection pool keeps a number of connections open for reuse, avoiding the overhead of opening new ones.

Pool too small: requests queue up waiting for a connection, then time out
Pool too large: wasted RAM (each connection uses about 5-10MB)

Rule: pool size <= max_connections * 0.8, which leaves headroom for admin/monitoring.

Make the change (on your local machine, not the VPS)

Edit the database pool config in your source code and change the max value:

const pool = new pg.Pool({
  connectionString: env.DATABASE_URL,
  max: 30,                        // Raised from 20 -> 30
  idleTimeoutMillis: 30_000,      // Close idle connections after 30s
  connectionTimeoutMillis: 10_000, // Time out if no connection is available within 10s
});

After the edit, rebuild and redeploy the backend.

Troubleshooting and notes

Roll back the PostgreSQL config

If something is wrong after restarting PostgreSQL (startup error, out of memory):

# CHANGE the path and the date if needed
sudo cp /etc/postgresql/16/main/postgresql.conf.bak.$(date +%Y%m%d) /etc/postgresql/16/main/postgresql.conf
sudo systemctl restart postgresql

Remove the swap if needed

sudo swapoff /swapfile2
sudo rm /swapfile2
# Remove the /swapfile2 line from /etc/fstab
sudo sed -i '/swapfile2/d' /etc/fstab

PostgreSQL does not start after the config change

Check the error log:

sudo tail -50 /var/log/postgresql/postgresql-16-main.log

Common errors:

Symptom	Fix
`shared_buffers` too large	Lower it. The maximum should be 40% of RAM
Syntax error in the config	Re-check the file and watch for stray spaces

Check the slow query log

With log_min_duration_statement = 1000 on, queries that run longer than 1 second are logged to:

sudo tail -f /var/log/postgresql/postgresql-16-main.log | grep "duration"

Useful tools

PGTune - calculates an optimal config for your server
pg_stat_statements - extension that tracks query performance
htop - live RAM/CPU view on the VPS

Tune VPS performance: swap, PostgreSQL and the DB pool

Updated: Sep 21, 20269 min read

PostgreSQL VPS Performance Ubuntu

On this page

Quick reference
Check the system before tuning
Add swap (no downtime)
Tune PostgreSQL (needs a restart of about 3-5 seconds)
Part 1: Add swap - keep the OOM Killer away
Why add swap?
Step 1.1 - Check the current swap
Step 1.2 - Create another 4GB of swap
Step 1.3 - Make the swap mount on reboot
Step 1.4 - Confirm the swap has grown
Part 2: Tune PostgreSQL - 2-5x faster
Why is a default PostgreSQL slow?
Config changes (for a 16GB RAM server)
Step 2.1 - Find the config file path
Step 2.2 - Back up the old config
Step 2.3 - Run the script that edits the config
Step 2.4 - Check that the config is correct
Step 2.5 - Restart PostgreSQL
Step 2.6 - Confirm PostgreSQL is healthy
Part 3: Raise the DB connection pool in the app
Why raise the pool?
Make the change (on your local machine, not the VPS)
Troubleshooting and notes
Roll back the PostgreSQL config
Remove the swap if needed
PostgreSQL does not start after the config change
Check the slow query log
Useful tools

The VPS feels slow and swap is almost exhausted
PostgreSQL still runs on its default config (only 128MB of shared_buffers)
The app regularly hits "too many connections" errors or timeouts

Paths in this guide assume PostgreSQL 16 - change 16 to match your version. The config values are sized for a VPS with 16GB RAM + SSD.

Quick reference

If you already know what you are doing, these three blocks are all you need. Each step is explained in the sections below.

Check the system before tuning

echo "===== SYSTEM INFO =====" && \
echo "--- RAM ---" && free -h && \
echo "--- DISK ---" && df -h / && \
echo "===== POSTGRESQL =====" && \
sudo -u postgres psql -c "SHOW shared_buffers;" && \
sudo -u postgres psql -c "SHOW effective_cache_size;" && \
sudo -u postgres psql -c "SHOW work_mem;" && \
sudo -u postgres psql -c "SHOW max_connections;" && \
sudo -u postgres psql -c "SELECT count(*) as active_connections FROM pg_stat_activity;"

Add swap (no downtime)

sudo fallocate -l 4G /swapfile2 && sudo chmod 600 /swapfile2 && \
sudo mkswap /swapfile2 && sudo swapon /swapfile2 && \
echo '/swapfile2 none swap sw 0 0' | sudo tee -a /etc/fstab

Tune PostgreSQL (needs a restart of about 3-5 seconds)

PG_CONF="/etc/postgresql/16/main/postgresql.conf"
sudo cp $PG_CONF ${PG_CONF}.bak.$(date +%Y%m%d)
sudo sed -i "s/^#*shared_buffers\s*=.*/shared_buffers = 512MB/" $PG_CONF
sudo sed -i "s/^#*effective_cache_size\s*=.*/effective_cache_size = 6GB/" $PG_CONF
sudo sed -i "s/^#*work_mem\s*=.*/work_mem = 8MB/" $PG_CONF
sudo sed -i "s/^#*maintenance_work_mem\s*=.*/maintenance_work_mem = 128MB/" $PG_CONF
sudo sed -i "s/^#*wal_buffers\s*=.*/wal_buffers = 16MB/" $PG_CONF
sudo sed -i "s/^#*checkpoint_completion_target\s*=.*/checkpoint_completion_target = 0.9/" $PG_CONF
sudo sed -i "s/^#*random_page_cost\s*=.*/random_page_cost = 1.1/" $PG_CONF
sudo sed -i "s/^#*effective_io_concurrency\s*=.*/effective_io_concurrency = 200/" $PG_CONF
sudo sed -i "s/^#*log_min_duration_statement\s*=.*/log_min_duration_statement = 1000/" $PG_CONF
sudo systemctl restart postgresql

Part 1: Add swap - keep the OOM Killer away

Why add swap?

Swap is disk space the system uses as backup RAM. When physical RAM runs out, Linux moves rarely used data from RAM to swap to free up memory.

On a 16GB RAM VPS running about 30 Docker containers, the default 2GB of swap is not enough. Raise it to at least 6GB.

Step 1.1 - Check the current swap

echo "=== CURRENT SWAP ===" && swapon --show && free -h | grep Swap

The output shows how much swap exists and how much is in use. If used is close to total, add more right away.

Step 1.2 - Create another 4GB of swap

# Create a 4GB file to use as swap
sudo fallocate -l 4G /swapfile2

# Let only root read/write it (security)
sudo chmod 600 /swapfile2

# Format the file as swap
sudo mkswap /swapfile2

# Turn the swap on immediately (no restart needed)
sudo swapon /swapfile2

What each command does:

Command	Effect
`fallocate -l 4G`	Creates an empty 4GB file on disk
`chmod 600`	Only root can read/write the file (avoids a security hole)
`mkswap`	Marks the file as a swap area (formats it)
`swapon`	Activates it right away, no reboot needed

Step 1.3 - Make the swap mount on reboot

echo '/swapfile2 none swap sw 0 0' | sudo tee -a /etc/fstab

/etc/fstab lists the partitions that are mounted automatically at boot. Skip this step and the swap disappears the next time the VPS reboots.

Step 1.4 - Confirm the swap has grown

echo "=== SWAP AFTER RESIZE ===" && swapon --show && free -h | grep Swap

Expected result: total swap of about 6GB (2GB old + 4GB new).

Part 2: Tune PostgreSQL - 2-5x faster

Why is a default PostgreSQL slow?

PostgreSQL ships with a very conservative default config (it uses only 128MB of RAM) so it can run on any server, even one with 512MB RAM. On a 16GB RAM VPS, tuning the config gives you:

Queries 2-5x faster because far more data is cached in RAM
Faster sorts/joins thanks to more memory per query
Faster VACUUM, so tables do not bloat

Config changes (for a 16GB RAM server)

Parameter	Default	Set to	Why
`shared_buffers`	128 MB (too low)	512 MB	Caches data in RAM. Rule of thumb: 25-40% of the RAM reserved for PG
`effective_cache_size`	4 GB	6 GB	Tells PG how much RAM is available for caching overall (OS + PG)
`work_mem`	4 MB	8 MB	RAM per sort/hash join operation. Careful: every connection gets its own
`maintenance_work_mem`	64 MB	128 MB	RAM for VACUUM, CREATE INDEX. Only one runs at a time, so it is safe to raise
`wal_buffers`	-1 (auto)	16 MB	Buffer for the Write-Ahead Log. 16MB is the best practice
`checkpoint_completion_target`	0.5	0.9	Spreads checkpoint I/O out evenly, avoids spikes
`random_page_cost`	4.0	1.1	For SSD. Makes PG prefer an Index Scan over a Seq Scan
`effective_io_concurrency`	1	200	For SSD. Lets PG read many pages concurrently
`log_min_duration_statement`	-1 (off)	1000	Logs queries that run longer than 1 second. Helps find slow queries

These values are tuned for a VPS with 16GB RAM + SSD. If your server is different, use PGTune to recalculate them.

Step 2.1 - Find the config file path

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

It is usually /etc/postgresql/16/main/postgresql.conf (change 16 to your PG version).

Step 2.2 - Back up the old config

# CHANGE the path if your PG version is not 16
sudo cp /etc/postgresql/16/main/postgresql.conf /etc/postgresql/16/main/postgresql.conf.bak.$(date +%Y%m%d)

Always back up before editing. If something goes wrong you can roll back immediately (see "Roll back the PostgreSQL config" near the end of this guide).

Step 2.3 - Run the script that edits the config

Copy the whole block below into your terminal:

# CHANGE the path if your PG version is not 16
PG_CONF="/etc/postgresql/16/main/postgresql.conf"

# Main memory
sudo sed -i "s/^#*shared_buffers\s*=.*/shared_buffers = 512MB/" $PG_CONF
sudo sed -i "s/^#*effective_cache_size\s*=.*/effective_cache_size = 6GB/" $PG_CONF
sudo sed -i "s/^#*work_mem\s*=.*/work_mem = 8MB/" $PG_CONF
sudo sed -i "s/^#*maintenance_work_mem\s*=.*/maintenance_work_mem = 128MB/" $PG_CONF

# Write-Ahead Log
sudo sed -i "s/^#*wal_buffers\s*=.*/wal_buffers = 16MB/" $PG_CONF
sudo sed -i "s/^#*checkpoint_completion_target\s*=.*/checkpoint_completion_target = 0.9/" $PG_CONF

# SSD tuning
sudo sed -i "s/^#*random_page_cost\s*=.*/random_page_cost = 1.1/" $PG_CONF
sudo sed -i "s/^#*effective_io_concurrency\s*=.*/effective_io_concurrency = 200/" $PG_CONF

# Logging
sudo sed -i "s/^#*log_min_duration_statement\s*=.*/log_min_duration_statement = 1000/" $PG_CONF

echo "Config updated!"

sed -i finds and replaces text in place inside a file. The pattern s/^#*param\s*=.*/param = value/ works like this:

^#* - drops the leading # if the line is commented out
\s*=.* - matches the = old_value part
The whole line is replaced with the new value

Step 2.4 - Check that the config is correct

PG_CONF="/etc/postgresql/16/main/postgresql.conf"
echo "=== NEW CONFIG ===" && \
grep -E "^(shared_buffers|effective_cache_size|work_mem|maintenance_work_mem|wal_buffers|checkpoint_completion_target|random_page_cost|effective_io_concurrency|log_min_duration_statement)" $PG_CONF

Expected result:

shared_buffers = 512MB
effective_cache_size = 6GB
work_mem = 8MB
maintenance_work_mem = 128MB
wal_buffers = 16MB
checkpoint_completion_target = 0.9
random_page_cost = 1.1
effective_io_concurrency = 200
log_min_duration_statement = 1000

Step 2.5 - Restart PostgreSQL

sudo systemctl restart postgresql

The restart drops every DB connection for about 3-5 seconds. Do it when traffic is at its lowest (for example 2-4 AM).

Step 2.6 - Confirm PostgreSQL is healthy

echo "=== PG STATUS ===" && \
sudo systemctl status postgresql --no-pager | head -5 && \
echo "" && \
echo "=== CONFIG VERIFY ===" && \
sudo -u postgres psql -c "SHOW shared_buffers;" && \
sudo -u postgres psql -c "SHOW effective_cache_size;" && \
sudo -u postgres psql -c "SHOW work_mem;" && \
sudo -u postgres psql -c "SHOW maintenance_work_mem;" && \
echo "" && \
echo "=== CONNECTIONS ===" && \
sudo -u postgres psql -c "SELECT count(*) as active_connections FROM pg_stat_activity;" && \
echo "" && \
echo "=== MEMORY AFTER RESTART ===" && \
free -h

Check that shared_buffers shows 512MB and the PG status is active (running).

Part 3: Raise the DB connection pool in the app

Why raise the pool?

Every request from the app needs a connection to PostgreSQL. A connection pool keeps a number of connections open for reuse, avoiding the overhead of opening new ones.

Pool too small: requests queue up waiting for a connection, then time out
Pool too large: wasted RAM (each connection uses about 5-10MB)

Rule: pool size <= max_connections * 0.8, which leaves headroom for admin/monitoring.

Make the change (on your local machine, not the VPS)

Edit the database pool config in your source code and change the max value:

const pool = new pg.Pool({
  connectionString: env.DATABASE_URL,
  max: 30,                        // Raised from 20 -> 30
  idleTimeoutMillis: 30_000,      // Close idle connections after 30s
  connectionTimeoutMillis: 10_000, // Time out if no connection is available within 10s
});

After the edit, rebuild and redeploy the backend.

Troubleshooting and notes

Roll back the PostgreSQL config

If something is wrong after restarting PostgreSQL (startup error, out of memory):

# CHANGE the path and the date if needed
sudo cp /etc/postgresql/16/main/postgresql.conf.bak.$(date +%Y%m%d) /etc/postgresql/16/main/postgresql.conf
sudo systemctl restart postgresql

Remove the swap if needed

sudo swapoff /swapfile2
sudo rm /swapfile2
# Remove the /swapfile2 line from /etc/fstab
sudo sed -i '/swapfile2/d' /etc/fstab

PostgreSQL does not start after the config change

Check the error log:

sudo tail -50 /var/log/postgresql/postgresql-16-main.log

Common errors:

Symptom	Fix
`shared_buffers` too large	Lower it. The maximum should be 40% of RAM
Syntax error in the config	Re-check the file and watch for stray spaces

Check the slow query log

With log_min_duration_statement = 1000 on, queries that run longer than 1 second are logged to:

sudo tail -f /var/log/postgresql/postgresql-16-main.log | grep "duration"

Useful tools

PGTune - calculates an optimal config for your server
pg_stat_statements - extension that tracks query performance
htop - live RAM/CPU view on the VPS

Related articles

Migrate a PostgreSQL database between two VPS with pg_dump

Set up PostgreSQL on a VPS securely

Where to put apps on a VPS: the /opt/apps directory layout

Related articles

Migrate a PostgreSQL database between two VPS with pg_dump

Set up PostgreSQL on a VPS securely

Where to put apps on a VPS: the /opt/apps directory layout