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.
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.
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.
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);
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.
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
Better datetime types support
- operators for
TIME + INTERVAL and
TIME - INTERVAL operators
DATE + INTERVAL and
DATE - INTERVAL operators
DATE + INTEGER and
DATE - INTEGER operators
● Comparison operators for
● Support for additional date input formats
● PostgreSQL-like unit abbreviations for
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)
@ absolute value
|| 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.
sin ------------------- 0.8414709848078965
We’ve also added more numeric functions compared to the tech preview version:
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_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_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 [email protected]!