I am currently working on an application that requires column-based decryption for a few thousand of rows on a regular basis. The queries are decrypted using pgp_sym_decrypt, where several columns are decrypted for each select.
For a few thousand records, the queries are unfortunately quite slow and I found out that the CPU and the RAM were not quite used.
top gives 6.6% CPU usage and 14 GB RAM available out of 16 GB. Therefore, a "standard query" takes around 30s to proceed, while an acceptable performance would be rather around 5 seconds.
I tried changing a few parameters in postgres.conf, but I didn’t get any performance improvement. The version of Postgres is 10.6.
Is there a possibility to increase the hardware usage of Postgres to make the decryption faster?