Table Of Contents

6.7. Managing tables

At QuasarDB, we encourage the use of many different tables: it improves write and read speed, and in most cases also improves your compression ratio. It is not uncommon for users to use tens of thousands of tables (or in rare occasions even millions).

To make the management and querying of these tables work at scale, QuasarDB provides a tags abstraction on top the timeseries tables. This document outlines strategies for defining your tables using tags, and shows you how to use them in practice.

6.7.1. Defining your tables

Before you can start tagging your tables, you need to figure out an appropriate way to split your data model into multiple tables, if possible. To do this, you need to determine the most appropriate partition identifier for your dataset: the best choice depends upon your dataset, but in most circumstances it is fairly straightforward to find out.

A good rule of thumb is to use the single key you most frequently query or group by, and use that as identifier for the tables.

Examples of schemes that work well are customer_id, stock_id or sensors_id. If you need help figuring out the best partitioning scheme, consult your solution architect.

6.7.2. Defining your tags

It is a common misconception that tags operate on the level of rows, similar to how a secondary index would work: this is not the case. Tags operate solely on the level of tables, and are in place to make querying easier.

As such, your tagging strategy depends upon your query patterns: when you find yourself wanting to join certain groups of tables frequently, the chance is likely that these tables should share a common tag.

Example: tagging stock tables by exchange:

CREATE TABLE stocks.apple (open DOUBLE, close DOUBLE, high DOUBLE, low DOUBLE, volume INT64)
CREATE TABLE stocks.facebook (open DOUBLE, close DOUBLE, high DOUBLE, low DOUBLE, volume INT64)
CREATE TABLE stocks.google (open DOUBLE, close DOUBLE, high DOUBLE, low DOUBLE, volume INT64)

ATTACH_TAG stocks.apple nasdaq
ATTACH_TAG stocks.facebook nasdaq
ATTACH_TAG stocks.google nasdaq

You can attach multiple tags to the same table, so you can query it in multiple dimensions.

6.7.3. Querying using tags

Since timeseries are stored as plain key/value objects under the hood, we are able to query them using the FIND instruction as well. Assuming you followed the stock exchange example from the previous paragraph, we can query these tables as follows:

FIND(tag='nasdaq')

1. stocks.apple
2. stocks.facebook
3. stocks.google

Because QuasarDB allows for expansion of the table selection using the FIND operator, we can take this one step further and query the contents of these tables:

SELECT * FROM FIND(tag='nasdaq')

timestamp                      timeseries      open             close            high             low              volume
-----------------------------------------------------------------------------------------------------------------------------
2018-01-01T00:00:00.000000000Z stocks.apple    10.240000        10.170000        10.360000        10.070000        1027389
2018-01-01T00:00:00.000000000Z stocks.facebook 902.750000       909.510000       925.000000       890.120000       8437
2018-01-01T00:00:00.000000000Z stocks.google   25.210000        24.910000        27.010000        25.190000        32497
arrow_backPrevious
6.6. Function & Operators
Next arrow_forward
6.8. Key/value lookups