Vertica Concepts

Understanding the Vertica Query Optimizer

This blog post was authored by Soniya Shah. The Vertica query optimizer uses statistics about the data to create a query plan, which contains a set of operations to compute the requested result. Depending on the properties of the projections defined in your database, the optimizer can choose faster and more efficient operations. This is […]

Improving Performance and Memory Acquisitions for Vertica Queries

This blog post was authored by Shrirang Kamat. The following design considerations will help you improve the performance and memory of your Vertica queries. When creating table definitions, you should carefully choose the size of the lookup column based on your data. Properly sizing your column based on your data will help to improve performance. […]

Authentication Methods for dbadmin

This blog post was authored by Sumeet Keswani. In Vertica, when you create a new database, there are no configured authentication methods. In this case, Vertica assumes that all users, including the dbadmin, have an implicit password authentication. Users can use this authentication method both for authenticating over a network interface and for over a […]

Flattened Tables

This blog post was authored by Soniya Shah. Before release 8.1., Vertica users could denormalize their data by combining all fact and dimension table columns in a single ‘fat’ table. These tables facilitated faster query execution. However, this approach required users to maintain redundant sets of normalized and denormalized data, which incurred its own overhead. […]

Phrase Search with Vertica Text Search

This blog post was authored by Serge Bonte. Vertica Text Search Vertica already provides Text Search. Text Search allows you to quickly search the contents of a single CHAR, VARCHAR, LONG VARCHAR, VARBINARY, or LONG VARBINARY field within a table to locate a specific token. Vertica implements that capability using a dedicated Text Index to […]

Effective vsql in Vertica

This blog post was authored by Maurizio Felici. vsql is included in each Vertica installation and is lightweight, with a tight integration with Vertica. Vsql is installed on every Vertica server and can also be installed on non-server hosts using the client package. Executing SQL commands through vsql is often faster than navigating GUI’s menu. […]

Analytic Queries in Vertica

This blog post was authored by Soniya Shah. Analytic functions handle complex analysis and reporting tasks. Here are some example use cases for Vertica analytic functions: • Rank the longest standing customers in a particular state • Calculate the moving average of retail volume over a specific time • Find the highest score among all […]

Working with Joins

This blog post was authored by Soniya Shah. Vertica supports a variety of join types. This post discusses the following joins: • Inner joins • Left, right, and full outer joins • Natural joins • Cross joins In Vertica, we refer to the tables participating in the join as left or right. The left table […]

Time Series Analytics

This blog post was authored by Soniya Shah. Time series analytics is a powerful Vertica tool that evaluates the values of a given set of variables over time and groups those values into a window based on a time interval for analysis and aggregation. Time series analytics is useful when you want to analyze discrete […]

Concurrency and Workload Management

This blog post was authored by Soniya Shah. Vertica workloads range from simple primary key lookups to analytical queries that include several large tables and joins. Different types of load jobs must keep the data updated. Vertica has a mixed-workload management capability that is easy to use. Vertica can process queries both concurrently and in […]

Understanding Users, Privileges, and Roles

This blog post was authored by Soniya Shah. Every Vertica database has one or more users. When users connect to the database, they log in with credentials that a superuser defines. Database users should only have access to the database resources they need to perform their tasks. To navigate these necessities, Vertica has designated users, […]

Getting Rid of Range Joins

This blog post was authored by Soniya Shah. You can use range joins to categorize data into buckets. Vertica provides performance optimizations for =, and BETWEEN predicates. These optimizations are particularly useful when a column from one table is restricted to be in a range specified by two columns of another table. Range joins can […]

Big Flat Fact Tables

This blog post was authored by Steve Sarsfield. For decades, it’s been widely accepted that snowflake and star schemas facilitate getting optimal performance from your data warehouse. You normalize data by identifying the rows of data that you typically ingest, and creating a schema that is optimized for the types of queries you want to […]

Understanding AT TIME ZONE

TIMESTAMPTZ AT TIME ZONE and TIMESTAMP AT TIME ZONE return date input in another time zone. How Vertica executes AT TIME ZONE varies, depending on whether the input is a TIMESTAMPTZ or TIMESTAMP. At first glance, this might be confusing. More about that later. First, let’s review AT TIME ZONE syntax: { TIMESTAMPTZ | TIMESTAMP […]

Create and Assign Roles

A role is a collection of privileges that can be granted to one or more users or roles. Assigning roles prevents you from having to manually grant sets of privileges for each individual user. For the most part, creating and assigning roles is fairly straightforward. However, the user to which roles are assigned needs to […]

Filtering Data While Loading into Vertica

Suppose you have a CSV file and you want to copy some, but not all, of its contents into a Vertica table. There are two ways you can to do this: • Use the SKIP keyword with COPY. • Use the head or tail Linux command. Let’s see how this works. The Data Here’s a […]

Dynamic Row and Column Access Policies

The content of this blog post is based on an article authored by Maurizio Felici. The Vertica Analytic Database access policies act on columns and rows to provide extra security on data in your tables. You can create flexible access policies that limit which users can access certain data by applying the access policy to […]

LDAP and User Accounts

This blog post was authored by Soniya Shah. If you are a database administrator, you probably need to authenticate users in Vertica. There are many methods users can use to authenticate, including Ident, Kerberos, LDAP, and hash. This blog walks you through the steps to take if you want to authenticate some users using LDAP […]

The Life of a Query, According to Henry Ford

While Henry Ford did not in fact develop or even patent the modern assembly line (that credit goes to Ransom E. Olds), he relied heavily on the process for automobile production.

Redesigning Projections for Query Optimization

When you submit a query to Vertica, the Vertica query optimizer automatically assembles a query plan, which consists of a set of operations to compute the requested result. Depending on the properties of the projections defined in your database, the query optimizer can choose faster and more efficient operations. Thus, it?s important to recognize what you can do to optimize your projections to improve query performance

Troubleshooting Vertica Query Performance with System Tables

Do you want to learn how to troubleshoot your query performance issues?  We’ve got you covered. Just attend the Query Performance Tuning and Troubleshooting Issues session at Vertica’s Big Data Conference.

Vertica Joins: A Refresher

As a Vertica user, you know that using joins can improve query performance by combining records from one or more tables. But sometimes, you need to develop complex joins. Vertica supports many different kinds of joins that perform different functions based on your needs.

Tell the Optimizer You Have a Better Plan

You’re upgrading your database and some query appears to execute less efficiently than before. What can you do? Vertica addresses this concern in Vertica 7.2 with directed queries