Optimizing Vertica Schemas for a Low Latency Application
What best practices are recommended to optimize Vertica for a UI dashboard? We use Vertica to populate a Reporting UI for our clients, it runs about 1 million queries per day on tables with rows in the hundreds of billions.
Currently we use 3 fact tables. A common use case is unioning a subset of columns from the 3 tables into a common view.
Ideally, we would like to have all of our UI based queries to take <500ms for a month of data (we store in an hourly format).
What approaches and schemas should I consider at a high level? Do flattened tables fit this type of use case? Are there other schema level optimizations I should consider? An obvious one is a new table eliminates the need for unioning. Any other approaches that folks here use, or is Vertica not even well suited to this type of use case? Thanks!