Setting PostgreSQL configuration parameters

A lot has been written about configuring postgresql.conf, postgresql.auto.conf and so on. However, sometimes it requires to take a second look in order to understand, how PostgreSQL really handles configuration parameters. You will notice that PostgreSQL configuration offers more than meets the eye at first glance. So let us dive into PostgreSQL GUCs and configuration on a more theoretical level!

postgresql.conf: The classical method

Most people will directly change settings in postgresql.conf silently, assuming that this is the place to change PostgreSQL configuration parameters. However, this is not the only place you can use. The purpose of this blog is to show you which other options you have and how you can use these features to make your database configuration better.

For the sake of simplicity, I will use an easy configuration parameter to demonstrate how PostgreSQL operates:

The first thing you have to learn is how to figure out where configuration parameters actually come from. To do that, take a look at the pg_settings view:

postgresql.conf allows to include files. The idea is to give users the chance to break up postgresql.conf into smaller chunks.

postgresql.conf and included files

The rule here is simple: If your parameter is used inside a configuration file more than once, the LAST entry is going to be taken. In general, a parameter should only be in a config file once, but in case an error happens, you can be sure that the last entry is the one that counts.

Understanding ALTER SYSTEM

After the builtin settings, after taking what there is in postgresql.conf and after taking those include files into account, PostgreSQL will take a look at postgresql.auto.conf. The main question is: What is postgresql.auto.conf? It happens quite frequently, that administrators don’t have full access to the system (e.g. no SSH access). In this case superusers can take advantage of ALTER SYSTEM, which allows you to change PostgreSQL parameters using plain SQL. Here is how it works:

If you run ALTER SYSTEM, the database will made changes to postgresql.auto.conf:

These values will have precedence over postgresql.conf.

Builtin settings

As you can see, the parameter is now GMT. This is the default value set by the PostgreSQL binaries, in case there are no configuration parameters at all.

However, in many cases you don’t want to set a value permanently. For instance, you might only want to set it during maintenance mode. Maybe you want to start PostgreSQL on a different port to manually, while fixing a problem, to lock out users. In this case you can pass parameters via pg_ctl directly:

Using ALTER DATABASE SET …

In 80% of cases it is totally enough to either take the built-ins, postgresql.conf, or postgresql.auto.conf. Using -o is already quite rare. However, there is a lot more. Sometimes you want your configuration to be way finer grained. What if a parameter should only be used inside a specific database? Here is how it works:

 After reconnecting to the database, you will see that the value is set correctly:

Not all changes can be made at the database level. Things such as “shared_buffers”, “port” can only be changed at the instance level and are not possible at the database level anymore, as shown in the next example:

ALTER USER … SET …

So far changes have been made to postgresql.conf, postgresql.auto.conf, on startup as well as on a per-database level. However, how about specific users? To do that, consider ALTER USER … SET …:

 After a reconnect the value will be shown:

ALTER USER … IN DATABASE … test …

But what if this is still not fine-grained enough? What if you only want to set a value for a user inside a transaction? PostgreSQL can even do that:

After a reconnect the value will be shown:

Why is this kind of configuration useful? Suppose you are using a “datawarehouse” user to run some specific aggregations in of the databases. These specific operations might need special memory parameters, such as work_mem, to be efficient.

Changing PostgreSQL parameter at the session level

Sometimes hardwiring configuration settings is still not flexible enough. In PostgreSQL configuration, parameters can even be changed on a per session level. But be careful: This seemingly simple feature is highly sophisticated. The important thing to consider, is that in PostgreSQL everything is transaction. This includes PostgreSQL configuration parameters, as you can see in the next example:

What you can see, is that PostgreSQL even takes savepoints et cetera into account. If a transaction is not committed, the configuration parameters will be rolled back.

Assigning parameters to functions

After this introduction, there is a final feature I want to share: Parameters can be assigned to functions. Consider the following scenario:

The problem is that a “day” is not the same everywhere on the planet. So let us assume you want to calculate the turnover of every office per day. You can basically assign the timezone setting to each of those functions. Every function could run in a different timezone within the same SELECT statements.

CREATE FUNCTION shows how a setting can be passed to a function:

Finally ...

Configuring PostgreSQL parameters is really way more powerful than most users recognize. There are many ways to set parameters and it makes sense to explore these options to optimize your configuration. If you want to learn more about PostgreSQL configuration, you might want to check out my post about configuring parallel index creation.