Postgres is a very fast relational database and one of the most used open-source databases in the software world. However, as the size of the database grows, you might begin noticing that some queries take more time to execute, or the memory, CPU, and disk resources are being utilized fully. But in general, PostgreSQL has default configuration settings, and it works equally on all servers.
The most effective method of postgres configuration tuning involves the following approach:
Do not tune PostgreSQL randomly. Measure the problem first and adjust the particular parameter.
1. Step One: Determine What Exactly Is Slowing Down
Before changing any configuration parameter, identify the queries responsible for the problem.
One of the biggest mistakes made during postgres tuning is changing parameters just based on assumptions.
shared_buffers = 8GB
work_mem = 256MB
max_connections = 500
Without knowing whether memory is even the problem.
Instead, start with:
EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM sale_order
WHERE partner_id = 123;
This tells you more than simply looking at the query execution time.
Result :
QUERY PLAN
---------------------------------------------------------------------------------------------------------
Seq Scan on sale_order (cost=0.00..1.25 rows=1 width=397) (actual time=0.022..0.024 rows=0.00 loops=1)
Filter: (partner_id = 123)
Rows Removed by Filter: 20
Buffers: shared hit=1
Planning:
Buffers: shared hit=237
Planning Time: 0.851 ms
Execution Time: 0.059 ms
(8 rows)
Here, we can see the query execution plan, like which types of scans are used here, how many pages are actually fetched from buffers, and how much is from disk.
For example:
If buffers: shared hit=50 read=2000
This means nearly 2000 pages are read from disk, and this shows the tuning of the parameter named shared_buffers in postgres.
You can see the metadata of shared_buffers from pg_settings like this.
select * from pg_settings where name = 'shared_buffers';
Result :
-[ RECORD 1 ]---+--------------------------------------------------------------
name | shared_buffers
setting | 987000
unit | 8kB
category | Resource Usage / Memory
short_desc | Sets the number of shared memory buffers used by the server.
extra_desc |
context | postmaster
vartype | integer
source | configuration file
min_val | 16
max_val | 1073741823
enumvals |
boot_val | 16384
reset_val | 987000
sourcefile | /home/cybrosys/dbblue_latest/postgres/pg_data/postgresql.conf
sourceline | 136
pending_restart | f
If you see:
Seq Scan on sale_order
that is not automatically bad.
A sequential scan can actually be the fastest option when the postgres needs to scan a large percentage of a table.
2. Tune shared_buffers
shared_buffers controls how much memory PostgreSQL uses for its shared buffer cache.
For example:
shared_buffers = 4GB
means PostgreSQL can use approximately 4 GB of RAM for its own shared buffer cache.
A commonly used starting point on a dedicated postgres server is around 25% of system RAM, but this is only a starting point.
For a server with 16 GB RAM, you might start around:
shared_buffers = 4GB
3. Understand work_mem Before Increasing It
work_mem is one of the most misunderstood parameters in postgres.
It is the approximate amount of memory available to an individual query operation such as:
- Sort
- Hash join
- Hash aggregation
- Some other executor operations
For example:
work_mem = 64MB
It does not necessarily mean a connection can use only 64 MB.
A single query can perform multiple operations that each use work_mem.
Imagine:
Query
+-- Sort ? 64 MB
+-- Hash Join ? 64 MB
+-- Hash Aggregate ? 64 MB
The query could potentially use considerably more than 64 MB.
And if many queries run simultaneously, memory usage can grow quickly.
The better approach
Keep the global value conservative:
work_mem = 16MB
or:
work_mem = 32MB
depending on your workload and available RAM.
Then, increase it for specific workloads when needed:
SET LOCAL work_mem = '128MB';
4. Watch for Disk-Based Sorts
One of the easiest ways to find a work_mem problem is to look for temporary files.
Run:
EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM account_move_line
ORDER BY date;
If PostgreSQL cannot fit a sort in memory, it may use temporary disk files.
You may see something like:
Sort Method: external merge
Disk: 250000kB
That is a strong signal that the operation spilled to disk.
If the query is important, increasing work_mem for that operation may help:
SET LOCAL work_mem = '128MB';
But don't blindly increase the global setting.
5. Tune maintenance_work_mem
The maintenance_work_mem parameter is used for operations such as:
- VACUUM
- CREATE INDEX
- ALTER TABLE
- Some other maintenance operations
Unlike work_mem, it is generally safer to make this substantially larger.
For example:
maintenance_work_mem = 512MB
or:
maintenance_work_mem = 1GB
may be reasonable on a server with sufficient RAM.
This can make large index creation and maintenance operations considerably faster compared to the default postgres configuration settings.
6. Don't Forget autovacuum
If PostgreSQL performance gradually becomes worse as tables grow, autovacuum should be one of the first things you investigate.
PostgreSQL uses MVCC, which means old row versions can remain in tables after updates and deletes.
Autovacuum cleans these dead tuples and also updates statistics in a regular interval based on the vacuum threshold criteria.
Check:
SELECT
relname,
n_live_tup,
n_dead_tup,
last_autovacuum,
last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC limit 5;
Result :
relname | n_live_tup | n_dead_tup | last_autovacuum | last_autoanalyze
--------------------+------------+------------+-----------------+------------------
uom_uom | 30 | 50 | |
product_product | 46 | 46 | |
payment_provider | 23 | 44 | |
account_tax | 50 | 43 | |
ir_module_category | 84 | 37 | |
(5 rows)
Here, we can see each table's live tuples and dead tuples count. And also, in the upcoming postgres 19, there is a new scoring system to select the priority for taking the tables for the autovacuum process. And also we can set specific table options related to autovacuum for separate tables based on our own needs.
For example:
ALTER TABLE account_move_line
SET (
autovacuum_vacuum_scale_factor = 0.02,
autovacuum_analyze_scale_factor = 0.01
);
7. Keep Statistics Accurate
The planner of postgres makes decisions based on statistics. Sometimes, after a big query execution like a bulk data insert, bulk update, or bulk delete, the statistics are not changed immediately. Sometimes executing the analyze command is a good option.
For example, the postgres needs to estimate:
How many rows will this WHERE condition return?
Estimated rows: 100
Actual rows: 2,000,000
Sometimes the planner may choose a very poor execution plan.
Run:
ANALYZE;
for a database-wide statistics refresh, or:
ANALYZE account_move_line;
for a specific table.
You can inspect statistics with:
SELECT
tablename,
attname,
n_distinct,
most_common_vals
FROM pg_stats
WHERE tablename = 'account_move_line';
Result :
tablename | attname | n_distinct |
most_common_vals
-------------------+--------------------------+--------------+----------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------
account_move_line | move_id | -0.41791046 | {2,3,16,30,31,44,1,4,8,14,15,29,32,36,42,43,5,6,7,9,10,11,12,13,17,18,19,20,21,22,23,24,25,26,27,28,33,34,35,37,38,39,40,41,45,46,47,48,49,50
,51,52,53,54,55,56}
account_move_line | journal_id | 8 | {1,7,6,12,2,8,3,9}
account_move_line | company_id | 2 | {1,2}
account_move_line | company_currency_id | 1 | {1}
account_move_line | sequence | 3 | {100,12000,10000}
account_move_line | account_id | -0.104477614 | {26,90,5,57,47,153,48,154,32,140,15,21,77,83}
You can also use the analyze command with three suboptions.
\h analyze
Result :
Command: ANALYZE
Description: collect statistics about a database
Syntax:
ANALYZE [ ( option [, ...] ) ] [ table_and_columns [, ...] ]
where option can be one of:
VERBOSE [ boolean ]
SKIP_LOCKED [ boolean ]
BUFFER_USAGE_LIMIT size
and table_and_columns is:
[ ONLY ] table_name [ * ] [ ( column_name [, ...] ) ]
URL: https://www.postgresql.org/docs/19/sql-analyze.html
8. Increase default_statistics_target Only When Needed
The default statistics target is often good enough.
You don't need to immediately change from the default value to a bigger value like this.
default_statistics_target = 100
to:
default_statistics_target = 1000
for the entire database.
Instead, if a particular column has a complicated data distribution, increase statistics for that column:
ALTER TABLE account_move_line
ALTER COLUMN account_id
SET STATISTICS 500;
ANALYZE account_move_line;
This gives the planner more information about that column without increasing statistics collection everywhere.
This is especially useful when data distribution is highly skewed.
9. Index the Queries, Not the Tables
A common mistake is when the size of the table becomes so large, and the query execution related to these tables takes much more time, then we add indexes, and we didn't check whether the newly created index is properly used or not.
Don't do that.
Indexes should be created based on actual query patterns.
Suppose your application frequently runs:
SELECT *
FROM sale_order
WHERE partner_id = 123;
An index may help:
CREATE INDEX idx_sale_order_partner_id
ON sale_order (partner_id);
But if your query is:
SELECT *
FROM sale_order
WHERE partner_id = 123
AND state = 'sale';
A multicolumn index may be more useful here rather than a normal btree index.
CREATE INDEX idx_sale_order_partner_state
ON sale_order (partner_id, state);
The correct index depends on:
- Filtering
- Joins
- Sorting
- Selectivity
- Query frequency
- Table size
- Data distribution
10. Too Many Indexes Can Also Hurt
Indexes are not free, and it aso takes different sizes based on the types of indexes we create. For example, the same column with indexes such as btree index and brin index would be entirely different because of the data storage architecture in these indexes.
So while indexes can make reads faster, too many indexes can make writes slower.
Before adding an index, check the query execution plan and ensure that it uses a sequential scan instead of an index scan.
EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM sale_order
WHERE partner_id = 123;
Result :
QUERY PLAN
---------------------------------------------------------------------------------------------------------
Seq Scan on sale_order (cost=0.00..1.25 rows=1 width=397) (actual time=0.022..0.024 rows=0.00 loops=1)
Filter: (partner_id = 123)
Rows Removed by Filter: 20
Buffers: shared hit=1
Planning:
Buffers: shared hit=237
Planning Time: 0.851 ms
Execution Time: 0.059 ms
(8 rows)
Here, the partner_id column has no index, and that’s why the planner chooses the sequential scan.
Now, create an index on the partner_id column and check the query execution plan again.
CREATE INDEX sale_order__partner_id_index ON public.sale_order USING btree (partner_id)
Now, check the usage of this index.
select * from pg_stat_user_indexes where indexrelname= 'sale_order__partner_id_index';
Result :
-[ RECORD 1 ]-+---------------------------------
relid | 27785
indexrelid | 27901
schemaname | public
relname | sale_order
indexrelname | sale_order__partner_id_index
idx_scan | 2
last_idx_scan | 2026-08-18 10:52:27.171795+05:30
idx_tup_read | 0
idx_tup_fetch | 0
stats_reset |
Now, we can ensure that the created index is beneficial for query execution.
11. Tune Parallel Query Related Parameters Carefully
Postgres can use multiple workers to make some operations faster. In postgres we have some configuration parameters related to parallel query execution.
Important parameters include:
max_worker_processes
max_parallel_workers
max_parallel_workers_per_gather
For example:
max_parallel_workers_per_gather = 2
This allows a query to use parallel workers up to 2 where the planner considers it worthwhile.
We can tune these parameters based on our query execution plan.
12. Tune effective_cache_size Correctly
The effective_cache_size does not allocate memory like shared buffers.
For example:
effective_cache_size = 8GB
It doesn't mean Postgres reserves 8 GB for separate caching to speed up the data reading process.
It tells the planner approximately how much data could potentially be cached, like the postgres shared buffers, including the OS filesystem cache
A more realistic value helps the planner estimate whether an index scan is likely to benefit from cached pages.
You can also check this parameter's metadata from pg_settings like this.
select * from pg_settings where name = 'effective_cache_size';
Result :
-[ RECORD 1 ]---+----------------------------------------------------------------------------------------------------------------------------------------------------------------------
name | effective_cache_size
setting | 2961002
unit | 8kB
category | Query Tuning / Planner Cost Constants
short_desc | Sets the planner's assumption about the total size of the data caches.
extra_desc | That is, the total size of the caches (kernel cache and shared buffers) used for PostgreSQL data files. This is measured in disk pages, which are normally 8 kB each.
context | user
vartype | integer
source | configuration file
min_val | 1
max_val | 2147483647
enumvals |
boot_val | 524288
reset_val | 2961002
sourcefile | /home/cybrosys/dbblue_latest/postgres/pg_data/postgresql.conf
sourceline | 464
pending_restart | f
13. Don't Blindly Change random_page_cost
You will often see advice like:
random_page_cost = 1.1
or:
random_page_cost = 1.0
because modern SSDs are fast.
That can be reasonable in some environments, but don't change it based on assumptions.
random_page_cost influences planner decisions between sequential and random/index access.
If you lower it too aggressively, PostgreSQL may start choosing index scans when sequential scans would actually be cheaper.
Use EXPLAIN (ANALYZE, BUFFERS) to determine whether the planner is making poor choices before changing this parameter.
14. Monitor Connections
Another common performance problem is too many connections.
For example:
max_connections = 500
It doesn't mean postgres will efficiently handle 500 heavy queries simultaneously. Each connection consumes CPU resources, and if an application creates hundreds of connections, a connection pooler such as PgBouncer can often be a better solution than simply increasing max_connections.
And also postgres have some additional connection parameters like this.
Reserved_connections
- We can reserve specific connections for specific users by using this parameter.
Superuser_reserved_connections
- We can reserve additional connections for superusers separately
We can also reserve additional connections above the max_connections value based on these parameters.
15. Use pg_stat_statements
If you want to seriously tune PostgreSQL, you need to know which queries are consuming the most resources and which queries are taking more execution time. pg_stat_statements is one of the most useful built-in extensions in postgres to track the long-running queries. After enabling it, you can identify queries by:
- Total execution time
- Number of calls
- Average execution time
- Rows returned
- Shared blocks read
- Shared blocks hit
- Temporary blocks written
For example:
SELECT
query,
calls,
total_exec_time,
mean_exec_time,
rows
FROM pg_stat_statements
ORDER BY total_exec_time
LIMIT 10;
Result :
query
| calls | total_exec_time | mean_exec_time | rows
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------+-------+----------------------+----------------+------
ROLLBACK
| 2 | 0.00426 | 0.00213 | 0
listen imbus
| 1 | 0.005797 | 0.005797 | 0
SELECT "pg_notify"($1, 2)
| 1| 0.006705| 0.006705| 1
SELECT"irembeddedactions"."id"FROM"irembeddedactions"WHERE"irembeddedactions"."parentactionid"IN(1) ORDER BY "ir_embedded_actions"."sequence" , "ir_embedded_actions"."id"
| 1 | 0.007124 | 0.007124 | 0
SELECT "mail_guest"."id" FROM "mail_guest" WHERE "mail_guest"."id" IN (1)ORDERBY"mailguest"."id"
| 1| 0.009079| 0.009079| 1
SELECT"irconfigparameter"."id"FROM"irconfigparameter"WHERE"irconfigparameter"."key"IN(1) ORDER BY "ir_config_parameter"."key"
| 1 | 0.009498 | 0.009498 | 1
LISTEN cron_trigger
| 5 | 0.009569000000000001 | 0.0019138 | 0
SELECT "res_users_settings_volumes"."id", "res_users_settings_volumes"."user_setting_id" FROM "res_users_settings_volumes" WHERE "res_users_settings_volumes"."user_setting_id" IN (1)ORDERBY"resusers
settingsvolumes"."id"| 1| 0.009638| 0.009638| 0
SELECT"rescompany"."id","rescompany"."parentid","rescompany"."active"FROM"rescompany"WHERE"rescompany"."parentid"IN(1 /*, ... */) ORDER BY "res_company"."sequence" , "res_company"."name
" | 1 | 0.009847 | 0.009847 | 0
SELECT "mail_presence"."id" FROM "mail_presence" WHERE ("mail_presence"."last_poll" >= 1::timestampAND("mailpresence"."guestid"IN(2) OR "mail_presence"."user_id" IN ($3 /*, ... */))) ORDER BY "ma
il_presence"."id" | 1 | 0.009988 | 0.009988 | 0
(10 rows)
16. A Practical Starting Configuration
For a dedicated postgres server with 16 GB RAM, a reasonable starting point might look like:
shared_buffers = 4GB
work_mem = 16MB
maintenance_work_mem = 512MB
effective_cache_size = 10GB
max_parallel_workers_per_gather = 2
max_parallel_workers = 4
random_page_cost = 1.1
The most common way to solve a database performance issue is mainly checking the long-running queries, creating better indexes, and checking the index usage. The second thing is tuning the postgres configuration parameters based on the server specification. A database administrator or a postgres developer needs to know these things. There are many more process happens inside when we execute a simple query in postgres. On the backend, it executes internal processes like checking the pages in the cache, going to disk, and then returning the results from disk to the psql terminal. These steps are the basic steps for maintaining a database for good performance. The values of postgres configuration parameters are entirely changed based on the server’s specifications.