I have a table with ~20 columns.
-----------------------------------------------------------------
GUID_PK | GUID_SET_ID | Col_3 | Col_4 | ... | Col_20
-----------------------------------------------------------------
There could be thousands of Sets each having tens to less than a thousand records. Records within a set are all related to each other. sets are totally independent to each other. A whole set is read/written at a time in one big transaction. Once a record is written, it is read-only for ever, never altered, only read. Data is rarely deleted from this table. when it is deleted, the whole set is deleted in one go.
Only SET_ID is an incoming foreign key. PK is an outgoing foreign key to a different table. in the detail table about 3 or 4 records (each a single blob) are kept per master record.
Question is: should I partition the tables? I think yes. My boss thinks better. he wants the tables be created dynamically, one master one detail for each set. I personally am not comfortable with the dynamic creation idea, but fear the one-table-to-rule-them-all architecture.
The bulk insertions and bulk selects are definitely going to hit performance. Bulk deletes will again reorder the indexes. What would be an optimal structure?
Taking into account the most of the Col_x columns are populated you can do a HASH PARTITIONING :
CREATE TABLE
....
PARTITION BY HASH(GUID_SET_ID)
PARTITIONS NO_PART;
Where NO_PART is the number of partitions that you want , this should be established taking into account:
1) the volume of data you receive daily
2) the volume of data you estimate that will be received in the future
Also you can check other partition types here.