I am currently testing on data encryption in PostgreSQL using pgcrypto lib. However I found that the decryption process is extremely slow when the table data is large (more than 10k). E.g.: for a table with 100k records, the query take more than 10 minutes to get the output.

I am using the encryption mostly on numeric columns and most of the time I am using the aggregate function like SUM to get the total, the query looks like:

SUM(to_number(pgp_pub_decrypt(unit, pg_read_binary_file('keys/secret.key')),'99999999999999990.00'))


to_number is required as PostgreSQL don't support SUM on string column.

Appreciate someone can advise whether I using the encryption in a correct way or is there any best practices when dealing with encryption in PostgreSQL.

Thanks a lot.