UPDATED July 2023: 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. (more…)

Since I started with PostgreSQL almost 15 years back, I've 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 were new to running databases (Oracle, DB2, PostgreSQL, etc.) got a decent share of frustration when configuring shared memory on the operating system side. (more…)

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. (more…)

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 this possibility completely. (more…)

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. (more…)

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 is a damn sophisticated piece of software and it provides end users with good plans in general – still, without statistics even the best SQL optimizer will yield bad performance. (more…)

“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 posting we want to focus on the first half of the story – minimizing locking. (more…)

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. Typically a tablespace makes a lot of sense if you want to access more than just one I/O subsystem. (more…)