Category Archives:Hans-Juergen Schoenig

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…)

20 years ago it was enough for a database to simply check if one string was identical to some other string. Those times are long gone and thus several algorithms to do fuzzy string matches have been developed over time. Many of these mechanisms, such as trigrams, regular expressions, soundex and so on are already available in PostgreSQL.
Those algorithms we currently got in PostgreSQL, have one thing in common: They all work for fairly short strings, such as names, addresses and so on. Recently I stumbled over the problem of having to come up with some algorithm to do Nearest-Neighbour-Search for slightly longer texts (maybe the size of a paragraph or a page).
Trigrams are clearly not feasible to solve this problem in PostgreSQL, and logically the same applies to regular expressions (how would this ever work?). I did some research on this subject and found out about a nice and pretty simple algorithm to check text similarity: Cosine similarity.

I borrowed some Python code and hacked up some stored procedure to see how things work. To make it short: Those results are pretty promising if you ask me: (more…)

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…)

Virtualization has been one of the pre-dominant topics in the past couple of years. Not going to the cloud is considered to be uncool – at minimum it feels like being stake in the stone age of computing.

What does going to the cloud actually mean? First of all it means “virtualization”. This is cool from many points of view but what does it mean for performance? Virtualization usually does not good, this was clear from the start. (more…)