Golden Proportions in PostgreSQL

As CYBERTEC keeps expanding, we need a lot more office space than we previously did. Right now, we have a solution in the works: a new office building. We wanted something beautiful, so we started to dig into mathematical proportions to achieve a reasonable level of beauty. We hoped to make the building not just usable, but also to have it liked by our staff.

I stumbled upon some old books about proportions in architecture and went to work. Fortunately, one can use PostgreSQL to do some of the more basic calculations needed.

Basic rules for beauty

This is of course a post about PostgreSQL, and not about architecture-- but let me explain a very basic concept: golden proportions. Beauty is not random; it tends to follow some mathematical rules. The same is true in music. “Golden proportions” are a common concept: Let’s take a look:

We are looking at a magic number here: 1.618. It has some nice attributes. If we invert it is basically “magic number - 1”. If we square it, what we get is “magic number + 1”. If we take a line and break it up into two segments we can use 1 : 1.618. Humans will tend to find this more beautiful than splitting the line using 1 : 1.8976 or so.
Naturally we can make use of this wisdom to create a basic rectangle:

In the case of our new building, we decided to use the following size:

The basic layout is 16 x 26 meters. It matches all mathematical proportions required in the basic layout.

Inlining a semi-circle

To make the building look more appealing, we decided to add a small semi-circle to the rectangle. The question is: What is the ideal diameter of the semi-circle? We can use the same formula as before:

I have used a CTE (common table expression) here. PostgreSQL 13 has a nice feature to handle those expressions. Let’s take a look at the execution plan:

The optimizer has inlined the expression. What we get is:

Then, the optimizer does “constant” folding. All that is left in the execution plan is a “result” node which just displays the answer. The CTE has been removed from the plan entirely.

Making the front of the building more interesting

To make sure the front of the building is not too boring, we need to figure out which numbers are “allowed” and which ones are not. The way to do that is to use recursion:

The idea is to make sure that all components are related to all other components. What we do here is start with 26 meters and calculate the “long” and the “short sides”. Then we use this input for the next iteration, so that we can get a series of valid numbers which guarantee that things fit perfectly. Fortunately, PostgreSQL can do recursion in a nice, ANSI-compatible way. Inside the WITH the first SELECT will assign the start values of the recursion. Then the second part after the UNION ALL will call the recursion which is terminated by the WHERE-clause. At the end of the day, we get a sequence of mathematically correct numbers.

However, once in a while you want to see which iteration a line belongs to. Here is how this can be achieved:

row_number() is a function returning a simple number indicating the line we are looking at.

More good numbers

However, there are more “good” numbers in architecture than golden proportions. Square roots and so on are also important. To help calculate those numbers one can use a function:

Golden proportions are a good thing. However, sometimes it is not possible to use those numbers alone. If we look at some of the greatest buildings (Pantheon, etc.) in the world, we will see that the architects also worked with square roots, cubic roots and so on.

The way to do that in PostgreSQL is to use a function. However, in this case we want a function to return more than just one field. The solution to the problem is a composite data type. It can return more than just one value.

22, 27m, and 35m meters are obviously correct values which we can use to ensure beauty.

What you see is that we have used the function call for one number. However, sometimes you might want to calculate things for many values. To do that, it is important to understand how you can handle the return value of composite types:

As you can see, “a” can be used in the SELECT-clause (“target list”) directly. The result is not that readily readable-- however, it is possible to expand this field and extract individual columns as shown in the next listing:

This opens the door to calculate our numbers for an entire series of numbers:

All we did was to pass the “id” to the function and expand those columns.

Finally …

If you want to learn more about upgrading PostgreSQL, you should check out our blog post about logical replication >>