I am using postgres 12 with pgp_sym_encrypt. It works. But encrypting a column in a very large table is taking days. Anyone else have experience making pgp_sym_encrypt faster? I am inserting/selecting into a new table to avoid slowness with and update in place. No where clause, really not much to tune that I can see.
To pull 10,000 rows for example takes 35 secs.
select
encode(pgp_sym_encrypt(col1,'KEY',' cipher-algo=aes256'),'hex') as col1
from tablea limit 10000
To pull 10000 rows w/o the function takes just milliseconds. select col1 from tablea limit 10000
There are a billion rows. I think that works out to 972 hours to encrypt this one column in the table.
PGP uses intentionally-slow hashing (although not all that slow) to make it harder to "brute force" weak passwords. You can get rid of this, or weaken it, by changing s2k-mode or s2k-count. Of course you should give it a lot of thought before intentionally weakening your own encryption.
pgp_sym_encrypt(col1, 'KEY', 'cipher-algo=aes256, s2k-mode=1')