An application that was fast with a thousand records often becomes slow with a million, and the database is usually where the time goes. The first instinct is to buy a larger server. That helps for a while and hides the cause. In most growing applications, a small number of specific problems account for nearly all of the slowness, and they can be found and fixed.
Measure before changing anything
Do not guess which queries are slow. PostgreSQL can tell you. The pg_stat_statements extension records every kind of query with how often it ran and how much time it used in total. Sort by total time. The top few entries are where the effort should go.
Note that the most important query is often not the slowest single one. A query that takes twenty milliseconds and runs two million times a day costs more than a report that takes ten seconds once a night.
Read the query plan
For a slow query, run it with EXPLAIN ANALYZE. The output shows how the database carried it out and where the time went. The thing to look for first is a sequential scan on a large table: the database read every row to find a few. That usually means an index is missing.
Indexes
An index lets the database find rows without reading the whole table, much as an index in a book does.
- Index the columns used to filter, join and sort in your frequent queries.
- Foreign key columns need an index. PostgreSQL does not create one automatically, and its absence makes joins and deletes slow.
- For queries that filter on several columns together, a single index covering those columns is better than separate ones. Column order matters: put the most selective filter first.
- A partial index covers only some rows, such as open orders, and stays small and fast.
Indexes are not free. Each one must be updated on every write and takes disk space. Remove indexes that are never used. The statistics views show which ones those are.
The N+1 problem
This is the most common cause of slowness in applications that use an object-relational mapper. The code loads a list of fifty orders with one query, then runs a separate query for the customer of each order. Fifty-one queries where two would do. Each is fast, and together they are slow.
The fix is in the application: tell the mapper to fetch the related records together. Logging the number of queries per page request makes these easy to spot. A page that runs three hundred queries has this problem.
Fetch less
Select only the columns you need, especially when rows contain large text or JSON fields. Paginate lists instead of loading everything. For deep pagination, filter by the last seen value instead of using a large offset, since the database must still read and discard every skipped row.
Connections
Each PostgreSQL connection uses memory, and a server handles a limited number well. An application that opens a new connection for every request, or many application servers each holding their own, can exhaust the limit. A connection pooler such as PgBouncer sits in between and shares a small number of database connections among many clients.
Maintenance
PostgreSQL keeps old versions of updated rows until a background process called autovacuum cleans them up. On tables with heavy updates, the default settings may not keep pace, and the table grows and slows. Check that autovacuum is running on your busiest tables and tune it for them if needed.
Watch for long-running transactions as well. One forgotten open transaction can prevent clean-up across the whole database.
Before buying a bigger server
Work through this order: fix the slow queries, add the missing indexes, remove N+1 patterns, cache results that are read often and change rarely, and add a connection pooler. If reads still dominate, add a read replica for reports and heavy queries.
A single well-tuned PostgreSQL server carries far more load than most teams expect. Splitting data across several databases adds real complexity and is needed much later than commonly assumed.
Summary
Find the queries that use the most total time, read their plans, add the indexes they need, fix N+1 patterns in the application and manage connections. Scale the hardware after that. Database review is part of our custom software development service. If your application has slowed as it grew, we can find the cause.