Re: encrypting data stored in PostgreSQL

On 9.4.2014 23:28, Martijn van Oosterhout wrote:
> On Wed, Apr 09, 2014 at 02:16:34PM -0700, Ken Tanzer wrote:
>>> Any thoughts on how to pull this off for PostgreSQL stored data?
>>>
>>> I looked at this a while ago because I have clients who might
>> require this in the future. ISTM you should be able to have your PG
>> data directory stored on an encrypted filesystem. I believe this
>> will decrease performance, but I have no idea by how much.
>
> FWIW, I have several databases running on encrypted filesystems. The
> performance difference is negligable *if* you have hardware
> acceleration for your encryption, which most modern processors have.
>
> Essentially, the processor can encrypt/decrypt data so much faster
> than the cost of reading/writing to disk, you don't notice the
> difference. There's surely a difference, but if this means you meet
> your requirements it's an excellent solution.

We're running a number of rather busy PostgreSQL boxes with encryption
at filesystem (or more precisely dm-crypt/LUKS with LVM, IIRC).

Support for encryption acceleration (AES-NI [1]) is an absolute must.

The other thing that is essential for good performance is reasonably
recent kernel. 2.6.x kernels have a single-threaded kcryptd, which means
you can't get more than ~150 MB/s AES-256 (per partition). With other
algorithms it's not much better (say, 170MB/s with AES-128, IIRC).

Somewhere in 3.x (or maybe very late 2.6.x) kcryptd was improved to use
multiple threads - that's a significant improvement, both for throughput
and latencies.

Clearly, it's going to eat (part of) your CPUs, but that's expected. The
encryption still has impact on latencies, but with the multi-threaded
kcryptd it's pretty-much negligible.

regards
Tomas

[1] http://en.wikipedia.org/wiki/AES_instruction_set