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?

To show which impact this can have, we can create a small table consisting of just integer values: (more…)

One tiny little feature many users of PostgreSQL have often forgotten is the ability to create similar tables. It happens quite frequently that you want to create a table, which is just like some other one. To achieve that most people will do ...

CREATE TABLE x AS SELECT ... LIMIT 0;

This works nicely, but what if you got 20 indexes and 50 default values around? Clearly, it can be painful to add all that later on. (more…)

Everybody needs a little toy to play with, so I thought: Why not buy a toy helping me to get rid of my energy bill? So, I ordered a 10.5 kWp photovoltaic system for my house. The system was shipped with a Kostal Pico inverted to make sure electricity can be used directly by the grid.
Kostal offers some additional device which allows people to chart your electricity production over time. But, why pay if you can do it yourself using some little shell script and most important: PostgreSQL

As there is hardly code available on the net showing how to access Kostal Pico, I decided to publish this one: (more…)

UPDATE 2023: for a more recent post, see Laurenz Albe's blog on pgbouncer authentication

PostgreSQL has proven to be an excellent database for web applications of all kinds. The important thing to notice is that many web applications will cause load which is quite different from what a normal desktop application would cause.
A web application will typically open a connection, fire some very short queries and disconnect. The SQL involved is usually quite simple – so, why care? Well, you must not forget that opening and closing a database connection over and over again is (compared to trivial SQL) not free, but overhead worth thinking about.

To solve the problem you can turn to using a connection pool. pgbouncer will be exactly what you have been looking for. The key advantage is that pgbouncer is an extremely lightweight solution to the problem of PostgreSQL connection pooling. With a reported overhead of around 2k per connection this is more than true. (more…)