PostgreSQL Performance Tuning

Razi Abuzar's picture

Performance Tuning:

Below are the parameters and their details which can have a significant effect on the performance of your database.

  • You can keep the value of your shared_buffers around 1/4th of the RAM in case of your RAM is 1GB or more.
  • In case you have RAM less than 1GB, you will have to keep it less than 15% of RAM, it has to be tested depending upon your RAM size and that how much RAM your OS is using.
  • Normally in Linux systems you will have to adjust kernel settings in order to keep your shared_buffers more than 32 MB.
  • If your OS is 32 bit you should not use the shared_buffers to be more than 2 to 2.5 GB.
  • On windows more shared_buffers value is not required as it uses OS cache. The range of shared_buffers on windows can be between 64MB to 512 MB


  • This parameter suggests maximum connections allowed to the PostgreSQL database at a time. A PostgreSQL on a good hardware server can support few hundreds of connections in number. If you need more than few hundreds as the value of max_conections you can use connection pooling software.
  • This is very important for few parameters (viz work_mem) because these parameters are allocated connections per client basis as a result more the value of max_connections more possible will the memory usage be.


  • WORK_MEM parameter is used to specifies the size of memory to be used by the queries for sorting. It allocates memory for sorts per user, it allows in-memory sorting which is faster if compared with sorts done on disks. This is in close connection with the value of max_connections parameter for if you set work_mem as 50MB and 15 users run queries then soon you would be requiring bigger memory for parallel execution of sorting require more memory.
  • If there is a query running which needs larger memory then the sort will spill on the disk and you will need to add the size to work_mem in order to accommodate the sort within the work_mem. To check the size spilling on the disk can be checked by using EXPAIN ANALYZE.
  • EXPLAIN ANALYZE will show it in below format:


So adding 4MB of extra space to work_mem will be good.



  • This specifies maximum size of memory that can be allocated for use by maintenance tasks such as alter table add foreign key, vacuum, create index etc.
  • The default value of this parameter is 64 MB since version 9.4.
  • To improve the performance, make sure that you have enough of memory assigned to maintenance_woek_mem parameter.
  • The value of maintainance_work_mem parameter should always be bigger than that of work_mem for better performance.


  • This has Boolean value which determines if the server should run AUTOVACUUM launcher daemon.


  • It has its value in number. The term FSM stands for FREE SPACE MAP. When you delete something from the table it is not removed from the disk but it is utilised when you insert something in that table. MAX_FSM_PAGES should be high in case you have high frequency of INSERTS and DELETES to prevent tables from bloating.


  1. FSYNC
  • This has a Boolean value. This determines whether the WAL pages are fsync’d to disk before the transaction is committed. Turning this off increases your server performance reducing the disk read write but can be risky leading to recoverable data corruption.


  • This parameter has an integer value. This informs the query planner about the effective size of the disk cache available for execution of a single query. While setting this you should consider the kernel disk cache size and SHARED_BUFFERS value.
  • This tells you where your database server is logging to.


  • Choices of DDLs are recorded in the log file in the location described by this parameter.


Below Queries can be helpful in optimising performance of your database.


1. EXPLAIN command can be used to explain queries:

EXPLAIN [option]    ---(ANALYZE, VERBOSE can be the option.


FROM articles, members
WHERE articles.member_id=16 and = articles.member_id

2. VACUUM command can be used to reclaim storage:

VACUUM [ ( { FULL | FREEZE | VERBOSE | ANALYZE } [, ...] ) ] [ table [ (column [, ...] ) ] ]
VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ table ]
VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] ANALYZE [ table [ (column [, ...] ) ] ]

(NOTE: VACUUM FULL is a different command and is not referable)
ANALYZE Table_name;
4. CLUSTER: when table is clustered it physically reorders itself based on the index information.

CLUSTER [VERBOSE] table_name [ USING index_name ]

5. AUTOVACUUM : AUTOVACUUM is a deamon which is controlled by few parameters in postgresql.conf automates the task of VACUUM and ANALYZE.
6. SELECT ….INTO; this query is used to copy data from bloated table to new table.

SELECT * INTO new_table FROM old_table WHERE date_prod >= '2017-05-01';




I am happy that I have seen

Harry Bell's picture

I am happy that I have seen this post. The things that were recognizing by our works will surely help us to accomplish more awards throughout our life in the future. This post will surely help us in our fights that we will confront. For more info you can use this best essay service to having a wonderful assistant in writing and even you can get the guidelines too.