The order of columns in a Table does matter?

Most of the time it does not matter. However, the order of columns in a DB might impact the physical storage of the data. Columns are ordered in the way they were added to the table.

For example in PostgreSQL if you have a table like this:

CREATE TABLE tbl1 (
    col1 smallint    -- smallint needs 2 bytes
  , col2 bigint      -- bigint needs 8 bytes
} 

+---+---+---+---+---+---+---+---+---+---+----+----+----+----+----+----+
|  2 bytes  |  6 bytes of padding   |           8 bytes               |
+---+---+---+---+---+---+---+---+---+---+----+----+----+----+----+----+
|  smallint |        padding        |            bigint               |
+---+---+---+---+---+---+---+---+---+---+----+----+----+----+----+----+

The smallint column will be padded to the same size as the bigint column due to a mechanism called alignment padding. This wastes both storage and performance since the additional bytes need to be read and written. Of course, the impact on smaller databases will be negligible. But in big databases, this might be a noticeable difference. If you now put the bigint column first you save 6 bytes per row.

Keep in mind that after a table is created columns cannot be reordered. Most of the times the only way is to create a new table with the desired column order and SELECT the data INTO it.

Hibernate 6.2 ColumnOrderingStrategy

Hibernate v6.2 introduced a new interface ColumnOrderingStrategy which has two implementations:

• ColumnOrderingStrategyLegacy

• ColumnOrderingStrategyStandard

ColumnOrderingStrategyLegacy is essentially a no-op implementation and should resemble the behavior before Hibernate v6.2 where columns were basically ordered alphabetically.

ColumnOrderingStrategyStandard is the new default used strategy from v6.2 onwards. It sorts columns roughly by this algorithm:

order by max(physicalSizeBytes, 4), physicalSizeBytes > 2048, name

If you want to go back to the old behavior you can set the strategy by setting the hibernate configuration property hibernate.column_ordering_strategy the the value legacy. If you are configuring your hibernate properties in Java code you can use the AvailableSettings.COLUMN_ORDERING_STRATEGY constant.

Own implementations

You can create your own ColumnOrderingStrategy and use it.

One way to implement our own ColumnOrderingStrategy is to implement the interface ColumnOrderingStrategy and its 4 methods:

public interface ColumnOrderingStrategy {
    List<Column> orderTableColumns(Table table, Metadata metadata);
    List<Column> orderConstraintColumns(Constraint constraint, Metadata metadata);
    List<Column> orderUserDefinedTypeColumns(UserDefinedType userDefinedType, Metadata metadata);
    void orderTemporaryTableColumns(List<TemporaryTableColumn> temporaryTableColumns, Metadata metadata);
}

This is the way that gives us the most control over the ordering process. We can define sorting for all columns in a Table, Constraint columns (PKs, FKs), UserDefinedType columns and temporary tables.

The methods are easy to implement. The methods returning List<Column> can return null and the last method can be a no-op. This is the way the legacy strategy is implemented - see the source on GitHub.

Thus If we want basic sorting of columns it is enough to extend the ColumnOrderingStrategyLegacy and override the method List<Column> orderTableColumns(Table table, Metadata metadata). Below you see an example of how to sort columns alphabetically.

package com.example.demo;

import java.util.Comparator;
import java.util.List;
import java.util.Map;

import org.hibernate.boot.Metadata;
import org.hibernate.boot.model.relational.ColumnOrderingStrategyLegacy;
import org.hibernate.cfg.AvailableSettings;
import org.hibernate.mapping.Column;
import org.hibernate.mapping.Table;
import org.springframework.boot.autoconfigure.orm.jpa.HibernatePropertiesCustomizer;
import org.springframework.context.annotation.Configuration;

@Configuration
public class AlphabeticalColumnOrderingStrategy 
    extends ColumnOrderingStrategyLegacy 
    implements HibernatePropertiesCustomizer{

    @Override
    public List<Column> orderTableColumns(Table table, Metadata metadata) {
        return table.getColumns().stream()
            .sorted(Comparator.comparing(Column::getName))
            .toList();
    }

    @Override
    public void customize(Map<String, Object> hibernateProperties) {
        hibernateProperties.put(AvailableSettings.COLUMN_ORDERING_STRATEGY, this);
    }

}

Note that the interface HibernatePropertiesCustomizer is a SpringBoot-specific interface that provides the customize method to register the current class as the one to use for ordering the columns.

Testing

Testing was done using

• Spring Boot v3.1.4

• Hibernate v6.2.9

• MySQL Community Server v8.1.0

  We use this Entity for testing:

@Entity
@Table(name = "employee")
public class Employee {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    Long id;

    @ManyToOne
    @JoinColumn(name = "id_department")
    Department department;

    @Column
    String firstName;

    @Column
    String lastName;

    @Column
    Integer age;
}

The Hibernate 6.2 default behavior

We can set spring.jpa.properties.hibernate.column_ordering_strategy=default or don't set it at all and get the following DDL generated by Hibernate:

create table employee (
    age integer,
    id bigint not null auto_increment,
    id_department bigint,
    first_name varchar(255),
    last_name varchar(255),
    primary key (id)
 ) engine=InnoDB

The column age is first because an integer in MySQL takes 4 bytes. Next, the two bigint take 8 bytes and last the varchar columns take 16kB each.

The legacy behavior

We set spring.jpa.properties.hibernate.column_ordering_strategy=legacy and get the following DDL generated by Hibernate:

create table employee (
    id bigint not null auto_increment,
    age integer,
    first_name varchar(255),
    last_name varchar(255),
    id_department bigint,
      primary key (id)
) engine=InnoDB

Here the primary key column id is first, the foreign key to the department is last and the remaining columns are alphabetically sorted.

Our own AlphabeticalColumnOrderingStrategy

See the code above. Now we get the following CREATE Statement:

    create table employee (
        age integer,
        first_name varchar(255),
        id bigint not null auto_increment,
        id_department bigint,
        last_name varchar(255),
        primary key (id)
    ) engine=InnoDB

Summary

Ordering columns by their physical size on the disk might improve the performance and disk space used in your database if you have lots of data.

Ordering columns might also improve the readability of the data in the tool you use to browse the DB. When you execute a SELECT * FROM MyTable you get the resultset in the order the columns were created/added to the database. With bigger tables, if the columns are ordered alphabetically it is easier to find the data we want. People may have also other requirements for the order of columns.

You might for example name your foreign key columns starting with id. After that, you could sort your columns by first ordering columns starting with id. This way you'd have your primary key and foreign keys first when you open a table in your DB Tool.