Example of working with ADB tables via psql

Anton Vasilev

Overview

This article describes the main operations with tables available in ADB. The SQL syntax in ADB is the same as in Greengage DB — the DBMS on which ADB has been based since version 6.29. Greengage DB, in turn, inherits its SQL syntax from PostgreSQL and extends it with new capabilities, such as those related to its distributed nature. The full list of commands with their parameter descriptions can be found in the official Greengage DB documentation.

To work with ADB tables, you can use the standard psql terminal client. This client allows you to enter SQL queries, pass them to ADB, and view the results.

To interact with ADB, you should connect to the master host where the psql tool is available after the ADB installation. Segments are not intended to accept client connections.

Connect to the default database

If you haven’t created any databases in ADB yet, you can use the default database, adb.

  1. Connect to the ADB master host (for example, via SSH) and log in under the default user gpadmin:

    $ sudo su - gpadmin

  2. Connect to the adb database:

    $ psql adb

As a result, the psql prompt is displayed with the psql version and the database name:

psql (9.4.26)
Type "help" for help.

adb=#

To log out from psql, use the following command:

\q

For remote connections via psql and for more details about the default database, refer to Use psql to connect to ADB.

Step 1. Create a database

To create a new database, use the CREATE DATABASE command followed by the database name and end the query with the semicolon ;.

The following example creates the new database named universe:

CREATE DATABASE universe;

Although the syntax of this command is identical to that in PostgreSQL, internally it differs because the master manages a distributed transaction across all segments to ensure the database is created on every node of the ADB cluster. After all segments complete this operation, the master commits the successful creation of the database in the system catalogs and sends the result to the client:

CREATE DATABASE

To switch to the newly created database, use the \c command:

\c universe

The result:

You are now connected to database "universe" as user "gpadmin".
universe=#
TIP

To monitor ADB sessions, transactions, and SQL commands, you can use ADB Control.

If you want the newly created database to be monitored in ADB Control, configure it in the Databases monitoring section of the ADB Control web interface.

Step 2. Create a table

To create a new table, use the CREATE TABLE command followed by the table name, describe all table columns (by defining their names and data types), and end the query with the semicolon ;.

Additionally, in contrast to standard PostgreSQL syntax, each table in ADB has a distribution policy that controls how rows are spread across segments.

If the DISTRIBUTED BY clause is not provided when you create a table, the gp_create_table_random_default_distribution server parameter controls how to distribute tables by default.

To illustrate the default behavior, the following command creates the missions table without a distribution policy specification:

CREATE TABLE missions (
    planet_id    INTEGER,
    planet_name  TEXT,
    program_name TEXT,
    launch_year  INTEGER
);

The result is:

NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'planet_id' as the Greengage Database data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
CREATE TABLE

Because the DISTRIBUTED BY clause is not provided, ADB uses the first hashable column — planet_id — as a distribution key. Distributing a table by the planet_id column means that the database applies a hash function to the row planet_id value when a row is added or updated, and that hash is used to determine which segment should store that row.

The following command creates a table where the first column is not hashable:

CREATE TABLE planet_locations (
    coord      POINT,
    planet_id  INT,
    name       TEXT
);

Because the POINT type is not hashable, ADB automatically skips the first column and selects the next suitable column (planet_id of the INT type in this example) as the distribution key:

NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'planet_id' as the Greengage Database data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
CREATE TABLE

Although the automatic distribution key selection works, it is recommended to explicitly specify the distribution key using DISTRIBUTED BY. A carefully planned distribution policy helps avoid data skew across segments and inefficient query plans.

The following command creates a table with a distribution key explicitly specified using the DISTRIBUTED BY clause:

CREATE TABLE moons (
    planet_id      INT,
    moon_id        INT,
    moon_name      TEXT,
    diameter_km    NUMERIC,
    coord          POINT
)
    DISTRIBUTED BY (moon_id);

The result is:

CREATE TABLE

Learn more about distribution in Distribution.

Create append-optimized tables

By default, if the table type is not specified, ADB creates a heap table. The gp_default_storage_options configuration parameter controls this default behavior.

Heap tables are used for OLTP workload when data is frequently modified. For example, if you often update coordinates of planets in the planet_locations table (created above), then the heap table handles updates efficiently.

For analytical workloads with bulk loads and infrequent updates, ADB supports append-optimized (AO) tables with optional columnar storage and compression.

Learn more about heap and append-optimized tables in Table types.

To create an append-optimized table, pass the appendoptimized = true storage option in the WITH clause:

CREATE TABLE planets (
    planet_id     INT,
    name          TEXT,
    type          TEXT,
    mass_kg       NUMERIC,
    diameter_km   NUMERIC,
    distance_au   NUMERIC,
    orbit_days    NUMERIC
) WITH (
    appendoptimized = true
);

Unlike heap tables, which are row-oriented, append-optimized tables can be row-oriented or column-oriented. If orientation is not provided (like in the example above), a row-oriented table is created by default. Use row orientation if queries typically retrieve a small number of rows and most columns.

If you expect a large row count, and a single query affects a small subset of columns, use the column orientation by specifying orientation = column.

The following command creates a column-oriented append-optimized table:

CREATE TABLE stars (
    star_id          BIGINT,
    designation      TEXT,
    distance         NUMERIC,
    temperature_k    INTEGER,
    luminosity_sun   NUMERIC,
    radius_sun       NUMERIC
) WITH (
    appendoptimized = true,
    orientation = column
)
DISTRIBUTED BY (star_id);

Create partitioned tables

Partitioning splits a large table into smaller, more manageable parts (partitions), which improves query performance and makes maintenance easier. Learn more about partitioning in Partitioning.

Note that whether a table is partitioned is defined when the table is created. You can only add partitions to a table (using the ALTER TABLE command) if that table was defined as a partitioned table.

To create a partitioned table, use the PARTITION BY <partition_type> clause where the <partition_type> value can be LIST (partitions are based on a list of values) or RANGE (partitions are based on a numeric or date range).

The following command creates the missions_partitioned table with partitions based on year range:

CREATE TABLE missions_partitioned (
    mission_id   INTEGER,
    planet_id    INTEGER,
    planet_name  TEXT,
    program_name TEXT,
    launch_year  INTEGER
) DISTRIBUTED BY (planet_id)
    PARTITION BY RANGE (launch_year)
        (
        PARTITION p2020_2029 START (2020) END (2030),
        PARTITION p2030_2039 START (2030) END (2040),
        PARTITION p2040_plus START (2040) END (9999)
        );

The output confirms the creation of each physical partition table:

NOTICE:  CREATE TABLE will create partition "missions_partitioned_1_prt_p2020_2029" for table "missions_partitioned"
NOTICE:  CREATE TABLE will create partition "missions_partitioned_1_prt_p2030_2039" for table "missions_partitioned"
NOTICE:  CREATE TABLE will create partition "missions_partitioned_1_prt_p2040_plus" for table "missions_partitioned"

Step 3. Get information about tables

To list all tables in your database, use the \dt meta-command.

For example, it can be used to check storage types of the tables:

                         List of relations
 Schema |       Name       | Type  |  Owner  |       Storage
--------+------------------+-------+---------+----------------------
 public | planet_locations | table | gpadmin | heap
 public | missions         | table | gpadmin | heap
 public | moons            | table | gpadmin | heap
 public | planets          | table | gpadmin | append only
 public | stars            | table | gpadmin | append only columnar

Also note that because no schema name was provided when creating the tables, all of them belong to the current schema (public). Learn more about schemas in Schemas.

To check a particular table, use the \d meta-command followed by a table name:

\d planets

The result should be similar to:

Append-Only Table "public.planets"
   Column    |  Type   | Modifiers
-------------+---------+-----------
 planet_id   | integer |
 name        | text    |
 type        | text    |
 mass_kg     | numeric |
 diameter_km | numeric |
 distance_au | numeric |
 orbit_days  | numeric |
Compression Type: None
Compression Level: 0
Block Size: 32768
Checksum: t
Distributed by: (planet_id)

You can also use \d+ <table_name> to get additional information about the table, such as storage and compression types and column comments:

\d+ planets

The result should be similar to:

                                   Append-Only Table "public.planets"
   Column    |  Type   | Modifiers | Storage  | Stats target |               Description
-------------+---------+-----------+----------+--------------+------------------------------------------
 planet_id   | integer |           | plain    |              |
 name        | text    |           | extended |              | Planet name (e.g., "Mars" or "Tatooine")
 type        | text    |           | extended |              |
 mass_kg     | numeric |           | main     |              |
 diameter_km | numeric |           | main     |              |
 distance_au | numeric |           | main     |              |
 orbit_days  | numeric |           | main     |              |
Compression Type: None
Compression Level: 0
Block Size: 32768
Checksum: t
Distributed by: (planet_id)
Options: appendonly=true, orientation=row

Step 4. Insert data

To add new data to the table, use the INSERT INTO command followed by the table name and column names, define column values after the VALUES keyword, and end the query with the semicolon ;. Values should be specified in the same order as column names.

The following example shows how to insert six new rows into the planets table:

INSERT INTO planets (planet_id, name, type, mass_kg, diameter_km, distance_au, orbit_days)
VALUES
  (1, 'Mercury', 'Terrestrial', 3.3011e23, 4879, 0.387, 88.0),
  (2, 'Venus',   'Terrestrial', 4.8675e24, 12104, 0.723, 224.7),
  (3, 'Earth',   'Terrestrial', 5.9722e24, 12742, 1.000, 365.25),
  (4, 'Mars',    'Terrestrial', 6.4171e23, 6779, 1.524, 687.0),
  (5, 'Jupiter', 'Gas giant', 1.8982e27, 139820, 5.203, 4331),
  (6, 'Tatooine', 'Desert', 4.5722e24, 10465, 1.800, 525.0);

The result is:

INSERT 0 6

In this output, 6 is the number of the added rows, and 0 indicates that no OID was assigned to the inserted rows because OIDs are disabled by default in ADB (and are not recommended for user-created tables).

You can omit the column names — in this case, provide the values in the same order as the table columns:

INSERT INTO missions VALUES
    (4, 'Mars', 'Ares Horizon', 2033),
    (5, 'Jupiter', 'Ganymede Orbiter', 2037);

Step 5. Select data

To select data from a table, use the SELECT command, then enter the column names, write the table name after the FROM keyword, describe the query conditions in the WHERE clause, and end the query with the semicolon ;.

Select all data

To get all columns, use * instead of column names:

SELECT * FROM planets;

The following command does the same thing (but it cannot be combined with a WHERE clause):

TABLE planets;

Select specific columns

To get specific columns, enter their names after SELECT.

In the following example, the gp_segment_id system column is used (available in all tables except those defined with DISTRIBUTED REPLICATED) to identify which segment stores each row:

SELECT gp_segment_id, planet_id, name
FROM planets
ORDER BY planet_id;

In the output, the gp_segment_id column identifies segments:

 gp_segment_id | planet_id |   name
---------------+-----------+----------
             4 |         1 | Mercury
             9 |         2 | Venus
             7 |         3 | Earth
            14 |         4 | Mars
             2 |         5 | Jupiter
             6 |         6 | Tatooine
(6 rows)

Filter data

If you need to filter rows based on specified conditions, use the WHERE clause.

For example, use the equals operator (=) to match a specific value:

SELECT * FROM planets
WHERE type = 'Gas giant';

The output contains only rows that have the specified column with the specified value:

 planet_id |  name   |   type    |           mass_kg            | diameter_km | distance_au | orbit_days
-----------+---------+-----------+------------------------------+-------------+-------------+------------
         5 | Jupiter | Gas giant | 1898200000000000000000000000 |      139820 |       5.203 |       4331
(1 row)

You can also use comparison operators such as < and > with numeric values, for example:

SELECT name, diameter_km
FROM planets
WHERE diameter_km < 1e4;

The output lists only planets with a diameter smaller than 10000 kilometers:

  name   | diameter_km
---------+-------------
 Mars    |        6779
 Mercury |        4879
(2 rows)

Mathematical and string functions and operators can be also used in WHERE. To combine conditions, use the logical AND or OR operators. The following command checks how tables are distributed among host data files. It queries the db_files_current table from the arenadata_toolkit schema provided by ADB:

SELECT content, address, table_name, file_size, file
FROM arenadata_toolkit.db_files_current
WHERE table_schema = 'public'
  AND table_name IN ('planets', 'stars', 'planet_locations')
  AND file_size != 0;

The result should be similar to:

 content |   address   | table_name | file_size |                   file
---------+-------------+------------+-----------+-------------------------------------------
       2 | val-6-sdw-1 | planets    |        88 | /data1/primary/gpseg2/base/67705/58277.1
       4 | val-6-sdw-2 | planets    |        88 | /data1/primary/gpseg4/base/67705/58277.1
       6 | val-6-sdw-2 | planets    |        88 | /data1/primary/gpseg6/base/67705/58277.1
       7 | val-6-sdw-2 | planets    |        88 | /data1/primary/gpseg7/base/67705/58277.1
       9 | val-6-sdw-3 | planets    |        88 | /data1/primary/gpseg9/base/67705/66449.1
      14 | val-6-sdw-4 | planets    |        88 | /data1/primary/gpseg14/base/67705/66449.1
(6 rows)

For more examples of the SELECT command, refer to the Greengage DB documentation.

Join tables

When related data is split across multiple tables, use JOIN to combine rows from them.

The following command combines data from the missions table with the planets table to show planets for which missions exist:

SELECT p.name,
       (m.program_name IS NOT NULL) AS has_mission
FROM planets p
         LEFT JOIN missions m ON p.planet_id = m.planet_id;

The output shows t (true) for planets that have missions and f (false) for planets that do not:

   name   | has_mission
----------+-------------
 Tatooine | f
 Mars     | t
 Jupiter  | t
 Venus    | f
 Earth    | f
 Mercury  | f
(6 rows)

In the example above, the LEFT JOIN query returns all rows from the left table (planets) even when no match is found in the right table (missions).

In contrast, the [INNER] JOIN query returns only rows with matching values — that is, only those planets that have missions:

SELECT p.name
FROM planets p
    JOIN missions m ON p.planet_id = m.planet_id;

The result is:

  name
---------
 Jupiter
 Mars
(2 rows)

Learn more about types of JOIN in the Greengage DB documentation.

Group the results

To group rows that have the same values in specified columns, use the GROUP BY clause. This is often used with aggregate functions like COUNT() or SUM().

The command below returns the number of planets for each type:

SELECT type, COUNT(*) AS planet_count
FROM planets
GROUP BY type;

The result is:

    type     | planet_count
-------------+--------------
 Desert      |            1
 Gas giant   |            1
 Terrestrial |            4
(3 rows)

Sort the results

If sorting is not used, the order of rows returned by the same query may vary between executions. To ensure a predictable order, use the ORDER BY clause.

The following query sorts the type groups in descending order of the type count:

SELECT type, COUNT(*) AS planet_count
FROM planets
GROUP BY type
ORDER BY planet_count DESC;

The result is:

    type     | planet_count
-------------+--------------
 Terrestrial |            4
 Gas giant   |            1
 Desert      |            1
(3 rows)

Step 6. Analyze queries

To understand how ADB executes a query and to identify potential performance issues, use the EXPLAIN command. It shows the query plan — a sequence of operations that ADB will perform, including how data is moved between segments.

In the command output, pay attention to the motion nodes:

  • Broadcast Motion — each segment sends a copy of a table to all segments.

  • Redistribute Motion — each segment sends each row to a single target segment.

  • Gather Motion — the master collects rows from segments (typically a final step).

The following query analyzes the SELECT statement with JOIN on planet_id from both tables:

EXPLAIN
SELECT p.name,
       (m.program_name IS NOT NULL) AS has_mission
FROM planets p
         LEFT JOIN missions m ON p.planet_id = m.planet_id;

The result should be similar to:

                                     QUERY PLAN
-------------------------------------------------------------------------------------
 Gather Motion 16:1  (slice1; segments: 16)  (cost=0.00..862.00 rows=10 width=9)
   ->  Result  (cost=0.00..862.00 rows=1 width=9)
         ->  Hash Left Join  (cost=0.00..862.00 rows=1 width=23)
               Hash Cond: (p.planet_id = m.planet_id)
               ->  Seq Scan on planets p  (cost=0.00..431.00 rows=1 width=11)
               ->  Hash  (cost=431.00..431.00 rows=1 width=20)
                     ->  Seq Scan on missions m  (cost=0.00..431.00 rows=1 width=20)
 Optimizer: Pivotal Optimizer (GPORCA)
(8 rows)

Because both tables use the same distribution key (planet_id), each segment can perform the join locally using only the data it already holds. No extra data motion is needed.

The following query analyzes the SELECT statement with JOIN on name from both tables:

EXPLAIN
SELECT p.name,
       (m.program_name IS NOT NULL) AS has_mission
FROM planets p
         LEFT JOIN missions m ON p.name = m.planet_name;

The result should be similar to:

                                                 QUERY PLAN
-------------------------------------------------------------------------------------------------------------
 Gather Motion 16:1  (slice2; segments: 16)  (cost=0.00..862.00 rows=10 width=9)
   ->  Result  (cost=0.00..862.00 rows=1 width=9)
         ->  Hash Left Join  (cost=0.00..862.00 rows=1 width=23)
               Hash Cond: (p.name = m.planet_name)
               ->  Seq Scan on planets p  (cost=0.00..431.00 rows=1 width=7)
               ->  Hash  (cost=431.00..431.00 rows=3 width=22)
                     ->  Broadcast Motion 16:16  (slice1; segments: 16)  (cost=0.00..431.00 rows=3 width=22)
                           ->  Seq Scan on missions m  (cost=0.00..431.00 rows=1 width=22)
 Optimizer: Pivotal Optimizer (GPORCA)
(9 rows)

Because the distribution key is planet_id, not name, rows with the same name value can reside on different segments. To ensure that every segment has all the necessary rows to perform the join by name, the Broadcast Motion operation is used, which means each segment sends its rows to all other segments. For large tables in a production environment, extra data motion can be expensive, which is why choosing the right distribution key is important for query performance.

TIP

If you use ADB Control, you can use the Monitoring page to view the graphical representation of the query plan, execution time, and status of the plan nodes.

Step 7. Alter a table

To change a table, use the ALTER TABLE command. The command allows you to rename a table, add, remove, and rename columns, modify partitions, change constraints, and more. This section illustrates adding and removing a constraint, as well as adding a column.

Add a constraint

The following command adds a CHECK constraint for the planet diameter:

ALTER TABLE planets
    ADD CONSTRAINT diameter_check CHECK (diameter_km > 0);

The output is:

ALTER TABLE

When a CHECK constraint is added, ADB performs a check to verify that all current rows in the table satisfy the constraint. With the NOT VALID option, this initial check can be skipped. The constraint is applied to future inserts or updates: attempts to add a planet with a diameter less than or equal to 0 will result in an error: new row for relation "planets" violates check constraint "diameter_check".

You can then rename and remove the constraint, but you can’t modify its condition.

Remove a constraint

The following command removes the diameter_check constraint:

ALTER TABLE planets
    DROP CONSTRAINT diameter_check;

The output is:

ALTER TABLE

Add a column

The following command adds a new discovered_year column to the planets table:

ALTER TABLE planets
    ADD COLUMN discovered_year INTEGER;

The output is:

ALTER TABLE

All existing rows will have NULL in the new discovered_year column.

Note that in ADB 6 (as well as in Greengage DB 6), the ADD COLUMN option requires a table rewrite for row-oriented tables. For large tables it can take significant time.

Step 8. Update the table data

To update one or more columns in specified table rows, use the UPDATE command. The columns to be modified are mentioned in the SET clause. The rows to be modified can be restricted using the WHERE condition. If WHERE is not specified, the update operation applies to all rows of the table.

The following command changes the diameter_km value for planets of the Terrestrial type:

UPDATE planets SET diameter_km = diameter_km - 1 WHERE type = 'Terrestrial';

The output shows the number of updated rows:

UPDATE 4

The following command adds (visited) to planets that have missions using data from the missions table:

UPDATE planets
SET name = name || ' (visited)'
WHERE planet_id IN (SELECT DISTINCT planet_id FROM missions);

The output shows the number of the updated rows:

UPDATE 2

Check the results:

SELECT name FROM planets;

The output should be:

       name
-------------------
 Venus
 Mercury
 Tatooine
 Mars (visited)
 Jupiter (visited)
 Earth
(6 rows)

Step 9. Delete table data

To delete one or more rows from a table, use the DELETE FROM command followed by the table name, describe query conditions in the WHERE clause, and finish the command with the semicolon ;.

The following command deletes rows containing the (visited) postfix in the name column:

DELETE FROM planets
WHERE name LIKE '% (visited)';

The output shows the number of the deleted rows:

DELETE 2

Note that omitting the WHERE clause will delete all rows from the table.

Clear the table

To remove all data from a table, use the TRUNCATE command followed by the table name. TRUNCATE is faster than DELETE, which makes it the preferred choice for clearing large tables.

The following command deletes all rows from the planets table:

TRUNCATE planets;

The result is:

TRUNCATE TABLE

To clear more than one table at once, enter the table names separated by commas in a single TRUNCATE command, for example:

TRUNCATE missions, stars;
IMPORTANT

Unlike DELETE, the TRUNCATE command immediately deletes the data files.

Rows that are deleted by DELETE (or obsoleted by UPDATE) are not physically removed from their table until a VACUUM operation is done. ADB provides maintenance scripts that periodically run on the cluster and include periodic VACUUM runs.

For more details about reclaiming disk space (including how to view "dead" rows in a table and how to check tables for bloat), refer to the Greengage DB documentation.

Step 10. Drop a table

To remove a table from a database, use the DROP TABLE command followed by the table name.

The following command removes the planets table:

DROP TABLE planets;

The result:

DROP TABLE

To remove more than one table at once, enter the table names separated by commas in a single DROP TABLE command, for example:

DROP TABLE missions, stars;

Step 11. Drop a database

To drop a database, use the DROP DATABASE command. Note that a database cannot be dropped if there are active client connections to it.

  1. Switch to another database:

    \c adb

  2. Enter DROP DATABASE followed by the database name:

DROP DATABASE universe;

The result is:

DROP DATABASE
Found a mistake? Seleсt text and press Ctrl+Enter to report it