PostgreSQL is a powerful, open-source object-relational database management system known for its reliability, feature robustness, and performance. It powers everything from small web applications to large-scale enterprise platforms, offering advanced features such as ACID compliance, JSON/JSONB support, full-text search, and powerful indexing options. In this tutorial, we will show you how to install and optimize PostgreSQL 18 on Ubuntu 26.04 for high-performance web apps. Installing and optimizing PostgreSQL on Ubuntu 26.04 is a straightforward process that should take no more than 15 minutes. Let’s get started!
Ubuntu 26.04 LTS (Resolute Raccoon) ships PostgreSQL 18 in its default repositories, which introduces a new asynchronous I/O engine, UUIDv7 support, and data page checksums enabled by default – all of which are great news for high-performance web applications.
Prerequisites
- A server running Ubuntu 26.04 LTS
- SSH access with root privileges, or a regular user with sudo privileges
Step 1: Log in via SSH and Update the System
First, log in to your Ubuntu 26.04 server via SSH as the root user:
ssh root@Your_Server_IP -p Port_numberDon’t forget to replace Your_Server_IP and Port_number with your actual server IP address and SSH port number.
Once logged in, update all installed packages to their latest available versions:
apt update && apt upgrade -yStep 2: Install PostgreSQL 18
Ubuntu 26.04 includes PostgreSQL 18 in its default repositories, so no third-party repository is required. Install the server along with the postgresql-contrib package.
apt install postgresql postgresql-contrib -yThe installation automatically creates a postgres system user and starts the service.
You can verify that PostgreSQL is running:
systemctl status postgresqlYou should see output similar to this:
● postgresql.service - PostgreSQL RDBMS
Loaded: loaded (/usr/lib/systemd/system/postgresql.service; enabled)
Active: active (exited)To enable PostgreSQL to start automatically on boot:
systemctl enable postgresqlYou can confirm the installed version by running:
sudo -u postgres psql -c "SELECT version();"Step 3. Optimize Postgresql
First, you will need to locate the PostgreSQL configuration files. All performance tuning in this tutorial occurs in the main configuration file. On Ubuntu 26.04, the configuration files are located at:
/etc/postgresql/18/main/postgresql.conf
/etc/postgresql/18/main/pg_hba.confThe biggest impact on PostgreSQL performance is the memory optimization. The default values are intentionally conservative so PostgreSQL can start on almost any machine; they are far too low for a production web application.
Please note that the RAM calculations assume you are using this server only as a PostgreSQL database server. Running other applications or a full stack on this server will have to account for their memory usage and subtract it from the available RAM on that machine.
For the shared_buffers value, set it to 20-25% of your RAM, and for the effective_cache_size value, set it to 50-75% of your RAM.
For this tutorial, we will assume you have a VPS used only for the database server with 12GB of RAM and an NVMe disk
So you can now edit /etc/postgresql/18/main/postgresql.conf configuration file, and the values would be
shared_buffers = 3GB
effective_cache_size = 9GBStep 4. Optimize for NVME Storage
The default planner cost settings still assume spinning hard drives. On modern SSD or NVMe storage (standard on all RoseHosting VPS plans), random reads are nearly as cheap as sequential reads, so you can adjust the settings to:
# Default is 4.0 (for spinning disks). Use 1.1 for SSD/NVMe.
random_page_cost = 1.1
# Number of concurrent I/O requests the storage can handle
effective_io_concurrency = 200
Step 5. Configure Connections and Parallelism
PostgreSQL 18 introduces native Asynchronous I/O (AIO), allowing the database to issue multiple disk read requests concurrently. You can also set the following values to configure the maximum number of connections and parallelism.
# Web apps should use connection pooling, so keep this reasonable
max_connections = 200
# Parallel query settings (assuming 4 CPU cores)
max_worker_processes = 4
max_parallel_workers = 4
max_parallel_workers_per_gather = 2
# Autovacuum keeps tables healthy — never disable it
autovacuum = onInstead of setting max_connections to 1000 or more, you can use connection pooling with PgBouncer.
Step 6. Add Connection Pooling with PgBouncer
High-traffic web applications constantly open and close database connections. PgBouncer sits between your app and PostgreSQL, reusing a small pool of server connections:
apt install pgbouncer -y
Then once installed, you will need to add a minimal configuration in /etc/pgbouncer/pgbouncer.ini:
[databases]
webapp_db = host=127.0.0.1 port=5432 dbname=webapp_db
[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
default_pool_size = 25
max_client_conn = 500 Add your application user’s credentials to /etc/pgbouncer/userlist.txt then start and enable the service: bash systemctl enable --now pgbouncer
Finally, point your web application at port 6432 instead of 5432. Transaction pooling mode allows hundreds of application connections to share a couple dozen actual PostgreSQL connections, dramatically reducing memory usage and connection overhead.
To further optimize PostgreSQL according to your application needs, you can enable the pg_stat_statements extension and track the statistics of every query
Conclusion
That’s it! You have successfully installed PostgreSQL 18 on Ubuntu 26.04 and tuned it for high-performance web applications. We covered memory tuning, NVMe-specific planner settings, PostgreSQL 18’s new asynchronous I/O engine, query monitoring, and connection pooling with PgBouncer.
Of course, you don’t have to install and optimize PostgreSQL on Ubuntu 26.04 yourself if you use one of our monthly server management plans, or grab a per-incident server support, in which case you can simply ask our expert Linux admins to set up and tune PostgreSQL for your specific workload. They are available 24/7 and will address your request immediately.
If you liked this post on how to install and optimize PostgreSQL on Ubuntu 26.04, please share it with your friends or leave a comment below.
