Indices on the Context

[valentijnscholten]

Indexing the context
The history page in my context also allows the user to filter by actor (user), ip (remote_addr) and possible future other entries in the Context.
I wonder what this means for performance? Do you have experience/advice here?
Should I create some indices to improve performance?

    operations = [
        # Add GIN index on the entire metadata JSON field for general JSON queries
        migrations.RunSQL(
            "CREATE INDEX IF NOT EXISTS pghistory_context_metadata_gin_idx ON pghistory_context USING GIN (metadata);",
            reverse_sql="DROP INDEX IF EXISTS pghistory_context_metadata_gin_idx;"
        ),

        # Add specific indexes for commonly queried JSON keys
        migrations.RunSQL(
            "CREATE INDEX IF NOT EXISTS pghistory_context_user_idx ON pghistory_context ((metadata->>'user'));",
            reverse_sql="DROP INDEX IF EXISTS pghistory_context_user_idx;"
        ),

        migrations.RunSQL(
            "CREATE INDEX IF NOT EXISTS pghistory_context_remote_addr_idx ON pghistory_context ((metadata->>'remote_addr'));",
            reverse_sql="DROP INDEX IF EXISTS pghistory_context_remote_addr_idx;"
        ),

        # Composite index for user + remote_addr queries
        migrations.RunSQL(
            "CREATE INDEX IF NOT EXISTS pghistory_context_user_remote_idx ON pghistory_context ((metadata->>'user'), (metadata->>'remote_addr'));",
            reverse_sql="DROP INDEX IF EXISTS pghistory_context_user_remote_idx;"
        ),
    ]

I can do some experiments/performance tests, but I'd rather build on knowledge already present here :-)

[wesleykendall]

I can do some experiments/performance tests

I think that's always the best thing to do when adding any index

[valentijnscholten]

Well first I'm trying to understand what is already provided by default. According to the docs there should be some indices generated on any foreign key fields from the model. But pghistory is not creating any indices in its migration. So all event tables have 0 indices. When I tread the docs I think this is unexpected and personally I would also expect some indices to present on pgh_ fields, for example pgh_context. My models are annotated like this:

    pghistory.track(
        pghistory.InsertEvent(),
        pghistory.UpdateEvent(condition=pghistory.AnyChange(exclude_auto=True)),
        pghistory.DeleteEvent(),
        pghistory.ManualEvent(label="initial_import"),
    )(Finding)

[valentijnscholten]

Turns out that pgadmin sometimes goes into a weird state where the connection with postgress is lost but it fails silently on refresh action in the UI making it appear as if there are no indexes.