Skip to main content

Slow search performance with Postgres after upgrading to Nexus Repository 3.44.0+ in HA Preview

Daniel Kane
Updated

From release 3.44.0 onwards, Nexus Repository 3 instances that use Postgres will require the pg_trgm (trigram) module to be installed with their Postgres instance. This requirement has been introduced to prepare for upcoming slow search performance improvements. This module is used to create an extension in the Nexus Repository 3 database that enables the creation of a more performant search index.

.

Problem

This module may not be installed with Postgres by default on all linux distributions.  If this is the case, the upgrade will log an exception like the following:

...
2022-12-28 12:03:49,020-0500 INFO  [FelixStartLevel] *SYSTEM org.sonatype.nexus.datastore.mybatis.MyBatisDataStore - nexus - Creating schema for SearchTableDAO
2022-12-28 12:03:49,064-0500 WARN  [FelixStartLevel] *SYSTEM Sisu - Problem adding: org.eclipse.sisu.inject.LazyBeanEntry@2c00368 to: org.sonatype.nexus.datastore.mybatis.MyBatisDataStore$$EnhancerByGuice$$366511306@400e761a via: org.sonatype.nexus.datastore.internal.DataStoreManagerImpl$DataAccessMediator@4debec0a
org.sonatype.nexus.datastore.api.DataAccessException: 
### Error updating database.  Cause: org.postgresql.util.PSQLException: ERROR: could not open extension control file "/usr/share/pgsql/extension/pg_trgm.control": No such file or directory
  Where: SQL statement "CREATE EXTENSION IF NOT EXISTS pg_trgm"
...

The consequences of not having this module available will vary depending on your particular upgrade/installation situation.

  • If you are attempting a fresh installation using a new, empty Postgres database, the creation of both the search table and its index will fail, resulting in broken search functionality.
  • If you are upgrading your existing installation, the search table will already exist, but the index creation will fail, resulting in no performance improvement - but search functionality will continue to operate as it did before.

 

Solution

In this situation, it is necessary to install the postgresql-contrib package available from your linux distribution.

eg. (Fedora)

sudo dnf install postgresql-contrib

Once installed, execute the following Postgres command:

create extension pg_trgm;

Note the documentation mentions having the correct privileges for the database.

  • This module is considered“trusted”, that is, it can be installed by non-superusers who haveCREATEprivilege on the current database.

Simply ensure the module is installed and that correct privileges are assigned to the database user, then restart your upgrade/installation.

 

Confirmation

Following successful upgrade/installation, the available extensions can be listed by the following Postgres commands/statements:

\c <nxrm_database>;
\dx

or

select * from pg_extension;

and should result in output like the following

                           List of installed extensions
  Name  | Version |   Schema   | Description 
---------+---------+------------+-------------------------------------------------------------------
pg_trgm | 1.5     | public     | text similarity measurement and index searching based on trigrams
plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language

(the first line above indicates the pg_trgm extension has been created for the current database)

Use the following commands to describe the search table and list its indexes

\c <nxrm_database>;
\d search_components;

The final entry in the list of indexes should be

"trgm_idx_search_components_paths" gist (paths gist_trgm_ops)

which confirms that the new index creation was successful.

Related articles