Chapter 3: Server Configuration

I get asked many questions about parameter settings in PostgreSQL. Everybody's busy, and most people want a 5-minute tour of how things work. That's exactly what a cookbook does, so we'll do our best.

Some people believe that some magical parameter settings will improve their performance and spend hours combing the pages of books to glean insights. Others feel comfortable because they have found a website somewhere that explains everything, and they know they have their database configured OK.

For the most part, the settings are easy to understand. Finding the best setting can be difficult, and the optimal setting may change over time. This chapter is mostly about knowing how, when, and where to change parameter settings.

In this chapter, we will cover the following recipes:

• Read the fine manual
• Planning a new database
• Setting configuration parameters for the database server
• Setting configuration...

RTFM

RTFM is often (rudely) used to mean don't bother me; I'm busy, or it is used as a stronger form of abuse. The strange thing is that asking you to read a manual is most often very good advice. Take the advice! The most important point to remember is that you should refer to a manual whose release version matches that of the server on which you are operating.

The PostgreSQL manual is very well written and comprehensive in its coverage of specific topics. However, one of its main failings is that the documents aren't organized in a way that helps somebody who is trying to learn PostgreSQL. They are organized from the perspective of people checking specific technical points so that they can decide whether their difficulty is a user error. It sometimes answers what? but it seldom answers why? or how?.

I've helped write sections of PostgreSQL documents, so I'm not embarrassed to steer you toward reading them. There are, nonetheless...

Planning a new database

Planning a new database can be a daunting task. It's easy to get overwhelmed by it, so here, we will present some planning ideas. It's also easy to charge headlong at the task, thinking that whatever you know is all you'll ever need to consider.

Getting ready

You are ready. Don't wait to be told what to do. If you haven't been told what the requirements are, then write down what you think they are, clearly labeling them as assumptions rather than requirements; you must not confuse the two.

Iterate until you get some agreement, and then build a prototype.

How to do it…

Write a document that covers the following items:

• Database design—plan your database design.
• Calculate the initial database sizing.
• Transaction analysis—how will we access the database?
• Look at the most frequent access paths (for example, queries).
• What are the requirements...

Setting configuration parameters for the database server

The parameter file is the main location that's used for defining parameter values for the PostgreSQL server. All the parameters can be set in the parameter file, which is known as postgresql.conf. There are also two other parameter files: pg_hba.conf and pg_ident.conf. Both of these relate to connections and security, so we'll cover them in later chapters.

Getting ready

In the pg_settings view, the context defines when each parameter can be set. The following table categorizes this so that we can see what action is needed for changes to take effect. SET is a command, but RELOAD and RESTART are actions, not specific commands. What is RESTART ALL? Some parameters marked POSTMASTER are marked as exceptions in the following table. These parameters must be set to a value less than or equal to their setting on standby. As a result, to increase them on the primary, we must first increase...

Setting configuration parameters in your programs

PostgreSQL allows you to override some parameter settings for each session or transaction using SQL commands. Here are some examples of parameters that are designed to be user-modifiable:

• application_name—to help identify the session for monitoring
• synchronous_commit—to set the level of durability desired
• Various timeouts and check intervals:
  • client_connection_check_interval
  • idle_on_transaction_session_timeout
  • idle_session_timeout
  • lock_timeout
  • statement_timeout
• Client-tuning parameters:
  • commit_siblings
  • cursor_tuple_fraction
  • maintenance_work_mem
  • vacuum_cost_delay
  • work_mem
• Data type-specific settings:
  • bytea_output
  • DateStyle
  • xmlbinary
  • xmloption
• Optimization settings (too many to list, but not normally changed)

How to do it…

Execute the following steps to set custom parameters:

1. You can change the value of a setting during your session, like this:

   SET work_mem = '16MB&apos...

Finding the configuration settings for your session

At some point, it will occur to you to ask: What are the current configuration settings?

Most settings can be changed in more than one way, and some ways do not affect all users or all sessions, so it is quite possible to get confused.

How to do it…

Your first thought is probably to look in postgresql.conf, which is the configuration file and is described in detail in the Updating the parameter file recipe. That works, but only as long as there is only one parameter file. If there are two, then maybe you're reading the wrong file! How would you know? So, the cautious and accurate way is to not trust a text file but to trust the server itself.

Moreover, you learned in the previous recipe, Setting configuration parameters in your programs that each parameter has a scope that determines when it can be set. Some parameters can be set through postgresql.conf, but others...

Finding parameters with non-default settings

Often, we need to check which parameters have been changed, or whether our changes have taken effect correctly.

In the previous two recipes, we have seen that parameters can be changed in several ways and with different scopes. You learned how to inspect the value of one parameter or get a full list of parameters.

In this recipe, we will show you how to use SQL capabilities to list only those parameters whose value in the current session differs from the system-wide default value.

This list is valuable for several reasons. First, it includes only a few of the 200+ available parameters, so it is more immediate. Also, it is difficult to remember all our past actions, especially in the middle of a long or complicated session.

How to do it…

We write an SQL query that lists all parameter values, excluding those whose current value is either the default or set from a configuration file:

postgres=# SELECT name, source,...

Setting parameters for particular groups of users

PostgreSQL supports a variety of ways of defining parameter settings for various user groups. This is very convenient, especially for managing user groups that have different requirements.

How to do it…

Follow these steps to set parameters at various levels as per the requirements:

1. For all users in the saas database, use the following commands:

   ALTER DATABASE saas
   SET configuration_parameter = value1;

2. For a user named simon connected to any database, use the following commands:

   ALTER ROLE simon
   SET configuration_parameter = value2;

3. Alternatively, you can set a parameter for a user only when they're connected to a specific database, as follows:

   ALTER ROLE simon
   IN DATABASE saas
   SET configuration_parameter = value3;

The user won't know that these have been executed specifically for them. These are default settings, and in most cases, they can be overridden if the...

A basic server configuration checklist

PostgreSQL arrives configured for use on a shared system, though many people want to run dedicated database systems. The PostgreSQL project wishes to ensure that PostgreSQL will play nicely with other server software and will not assume that it has access to full server resources. If you, as the system administrator, know that there is no other important server software running on the system, then you can crank the values up much higher. The default values are conservative, so for many workloads, you will want a much larger value.

Getting ready

Before we start, we need to know two sets of information:

• The size of the physical RAM that will be dedicated to PostgreSQL
• The types of applications for which you will use PostgreSQL

How to do it…

If your database is larger than 128 megabytes (MB), then you'll probably benefit from increasing shared_buffers, the physical cache size. You can increase...

Adding an external module to PostgreSQL

Another strength of PostgreSQL is its extensibility. Extensibility was one of the original design goals, going back to the late 1980s. Now, in PostgreSQL 14, there are many additional modules that plug into the core PostgreSQL server.

There are many kinds of additional module offerings, such as the following:

• Additional functions
• Additional data types
• Additional operators
• Additional index types

Some extensions come preloaded with cloud services such as EDB BigAnimal, which preloads pg_stat_statements and pgaudit when selecting a PostgreSQL database. Other extensions are available from a pre-selected list, so move directly to the Using an installed module/extension recipe if using PostgreSQL alongside a cloud service.

Many tools and client interfaces work with PostgreSQL without any special installation. Here, we are discussing modules that extend and alter the behavior of the server...

Using an installed module/extension

In this recipe, we will explain how to enable an installed module so that it can be used in a particular database. The additional types, functions, and so on will exist only in those databases where we have carried out this step.

As we mentioned in the previous recipe, Adding an external module to PostgreSQL, specially packaged modules are called extensions in PostgreSQL. They can be managed with dedicated SQL commands.

Getting ready

The pg_available_extensions system view shows one row for each extension that can be installed. All you need to know is the extension name.

How to do it…

Each extension has a unique name, so it is just a matter of issuing the following command:

CREATE EXTENSION myextname;

This will automatically create all required objects inside the current database.

For security reasons, you need to do this as a database superuser. For instance, if...

Managing installed extensions

In the previous two recipes, we showed you how to install external modules in PostgreSQL to augment its capabilities.

In this recipe, we will show you some more capabilities that are offered by the extension infrastructure.

How to do it…

Here are the steps to manage extensions:

1. First, we list all the available extensions:

   postgres=# \x on
   Expanded display is on.
   postgres=# SELECT *
   postgres-# FROM pg_available_extensions
   postgres-# ORDER BY name;
   -[ RECORD 1 ]-----+----------------------------------- 
   name              | adminpack
   default_version   | 2.0
   installed_version |
   comment           | administrative functions for PostgreSQL
   -[ RECORD 2 ]-----+----------------------------------- 
   name              | pg_stat...