Introducing Oxla BETA – A Next-Generation Data Warehouse, Ready to Roll into Production!

Hello, and welcome to a quick overview of the updates we've made since the August Tech Preview. After three months of hard work, Oxla is now production-ready. So today, without further due, we would like to introduce you – drumroll, please – Oxla BETA!

From now on, all future Oxla updates will guarantee backward compatibility, ensuring your data remains accessible without interruption. We've also resolved all known bugs related to data persistence and query correctness. Plus, we've added several new features to make it easier to integrate Oxla with other tools and streamline the migration process.

Here's a sneak peek: We're actively working on making Oxla available on the AWS Marketplace in the weeks ahead. Stay tuned for more updates, and in the meantime, be sure to explore all of the new features!

New query planner

A centerpiece of serving user queries is a component named query planner. It is a part of Oxla responsible for understanding the query and translating it into a query plan. The query plan is a step-by-step instruction for Oxla on how to deliver data to a user. We have redesigned it from the ground up, and delivering it was a hell of a work. This has led to a massive number of new features. This includes:

Improved query performance

Our new query planner has two features that have the most significant impact on your query execution time:

• Common sub-expression elimination – meaning that if you use some expression values multiple times, it will ensure it gets computed only once
• dead code elimination – meaning it will not compute things you don't use (e.g., in a query SELECT id FROM (SELECT id, name, 2023-year_of_birth FROM users);) it won't compute the expression 2023-year_of_birth or load name and year_of_birth columns from storage.
• constant folding – precomputing the part of SQL query, e.g., if you send a query SELECT * FROM table WHERE id=1+1; the addition is evaluated at the planning phase, and execution is comparing id=2. Implementation covers right now common arithmetic operations.
• common sub-expression elimination – if there is a common subpart of a query, we will evaluate it only once.

Cross joins

You can CROSS JOIN multiple tables: SELECT * FROM table_0 CROSS JOIN table_1 if you need a Cartesian product of it, or you can use implicit select from multiple tables: SELECT * FROM table_0, table_1. Both syntaxes are supported.

Set operations

Support for set operations has been added. One may use UNION | INTERSECT | EXCEPT [ALL] in its queries:

SELECT id,values FROM events WHERE att=1 UNION SELECT id,values FROM logs WHERE ts>100;

This is a feature a lot of analytical tools are using.

ORDER BY / GROUP BY indexed select expressions / select aliases

With the latest update, users may use aliases and indexed expressions when grouping and ordering the data.

For example, SELECT id, SUM(value) val FROM events GROUP BY 1 ORDER BY val works just fine, whereas before, it would have to be SELECT id, SUM(value) FROM events GROUP BY id ORDER BY SUM(value).

Additionally, the new query planner allowed us to add:

• full support for NULL literalThe NULL is placed in a query plan as a separate untyped value;
• SELECT DISTINCT, Oxla enables you to select a distinct set of values;
• Improved string literals supportThe support for more complex string literals has been dramatically improved, e.g., more complex intervals: SELECT' 1 month 1 day'::INTERVAL; works!;
• PostgreSQL compliant type resolving and Literal coercion;
• improved, PostgreSQL-like error messages.

Amazon QuickSight support

Oxla right now supports Amazon QuickSight BI visualization tool! You can add Oxla tables as PostgreSQL-compatible dataset, combine it with other data sources, and visualize the data using complex dashboards!

We currently have full support for the cached SPICE mode with incremental refreshes. We're working towards full support in direct query mode. Let us know if you're interested in having it.

AWS credentials passed in a COPY statement

Oxla is well integrated with AWS S3 as the main data storage. With COPY supporting AWS credentials, there is no longer a need for having CSV files available on the same account. e.g.

COPY small_table FROM 's3://my_bucket/my_file.csv' (aws_cred(aws_region us-central-1, aws_key_id my_key_id, aws_private_key my_private_key);

Configuration file

As Oxla grows, there are more and more knobs to adjust. We've introduced the YAML config file to make maintenance easier. But don't worry; if you're already accustomed to environment variables, we still support them. Oxla docker image can even generate the configuration file for you based on the env vars you've provided.

SHOW NODES query

To ease maintenance and troubleshooting, Oxla supports new query SHOW NODES; It collects the state of all the nodes visible to the cluster, including reasons of degradation should there be any.

DISTINCT operator

We've added support for IS DISTINCT FROM and IS NOT DISTINCT FROM operators. This in turn enables you to compare two values (or columns) to verify if they are distinct from each other – even if they are NULL.

Better datetime types support

We've extended support for DATE, TIME, TIMESTAMP, and INTERVAL types and implemented various features to make it easier to work with datetime data:

• Unary + and - operators for INTERVAL type
• TIME + INTERVAL and TIME - INTERVAL operators
• DATE + INTERVAL and DATE - INTERVAL operators
• DATE + INTEGER and DATE - INTEGER operators
• Comparison operators for TIME type: <, <=, >, >=, =, !=‍
• Support for additional date input formats MM-DD-YY and MM-DD-YYYY‍
• PostgreSQL-like unit abbreviations for INTERVAL type

More numeric operators

We've added all operators operating on bits and numbers:

• ‍& binary AND operator
• | binary OR operator
• # binary XOR operator
• ~ binary NOT operator
• << shift left operator
• >> shift right operator
• ^ exponentiation operator (SELECT 2^5;returns 32)
• |/square root
• ||/cube root
• @ absolute value

Other

● || concatenation operator (e.g. SELECT ‘one' || 'two'; will return 'onetwo’)

More numeric functions

Oxla gained full support for trigonometric functions! Right now, you can use SIN(), COS(), and other inverse functions, both in degrees and radians flavors.

select sin(1);

        sin         
-------------------
 0.8414709848078965

We've also added more numeric functions compared to the tech preview version:

• ‍ROUND()‍
• LEAST() and GREATEST()‍
• EXP()
• POWER()
• LOG()‍
• SQRT() and CBRT()‍
• LN()

PostgreSQL system tables and functions

On our journey towards various tools support, we're including more and more PostgreSQL intrinsic features used by them. This time, we've added some of the basic System Catalogs residing in pg_catalog namespace:

• ‍pg_class – all database tables
• pg_attribute – all database columns
• pg_attrdef – lists column default _values‍
• pg_type – all database types
• pg_index – created indexes
• pg_constraint – table constraints
• pg_description – comments
• pg_depend – object dependencies
• pg_settings – database settings
• pg_statio_user_tables – table I/O statistics

All of the above are querable, have a Postgres-compatible column layout, and provide basic information about the DB instance so that tools can automatically discover Oxla tables and their structure. Note that many fields not applicable to Oxla are mocked to satisfy the tool.

We've also implemented or mocked several necessary pg_catalog functions:

• ‍pg_get_expr - translates AST of the default expression in pg_attrdef.adbin back to the SQL
• pg_relation_size - obtains table storage size, in bytes
• pg_typeof - gets type of given expression

If you're eager to explore these features, Oxla BETA is now available to try in just 2 minutes! It's free, so enjoy the experience, and don't forget to share your thoughts with us at hello@oxla.com!