2

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.

1

It's probably fastest to use COPY (or \copy) to copy the data into unencrypted tables. Disable triggers, foreign keys, indexes.

Then encrypt, and add your indexes and keys again.

Use one SQL operation to encrypt, like

update my_table set 
    bytea1 = pgp_sym_encrypt_bytea(bytea1,var_pwd),
    bytea2 : = pgp_sym_encrypt_bytea(bytea2,var_pwd)
    ....;

Or do a insert into...select into a new table, encrypting in the select.

See also https://wiki.postgresql.org/wiki/Performance_Optimization

  • Thanks for the suggestion, but the performance gains is really small ( i'm now to 54 min instead of 55). It looks like the encryption is done in a loop even in a set based operation. – user34973 Feb 19 '15 at 15:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.