Allow marking partition columns

[beikov]

The idea is, to introduce an annotation e.g. @PartitionColumn, which can be used to mark persistent attributes/columns that are partition columns. The effect of the annotation is, that update and delete statements executed through entity flushing will mention the initialState of partition columns in the where clause, which will allow the query planner of the database to do partition pruning and target the correct partition table instead of possibly looking into every possible partition table.

We could of course go one step further and explore adding support for application based partitioning, but I don't think this is reasonable, given that all major databases support partitioning natively already.

It might be interesting though to generate DDL for the partitioning type and maybe also allow configuring initial partitions or so. A portable way to declare common partition types could be done through the following annotations:

• @PartitionByRange({ @RangePartition(tableName = "...", from = "...", to = "...") })
• @PartitionByList({ @ListPartition(tableName = "...", keys = {...}) })
• @PartitionByHash({ @HashPartition(tableName = "...", modulus = 3, remainder = 0) })

The DDL part is just an idea, but I'm happy to create a dedicated design discussion for a more concrete proposal.

See #5360 for further discussions about DDL support.

Any thoughts on the @PartitionColumn annotation? My proposal for the definition would be:

@Repeatable(PartitionColumns.class)
@Target({METHOD, FIELD, TYPE})
@Retention(RUNTIME)
public @interface PartitionColumn {

  /**
   * The <code>attributeName</code> element must be specified unless the 
   * <code>PartitionColumn</code> annotation is on an attribute of basic type 
   * or on an element collection of basic type.  In these cases, the
   * <code>attributeName</code> element  must not be specified.
   */
  String attributeName() default "";
}

This allows specifying partition columns in embeddables and also for collection tables. For join tables (*-to-many), I think we simply can't reasonably support partition columns, as we have no place where we could store the value. I'd say that people who want to use partition columns in "join tables", will have to map these tables as entities.

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

[gavinking]

Good idea, I like it.

We could of course go one step further and explore adding support for application based partitioning

I assume that Hibernate Shards died for a good reason :)

So I agree: much better to support what the databases offer natively.

A portable way to declare common partition types could be done through the following annotations

I don’t hate it, and it sorta seems like a nice thing to have, but on the other hand it’s also the sort of thing that you only really need in the sort of schema that we don’t recommend the use of SchemaExport for. So I dunno if it’s really worth doing this, as opposed to just giving people a way to append some arbitrary SQL to the end of generated create table statements.

[beikov]

I think we will have three kinds of partitioning user camps.

Camp 1: Don't need this and want to be in control. They probably develop against only one database anyway, so why even bother giving them a way to specify arbitrary DDL fragments? My thinking is, these people have other means to specify partitioning already, so they won't care.

Camp 2: Just start using partitioning and want some portability because they target different databases. They create initial schemas with hbm2ddl and export the schemas into a Liquibase changelog or Flyway script for each DB.

Camp 3: These people are probably also in camp 1 or 2, but want for local development an experience that just uses hbm2ddl.auto=update so they can start hacking without necessarily introducing Liquibase or Flyway into their workflow.

Maybe others have different opinions, but I think that portable annotation support could please camps 2 and 3.

[gavinking]

So the issue is this: from what I can see, there is nothing remotely like a "standard" for the DDL to create a partitioned table. So to develop something remotely portable looks like a rather lot of work, and a lot of code to maintain.

For example, the way SQL Server handles this looks really quite complicated, and it looks quite nontrivial to abstract over even just SQL Server, Postgres, and MySQL. (Perhaps I'm overestimating the difficulty here, but that's my initial impression.)

So I would prefer to not grow all that code in a first iteration. I would say provide the minimum to make this useful, and then if people like it and start using it, then come back and add extra stuff.

[beikov]

I looked into SQL Server a bit as it really looked different from how other databases support partitioning, but in the end, it's roughly the same. Instead of naming actual tables for a partition, SQL Server has file groups, so that's certainly a difference. I'll move the discussion about DDL support to a different GH discussion.

[beikov]

See #5360 for some further analysis and a place to discuss this.

[gavinking]

Could you please explain the purpose of attributeName?

[beikov]

This allows specifying partition columns in embeddables and also for collection tables.

[gavinking]

OK, but how do you use it? What is the value of attributeName for a collection? And why would I need it for @Embeddables?

[beikov]

Like you use the attributeName member for the Convert annotation i.e.

@Embeddable
class MyEmbeddable {
  String attr1;
}

...

@Embedded
@PartitionColumn("attr1")
MyEmbeddable embedded;

@ElementCollection
@PartitionColumn("attr1")
Set<MyEmbeddable> elementCollection;

[gavinking]

Ugh. I hate that. I would say this is definitely a thing that's not needed in the first iteration. Start simple.