Effective SQL - Indexing and denormalizing JSONB

• AppMap

  4 63 5.5

  AppMap is the ultimate code reviewer for issues that static analysis and AI code assistants cannot find.

  

Here's a concrete example of a JSONB index and generated column. For this example, I will use a JSON format called AppMap, which represents a class dependency diagram and execution trace of a program. At the top level, the AppMap format includes metadata, classMap, and events.

• appmap-ruby

  8 96 7.0 Ruby

  AppMap client agent for Ruby

  

Now, what if you want to be able to efficiently fetch a subset of JSONB data, without having to load and parse every object? At AppLand, we store AppMap data in the database. Each AppMap has a mandatory section called metadata, and we want to be able to efficiently pull out the metadata of selected rows without having to parse the entire JSONB column (the metadata is usually less than 1% of the total JSON object). Awesomely, since PostgreSQL 12, PostgreSQL has a feature called generated columns.

• InfluxDB

  www.influxdata.com sponsored

  Power Real-Time Data Analytics at Scale. Get real-time insights from all types of time series data with InfluxDB. Ingest, query, and analyze billions of data points in real-time with unbounded cardinality.

  

Suggest a related project