7 - HP Vertica Database Intro Series
Database Designer (DBD - database designer)
A challenging task for any DBA and the creation of the physical model. Different customers have different needs and expectations, bounded by its limited resources. To deal with these challenges Vertica uses a personalized approach, allowing users to customize their designs for specific applications and scenarios. To meet the different needs of customers, any physical database design tool should allow its users to exchange query performance and storage space for different applications.
Information Database (DBD) uses three design policies:
DBD Optimized for Data Load - propose the minimum required set of Super projections (containing all columns) to enable quick loading and provide the necessary tolerance failures.
DBD Optimized for consultation - may propose additional projections (possibly non-Super) so that all the workload queries are fully optimized.
DBD Balanced - proposes projections up to the point where the additional projections do not bring sufficient benefits in query optimization.
These options allow users to choose to exchange query performance and storage space, considering the upgrade costs. These policies will indirectly control the number of projections proposed to achieve the desired balance between the constraints of query performance, storage and handling.
In real-world environments, loads of consultation work often evolve over time. A projection that was useful in the past may not be relevant today and may be losing space or slow down loads. This space can be reused instead of creating new projections that optimize the current workloads.
To meet these workload changes, DBD operates in two different modes:
Understandable - DBD creates a whole new physical design that optimizes and for the current workload while maintaining the existing parts of the project that are beneficial and erasing the parts that are not beneficial.
Incremental - will optionally create additional projections that optimize new queries without disturbing the existing physical design. The incremental mode should be used when workloads have not changed significantly. No queries as input, DBD will create optimizations purely for storage and cargo.
We will talk more about DBD in future articles because this is a very important topic and is one of the items that should be well understood by the DBA Vertica.
