Cube

The cube extension in PostgreSQL provides a data type for multi-dimensional cubes. It is useful for applications requiring vector operations, such as geometric data, multi-dimensional indexing, and scientific computing. Your Nile database arrives with cube extension already enabled, so there’s no need to run create extension.

​

Creating and Populating a Sample Table

Before creating the index, let’s create a sample table and populate it with cube data:

CREATE TABLE my_table (
    id SERIAL PRIMARY KEY,
    dimensions CUBE
);

INSERT INTO my_table (dimensions) VALUES
    ('(1,2,3)'),
    ('(4,5,6)'),
    ('(7,8,9)'),
    ('(10,11,12)'),
    ('(13,14,15)');

​

Querying cube Data

With the cube extension, you can perform various operations on multi-dimensional data:

​

Find Points within a Specific Range

SELECT * FROM my_table WHERE dimensions <@ '(0,0,0),(5,5,5)';

This query returns all points within the cube defined by (0,0,0) to (5,5,5).

​

Compute the Distance Between Two Points

SELECT cube_distance('(1,2,3)', '(4,5,6)');

This function computes the Euclidean distance between two cube points.

​

Creating an Index on cube Columns

To optimize queries, you can create a GiST index:

CREATE INDEX cube_idx ON my_table USING gist (dimensions);

This index improves performance for queries filtering cube data.

​

Limitations

• The cube type supports up to 100 dimensions by default.
• It does not support operations like +, -, or * directly; you must use provided cube functions.
• Indexing performance depends on the number of dimensions and the dataset size.

​

Removing a cube Index

If you need to remove an index:

DROP INDEX cube_idx;

​

Conclusion

The cube extension in PostgreSQL enables efficient storage and querying of multi-dimensional data. It is particularly useful for geometric and scientific applications where vector operations and spatial indexing are needed.

For more details, refer to the PostgreSQL documentation.