Column ordering SPI and annotations support for static ordering

[beikov]

Copying from the Trello card (https://trello.com/c/OITwGJ5I/211-column-ordering-spi-and-annotations-support-for-static-ordering)

Developers often use the schemas as generated by Hibernate but currently we order columns in table DDL by name which can lead to inefficient physical layouts on a few DBs due to the need for padding.

I propose we create a SPI for the ordering as well as a configuration parameter and by default order by coalesce(staticOrder, Integer.MAX_VALUE), max(physicalSizeBytes, 4), physicalSizeBytes > 2048, name to list small columns first and clobs as well as oversized columns last.
This will reduce the necessary padding on DBs like PostgreSQL.

I'd also like to add some kind of possibility to specify the order of columns through a static ordinal per column, if possible, but that would probably require a few changes to some annotations.

Note though, that a static order is required for user defined types as the SQLData interface requires data to be read/written in the order it is defined in the user type.

See https://hibernate.atlassian.net/browse/HHH-15872

[djarnis73]

Hi

I created the topic on discourse, I would love to participate, but I suspect this may be outside my skill level.

My initial idea was a somewhat simple hack where you could set a property that toggles the following feature:

If possible would just retain the order in which the fields were defined in the java class. If this is not possible (I suspect the JVM spec does not guarantee any ordering when traversing fields) then add a simple ordering annotation that can be used with a comparator to sort the non-id columns prior to generation.

I peeked around in the codebase and it looks doable without too much hassle, but I also understand that you need to keep the codebase clean without too many dirty hacks.

Best regards Jens

[beikov]

The @ColumnOrder annotation is actually an addition that allows fine grained control of the order. The main purpose for this annotation would be ordering of columns for SQL/JDBC struct types, which AFAIU requires that the Java code knows the order in which the type defines its components.

The main addition would be the SPI that allows automatic ordering based on some logic which a user configures just once. The default implementation would then order columns as outlined in the description above, which will make many users happy and can be used to solve the PK index selectivity issue Chris has in Envers due to the PK ordering.

[gavinking]

But it's terrible: I add a column to my table and in principle I might have to change an annotation on every other field. That's just awful.

[beikov]

Maybe we should split off the discussion about the static ordering as that is really only interesting for supporting struct types.

I add a column to my table and in principle I might have to change an annotation on every other field

If you decide that you want to make the column order static via annotations, then that is what will be necessary, but I really doubt anyone would do that in general. I actually believe that the few people that will use this for regular table (note the main purpose really is for struct types) will mostly define a partial order to make sure a certain column is ordered first/last.

[gavinking]

will mostly define a partial order to make sure a certain column is ordered first/last.

I agree, and that's one of the reasons why @ColumnIndex seems like such a poor fit to the problem: it's not a natural way to define a partial order.

[gavinking]

I mean, the usage example you gave is of the form: "I want columns of these types to go later". @ColumnIndex would be a truly inconvenient way to do that. Now, I can't prove to you that your example is canonical, but it feels more likely to me.

[matthiaswelz]

Even more important than ordering regular columns would be ordering the columns in the primary key (which is currently not possible).

Thus, it would make sense to also address that issue in my opinion - the order of the primary key columns should not necessarily depend on the regular column order. An annotation (or actually two annotations) would be very nice. Maybe on class-level like @ColumnOrder("col1, col2, col3") / @PrimaryKeyOrder("col2, col1, col3")?

[gavinking]

I think we could reuse the @Index annotation for this. Specifying the order of column in an index is what it's there for, don't you think?

[beikov]

How would the matching to the PK work? Through a special name or by matching column names? I think this is a good idea for this use case though.

[gavinking]

I was thinking that just matching the column names would be perfectly fine.

But sure, a special value of name would work too I guess.

[beikov]

I actually think matching the column names is great as that would allow a user to provide a name for the PK constraint.

[Naros]

name for the PK constraint

Agreed, but again I think it's important that we analyze the mapping landscapes and verify that the same information that can be reflected by the annotation is also available via the other avenues. IIRC at least in HBM, the only option you have would be explicitly specifying the index name on the individual properties. There is no concept of uniqueness afair.

[sebersole]

Let's keep in mind that we have pretty much forever not really supported tweaking the generated schema and suggest that users manage their schema themselves if they want specific things. Personally, I've never worked on a real system that would use the schema created by any tool.

I'm not particularly against this, but it just seems like unnecessary overhead/complexity.

I peeked around in the codebase and it looks doable without too much hassle...
this may be outside my skill level.

Those 2 statements are mostly mutually exclusive ;)

which is currently not possible

But it is - by managing the creation scripts yourself which has generally been the supported approach.

[gavinking]

Right, an API is great. If users wish to invent a crappy @ColumnOrder annotation than they can do that. They're programmers; they can write code. And I agree with the suggestion on the original Trello card to just put LOBs last by default. Sounds reasonable.

On the other hand, I'm much more concerned about this issue of whether the user has control over the order of columns within the PK declaration. That seems pretty important, though I really have not thought it through at all.

[beikov]

Yeah, the PK stuff is a related concern, but I thought you had an idea on how to fix this through @Index?

[matthiaswelz]

Regarding the PK order:

How about just using the existing @Index annotation and when it is used with all columns that are part of the PK (and unique=true), then it changes the order of the PK key columns? Right now, an @Index that is specified like that will be silently ignored because Hibernate assumes it to be equal to the PK (even if the order is different).

That would be much more elegant than the @ColumnOrder for the PK part of this issue in my opinion.

[gavinking]

Sorry, I was writing too fast on my phone on the metro earlier. Just wanted to say:

• An API where you can plug in your own strategy is good—it's not even really new configuration of Hibernate itself, it's really just a parameter of SchemaExport and no more. So that's in principle pretty low-impact and very flexible.
• Doing something potentially-helpful by default is also a fine idea: at least, it surely can't do any harm!
• Yes, I still think using @Index to order the primary key columns is the most natural way to solve the problem of PK column ordering. And I think this one is actually pretty important.

The only bit I was objecting to was @ColumnIndex, which just seems really verbose/fragile.

[Naros]

ow about just using the existing @Index annotation and when it is used with all columns that are part of the PK (and unique=true), then it changes the order of the PK key columns?

I would strongly caution that whatever we do annotation-wise that there is a comparable solution for XML mappings. I know in HBM that an application can specify an index name on the property mappings in the primary key; however, I don't believe there is anything that can be passed to indicate uniqueness currently. So if unique=true is mandatory for ORM to ignore the index, let's make sure that such a solution is portable across all mapping landscapes.

Doing something potentially-helpful by default is also a fine idea:

Exactly, I think it's really important we do not lose sight of the user experience in upgrading. The more difficult the upgrade path ends up being or aspects that go against older behavior will only slow down adoption and ideally we should do anything possible to avoid that and to make that upgrade process as streamlined and easy as possible.

[gavinking]

list small columns first and clobs as well as oversized columns last

This part of the proposal I must admit I kinda like.

I propose we create a SPI for the ordering as well as a configuration parameter

Grumble another damn property!

But yeah, despite my grumble, I ultimately do think this makes sense.

specify the order of columns through a static ordinal per column

I'm not terribly keen on this part of the proposal.

[beikov]

I'm not terribly keen on this part of the proposal.

Any better idea for this? Struct support kinda needs this to be useful for existing schemas.

[gavinking]

So I have not changed my opinion that annotating fields of entities with integers is inelegant, verbose, and extremely fragile (if I add a field, I need to renumber existing fields).

The two concrete use cases for this that we know of are:

1. sorting column by type, and
2. mapping fields of an embeddable class to a SQL struct type.

The first problem is, I believe, much better solved via a plugin SPI which allows for sorting based on column type.

As to the second problem, it seems to me that it's much more natural to map the struct type to either:

• a Java record type, or
• a constructor of an embeddable class.

Fortunately, we've recently added this Instantiator stuff which opens the door to treating records and constructors as embeddables.

So you would stick the @Struct annotation on the record type or on the constructor.

[beikov]

I agree with the solutions you mention and I think that this is how this should be used ideally. I'll try to make this work that way, but I fear that we might still need some sort of canonical way to configure an explicit order for struct type. Let's see how far I can get without an explicit API though.

[djarnis73]

Would an SPI solution allow for an implementer to build the inelegant integer (or similar crude) solution?

I'm have no real knowledge of the hibernate internals, but I work on a project where we drive our data model through JPA combined with flyway. We generate DDLs for H2 and mySQL with hibernate and then we tweak the model with flyway (we even add partitioning to the mysql model, which is used in production, where h2 is used for basic unit testing).

This approach does have its drawbacks but also some advantages and if there was a way for us to tweak column ordering (for instance by implementing the mentioned SPI) it would be a huge benefit, even if it was not the most clever/beautiful solution.

[gavinking]

Would an SPI solution allow for an implementer to build the inelegant integer (or similar crude) solution?

Yes, that's the idea.

[gavinking]

a constructor of an embeddable class.

yeah so I think I got this one wrong. I can make it work if the SQL struct/composite type is already defined in the database. (At least if I ask the user to add a de-constructor method to the embeddable class.)

But it does not work for generating the DDL which defines the composite type. I missed that. At least not without the sort of byte code analysis that Christian was talking about on the other discussion.

Given that, I concede the necessity for some sort of annotation to indicate column order. My personal aesthetic preference would be something like:

@ColumnOrder({“First”,”Second”})

(where the quoted strings are column names) since I think it’s more consistent with how some of the other annotations work. And also because it looks more like an ordered list.

[beikov]

For struct types we drive the order through the constructor now. Record classes have a natural ordering and for regular classes we now have the @Instantiator annotation. I understand that there is still the need for explicitly ordering primary key columns somehow, as the column order is important for the index that is created behind the scenes to allow efficient ordering a partial filtering.

I propose adding a @PrimaryKey annotation for this purpose, which would also allow naming the primary key constraint. Wdyt @gavinking, @djarnis73 and @quaff?

Other than that, I think there is no need for explicit column ordering.

[gavinking]

I propose adding a @PrimaryKey annotation for this purpose, which would also allow naming the primary key constraint.

Why can't @Index work for this?

[beikov]

Hmm, it could I guess. So if we find an index that mentions all primary key columns and is unique, the we can treat that as definition for the primary key constraint. Sounds good to me.

[djarnis73]

For my personal needs (which is a general way to control column ordering for table generation) I would prefer the @ColumnOrder({“First”,”Second”}) that @gavinking describes above (and then use the same ordering when creating the primary key). I believe that would be a more flexible solution.

But from a hibernate maintenance/documentation perspective it may not be intuitive how the pk is ordered.

[gavinking]

So the currently-proposed @Instantiator annotation works like this:

        @Embeddable
        @Struct(name="my_point_type")
	public static class Point {

		private final String y;
		private final long z;
		private final Integer x;

		@Instantiator({"y","z","x"})
		public Point(String y, long z, Integer x) {
			this.y = y;
			this.x = x;
			this.z = z;
		}

		public String getY() {
			return y;
		}

		public Integer getX() {
			return x;
		}

		public long getZ() {
			return z;
		}
           ...
     }

where the names that occur in the list are names of Java fields.

That is, it's telling Hibernate which constructor parameters get assigned to which fields by the constructor. It's providing information about the internal impl of the Java class, not about how it relates to the the relational schema.

My not-necessarily-100%-rational prejudice here is that this is a rather big break from how all our other annotations work, and wasn't what I had envisaged.

I think that our annotations usually work by mapping Java elements to database tables or columns. They don't usually provide information about how the Java class is implemented.

So I think this should be changed so that @Instantiator lists column names, not field names.

(Unless someone can convince me that that would be worse somehow.)

[gavinking]

An option which I think I prefer at least as an interim solution, because I'm not happy with the @Instantiator annotation is:

        @Embeddable
        @Struct(name="my_point_type", attributes={"y","w","x"})
	public static class Point {

		private final Integer x;

		private final String y;

                @Column(name="w")
		private final long z;

           ...
     }

Where the @Struct annotation would define the name of the UDT and of its members, and it would be our job to correlate the fields of embeddable class with the attributes of the UDT based on the mapped column names.

[jilvin]

What's the status of this issue? It is marked CLOSED at https://hibernate.atlassian.net/browse/HHH-15872 .

[beikov]

org.hibernate.boot.model.relational.ColumnOrderingStrategy was introduced for this purpose which can be used to order columns. The default implementation will order by column size to get the best data alignment, just like described in the main description.

[jilvin]

@beikov Thanks a lot for the reply. 👍 :)