How can I specify the order of a composite primary key in an Ecto migration?

Questions / Help
, , ,
1

I am creating a schema where the order of the column definitions is relevant due to optimum memory alignment. The table needs to hold a very large number of rows, hence the additional pain here.

When I add columns with primary: true, the resulting composite primary key is defined like this: PRIMARY KEY (timestamp, record_id, confirmed)

create table(:foo, primary_key: false) do
  add :timestamp, :timestamptz, null: false, primary: true
  add :bar, :real, null: false
  add :baz, :real, null: false
  # ... other columns
  add :record_id, :smallint, null: false, primary: true
  add :confirmed, :boolean, null: false, primary: true
end

However, I need to modify this order and generate a PRIMARY KEY (record_id, timestamp, confirmed) to better suit the table partitioning. The order of the columns in the table itself must remain as it is.

Right now, the only way I see is to generate the table without any primary: true columns, then execute a custom SQL statement to add the properly ordered primary key.

Did I miss an Ecto feature that does this for me?

2

It uses the order in which you define the columns. It’s unfortunately a bit unintuitive for people used to real SQL :slight_smile:

3

BTW, at least in Postgres, you do not need null: false on primary key columns. They are disallowed from being null by virtue of being a primary key. Not sure about other DBs though.

4

It uses the order in which you define the columns. It’s unfortunately a bit unintuitive for people used to real SQL :slight_smile:

Damn, that’s what I thought :wink:

Something like create primary_key(...) would be great, similar to create index(...).

in Postgres, you do not need null: false on primary key columns

I know, I just use this to clarify the intention in case the keys are changed later.

1 Like