Columnar JSONB

Enable columnar JSONB

Use the following statement to enable column-oriented storage optimization for a specific JSONB column in a table.

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

table_name is the table name. column_name is the column name.

Enable DECIMAL type inference

Hologres V2.0.11 and later support column-oriented storage optimization for DECIMAL data. Consider the following JSON data as an example:

{
  "name":"Mike",
  "statistical_period":"2023-01-01 00:00:00+08",
  "balance":123.45
}

After you enable DECIMAL type inference, the value of balance also supports column-oriented optimization. Use the following statement to enable this feature:

-- Enable column-oriented optimization for DECIMAL values in a specific column in a specific table.
ALTER TABLE <table_name> ALTER COLUMN <column_name> SET (enable_decimal = ON);

table_name is the table name. column_name is the column name.

Checking columnar JSONB status

Use the following statements to check the columnar JSONB status of a table.

• The following command is supported in Hologres V1.3.37 and later.

  Note

  In Hologres V2.0.17 and earlier, this command can show only tables in the public schema. Starting from V2.0.18, it can show the status of tables in other schemas.

  -- In V2.0.17 and earlier, you can query only tables in the public schema. In V2.0.18 and later, you can query tables in other schemas.
  SELECT * FROM hologres.hg_column_options WHERE schema_name='<schema_name>' AND table_name = '<table_name>';

  schema_name is the schema name, and table_name is the table name.

• For Hologres V1.3.10 to V1.3.36, use the following command.

  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;

  table_name is the table name.

• Example result:

  In the result, if the attoptions or option property for a column is enable_columnar_type = ON, this indicates that the configuration is successful.

   num | name |           type           | notnull | comment | primary_key | default |          attoptions
  ------+------+--------------------------+---------+---------+-------------+---------+------------------------------
     1 | ds   | timestamp with time zone | f       |         | f           |         |
     2 | tags | jsonb                    | f       |         | f           |         | {enable_columnar_type=on}
  (2 rows)

Disable columnar JSONB

Use the following command to disable column-oriented storage optimization for a specific JSONB column in a table.

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

table_name is the table name. column_name is the column name.

Disable DECIMAL type inference

To disable DECIMAL type inference for a specific column, use the following command.

-- Disable column-oriented optimization for DECIMAL values in a specific column in a specific table.
ALTER TABLE <table_name> ALTER COLUMN <column_name> SET (enable_decimal = OFF);

table_name is the table name. column_name is the column name.

Setting a bitmap index

In Hologres, the bitmap_columns property specifies a bitmap index, which is an independent index structure separate from data storage. It uses a bitmap vector structure to accelerate equality comparisons, allowing for fast equality filtering of data within file blocks. Starting from V2.0, Hologres supports setting a bitmap index for JSONB columns that have column-oriented storage enabled. After columnar JSONB is enabled, the system parses data into seven data types: int, int[], bigint, bigint[], text, text[], and jsonb. When a bitmap index is enabled, the system builds a bitmap index for data inferred as int, int[], bigint, bigint[], text, and text[] types.

The syntax is as follows:

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

Parameters:

Example

1. Create a table.

   DROP TABLE IF EXISTS user_tags;
   -- Create a data table
   BEGIN;
   CREATE TABLE IF NOT EXISTS user_tags (
       ds timestamptz,
       tags jsonb
   );
   COMMIT;

2. Enable column-oriented storage optimization for the tags column.

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

3. Check the status of columnar JSONB storage.

   select * from hologres.hg_column_options where table_name = 'user_tags';

   In the following result, the tags row's options property is {enable_columnar_type=on}, which indicates that the configuration is successful.

    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) Force a data flush.

   After data is written, the system performs columnar JSONB optimization during a data flush. To see the effect immediately, run the following command to force a data flush.

   VACUUM user_tags;

6. Run a sample query.

   Run the following SQL statement to query the first_name where id is 10.

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

7. Check the execution plan to verify that the query uses the columnar optimization.

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

   If columnar_access_used appears in the result, the columnar JSONB optimization was used.

8. For the query in Step 6, you can also set a bitmap index on the tags column to improve the efficiency of equality queries for a specific key by using the following command:

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

9. Check the execution plan to verify that the bitmap index is effective.

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

   The result is as follows:

   QUERY PLAN
   Gather  (cost=0.00..6.42 rows=3334 width=8)
   [2:1 id=100002 dop=1 time=7/7/7ms rows=1(1/1/1) mem=584/584/584B open=0/0/0ms get_next=7/7/7ms]
    -> Local Gather  (cost=0.00..6.30 rows=3334 width=8)
        [id=6 dop=2 time=6/4/3ms rows=1(1/0/0) mem=584/584/584B open=0/0/0ms get_next=6/4/3ms pull_dop=0/0/0]
        -> Decode  (cost=0.00..6.30 rows=3334 width=8)
            [id=4 dop=2 time=7/5/3ms rows=1(1/0/0) mem=0/0/0B open=7/5/3ms get_next=0/0/0ms]
            -> Project  (cost=0.00..6.20 rows=3334 width=8)
                [id=3 dop=2 time=7/5/3ms rows=1(1/0/0) mem=2/2/2KB open=7/5/3ms get_next=0/0/0ms]
                -> Seq Scan on user_tags  (cost=0.00..5.18 rows=3334 width=8)
                    Filter: (int4((tags -> 'id'::text)) = 10)
                    [id=2 dop=2 time=7/5/3ms rows=1(1/0/0) mem=260/160/60KB open=7/5/3ms get_next=0/0/0ms scan_rows=10001(8192/5000/1809) bitmap_used=1]
   ADVICE:
   [node id : 2]  Table user_tags misses bitmap index: ColRef_0012.

   If bitmap_used appears in the result, the bitmap index was used.

Returning the entire JSONB column

Hologres columnar JSONB provides good optimization for most use cases. However, for scenarios where the query result needs to include the entire JSONB column, performance may be lower compared to storing data in the original JSONB format. For example, consider the following SQL statements:

-- DDL for creating the table
CREATE TABLE TBL(key int, json_data jsonb); 
SELECT json_data FROM TBL WHERE key = 123;
SELECT * FROM TBL limit 10;

This performance degradation occurs because the underlying layer has already converted the JSONB data into column-oriented storage. Therefore, when you need to query the complete JSON data, the system must reassemble the columnar data back into the original JSONB format:

This step generates significant I/O and conversion overhead. If the data volume is large and the number of columns is high, this process could become a performance bottleneck. Therefore, we recommend not enabling column-oriented optimization in this scenario.

Extremely sparse JSONB data

When Hologres encounters a sparse field while converting JSONB data to a column-oriented format, it merges these fields into a special column named holo.remaining to prevent the number of columns from exploding. Therefore, if the JSONB data consists entirely of sparse fields—for example, in an extreme case where each field appears only once—columnar conversion will not be effective. Because all fields are sparse, they are all merged into the holo.remaining column, preventing any actual columnar conversion. In this case, there will be no improvement in query performance.

JSONB data with complex nested structures

In the following JSONB data, the root node is an array that contains non-homogeneous JSONB data. Currently, when Hologres converts JSONB data to a column-oriented format, it downgrades such complex nested structures to a single column. Therefore, enabling columnar JSONB optimization for this type of JSONB data will not yield significant query performance benefits.

'[
  {"key1": "value1"}, 
  {"key2": 123},
  {"key3": 123.01}
]'

Slow query diagnosis

If you find that query performance is worse after you enable columnar JSONB, first check if the query returns the entire JSONB column. If the SQL statement is too complex, you can use the EXPLAIN ANALYZE command for diagnosis. An example command is as follows:

CREATE TABLE TBL(key int, json_data json); -- DDL for creating the table
ALTER TABLE TBL ALTER COLUMN json_data SET (enable_columnar_type = on);
Explain Analyze SELECT json_data FROM TBL WHERE key = 123;

The EXPLAIN ANALYZE result contains hint information. If the hint contains the following message, it means the query returned the entire JSONB column, which caused performance degradation:

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

More efficient SQL syntax

• There are multiple ways to convert JSONB field data to TEXT format, but the ->> operator performs better. For example, to get the json_data column's name attribute:

  -- Better performance
  SELECT json_data->>'name' FROM tbl; 
  -- Average performance
  SELECT (json_data->'name')::text FROM tbl;

• If a JSON field stores a TEXT array and you need to check whether the array contains a specific value, we recommend using the following syntax:

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