Automatic handling of indexes for Postgres 10

[aliou]

Postgres 11 introduces automatic index creation:

Indexes had to be created manually for each partition in v10. Trying to create a partition on the parent table fails:

pg10=> CREATE INDEX ixsales ON measurement(unitsales);
ERROR:  cannot create index on partitioned table "measurement"

In v11, if you create an index on the parent table, Postgres will automatically create indexes on all the child tables:

pg11=# CREATE INDEX ixsales ON measurement(unitsales);
CREATE INDEX
pg11=# \d measurement_y2016
           Table "public.measurement_y2016"
  Column   |  Type   | Collation | Nullable | Default
-----------+---------+-----------+----------+---------
 logdate   | date    |           | not null |
 peaktemp  | integer |           |          |
 unitsales | integer |           |          |
Partition of: measurement FOR VALUES FROM ('2016-01-01') TO ('2017-01-01')
Indexes:
    "measurement_y2016_unitsales_idx" btree (unitsales)

Any new partitions created after the index was created, will also automagically get an index added to it.

— PostgreSQL 11 Partitioning Improvements

---

Postgres 10 doesn't support indexes. To handle it anyway, we could create a custom method:

create_range_partition :measurements, partition_key: :logdate do |t|
  t.integer :unitsales
end

create_partition_index :measurements, :unitsales

And then when calling create_range_partition_of :measurements, re-apply the index on the created partition.