It does matter what the column order is in PostgreSQL.

Preamble

Creating a large table

I’ve only used 4 columns here for simplicity’s sake. The string we just compiled can be executed using gexec once the command has been produced. A highly useful feature of gexec is that it treats the prior result as SQL input, which is exactly what we need in this case. We are left with a table that has four columns. 

But let’s abandon the table and build a massive one instead.

Create an extremely wide table

The statement that comes after produces a table with 1500 columns. Remember that there are a maximum of 1600 columns:

Such a table is rarely effective in real life and shouldn’t typically be utilized to store data. Simply put, it will add too much overhead, and the majority of the time, the modeling is poor to begin with.

Let’s fill the table with 1 million more rows.

The default values in the table let us know that the columns do, in fact, contain something. In order to ensure that all hint bits and similar items are set, I have finally run VACUUM.

The following line can be used to quickly calculate the size of the newly formed table, which is around 4 GB in size:

Accessing various columns

Rows are how PostgreSQL stores data. As you may be aware, data can be stored either row- or column-oriented. The benefits of either strategy may depend on your use case. A row-based method is typically far more effective in the case of OLTP.

We’ll count (*) the seconds and see how long it takes:

We can complete the query in about 400 milliseconds, which is good. The optimizer will perform a parallel sequential scan as anticipated.

Comparing this to a count in the first column will help. Performance will differ just a little bit. The rationale is that count(*) must verify the row’s existence, whereas count(column) must determine whether or not a NULL value was supplied to the aggregate. If the value is NULL, it must be ignored:

Let’s check out what happens if we access column 100, though. There will be a big difference in how long that takes.

Execution times have nearly doubled. If we run a count on column 1000, the performance is considerably worse:

Wow, we’ve already slowed down 20 times compared to before. This is not a small difference, so it is important to understand what is going on.

Debunking PostgreSQL performance issues: column order

Consider the way PostgreSQL stores data in order to see why the issue arises in the first place: Following the tuple header, which appears in each row, there are a few varchar columns. Here, we’ve only utilized varchar to demonstrate the idea. The same problems will arise with other data types as well; however, because varchar has greater internal complexity than, say, integer, the issue is more obvious.

How can I access a column in PostgreSQL? To determine the location of the requested column within the row, it will first fetch the row and then analyze this tuple. Therefore, if we wish to access column #1000, we must determine how lengthy the first 999 columns are in actuality. This might be really complicated. For an integer, we just need to add 4, but for a varchar, the procedure becomes very expensive. Let’s look at PostgreSQL’s varchar storage method to understand why it is so expensive:

• A single bit designates a string as short (127 bytes) or lengthy (> 127 bits).
• length of 7 bits or 31 bits (depending on the first bit)
• “data” + 0 (to terminate the string ) (to terminate the string) 
• alignment (to ensure the following column starts at a multiple of CPU-word length) (to make sure the next column starts at a multiple of CPU-word length)

Think about the implications if we need to loop over 1000 columns. It does result in some significant overhead.

Finally …

About Enteros

​​​​Enteros offers a patented database performance management SaaS platform. It proactively identifies root causes of complex business-impacting database scalability and performance issues across a growing number of clouds, RDBMS, NoSQL, and machine learning database platforms.​

​