Audit-Event Indexing for Faster Searches
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;
For a clean installation of PrivX, the trigram index will be automatically created during the execution of the
postinstall.sh script, except in cases where an external database is being used. In such cases, please refer to the instructions for manual index creation.
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_eventstable specifically, the returned value should be
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
Create trigram index for specific table and specific column. To prevent blocking the whole table, use
CONCURRENTLY. Table name is
audit_events, column is
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
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.
Updated 20 days ago