Avoid 1:1 Relationships in Your Data Model

Posted July 24, 2019 by Jim Knicely, Vertica Field Chief Technologist

Helpful Tips text with hand and marker over data center background
1:1 relationships exist in row-oriented databases because of performance concerns. As a columnar database, there is no reason in Vertica to separate large tables out into separate structures.

That is, instead of joining two large fact tables together, combine them into a single table!

Example: dbadmin=> \d big1; List of Fields by Tables Schema | Table | Column | Type | Size | Default | Not Null | Primary Key | Foreign Key --------+-------+--------+------+------+---------+----------+-------------+------------- public | big1 | a | int | 8 | | f | f | (1 row) dbadmin=> \d big2; List of Fields by Tables Schema | Table | Column | Type | Size | Default | Not Null | Primary Key | Foreign Key --------+-------+--------+------+------+---------+----------+-------------+------------- public | big2 | b | int | 8 | | f | f | (1 row) dbadmin=> SELECT COUNT(*) FROM big1; COUNT ------------ 5000000000 (1 row) dbadmin=> SELECT COUNT(*) FROM big2; COUNT ------------ 5000000000 (1 row) dbadmin=> \timing Timing is on. dbadmin=> SELECT COUNT(*) FROM big1 JOIN big2 ON b = a; COUNT ------------ 5000000000 (1 row) Time: First fetch (1 row): 352338.000 ms. All rows formatted: 352338.038 ms dbadmin=> SELECT COUNT(b) FROM big1 JOIN big2 ON b = a; COUNT ------------ 5000000000 (1 row) Time: First fetch (1 row): 311465.060 ms. All rows formatted: 311465.092 ms dbadmin=> CREATE TABLE big1_big2 AS SELECT a, b FROM big1 JOIN big2 ON b = a; CREATE TABLE Time: First fetch (0 rows): 1192135.459 ms. All rows formatted: 1192135.481 ms dbadmin=> SELECT COUNT(b) FROM big1_big2; COUNT ------------ 5000000000 (1 row) Time: First fetch (1 row): 15068.069 ms. All rows formatted: 15080.214 ms Helpful Links:

https://www.vertica.com/docs/latest/HTML/Content/Authoring/AnalyzingData/FlattenedTables/FlattenedTables.htm

Have fun!