Sql-server – Table partitioning on OLTP

global indexes are made unusable when you drop or move a partition. This is because there are entries pointing to physical addresses in your partition, that are no longer valid. If you can, try to avoid global indexes. You can do this by making them local.

create index idx_fk_opt_run_case on opt_run (case_id) local;

Should do the trick. If you can use reference partitioning, you don't have to add the redundant data in the table because the relation is already defined in the partition definition.

an example:

RONR SQL>create table parent
(id number not null
, x varchar2(10)
)
partition by list (id) (
partition par_1 values (1),
partition par_2 values (2)
);  2    3    4    5    6    7    8  

Table created.

RONR SQL>create unique index pk_parent
on parent (id) local;
  2  
Index created.

RONR SQL>alter table parent
add CONSTRAINT pk_parent
PRIMARY KEY (id)
USING INDEX;  2    3    4  

Table altered.

RONR SQL>create table child
( parent number not null
, y      varchar2(10)
,
constraint fk_p_c 
foreign key (parent) references parent(id))
partition by reference (fk_p_c);  2    3    4    5    6    7  

Table created.

RONR SQL>insert into parent (id, x) values (1,'boe');
insert into parent (id, x) values (2,'oeps');
1 row created.

RONR SQL>

1 row created.

RONR SQL>select * from parent;

    ID X
---------- ----------
     1 boe
     2 oeps

RONR SQL>insert into child (parent, y) values (1,'ggg');

1 row created.

RONR SQL>insert into child (parent, y) values (1,'ggg');

1 row created.

RONR SQL>insert into child (parent, y) values (2,'ppp');

1 row created.

RONR SQL>select * from child;

    PARENT Y
---------- ----------
     1 ggg
     1 ggg
     2 ppp

RONR SQL>select table_name, partition_name from user_tab_partitions;

TABLE_NAME             PARTITION_NAME
------------------------------ ------------------------------
PARENT                 PAR_1
PARENT                 PAR_2
CHILD                  PAR_1
CHILD                  PAR_2

RONR SQL>select index_name, partition_name, status from user_ind_partitions;

INDEX_NAME             PARTITION_NAME             STATUS
------------------------------ ------------------------------ --------
PK_PARENT              PAR_1                  USABLE
PK_PARENT              PAR_2                  USABLE

RONR SQL>alter table parent drop partition par_1;

Table altered.

RONR SQL>select * from parent;

    ID X
---------- ----------
     2 oeps

RONR SQL>select * from child;

    PARENT Y
---------- ----------
     2 ppp

RONR SQL>select table_name, partition_name from user_tab_partitions;

TABLE_NAME             PARTITION_NAME
------------------------------ ------------------------------
PARENT                 PAR_2
CHILD                  PAR_2

RONR SQL>select index_name, partition_name, status from user_ind_partitions;

INDEX_NAME             PARTITION_NAME             STATUS
------------------------------ ------------------------------ --------
PK_PARENT              PAR_2                  USABLE

I see no unusable index. What I do see is that I can drop a parent partition and this cascades to the child partition. does this help?

At almost 3 million rows in the postmeta table and almost 250,000 rows in the posts table, it sounds like you have long-ago outgrown WordPress.

The custom post types and custom taxonomies in WordPress were developed as an after-thought so the implementation won't be that efficient. (A lot of WordPress isn't very efficient, but that's another thing altogether.)

Rather than building a "hack" to work around the WordPress "hack" that is custom post types and custom taxonomies, I would suggest moving the client and meal data out of the posts and postmeta table and moving them into a custom table(s). You can still use WordPress as the CMS for the site (and even use their database classes and helpers), but you'll gain a lot of efficiency by creating your own tables and PHP code to manage this information instead of trying to make it work in tables that weren't designed to hold that data...

One example that comes to mind is the GigPress plugin, which creates its own tables to store event/artist/venue information, not the posts and postmeta table.

The biggest problem I see with trying to modify or partition the native WordPress tables is that whenever WordPress is updated, it could/would break your changes. You don't want to put yourself in a position where you have to spend lots of time just to update to a newer version of WordPress.