In one of my use cases I need to provide the similar experience people have while they search their mailbox like Gmail. It can search through both email subject and body.

I have some support tickets saved in Postgres. These tickets contain user messages which we can't store in plain text. We have to encrypt the data. Now if we want to build an index for providing full text search how do we go about it considering the index can't contain the actual data

Any pointer on how Gmail or any other similar providers solve this problem would also be great.

CodePudding user response：

There is no way to do that. Either data are encrypted or not. If they are encrypted, the database does not know the value, so it cannot perform full text search. That's pretty obvious.

Security comes at a price, and in this case, it is performance.

CodePudding user response：

PostGreSQL is not able to encrypt data at the storage level as Oracle or Microsoft SQL Server do with Transparent Data Encryption (TDE). When Using TDE the data are encrypted in the storage files, not in memory so, you can use a fulltext search as usual.