Hologres:Accelerate JSONB queries

How it works

When you enable column-oriented storage on a JSONB column, Hologres converts the column into multiple strongly typed sub-columns at the storage layer, one per JSON key. Queries that target a specific key scan only that sub-column instead of deserializing the entire JSONB value — the same efficiency gain you get from columnar storage on structured data.

Prerequisites

Before you begin, make sure that:

• Your Hologres instance is V1.3 or later. For best performance, upgrade to V1.3.37 or later. To upgrade manually, see Instance upgrades. To request an upgrade through the support team, see Obtain online support for Hologres.

• The target column is in a column-oriented table. Column-oriented storage for JSONB is not supported on row-oriented tables.

• The table contains at least 1,000 rows. Column-oriented storage is triggered only at that threshold.

When column-oriented storage underperforms

Column-oriented storage does not improve — and may hurt — performance in these cases:

Only the -> and ->> operators trigger column-oriented access. Queries using other operators fall back to full JSONB scans, which may be slower than the original storage mode.

Enable column-oriented storage

-- Enable column-oriented storage for a JSONB column.
ALTER TABLE <table_name> ALTER COLUMN <column_name> SET (enable_columnar_type = ON);

VACUUM <table_name>;

-- Enable DECIMAL type inference for a JSONB column.
ALTER TABLE <table_name> ALTER COLUMN <column_name> SET (enable_decimal = ON);

Verify the storage mode

Hologres V1.3.37 and later

SELECT * FROM hologres.hg_column_options
WHERE schema_name = '<schema_name>' AND table_name = '<table_name>';

Note

In V2.0.17 and earlier, this query works only for tables in the public schema. In V2.0.18 and later, it works for all schemas.

A column with {enable_columnar_type=on} in the options field has column-oriented storage enabled.

SELECT DISTINCT
    a.attnum as num,
    a.attname as name,
    format_type(a.atttypid, a.atttypmod) as type,
    a.attnotnull as notnull,
    com.description as comment,
    coalesce(i.indisprimary,false) as primary_key,
    def.adsrc as default,
    a.attoptions
FROM pg_attribute a
JOIN pg_class pgc ON pgc.oid = a.attrelid
LEFT JOIN pg_index i ON
    (pgc.oid = i.indrelid AND i.indkey[0] = a.attnum)
LEFT JOIN pg_description com ON
    (pgc.oid = com.objoid AND a.attnum = com.objsubid)
LEFT JOIN pg_attrdef def ON
    (a.attrelid = def.adrelid AND a.attnum = def.adnum)
WHERE a.attnum > 0 AND pgc.oid = a.attrelid
    AND pg_table_is_visible(pgc.oid)
    AND NOT a.attisdropped
    AND pgc.relname = '<table_name>'
ORDER BY a.attnum;

Disable column-oriented storage

-- Disable column-oriented storage for a JSONB column.
ALTER TABLE <table_name> ALTER COLUMN <column_name> SET (enable_columnar_type = OFF);

-- Disable DECIMAL type inference for a JSONB column.
ALTER TABLE <table_name> ALTER COLUMN <column_name> SET (enable_decimal = OFF);

Add bitmap indexes for point queries (V2.0+)

After column-oriented storage converts JSONB keys into typed sub-columns, you can add bitmap indexes on those sub-columns to accelerate point queries (equality comparisons). Bitmap indexes are most effective when the key appears frequently across rows and you query by exact value.

After column-oriented storage is enabled, Hologres parses these types: INT, INT[], BIGINT, BIGINT[], TEXT, TEXT[], and JSONB. Bitmap indexes are created for INT, INT[], BIGINT, BIGINT[], TEXT, and TEXT[] sub-columns.

call set_table_property('<table_name>', 'bitmap_columns', '[<columnName>{:[on|off]}[,...]]');

Example

The following example walks through enabling column-oriented storage, importing data, querying, and verifying the execution plan.

1. Create the table.

   DROP TABLE IF EXISTS user_tags;
   
   BEGIN;
   CREATE TABLE IF NOT EXISTS user_tags (
       ds   timestamptz,
       tags jsonb
   );
   COMMIT;

2. Enable column-oriented storage on the tags column.

   ALTER TABLE user_tags ALTER COLUMN tags SET (enable_columnar_type = ON);

3. Verify that column-oriented storage is enabled.

   SELECT * FROM hologres.hg_column_options WHERE table_name = 'user_tags';

   Expected output — the options field for tags shows {enable_columnar_type=on}:

    schema_name | table_name | column_id | column_name |       column_type        | notnull | comment | default |          options
   -------------+------------+-----------+-------------+--------------------------+---------+---------+---------+---------------------------
    public      | user_tags  |         1 | ds          | timestamp with time zone | f       |         |         |
    public      | user_tags  |         2 | tags        | jsonb                    | f       |         |         | {enable_columnar_type=on}
   (2 rows)

4. Import data.

   INSERT INTO user_tags (ds, tags)
   SELECT
       '2022-01-01 00:00:00+08',
       ('{"id":' || i || ',"first_name":"Sig","gender":"Male"}')::jsonb
   FROM generate_series(1, 10001) i;

5. (Optional) Flush data to disk to see the column-oriented storage effect immediately.

   VACUUM user_tags;

6. Query data by a specific key.

   SELECT (tags -> 'first_name')::text AS first_name
   FROM user_tags
   WHERE (tags -> 'id')::int = 10;

7. Verify that column-oriented storage is active by checking the execution plan.

   -- Enable detailed execution statistics.
   SET hg_experimental_show_execution_statistics_in_explain = ON;
   
   EXPLAIN ANALYZE
   SELECT (tags -> 'first_name')::text AS first_name
   FROM user_tags
   WHERE (tags -> 'id')::int = 10;

   columnar_access_used in the output confirms that column-oriented storage is being used.

   

8. Add a bitmap index on tags to speed up the point query in step 6.

   call set_table_property('user_tags', 'bitmap_columns', 'tags');

9. Verify the bitmap index is used.

   EXPLAIN ANALYZE
   SELECT (tags -> 'first_name')::text AS first_name
   FROM user_tags
   WHERE (tags -> 'id')::int = 10;

   bitmap_used in the output confirms the bitmap index is active.

   

Best practices

-- Faster: uses ->> directly
SELECT json_data->>'name' FROM tbl;

-- Slower: converts JSONB field to text via cast
SELECT (json_data->'name')::text FROM tbl;

SELECT key FROM tbl WHERE jsonb_to_textarray(json_data->'phones') && ARRAY['123456'];

CREATE TABLE tbl (key int, json_data json);
ALTER TABLE tbl ALTER COLUMN json_data SET (enable_columnar_type = on);

EXPLAIN ANALYZE SELECT json_data FROM tbl WHERE key = 123;

Column 'json_data' has enabled columnar jsonb, but the query scanned the entire Jsonb value

VACUUM <table_name>;

FAQ