In 99% of all cases the PostgreSQL planner does a perfect job to optimize your queries and to make sure that you enjoy high performance and low response times. The infrastructure ensuring this is both sophisticated as well as robust. However, there are some corner cases which can turn out to be quite nasty. In […]

When it comes to performance, people tend to forget some basic topics entirely. One of those topics is the impact of writing log files. No, in a typical OLTP workload the creation of log files is definitely not free (in terms of performance). Quite the opposite – having to write millions of lines into a […]

It’s commonly known that setting max_connections to an insanely high value is not too good for performance. Generations of system administrators have followed this rule. Just out of curiosity I thought I’d give it a try to see which impact setting max_connections in PostgreSQL  really has. UPDATE 2023: See also this more recent blog post […]

After testing shared_buffers recently, I decided to do a little more testing on our new office desktop PC (8 core AMD CPU, 4 GHZ, 8 GB RAM and a nice 128 GB Samsung 840 SSD). All tests were conducted on the SSD drive. This time I tested the impact of maintenance_work_mem on indexing speed. To get […]

A lot has been said about PostgreSQL shared buffers and performance. As my new desktop box has arrived this week I decided to give it a try and see, how a simple benchmark performs, given various settings of shared_buffers. The first thing to run the test is to come up with a simple test database. […]

I have been wondering a while about the optimal block size in PostgreSQL. Usually the default value of 8kb has proven to be beneficial for most applications. However, after listening to some talk about MonetDB, I got interested in testing various workloads for different block sizes – especially with blocksizes larger than PostgreSQL’s current maximum […]

It happens quite frequently that people complain about stored procedure performance in PostgreSQL. In many cases the reason for bad performance can be explained quite nicely when looking at the definition of a function. In general, a PostgreSQL function can be marked as follows: VOLATILE, STABLE, IMMUTABLE or [NOT] LEAKPROOF. What does this actually mean? […]

The optimizer uses several parameters to optimize queries. Those parameters have been constant for many years in the past. This was fine for most users. Recently we have seen a couple of systems which were already fully based on SSD disks. SSDs have a nice advantage over traditional disks: Random disk access is not a […]