JSON logs in PostgreSQL 15

As of version 15, PostgreSQL offers you the ability to write the server log in JSON format. This feature has been desired for a long time and has finally made it to PostgreSQL core. In this post we will discuss how JSON logs can be configured and what this brand new feature does for users.

Configuring JSON logs in PostgreSQL

If you want to ensure that your database server produces log in JSON format, you first have to make some adjustments to postgresql.conf or user ALTER SYSTEM to make changes to postgresql.auto.conf.

Basically, a handful of settings are needed to teach PostgreSQL what we want:

First of all, you need to adjust the log_destination to “jsonlog”. The default setting here is stderr which simply writes logs in the standard text format which has been in use for years. In most cases, we want the log to be written to a file, so we turn the logging collector on. Voila, we are done. To enable log rotation, I changed the log_filename to postgresql-%a.log which ensures that we produce a logfile for every day of the week.

Once this is done, a simple restart will enable those settings. Actually, jsonlog does not require a restart – however, enabling the logging collector does. Usually this does not cause any additional downtime because those settings are in most cases set when the server is deployed. Turning the logging collector on later is the exception rather than the rule.

Checking the JSON logs

The server is now going to produce the desired files. What you'll see are actually two files:

Why is that the case? Let's dive in and figure out what's going on:

The .log file is created before the JSON machinery starts. You'll simply find two lines in there – nothing more. The bulk of the log will be sent to the .json file, which is the expected behavior:

Use a tool to read JSON logs

Reading a tightly packed file containing millions of JSON documents is not really user-friendly, so I recommend using a tool such as “jq” to make this stream more readable and easier to process:

Check system messages

What's really important is that a line of JSON does not always contain the same number of fields. System messages, etc will show different information than query-related stuff. This is done for efficiency reasons, as the following log snippet reveals:

The second message contains information about the database (which is not relevant to the checkpoint message before), the user and a lot more. You need to be aware of this when processing the JSON document for later use.

JSON logs: Efficiency matters

Keep in mind that producing log in JSON format is a lot more verbose than, say, the standard text log. If you happen to log system events and errors only, this is not that much of an issue. However, it is a major problem if you happen to log every single query. You need to be aware of the fact that producing millions and millions of lines does impact performance and space consumption.

Finally …

PostgreSQL offers many additional features which are particularly useful if you are dealing with a large data set. One of those features is called “GROUPING SETS”. I've written a blog to help you understand how cool this feature really is. Check out my performance-related presentation about GROUP BY CUBE on YouTube which will give you a quick insider view.

If you want to start building PostgreSQL applications more quickly, I also recommend checking out our CYPEX video tutorials. The tutorials will help you to build powerful replacements for Oracle APEX and Oracle Forms.