I'm converting a sql server database to postgresql 9.4. I'm stuck on something that is really really slow.
I use copy to bulk insert 200 files of 5000 lines each into 200 tables (one file per table).
Each table have a before trigger that use pgcrypto to encrypt two bytea column from the files and 3 default text into 3 additional column
The trigger looks like this :
select password into var_pwd from temp_table; --The password is put into a temporary table before the copy. new.bytea1 : = pgp_sym_encrypt_bytea(bytea1,var_pwd); new.bytea2 : = pgp_sym_encrypt_bytea(bytea2,var_pwd); new.text1 : = pgp_sym_encrypt('default text1',var_pwd); new.text2 : = pgp_sym_encrypt('default text2',var_pwd); new.text3 : = pgp_sym_encrypt('default text3',var_pwd);
It takes 55 minutes to load everything with the trigger(3 minutes without it, so the encryption is the problem).
It takes around 7 minutes to insert and encrypt everything in Sql Server (if it is of any use, i use a symmetric key, the EncryptByKey command and an instead of trigger. Everything else is the same)
The data needs to be decrypted, so a two way encryption is needed.
Is there any alternative i could try to speed up the query (another encryption extension, some postgresql.conf settings i should look into / etc)?
Thanks in advance.