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).

Creating and inserting data into an analytics table in the analytics schema 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)

To create an analytics table you must include the USING duckdb keyword.

example:

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

​

Switching the default table type

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

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

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

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

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

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

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

​

Creating heap tables with analytics as default

With the default switched to duckdb, CREATE TABLE will create an analytics table without needing to add USING duckdb. With the default changed, to create a standard Postgres rowstore table you must include the USING heap keyword.

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

​

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.