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.
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;
Make sure PrivX database support pg_trgm
extension before you proceed
Supported indexes
Index Name | Type | Supported from |
---|---|---|
trigram_audit_events_keywords_index | trigram | PrivX 28 |
connections_connected_index | btree | PrivX 29 |
connections_disconnected_index | btree | PrivX 29 |
connections_type_mode_status_disconnected_index | btree | PrivX 29 |
connections_status_index | btree | PrivX 29 |
trigram_connections_keywords_index | trigram | PrivX 29 |
connections_session_id_index | btree | PrivX 30 |
- 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
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.
Later, verify that migrations are complete. For
audit_events
table specifically, the returned value should be0
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.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 isaudit_events
, column iskeywords
: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:
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 underMonitoring
tab. The search results during the migration might be inconsistent and slow to load with even timeouts.Later, verify that migrations are complete. For
connections
table specifically, the returned value should be0
.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.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
.