General events

In case an event with some custom structure generated by video analytics, plugin or some client application needs to be stored, general events mechanism should be used. It’s intended for storing the events of free structure. Overall size of the general event shouldn’t exceed limit of 2 MB.

Since general events have a free-form structure, they have to be separable by type, which should be specified when saving the event. When saving an general event, some common info should also be provided (e.g., the time the event was created, the time the event ended, the account the event owned by). Despite the fact the general event structure is free, it may contain a certain set of platform-specific fields (e.g. location, stream ID, track ID), the search for which can be performed more efficiently due to the optimization of storing this data in the database. See create general events for details.

The user is provided with:

The user undertakes:

Index building

For efficient lookups indexes should be built. Indexes have to be partial, that is, built for a specific general event type. Custom event 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.

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

CREATE INDEX ix_general_event_user_status ON general_event USING BTREE ((jsonb_extract_path_text(event, 'user_info', 'status'))) WHERE event_type = 'my-shiny-event';

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 partial B-tree indexes will be useful:

CREATE INDEX ix_general_event_user_temperature ON general_event USING BTREE ((jsonb_extract_path_text(event, 'user_info', 'temperature')::int)) WHERE event_type = 'my-shiny-event';
CREATE INDEX ix_general_event_user_temperature ON general_event USING BTREE ((jsonb_extract_path_text(event, 'user_info', 'temperature')::float)) WHERE event_type = 'my-shiny-event';

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, categories)

  • 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 partial GIN indexes for general events:

Since general events must be filtered by event_type, GIN indexes should be partial (with WHERE clause):

-- General GIN index for all JSONB operations on event field for specific event type
-- This enables efficient queries on any keys/values in the event column
CREATE INDEX idx_general_event_my_type_gin ON general_event USING GIN (event) WHERE event_type = 'my-shiny-event';

-- Path-specific indexes for frequently filtered fields (more efficient)
CREATE INDEX idx_general_event_tags_gin ON general_event USING GIN ((event -> 'tags')) WHERE event_type = 'my-shiny-event';
CREATE INDEX idx_general_event_categories_gin ON general_event USING GIN ((event -> 'categories')) WHERE event_type = 'my-shiny-event';
CREATE INDEX idx_general_event_metadata_gin ON general_event USING GIN ((event -> 'metadata')) WHERE event_type = 'my-shiny-event';

Example use cases for general events:

  1. Filtering by tag arrays:

    Query: event.tags__contains:json=[\"analytics\", \"real-time\"]

    With GIN index on event field, PostgreSQL quickly finds rows where tags contain both elements.

  2. Finding overlapping categories:

    Query: event.categories__overlaps:json=[\"video\", \"audio\"]

    GIN index efficiently identifies rows sharing any of these categories.

  3. Complex object matching:

    Query: event.metadata__contains:json={\"status\": \"active\", \"priority\": 1}

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

Important notes for general events:

  • Always use partial indexes with WHERE event_type = 'your-type' clause to optimize index size and performance

  • Build separate GIN indexes for each event_type that uses JSON operators

  • Path-specific indexes (e.g., event -> 'tags') are more efficient than general indexes when you filter specific fields frequently

Note: For standard events (with meta field), see the User defined data 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 general_event_test (id serial primary key, event_type varchar, event jsonb);
CREATE INDEX IF NOT EXISTS ix_geneal_event_test_status ON general_event_test USING BTREE ((jsonb_extract_path_text(event, 'user_info', 'status')::smallint)) WHERE event_type = 'my-shiny-event';

    Then trying to insert the following data into the table

    INSERT INTO general_event_test (event_type, event) VALUES ('my-shiny-event', '{"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 event custom data 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 general_event_test (id serial primary key, event_type varchar, event jsonb);
INSERT INTO general_event_test (event_type, event) VALUES ('my-shiny-event', '{"user_info": {"status": 0, "temperature": 36}}');
INSERT INTO general_event_test (event_type, event) VALUES ('my-shiny-event', '{"user_info": {"status": 5, "temperature": "normal"}}');

    Then trying to build the following index on the table

    CREATE INDEX IF NOT EXISTS ix_general_event_test_temperature ON general_event_test USING BTREE ((jsonb_extract_path_text(event, 'user_info', 'temperature')::int4)) WHERE event_type = 'my-shiny-event';

    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 general_event_test (id serial primary key, event_type varchar, event jsonb);
INSERT INTO general_event_test (event_type, event) VALUES ('my-shiny-event', '{"user_info": {"group": "aliens"}}');
INSERT INTO general_event_test (event_type, event) VALUES ('my-shiny-event', '{"user_info": {"group": 1}}');

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

    SELECT * FROM general_event_test WHERE jsonb_extract_path_text(event, '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. event.user_info.group=1.