Audit-Event Indexing for Faster Searches

Introduction

From PrivX 28, PrivX automatically creates a trigram index to improve audit-event-search performance.

Trigram index requires your PostgreSQL to support pg_trgm extension. If your PrivX database lacks this extension, the index will not be created. PrivX will continue working normally, but audit-event search performance may be poor. An alternate way to improve audit-event-search performance is to restrict the amount of stored audit events.

Before Installation or Upgrade

Before install or upgrade to PrivX 28, please consult your PostgreSQL administrator if the database PrivX connects to already has the pg_trigram extension. If the extension is not added, please request the database administrator to install it.

If you manage the PostgreSQL database, you may install the extension similar to the following. This example installs the extension to PostgreSQL 13 on CentOS 7. You need DBA access to do this task.

Install the dependency postgresql-contrib, if it not already installed:
```
sudo yum install postgresql-contrib
```
Connect to the PostgreSQL database as a database administrator:
```
psql -U <database_admin>  -d <PrivX_database_name> 
```
Create extension for <PrivX_database_name>:
```
CREATE EXTENSION IF NOT EXISTS pg_trgm;
```

Upgrade From PrivX 27 or Earlier Versions and Manual Index Creation

When upgrading from a previous version to PrivX 28:

Perform upgrade normally. PrivX runs background tasks to migrate existing audit events in small batches. PrivX functions normally during the migration.
Later, verify that migrations are complete. For audit_events table specifically, the returned value should be 0.
```
SELECT count(*) from audit_events WHERE upgraded is NULL;
```
When the returned row count is 0, migration is complete. This step may take a while to complete, for example with 10 millions audit events this may take up to 1 hour. Do not continue until the migration is complete.
After existing audit events are migrated, manually create the trigram index in the audit_events table.

Create trigram index for specific table and specific column. To prevent blocking the whole table, use CONCURRENTLY. Table name is audit_events, column is keywords:
CREATE INDEX CONCURRENTLY trigram_audit_events_keywords_index ON audit_events USING GIN (keywords gin_trgm_ops);
Index creation may take considerable time depending on the amount of audit events, database server loads, etc. For example with 10 millions of audit events, this may take up to 2 hours. Also note that the process uses extra CPU and database storage while running.
After the previous step completes, verify that the index was created:
```
SELECT EXISTS (SELECT 1 FROM pg_indexes WHERE indexname = 'trigram_audit_events_keywords_index');
```
The returned value should be t.

When creating an index using CONCURRENTLY, PostgreSQL is able to avoid blocking the table and execute other operations concurrently. For the exact version of your PostgreSQL database, please consult the PostgreSQL documentation about index creation. Also note that creating an index using CONCURRENTLY may require more time and hardware resources.

Index creation process has been tested only for GIN type indices.