Good encryption performance

Question

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.

Neil McGuigan · Accepted Answer · 2015-02-18 05:52:00Z

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

answered Feb 18, 2015 at 5:52

Neil McGuigan

8,6735 gold badges42 silver badges57 bronze badges

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
– user34973

2015-02-19 15:47:15 +00:00
Commented Feb 19, 2015 at 15:47

Add a comment |

Stack Exchange Network

Good encryption performance

1 Answer 1

Your Answer

Hot Network Questions

Good encryption performance

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions