Analytics

Hydra makes it easier to build realtime analytics on time series data by automatically managing the analytical columnstore (highlighted in green) and transactional rowstore (shown in grey).

Inserting data into a table will automatically convert data into analytics-optimized columnar format. Hydra’s serverless processing removes the possibility of resource contention with Postgres’ transactional rowstore.

​

Using analytics (columnstore)

Hydra use its columnstore as the default table type. To create an analytics table, simply create a table normally.

example:

CREATE TABLE analytics.my_table
-- columns,type
);

​

Creating rowstore tables with columnstore as default

To create a standard Postgres rowstore “heap” table you must include the USING heap keyword.

CREATE TABLE my_table
-- columns,type
)
USING heap;

​

JOIN rowstore and columnstore tables

To join a standard PostgreSQL rowstore “heap” table with an analytics columnstore table, use a standard JOIN operation. The database handles the differences between row-based and columnar storage internally during table creation, allowing joins to be performed as usual.

example: consider high volume events and audit logs in the columnstore and user data in the rowstore tables.

-- Rowstore table for users
CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50)
) USING heap;

-- Columnstore table for events
CREATE TABLE analytics.events (
    event_id BIGINT,
    user_id INT,
    event_time TIMESTAMP
);

-- Join tables
SELECT u.user_id, u.username, e.event_time
FROM users u
JOIN analytics.events e ON u.user_id = e.user_id;

​

Switching the default table type to the Postgres rowstore

When CREATE TABLE is run a Postgres columnstore table, known as an “analytics” table is created. To switch the default DDL from Postgres analytics columnstore to the rowstore, change the default_table_access_method to heap.

-- set for your current system
SET default_table_access_method = 'heap';

-- assumes a rowstore user has been created, could use `postgres` instead to change it for the default/superuser
ALTER USER rowstore SET default_table_access_method = 'heap';

-- alter anytime you use a particular database
ALTER DATABASE postgres SET default_table_access_method = 'heap';

Note that for ALTER USER, that user must also be a superuser to use rowstore tables. Superuser can be granted to any user:

-- at creation time
CREATE USER rowstore WITH superuser PASSWORD 'VerySecurePassword';
-- or sometime later
ALTER USER rowstore WITH superuser;

Contact support@hydra.so to modify superuser roles and access for users.

​

Creating columnstore tables when rowstore is default

When the default is switched to heap, CREATE TABLE will create a heap table without needing to add USING heap. With the default changed, to create a Postgres columnstore table you must include the USING duckdb keyword.

CREATE TABLE my_table
-- columns,type
)
USING duckdb;

​

Analytics limitations (columnstore)

Note that all limitations listed below are for analytics tables only. For example, indexes are supported on Postgres’ standard tables (rowstore), but not supported for query acceleration on analytics tables (columnstore). This is generally not a concern as analytics tables are already highly optimized for analytical workloads and provides excellent query performance through optimized data storage and processing.