Improving Dashboard Performance with Postgre SQL JSON Fields in Bold BI®
When working with Bold BI®, you may notice a delay in dashboard performance when using joins and accessing JSON data for the data source created with Postgre SQL. This is because the usage of JSON fields can significantly slow down query execution times.
Understanding the Impact of JSON Fields
A simple query with one measure and one dimension can take more than 150 seconds to execute when using JSON fields. If the complexity of the query increases, it may even reach a timeout.
In contrast, the same query without JSON fields can run in less than 5 seconds.
Enhancing Performance with JSONB and JsonPath Datatypes
To mitigate this performance issue, PostgreSQL recommends using JSONB datatype or JsonPath datatype for JSON fields. These datatypes are designed to provide efficient and flexible manipulation and querying of JSON data.
Indexing Operations for Improved Performance
In addition to using the recommended datatypes, you can also perform indexing operations in PostgreSQL to further enhance the performance of JSON fields. Indexing can help speed up data retrieval times, making your Bold BI® dashboard more responsive.
For more information on JSON indexing and the use of JSONB and JsonPath datatypes, refer to the following PostgreSQL documentation:
By implementing these recommendations, you can significantly improve the performance of your Bold BI® dashboard when working with JSON data.
References
