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 an analytics 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;

​
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 analytics tables with rowstore as 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.