Check sequence generation health

EclecticIQ Intelligence Center (EIQ IC) depends on sequence generators to provide IDs for the records in its databases. Although this generator can provide a large number of IDs (about 2 billion), we’ve recently seen the first instances of the sequence exceeding the generators’ ability to provde IDs.

When this happens, the generators and databases need to be migrated to an architecture that allowsfor the generation of a significantly larger number of IDs. Because the part of the records will have been replicated in the ElasticSearch (ES) database a migration of the ES database is also necessary, in order for it to accept the new ID format.

Mitigation strategy

Read the instructions for the scenario matching your situation below.

The best mitigation strategy is to upgrade to v3.4.4 and then v.3.5.0 as soon as they release, because they come with built-in sequence generator health check and database migration, respectively.

What to do if you will or did already upgrade to 3.4.4?

EIQ IC v3.4.4 comes packaged with a health check that will warn you when the sequence generator is approaching its limit. If it does, please contact support.

What to do if you’re NOT upgrading to 3.4.4?

To see if your EIQ IC instance is close to running out of IDs, please run the script provided below:

SELECT
    sequencename,
    (
        (last_value / max_value::REAL) * 100
    )::NUMERIC(10, 2) AS percent_usage
FROM
    pg_sequences
WHERE
    max_value > 0
    AND (last_value / max_value::REAL) > 0.7
ORDER BY
    percent_usage DESC
LIMIT
    50
;

If the script returns one or more rows with more than 70% sequence usage

It means your database is at risk of running out of available IDs. Please contact support.

>90% usage

If the script returns one or more rows with more than 90% sequence usage, immediate action is recommended.

If the script returns 0 rows

It means your database isn’t close to running out of available IDs. The mitigation packaged with v3.5.0 and later releases will prevent it from becoming a problem.

Mitigations if not upgrading

If you are not planning an upgrade to v3.5.0 or a later version in the foreseeable future make sure you contact support to migrate your databases as the ID limit might still cause issues.

If you’re not planning an upgrade to v3.4.4, make sure to intermittenly run the script to stay aware of the sequence generators’ health.