Partitioning

[notramo]

Is table partitioning supported in the schema? Or does EdgeDB automatically partition tables somehow?
E.g. in a database that stores results of played game results, there are millions of historical Game records, but only a few thousands new game results are added each day. Partitioning by start_time with time intervals would be a good choice here.

type Game {
  required property start_time -> datetime;
  property points -> int; # optional, empty if game is not finished yet
}

[elprans]

Table partitioning is not currently supported at the schema level, though it should be a relatively straighforward Postgres feature to expose. We need syntax and migration support.

[notramo]

What about the following syntax?

type User {
  required property username -> str {
    constraint exclusive;
    # partition based on the first 2 letters of the username
    partition [0:2];
}
type Game {
  required link user -> User;
  required property start_time -> datetime {
    # there are millions of finished games, so it's recommended to partition by days
    partition days;
}

[elprans]

I think it would make more sense to declare partitions at the type level like indexes, e.g

type Game {
  required link user -> User;
  required property start_time -> datetime;
  
  # there are millions of finished games, so it's recommended to partition by days
  partition on (datetime_truncate(.start_time, 'days'));
}

[notramo]

This looks better indeed.
Would it be able to partition depending on the result of a custom defined property (return value of some expression)?

[elprans]

Yes.

[notramo]

Is there any progress on this?

[raddevon]

This is not a feature we're shipping in our upcoming 4.0 release as far as I'm aware. Am I right on that, @elprans?

[elprans]

Yes, it's tentatively a 5.0 thing