Improve Performance with Indexing

Indexing improves the performance of database queries. As part of our ongoing effort to optimize PrivX performance, we have introduced additional indexing support from PrivX 28. Customers can create these indexes manually after PrivX upgrade.

If you skip the indexes creations, PrivX will continue to function normally, but listing and search audit events and history connections may be slow.

pg_trgm extension

pg_trgm is a PostgreSQL extension that provides support for trigram matching in PostgreSQL databases. Some improvements involve trigram indexes, which requires your PrivX databases to support pg_trgm 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.

1. Install the dependency postgresql-contrib, if it not already installed:

   sudo yum install postgresql-contrib

2. Connect to the PostgreSQL database as a database administrator:

   psql -U <database_admin>  -d <PrivX_database_name> 

3. Create extension for <PrivX_database_name>:

   CREATE EXTENSION IF NOT EXISTS pg_trgm;

Supported indexes

• Please check if an index already exists before you create it, you can skip it if the return value is t

SELECT EXISTS (SELECT 1 FROM pg_indexes WHERE indexname = '<index name>');

• If you skipped an index creation in an early PrivX version, you can create it in a later version by applying the same instruction. For example, you can create the trigram_audit_events_keywords_index when running PrivX 29 if it is not created in PrivX 28 or if you upgrade from PrivX 27 to PrivX 29.
• Creating index in a supported PrivX version does not require PrivX downtime. However creating a trigram index consumes extra CPU load and database storage during data migration and index creation.

Creating indexes

trigram_audit_events_keywords_index

If your PrivX database supports pg_trgm extension, the index is created automatically during a clean PrivX 28 or later version installation.

If you need to create this index manually

1. Perform upgrade to PrivX 28 or later version normally. PrivX runs background tasks to migrate existing audit events in small batches. PrivX functions normally during the migration.

2. Later, verify that migrations are complete. For audit_events table specifically, the returned value should be 0 for the query below.

   SELECT count(*) from audit_events WHERE upgraded is NULL;

   When the returned row count is 0, migration is complete. The migration in step 1 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.

3. 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 million 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.

btree indexes on connection table

We recommend creating the following btree indexes before upgrading to PrivX 29. This will speed up the trigram_connections_keywords_index index creation supported from PrivX 29.

  CREATE INDEX CONCURRENTLY connections_connected_index ON connections (connected);
  CREATE INDEX CONCURRENTLY connections_disconnected_index ON connections (disconnected) WHERE audit_enabled = true AND trail_removed = FALSE AND trail_id IS NOT NULL;
  CREATE INDEX CONCURRENTLY connections_status_index ON connections (status);
  CREATE INDEX CONCURRENTLY connections_type_mode_status_disconnected_index ON connections (type,mode,status,disconnected);
  CREATE INDEX CONCURRENTLY connections_session_id_index ON connections (session_id);

trigram_connections_keywords_index

If your PrivX database supports pg_trgm extension, the index is created automatically during a clean PrivX 29 installation.

If you need to create this index manually:

1. Perform upgrade to PrivX 29 or later version normally. PrivX runs background tasks to migrate existing connections in small batches. PrivX functions normally during the migration, except connections page under Monitoring tab. The search results during the migration might be inconsistent and slow to load with even timeouts.

2. Later, verify that migrations are complete. For connections table specifically, the returned value should be 0.

   SELECT count(*) from connections WHERE upgraded is NULL;

   When the returned row count is 0, migration is complete. The migration in step 1 may take a while to complete, for example with 12 millions connections this may take up from 1 hour to few hours (depending on hardware). Do not continue until the migration is complete.

3. After existing connections are migrated, manually create the trigram index for the connections table.

• To prevent blocking the whole table, use CONCURRENTLY.

  CREATE INDEX CONCURRENTLY trigram_connections_keywords_index ON connections USING GIN (keywords gin_trgm_ops);

  Index creation may take considerable time depending on the amount of connections, database server loads, etc. For example with 12 millions of connections, this may take up to few 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_connections_keywords_index');

  The returned value should be t.