<toby.corkindale@strategicdata.com.au> wrote: > Do I need to make sure I re-create every index on every child table I > create? > That would be.. annoying, at best.
Yes, it is little bit annoying but I like it. You don't need any index on parent table but you have to create them "manually". I wrote simple python script which creates partitions and required indexes in advance (tables are partitioned by date).
I like the flexibility because you can have different indexex on different partitions. For example, I discovered that adding index will improve several queries. In the production I can't afford exclusive lock (build index concurrently takes ages) so I updated and re-run the script which re-created future partitions.
My Personal favorite is the LIKE syntax:
CREATE TABLE foo_1 (LIKE foo including indexes ) inherits (foo);
It doesn't help you change children after the fact, but your new partitions automatically get whatever indexes you've stuck on the master table.