PostgreSQL: Tuning SQL Statements Efficiently
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Since the first programs were written, programmers and administrators have tried to tune their applications to reach the maximum amount of performance. Back in the early days, people optimized their programs by hacking some assembler code. With the introduction of SQL, a lot of work has moved to the optimizer of a database. Finding the best way through a query may be difficult. In many cases, it is necessary to give the SQL optimizer some hints so the query can be executed faster.
In this article, we will provide an overview of how PostgreSQL databases can be tuned efficiently. You will learn how a query is executed by the database, and what you can do to increase the overall performance of your PostgreSQL server.
Tuning databases has always been a crucial thing. In this section, you learn about the basics of tuning a PostgreSQL database efficiently. In general, two types of tuning can be distinguished: the performance of the hardware and the queries sent to the server can be tuned.
Hardware Tuning
To understand hardware performance issues, it is important to understand what is going on inside a database and a computer. Information is stored on hard disks, in memory, and in the CPU itself (registers). Every piece of information processed by the system has to be loaded into the CPU—the longer it takes to load the data, the lower the performance of your system will be.
Caching is a method to reduce the average time data needs to reach the CPU. The target is to keep as much information as possible in memory. In many cases, this raises the overall performance of your system.
Another important thing is the sort buffer of your database, which defines the amount of data the database is allowed to sort in memory before writing to a temporary file. Because hard disks are much slower than RAM, it is recommended to sort as much data as possible in the memory, as long as there is enough RAM available on the system. If the system is lacking memory, the kernel has to swap to disk; this decreases the performance of your system.
The target of tuning is to find the right settings for your server. If the amount of cache is too high, your system runs out of memory, and swapping decreases the overall performance of the database. If the amount of cache is too low, your system suffers because of additional I/O operations. The same applies to the size of the sort buffers. It takes some skill to find the right setting for your systems. There are no general rules because it largely depends on the type of operations you have to perform with your database.