Creating a database design
A design is a physical storage plan that optimizes query performance. Data in Vertica is physically stored in projections. When you initially load data into a table using INSERT, COPY (or COPY LOCAL), Vertica creates a default superprojection for the table. This superprojection ensures that all of the data is available for queries. However, these superprojections might not optimize database performance, resulting in slow query performance and low data compression.
To improve performance, create a design for your Vertica database that optimizes query performance and data compression. You can create a design in several ways:
-
Use Database Designer, a toolthat recommends a design for optimal performance.
-
Use Database Designer to create an initial design and then manually modify it.
Database Designer can help you minimize how much time you spend on manual database tuning. You can also use Database Designer to redesign the database incrementally as requirements such as workloads change over time.
Database Designer runs as a background process. This is useful if you have a large design that you want to run overnight. An active SSH session is not required, so design and deploy operations continue to run uninterrupted if the session ends.
Tip
Vertica recommends that you first globally optimize your database using the Comprehensive setting in Database Designer. If the performance of the comprehensive design is not adequate, you can design custom projections using an incremental design and manually, as described in Creating custom designs.In this section
- About Database Designer
- How Database Designer creates a design
- Database Designer access requirements
- Logging projection data for Database Designer
- General design settings
- Building a design
- Resetting a design
- Deploying a design
- How to create a design
- Running Database Designer programmatically
- Creating custom designs