CYBERTEC Logo

Setting PostgreSQL configuration parameters

12.2019 / Category: / Tags: |

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.

0 0 votes
Article Rating
Subscribe
Notify of
guest
3 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Kaarel
Kaarel
4 years ago

Just a small addition - not to forget about the SET LOCAL syntax of transaction level setting of parameters:

BEGIN;
SET LOCAL timezone = ... ;
COMMIT;

Ach So
Ach So
4 years ago

This article didn't display correctly on Chrome/Android (neither mobile not desktop view). There is nothing between colon and next sentence, when explaining -o, ALTER SYSTEM and other below explanations with colon (images not loading?).

Hans-Jürgen Schönig
Hans-Jürgen Schönig
4 years ago
Reply to  Ach So

wordpress had eaten the codes for some reason. i just fixed it

CYBERTEC Logo white
CYBERTEC PostgreSQL International GmbH
Römerstraße 19
2752 Wöllersdorf
Austria

+43 (0) 2622 93022-0
office@cybertec.at

Get the newest PostgreSQL Info & Tools


    This site is protected by reCAPTCHA and the Google Privacy Policy & Terms of Service apply.

    ©
    2024
    CYBERTEC PostgreSQL International GmbH
    phone-handsetmagnifiercrosscross-circle
    linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram