Reducing space consumption

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.

Here is an example:

Unless we have defined a check constraint we can add any data we want:

The problem I want to point out here is that a string needs a fair amount of space. In addition to that, it is pretty likely that there is only a handful of status flags available anyway. Just imagine that you have to store billions of rows – a couple of bytes can definitely make a difference.

Space optimization

To reduce the amount of space needed, we can create an enum-type. The advantage here is that we need just one byte to store up to 255 different values. An enum is also an implicit “check constraint” making sure that only those values we really want are allowed:

In our example three values are allowed.

We can now use this type in our table:

When we try to insert data, we will say that bad rows are not accepted:

Enum data types are really a nice way to model a finite list of textual values in an efficient way. One cool feature is also that those types can preserve order. What does that mean? It basically means that if you sort an enum column, data will be returned in the same order as the enum.

Changing enums later on in the process is definitely possible. ALTER TYPE provides a nice interface to add values to an enum type.

In order to receive regular updates on important changes in PostgreSQL, subscribe to our newsletter, or follow us on Facebook or LinkedIn.