Waiting for PostgreSQL 12 – Log all statements from a sample of transactions

On 3rd of April 2019, Alvaro Herrera committed patch:

Log all statements from a sample of transactions
 
This is useful to obtain a view of the different transaction types in an
application, regardless of the durations of the statements each runs.
 
Author: Adrien Nayrat

Continue reading Waiting for PostgreSQL 12 – Log all statements from a sample of transactions

What logging has least overhead?

When working with PostgreSQL you generally want to get information about slow queries. The usual approach is to set log_min_duration_statement to some low(ish) value, run your app, and then analyze logs.

But you can log to many places – flat file, flat file on another disk, local syslog, remote syslog. And – perhaps, instead of log_min_duration_statement – just use pg_stat_statements?

Well, I wondered about it, and decided to test.

Continue reading What logging has least overhead?

Waiting for 9.5 – Add cluster_name GUC which is included in process titles if set.

On 29th of June, Andres Freund committed patch:

Add cluster_name GUC which is included in process titles if set.
 
When running several postgres clusters on one OS instance it's often
inconveniently hard to identify which "postgres" process belongs to
which postgres instance.
 
Add the cluster_name GUC, whose value will be included as part of the
process titles if set. With that processes can more easily identified
using tools like 'ps'.
 
To avoid problems with encoding mismatches between postgresql.conf,
consoles, and individual databases replace non-ASCII chars in the name
with question marks. The length is limited to NAMEDATALEN to make it
less likely to truncate important information at the end of the
status.
 
Thomas Munro, with some adjustments by me and review by a host of people.

Continue reading Waiting for 9.5 – Add cluster_name GUC which is included in process titles if set.

What is the overhead of logging?

update at the end of the post!

There obviously is one – after all, logging information has to be more expensive than not logging it. But how big is it? And more importantly, what is the difference between logging to stderr/file, csvlog and syslog? And what about syslog to remote machine?

Let's see.

Continue reading What is the overhead of logging?

Understanding postgresql.conf : log*

After loooong pause, adding next (well, second) post to the “series“. This time, I'd like to describe how logging works. And I don't mean binary logging (WAL), but the log for us, humans, to read.

Before I will go to the postgresql.conf options, let me first describe what happens when PostgreSQL wants to log something.

Continue reading Understanding postgresql.conf : log*

Logging queries – how?

One of the questions that pop up frequently on IRC is how to see queries are now executed on the server, and what queries were earlier.

Theoretically answer to this is simple – pg_stat_activity and log_min_duration_statement. Or log_statement. What is the difference? That's exactly why I'm writing this post.

Continue reading Logging queries – how?