PostgreSQL: Improving performance

This a continuation of my PostgreSQL series (an installation guide for Ubuntu, useful functions and operators, an overview of some basic concepts and some more advanced ones). Here I will briefly outline some functions available on the PostgreSQL database.

You can also check out my notes on designing relational databases.

• Although PostgreSQL does a good job of maintenance of tuning. However, it is useful to analyses the systems real-time query use of indexes. There are plenty of tools available to profile your server, but the usefulness depends on the requirements of your particular system.
  
  NOTE: Always performing tuning on real data. Test data will just tell you what will be optimal for the test data only.


• EXPLAIN is a tool that shows the execution plan of a statement and the associated costs (measured in disk page fetches). You can add the options ANALYZE (actually executes the statement; no longer uses estimates), and VERBOSE (display additional information).
  
  A good explanation on how to use EXPLAIN is provided by the documentation.
  

  EXPLAIN [ANALYZE] [VERBOSE] statement



• ANALYZE collects statistics about the contents of tables and stores the results in a system catalog. These statistics are then used by the planner in selecting an appropriate query plan.
  
  It is useless to conduct performance tuning without first running the ANALYZE command. Otherwise, any results gathered will be generating using default values that are sure to be wrong.
  

  ANALYZE [VERBOSE] [table]



• There are a number of useful views to display statistical information about the server:
  

  View	Description
  pg_stat_activity	One row per server process
  pg_stat_database	One row per database
  pg_stat_database_conflicts	Show standby server database conflicts due to dropped tablespaces, lock timeouts, old snapshots, pinned buffers and deadlocks.
  pg_stat_all_tables	Show all tables in the current database
  pg_stat_all_indexes	Show all indexes in the current database
  pg_statio_all_tables	Show all the tables' disk io statistics
  pg_statio_all_indexes	Show all the indexes' disk io statistics



• The default configuration of PostgreSQL is designed to work on a wide variety of hardware and software installations and is most definitely not optimal for your system. You should edit the postgresql.conf file to increase the values of shared_buffers, effective_cache_size, sort_mem, max_fsm_pages and max_fsm_relations.


• The physical memory and disks will most probably be the slowest thing in your system. You should always tweak your hardware to generate the optimal performance in your system.

References:

• Examining indexes to improve performance from the PostgreSQL documentation
• Performance tips chapter from the PostgreSQL documentation
• 5-minute introduction to PostgreSQL performance tuning by Gregory Smith
• PostgreSQL performance tuning by Christopher Browne
• Performance tuning PostgreSQL by Frank Wiles
• Performance checklist by Selena Deckelman