Best practices for Redshift connections
Configuring your ThoughtSpot server
We recommend configuring the following settings when creating connections:
-
Turn off ‘Auto Indexing’, and manually set the Index Type on columns that would be helpful when searching.
Contact ThoughtSpot Support to do this configuration.
Optimizing query performance
User experience is strongly impacted by the performance of queries in the underlying database. Redshift is a columnar MPP database built for analytic workloads, and proper tuning can have a significant impact on query performance.
You should always follow AWS best practices to optimize query performance. For details, see Tuning query performance in Amazon’s Redshift documentation.
Workload management and monitoring
We recommend that you implement workload management (WLM) within Redshift to prioritize queries appropriately. For more information, refer to the following links in Amazon’s Redshift documentation:
-
Implementation: Implementing workload management
-
Workload and performance data: System monitoring tables
Concurrency scaling
ThoughtSpot may generate large numbers of concurrent queries. Redshift’s concurrency scaling feature can be enabled to provide consistent query performance during periods of high activity.
For details, see Working with concurrency scaling in Amazon’s Redshift documentation.
Data modeling
In Redshift, query performance can be improved significantly using Sort and Distribution keys on large tables. Selecting an optimized compression type can also have a big impact on query performance.
These and other important topics are covered in Amazon Redshift best practices for table design in Amazon’s Redshift documentation.
Materialized views
Redshift now supports materialized views, and it is recommended to create a materialized view and use it in place of any complex views or poor performing table joins.
For details, see Creating materialized views in Amazon Redshift in Amazon’s Redshift documentation.
Defining joins between tables
To search more than one table at the same time in ThoughtSpot, you must define joins between these tables by specifying the columns that contain matching data across two tables. These columns represent the ‘primary key’ and ‘foreign key’ of the join. You can define them in either Redshift or ThoughtSpot.
For details on the syntax for defining primary and foreign keys in Redshift, see ALTER TABLE in Amazon’s Redshift documentation.
For details on defining joins in ThoughtSpot, see Join a table or view to another data source.
Related information
