Does the order of columns in a Postgres table impact performance?

Question

In Postgres does the order of columns in a CREATE TABLE statement impact performance? Consider the following two cases:

CREATE TABLE foo (
  a      TEXT, 
  B      VARCHAR(512),
  pkey   INTEGER PRIMARY KEY,
  bar_fk INTEGER REFERENCES bar(pkey),
  C      bytea
); 

vs.

CREATE TABLE foo2 (
  pkey   INTEGER PRIMARY KEY,
  bar_fk INTEGER REFERENCES bar(pkey),
  B      VARCHAR(512),      
  a      TEXT, 
  C      bytea
);

Will performance of foo2 be better than foo because of better byte alignment for the columns? When Postgres executes CREATE TABLE does it follow the column order specified or does it re-organize the columns in optimal order for byte alignment or performance?

Answer 1

Question 1

Will the performance of foo2 be better than foo because of better byte alignment for the columns?

Yes, the order of columns can have a small impact on performance. Type alignment is the more important factor, because it affects the footprint on disk. You can minimize storage size (play "column tetris") and squeeze more rows on a data page - which is the most important factor for speed.

Normally, it's not worth bothering. With an extreme example like in this related answer you get a substantial difference:

• Calculating and saving space in PostgreSQL

Type alignment details:

• Making sense of Postgres row sizes

The other factor is that retrieving column values is slightly faster if you have fixed size columns first. I quote the manual here:

To read the data you need to examine each attribute in turn. First check whether the field is NULL according to the null bitmap. If it is, go to the next. Then make sure you have the right alignment. If the field is a fixed width field, then all the bytes are simply placed. If it's a variable length field (attlen = -1) then it's a bit more complicated. All variable-length data types share the common header structure struct varlena, which includes the total length of the stored value and some flag bits.

There is an open TODO item to allow reordering of column positions in the Postgres Wiki, partly for these reasons.

Question 2

When Postgres executes a CREATE TABLE does it follow the column order specified or does it re-organize the columns in optimal order for byte alignment or performance?

Columns are stored in the defined order, the system does not try to optimize.

I fail to see any relevance of column order to TOAST tables like another answer seems to imply.

Answer 2

As far as I understand, PostgreSQL adheres to the order in which you enter the columns when saving records. Whether this affects performance is debatable. PostgreSQL stores all table data in pages each being 8kb in size. 8kb is the default, but it can be change at compile time.

Each row in the table will take up space within the page. Since your table definition contains variable columns, a page can consist of a variable amount of records. What you want to do is make sure you can fit as many records into one page as possible. That is why you will notice performance degradation when a table has a huge amount of columns or column sizes are huge.

This being said, declaring a varchar(8192) does not mean the page will be filled up with one record, but declaring a CHAR(8192) will use up one whole page irrespective of the amount of data in the column.

There is one more thing to consider when declaring TOASTable types such as TEXT columns. These are columns that could exceed the maximum page size. A table that has TOASTable columns will have an associated TOAST table to store the data and only a pointer to the data is stored with the table. This can impact performance, but can be improved with proper indexes on the TOASTable columns.

To conclude, I would have to say that the order of the columns do not play much of role in the performance of a table. Most queries utilise indexes which are store separately to retrieve records and therefore column order is negated. It comes down to how many pages needs to be read to retrieve the data.