myVertica  

Vertica Blog

Vertica Tip: The System Table for System Tables

This blog post was authored by Sarah Lemaire. Most of you probably know that Vertica provides system tables that allow you to monitor • System resources • Background processes • Workload • Performance • Catalog size These tables help you to profile, diagnose, and view historical data equivalent to load streams, query profiles, Tuple Mover […]

Identifying Projection Skew

This blog post was authored by Curtis Bennett. In Vertica, projections can either be replicated (unsegmented), or segmented. A segmented projection divides the data up across all the nodes in your cluster. Segmentation works by hashing a key value, and then using some simple math, figuring out which node that piece of data will live […]

What Projections are not Being Used

This blog post was authored by Eugenia Moreno. It is common to create new projections to improve performance in Vertica. However, you might forget about the old projections. Vertica still loads data in projections that you might not be using. A projection that is loaded but not picked up by the Vertica optimizer consumes storage […]

Vertica Quick Tip: Sampling Data from the Entire Table

This blog post was authored by Jim Knicely. A development or quality assurance team often times requests access to a sub-set of production data. One way to do that would be to make use of the LIMIT clause. Example: dbadmin=> SELECT COUNT(*) FROM big_number_table; COUNT ———— 1000000000 (1 row) dbadmin=> SELECT 0.05*1000000000 “5_percent”; 5_percent ————- […]

Query Execution in Eon Mode

This blog post was authored by Ben Vandiver. How Vertica distributes query processing across the cluster in Eon mode is a complex topic that is best illustrated through a concrete example. As part of this post, we’ll start with a simple data load and walk through metadata storage and query execution. To begin, we need […]

Vertica Quick Tip: Superfast Table Copy

This blog post was authored by Jim Knicely. Very often we need to make a copy of a very large table in order to do some development or quality assurance type of duties. Typically we’ll use a CREATE TABLE AS SELECT (CTAS) statement to make that copy. Example: dbadmin=> SELECT COUNT(*) FROM big_number_table; COUNT ———— […]

Vertica Quick Tip: Add a Time Zone

This blog post was authored by Jim Knicely. Vertica recognizes many time zones. However, there might come a time (zone) when you will need to reference one that is not available by default. Luckily it’s relatively easy to add a time zone to Vertica. Example: The “Hawaii Standard Time (HST)” is a default time zone […]

What’s New in Vertica 9.0.1: Security and Authentication

This blog post was authored by Soniya Shah. In this release, we introduce some security enhancements. We’ve added the ability to grant and revoke privileges on system tables, using the same syntax as you would for granting and revoking on tables. However, there are some limitations about the types of privileges you can use with […]

Vertica Quick Tip: Default Size of the NUMBER Data Type

This blog post was authored by Jim Knicely. When creating a table where you do not define a precision for a NUMBER column data type, Vertica will use a default precision of 38 digits. Often this is larger than necessary. By specifying NUMBER(37) you will potentially get better query performance and save on storage. Why? […]

Vertica Tip: Predicting the Resources a Statement Needs

This blog post was authored by Eugenia Moreno. You may find you want to set up resource pools before running queries to know how many resources a particular query needs. One way to do this is to create a small resource pool, profile the query, and note when the query is rejected. When the query […]