Foreign keys vs partitioning

Question

Since foreign keys are not supported by partitioned mySQL databases for the moment, I would like to hear some pro's and con's for a read-heavy application that will handle around 1-400 000 rows per table. Unfortunately, I dont have enough experience yet in this area to make the conclusion by myself...

Thanks a lot!

References:

How to handle foreign key while partitioning

Partitioning mySQL tables that has foreign keys?

Islean

Is it possible now to partition mysql tables with foreign keys after 12 Years? – Hydatid

Answer 1

Well, if you need partitioning for a table as small as 400.000 rows get another database than MySQL. Seriously. By modern standards any table below 1.000.000 rows is normally neglegible in size (not even small), unless you also dont have any index etc. And modern standards are about 10 years old in this regard.

Vanir

Well, No. I couldn't find any recommendations whether to partition or not. I assumed that it would make a noticeable difference for small tables as well. – Islean

Nope. Partitioning is basically when you run into problems with having one table. In theory for example because you keep sales per year - deleting all sales for a year is really hard work, but with partitions you can just drop the table for the one year. Small things make zero sense like that. – Vanir

Thanks a lot TomTom. Appreciate your answer. The deleting part of working with partitions is obvious, but from what I have heard previously, a partitioned table always worked better than a non-partitioned, even with smaller data sets. I have no Idea of the comparision in performance increase with foreign keys over partions however. Seems like a subject with little information about. Maybe I will have to go down the hard way to find out with benchmarking and sample data... – Islean

Answer 2

Well, partition is not a good solution for complicate data model. If you only have 2 to 3 tables depending on each other, you may be able to do it but it is not pretty. Each table must have an column that determine the partition. Then, each table must have a trigger to create the new table, set the foreign key and unique constraint.

For example, audittransaction<- auditentry

Each audittransactionhas 0 to n auditentry. table auditentry contains the foreign key of transaction. Both table have to have column creationDate since it is used for partition both tables.

------ create a trigger to insert audittransaction within the trigger

create or replace function audittransaction_insert_function() 
returns trigger as $$ 
DECLARE

    tablepartition varchar;
    tablename varchar;
    startbounds timestamp;
    endbounds timestamp;                


BEGIN
    tablepartition :=  to_char(date_trunc('month', NEW.whendone), 'YYYYMMDD');  
    tablename := 'audittransaction_' || tablepartition ;        

    if not exists(select * from information_schema.tables where table_name = tablename) then
        startbounds := date_trunc('month', NEW.whendone);
        endbounds := startbounds + cast('1 months' as interval);
        execute 'create table ' || tablename || ' ( CHECK (whendone >= ' || quote_literal(startbounds) || ' and whendone < ' || quote_literal(endbounds)|| ') ) inherits (audittransaction)';
        execute 'ALTER TABLE '||  tablename ||' ADD CONSTRAINT '||tablename||'_unique_id UNIQUE (id)';          
    end if;     
    execute 'insert into ' || tablename || ' (id, operationid, whendone, "comment", ticketid ,transactionid, userid )  values (' || quote_literal(NEW.id) || ',' || quote_literal(NEW.operationid) || ',' || quote_literal(NEW.whendone) || ')';                
    return null; 
END; $$ 
LANGUAGE plpgsql;

create trigger insert_audittrans

----- then, create a trigger for autientry

create or replace function auditentry_insert_function() 
returns trigger as $$ 
DECLARE
    tablepartition varchar;
    tablename varchar;
    startbounds timestamp;
    endbounds timestamp;                


BEGIN
    tablepartition :=  to_char(date_trunc('month', NEW.transactiontimestampgmt), 'YYYYMMDD');   
    tablename := 'auditentry_' || tablepartition ;

    if not exists(select * from information_schema.tables where table_name = tablename) then
        startbounds := date_trunc('month', NEW.transactiontimestampgmt);
        endbounds := startbounds + cast('1 months' as interval);
        execute 'create table ' || tablename || ' ( CHECK (transactiontimestampgmt >= ' || quote_literal(startbounds) || ' and transactiontimestampgmt < ' || quote_literal(endbounds)|| ') ) inherits (auditentry)';
        execute 'ALTER TABLE '||  tablename ||' ADD CONSTRAINT '||tablename||'_unique_id UNIQUE (id)';  
        execute 'ALTER TABLE ' || tablename ||' ADD CONSTRAINT auditentry FOREIGN KEY (audit_transaction_id) REFERENCES audittransaction_'||tablepartition ||'(id)';                
    end if;     
    execute 'insert into ' || tablename || ' (id, audit_transaction_id, eventid, transactiontimestampgmt,timestampgmt, acknowledged, resolved, acknowledgedbyusername, acknowledgeddate,  notificationlevel, resolvedbyusername, resolveddate, severity,  parentauditentry_id )  values (' || quote_literal(NEW.id) || ',' || quote_literal(NEW.audit_transaction_id) || ',' || quote_literal(NEW.eventid) || ','||quote_literal(NEW.transactiontimestampgmt)||')';             
    return null; 
END; $$ 
LANGUAGE plpgsql;

create trigger insert_auditentry before insert on auditentry for each row execute procedure auditentry_insert_function();

Seiter