GIN Index Performance | Medplum

Operations with GIN (Generalized Inverted Index) in PostgreSQL

The operations described in this guide require super admin privileges and are only available to self-hosted deployments.

Medplum uses PostgreSQL GIN (Generalized Inverted Index) indexes on resource tables for search operations. By default, GIN indexes use a mechanism called fastupdate that defers index maintenance by buffering changes in a pending list. This improves write throughput for low-concurrency workloads, but causes significant problems during bulk operations.

When You'll Hit This

You're likely to encounter issues if you are:

• Running bulk data imports or migrations
• Performing high-concurrency upserts (e.g. parallel batch requests with conditional creates/updates)

Symptoms include:

• Serialization errors (40001): Parallel writes contend on the same pending list pages, causing predicate lock conflicts and transaction failures
• Slow queries: The pending list (up to 4MB) must be scanned on every read, inflating cost estimates and causing the query planner to choose sequential scans over the GIN index
• Sporadic slow writes: Occasional inserts trigger a full pending list flush, causing unpredictable latency spikes

Disabling fastupdate

Disabling fastupdate causes each insert/update to immediately update the index instead of deferring to the pending list. This eliminates both the serialization errors and the bad query plans.

Using the Medplum API

Medplum provides a super admin operation to configure GIN indexes. This requires the Prefer: respond-async header and runs as an async job:

POST /fhir/R4/$db-configure-indexes

Content-Type: application/fhir+json

Prefer: respond-async

{
  "resourceType": "Parameters",
  "parameter": [
    {
      "name": "tableName",
      "valueString": "Patient"
    },
    {
      "name": "fastUpdateAction",
      "valueString": "set"
    },
    {
      "name": "fastUpdateValue",
      "valueBoolean": false
    }
  ]
}

This disables fastupdate on all GIN indexes for the specified table and automatically runs VACUUM to flush the existing pending list.

Using SQL Directly

If you have direct database access, you can disable fastupdate on a specific index:

ALTER INDEX "Patient___idnt_idx" SET (fastupdate = off);

VACUUM "Patient";

The VACUUM is necessary to flush any entries remaining in the pending list after disabling fastupdate.

Transaction Retry Settings

As a complementary measure, tuning the server's transaction retry settings helps handle any remaining serialization errors gracefully. Set the following in your server config:

Caution: Transaction retries mask the symptoms but don't address the root cause. If you're seeing a high rate of serialization errors, disabling fastupdate on the affected tables is the recommended fix.

• When You'll Hit This
• Disabling fastupdate
  • Using the Medplum API
  • Using SQL Directly
• Transaction Retry Settings