Book Image

PostgreSQL Developer's Guide

By : Ibrar Ahmed, Asif Fayyaz, Amjad Shahzad
Book Image

PostgreSQL Developer's Guide

By: Ibrar Ahmed, Asif Fayyaz, Amjad Shahzad

Overview of this book

<p>PostgreSQL is an enterprise-level database that competes among proprietary database vendors, owing to its remarkable feature set and reliability without the expensive licensing overhead.</p> <p>This book is a comprehensive and pragmatic guide to developing databases in PostgreSQL. Beginning with a refresher of basic SQL skills, you will gradually be exposed to advanced concepts, such as learning how to program in native PostgreSQL procedural languages, implementing triggers, custom functions, and views. You will learn database optimization techniques such as query optimization and indexing while exploring extensive code examples. Lastly, you will explore foreign data wrappers, implementing extensibility, and improving maintainability.</p>
Table of Contents (19 chapters)
PostgreSQL Developer's Guide
Credits
About the Authors
About the Reviewers
www.PacktPub.com
Preface
Index

Configuration parameters to optimize queries


Like other databases, PostgreSQL has configuration parameters that can be configured permanently or session predicated. The postgresql.conf file is utilized to configure most of the configuration parameters. We will only discuss the parameters that can affect the performance. They are as follows:

  • work_mem: The disk I/O is the dominant cost factor in queries; if case queries involve a large number of complex sorts, then it can increment the disk access. If the system has lots of recollection, then the database should perform the in-recollection sorting to reduce the disk read. The work_mem configuration parameter is utilized to determine when the sorting will be performed in a recollection or the disk sort will be utilized. If we have lots of recollection, then the work_mem parameter should be set to the optimal value so that every sort can be performed in-recollection. The work_mem parameter is per connection for each sort; this makes it authentically...