User defined data (meta)

In case some additional data generated by plugin or some client application needs to be stored along with the event, meta field should be used. It’s intended for storing key-value object of free form. Overall size of the object shouldn’t exceed limit of 2 MB.

The user is provided with:

The user undertakes:

Index building

For efficient lookups indexes should be built. User-defined data is stored in PostgreSQL as JSONB that supports GIN, B-tree and Hash indexes. For additional info see https://www.postgresql.org/docs/current/datatype-json.html#JSON-INDEXING

If you are going to work with some custom plugins, you can build any indexes you are interested in, which are supported by the Postgresql JSONB.

If you intend to use the regular event search, there are functional restrictions on the indexes that can be used for search.

For scalar types (string, integer, numeric):

To filter by string field, the following B-tree index will be useful:

CREATE INDEX ix_event_meta_user_status ON event USING BTREE ((jsonb_extract_path_text(meta, 'user_info', 'status')));

Indexes for string fields to be involved must be built using jsonb_extract_path_text, since service queries to the database implement this format of the string field extract.

To filter by integer / float field that takes some range of values, the following B-tree indexes will be useful:

CREATE INDEX ix_event_meta_user_temperature ON event USING BTREE ((jsonb_extract_path_text(meta, 'user_info', 'temperature')::int));
CREATE INDEX ix_event_meta_user_temperature ON event USING BTREE ((jsonb_extract_path_text(meta, 'user_info', 'temperature')::float));

Indexes for integer / float fields to be involved must be built using jsonb_extract_path_text + cast, since service queries to the database implement this format of the integer / float field extract.

For JSON type (new operators: contains, overlaps, etc.):

What are GIN indexes?

GIN (Generalized Inverted Index) is a specialized PostgreSQL index type designed for indexing composite values where each value can contain multiple elements. For JSONB data, GIN indexes create an inverted index structure that maps each key and value in the JSON document to the rows containing them. This makes them highly efficient for queries that search for specific keys, values, or key-value pairs within JSON structures.

Why use GIN indexes for JSONB operations?

The new JSON operators (contains, ncontains, overlaps, noverlaps) work with arrays and objects in JSONB fields. Without indexes, PostgreSQL must perform sequential scans, extracting and comparing JSON structures for every row. GIN indexes dramatically improve performance by:

  • Containment searches (@> operator): Checking if a JSONB field contains specific elements (e.g., tags, roles, permissions)

  • Overlaps operations (&& / ?| operators): Finding rows where JSON arrays share common elements

  • Existence checks: Quickly determining if specific keys or values exist in JSON documents

When to use GIN vs B-tree indexes:

  • Use GIN indexes for:

    • JSON arrays (tags, categories, roles, permissions)

    • JSON objects with multiple key-value pairs

    • Queries using contains, ncontains, overlaps, noverlaps operators

    • Searches where you need to match multiple elements or nested structures

  • Use B-tree indexes for:

    • Single scalar values extracted from JSON (strings, numbers)

    • Range queries on numeric fields (__gte, __lte)

    • Exact match queries on string fields

    • Queries using standard operators (__eq, __in, __like)

Performance and storage considerations:

  • Query performance: GIN indexes can speed up JSONB queries by 10-1000x compared to sequential scans, especially on large tables

  • Index size: GIN indexes are typically larger than B-tree indexes (often 50-100% of table size for JSONB columns)

  • Write performance: Inserts and updates are slightly slower with GIN indexes due to the complex index structure

  • Trade-off: The query performance gain far outweighs the storage and write overhead for most use cases

Creating GIN indexes for events (meta field):

-- General GIN index for all JSONB operations on meta field
-- This enables efficient queries on any keys/values in the meta column
CREATE INDEX idx_events_meta_gin ON event USING GIN (meta);

-- Optional: Path-specific indexes for frequently filtered fields
-- These are smaller and faster for queries on specific JSON paths
CREATE INDEX idx_events_meta_tags ON event USING GIN ((meta -> 'tags'));
CREATE INDEX idx_events_meta_config ON event USING GIN ((meta -> 'config'));
CREATE INDEX idx_events_meta_user_roles ON event USING GIN ((meta -> 'user' -> 'roles'));
CREATE INDEX idx_events_meta_permissions ON event USING GIN ((meta -> 'security' -> 'permissions'));

Example use cases for events (meta field):

  1. Filtering by tag arrays:

    Query: meta.tags__contains:json=[\"security\", \"high-priority\"]

    With GIN index on meta, PostgreSQL quickly finds events where tags contain both elements.

  2. Finding overlapping categories:

    Query: meta.categories__overlaps:json=[\"analytics\", \"monitoring\"]

    GIN index efficiently identifies events sharing any of these categories.

  3. Complex object matching:

    Query: meta.config__contains:json={\"enabled\": true, \"level\": 3}

    GIN index accelerates searches for specific key-value combinations in nested objects.

  4. User roles filtering:

    Query: meta.user.roles__overlaps:json=[\"admin\", \"moderator\"]

    Finds events where user has any of the specified roles.

Note: For general events (with custom event field structure), see the General events documentation section.

GIN indexes are particularly efficient for the @> (contains), <@ (contained-by), ?| (exists-any), and && (overlaps) PostgreSQL operators used internally by the new JSON filters.

Troubleshooting

  1. Why can’t save an event?

    PostgreSQL will not allow inserting a row with a type that cannot be added to the existing index. Suppose such an index is built:

    CREATE TABLE IF NOT EXISTS event_meta_test (id serial primary key, meta jsonb);
CREATE INDEX IF NOT EXISTS ix_event_meta_test_status ON event_meta_test USING BTREE ((jsonb_extract_path_text(meta, 'user_info', 'status')::smallint));

    Then trying to insert the following data into the table

    INSERT INTO event_meta_test (meta) VALUES ('{"user_info": {"status": "unknown", "temperature": 36}}');

    will cause the following error: ERROR: invalid input syntax for type smallint: “unknown”.

    This, attempting to save inconsistent data will result in a 422 (Unprocessable) error from the server if wait_events_saving=1 query parameter is set, or background error otherwise.

    Solution options:

    • check the correctness of setting the meta for saving within the existing scheme;

    • revise the data scheme, apply migrations if needed, and build an appropriate index.

  2. Why can’t build an index?

    Suppose the following data is already stored in the database:

    CREATE TABLE IF NOT EXISTS event_meta_test (id serial primary key, meta jsonb);
INSERT INTO event_meta_test (meta) VALUES ('{"user_info": {"status": 0, "temperature": 36}}');
INSERT INTO event_meta_test (meta) VALUES ('{"user_info": {"status": 5, "temperature": "normal"}}');

    Then trying to build the following index on the table

    CREATE INDEX IF NOT EXISTS ix_event_meta_test_temperature ON event_meta_test USING BTREE ((jsonb_extract_path_text(meta, 'user_info', 'temperature')::int4));

    will cause the following error: ERROR: invalid input syntax for type integer: “normal”.

    Thus, it will not be possible to create an incompatible index.

    Solution options:

    • revise the data scheme, and apply migrations if needed;

    • build another index (jsonb_extract_path_text with no cast).

  3. Why can’t search with some filter?

    Suppose the following data is already stored in the database:

    CREATE TABLE IF NOT EXISTS event_meta_test (id serial primary key, meta jsonb);
INSERT INTO event_meta_test (meta) VALUES ('{"user_info": {"group": "aliens"}}');
INSERT INTO event_meta_test (meta) VALUES ('{"user_info": {"group": 1}}');

    Then trying to use meta.user_info.group:integer=1 query parameter that will result in the following database query

    SELECT * FROM event_meta_test WHERE jsonb_extract_path_text(meta, 'user_info', 'group')::int = 1;

    will cause the following error: ERROR: invalid input syntax for type integer: “aliens”.

    Thus, attempting to use incompatible filters will result in a 422 (Unprocessable) error from the server.

    Solution options:

    • revise the data scheme, and apply migrations if needed;

    • use another filter type, e.g. meta.user_info.group=1.