Projection Disk Space

[NorbertKrupa]

Does the physical disk space taken up by projections count towards the licensed space utilization?

[JimKnicely]

Vertica uses raw data size (as if it was exported in text format) to evaluate your license compliance. The physical space on disk is compressed data and is therefore typically smaller than the space used by raw uncompressed data.

[NorbertKrupa]

Just to clarify, it's only data that counts against the license utilization, and not space taken up by a projection?

[JimKnicely]

Not necessarily.

Vertica excludes the following when calculating the raw data size estimate:

• 1. Multiple projections (underlying physical copies) of data from a logical database entity (table). Data appearing in multiple projections of the same table is counted only once.
  2. Data stored in temporary tables.
  3. Data accessible through external table definitions.
  4. Data that has been deleted, but which remains in the database.
  5. Data stored in the WOS.
  6. Data stored in system and work tables such as monitoring tables, Data Collector tables, and Database Designer tables.

Refer to the Admin Guide for details (starting on page 108)

[NorbertKrupa]

I found the exclusions on page 112 and just want further clarification.

For example, if I have a table such as:

Code: Select all

customer
-----------
A  B  C  D

And I create two projections such as:

Code: Select all

CREATE PROJECTION customer_p1 AS
SELECT A, B
FROM customer
ORDER BY A

Code: Select all

CREATE PROJECTION customer_p2 AS
SELECT C, D
FROM customer
ORDER BY C

Would this would mean that the space utilized by both projections will be counted towards the raw data size? If this is the case, the data of the table and projections are counted towards the raw data size, which is extremely costly.

[JimKnicely]

You are going to have a super projection on the table that includes all of the columns. Creating the additional projection won't count against your raw data usage.

Code: Select all

dbadmin=> create table t (a int, b int, c int, d int);
CREATE TABLE

dbadmin=> create projection t_p1 as select a, b from t order by a;
WARNING 4116:  No super projections created for table snowfall.t.
HINT:  Default super projections will be automatically created with the next DML
CREATE PROJECTION

dbadmin=> create projection t_p2 as select c, d from t order by c;
WARNING 4116:  No super projections created for table snowfall.t.
HINT:  Default super projections will be automatically created with the next DML
CREATE PROJECTION

dbadmin=> insert into t values (1, 2, 3, 4);
 OUTPUT
--------
      1
(1 row)

dbadmin=> select projection_name, is_super_projection from projections where anchor_table_name = 't';
 projection_name | is_super_projection
-----------------+---------------------
 t_p1            | f
 t_p2            | f
 t_b0            | t <== Only the raw data from one of buddy projections is counted
 t_b1            | t
(4 rows)

Note, that's my understanding.

You can create a 100's of projections on the same table and their storage won't count against your license. But you'd have terrible DML performance