PostgreSQL has a sophisticated security system capable of handling complex policies and user permissions are an essential component of every productive setup many people rely on. However, over the past couple of years I have noticed that many users fall into the same trap: The “public” schema.

Since I have started with PostgreSQL almost 15 years back I have adjusted shared memory settings literally hundreds of times. Changing kernel parameters has always been a central task for all users. The reason for this was mostly that many UNIX systems restricted the use of shared memory by default (Linux, Solaris, etc.). People who […]

After digging through some customer source code yesterday I thought that it might be worth publishing a post about space consumption and enum types. It seems to be quite common to store status information as text fields. This is highly inefficient.

If a good programmer is facing a technical problem, he will always try to abstract the problem. In a procedural language such as C people will create typedefs, functions and so on – in object oriented programming people will simply create objects. But what about data type abstraction in SQL? Most people seem to miss […]

When you are installing a database server, PostgreSQL is not the only thing, which can be tuned. The operating system will also provide a handful of switches capable of improving performance nicely. If you are using Linux, the I/O scheduler of the operating system can be something worth investigating.

Histograms are a common way for a relational database to store statistical information about data. What is this kind of statistical information good for? Well, statistics are the rocket fuel behind performance. If statistics are bad, the optimizer will come up with bad decisions and poor execution plans will be the result. The PostgreSQL optimizer […]

“Waiting is the slowest way to execute an operation” – this is how a professor at my university in Vienna usually described bad locking and low concurrency. He could not have been more right. The same applies to performing calculations: The fastest way to calculate something is to try to skip it entirely. In this […]

Just like most other database systems I am aware of PostgreSQL providing a feature called tablespaces. In general a tablespace is a collection of storage files residing in a certain mount point. The main idea of a tablespace is to give administrators a chance to scale out their I/O and to make things more efficient. […]