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.