Database Designer Tool

After you have created a new database, you want to create a physical design that allows the database to run efficiently, but without using too much physical storage. The best way to create a physical design for your database is to run the Vertica Database Designer (DBD).

What is Database Designer?

Database Designer (DBD) uses sophisticated strategies to create a design that provides excellent performance for ad-hoc queries and specific queries while using disk space efficiently. During the design process, DBD analyzes the logical schema definition, sample data, and sample queries, and creates a projections in the form of a SQL script that you deploy automatically or manually. This script creates a minimal set of superprojections to ensure K-safety.

How Database Designer Works

The DBD creates a physical database design based on sample queries and data that you provide. You can also specify optimization objectives:

• Optimize for query performance
• Optimize for storage footprint
• Optimize for a balance of query performance and storage footprint

After DBD deploys (you can specify automatic or manual deployment in the Administration Tools or MC wizard), you’ll have the following three scripts and log file:

• The design script (db_name_design.sql) contains SQL statements create the projections for your design and distribute data uniformly across the cluster.
• The deployment script (db_name_deploy.sql) that creates new projections and refreshes existing projections for your design.
• A backup script (db_name_design.sql.xxxxxxx) that contains SQL statements to deploy the design that existed on the system before.
• The log file (designer.log) records the steps that the deployment script took and any errors that occurred.

Running Database Designer

There are three methods for creating a physical design with DBD. This tutorial explains how to run DBD using the Administration Tools. Visit the following links to learn about the other two methods.

To use the Administration Tools to create an optimized database design, follow these steps as a DBADMIN user.

1. Log in as the dbadmin user and start Administration Tools by typing man admintools.
2. From the main menu, start the database for which you want to create a design by selecting Start Database.
3. On the main menu, select Configuration Menu and click OK
4. On the Configuration Menu, select Run Database Designer and click OK.
5. On the Select a database to design window, select the database for which you are creating a design and click OK.
6. On the Enter the directory for Database Designer output window, enter the full path to the directory to contain the design script, deployment script, backup script, and log files, and click OK.
7. On the Database Designer window, enter a name for the design and click OK.
8. On the Design Type window, choose which type of design to create and click OK.
9. The Select schema(s) to add to query search path window lists all the schemas in the database that you selected. Select the schemas that contain representative data that you want Database Designer to consider when creating the design and click OK.
10. On the Design Options window, select whether you want DBD to do one or all of the listed tasks.
11. The final window summarizes the choices you have made and offers you two choices:
    • Click Proceed to build the design.
    • Click Cancel the design and go back to change some of the parameters as needed.
12. Creating a design can take a long time. To cancel a running design from the Administration Tools window, enter Ctrl+C.

Watch this video for a demo:

After Running Database Designer

If you didn’t tell DBD to immediately deploy the scripts and create the design, take these steps when you are ready:

1. Review the deployment script to make sure you are comfortable with the design that DBD created.
2. If you need to make changes, modify the deployment script.
3. Test the design on a non-production server before deploying the design to your production server.
4. Deploy the design by executing the deployment script.

Learn More

For comprehensive information about working with Database Designer, see Creating a Database Design in the Vertica product documentation.

• Database tools
• Database difference tool
• Database versioning tools
• Database diagram tool
• Database Merge tool