So the thing about QuestDB, apart from being open source, we want people to know us because
we try to be very performant, but specifically in small machines.
It, like, perform very well in 120 CPUs and 200 gigs of RAM, it's okay.
Performing very well in 4 CPUs and 16 of RAM, 16 gigs is more difficult.
So that we try to optimize for that.
Actually in the past, we were optimizing for the larger instance use case and then we realized
not everybody has, like, a super large instance at home, so, you know, we try to be better
at that.
We also try to be very good with developer experience, that you get performance out of
the box.
There are many things you can tweak in QuestDB, you know, in every other database, every other
system, lots of configuration, the, I don't know, the memory, page size, the buffers and
what not, which CPUs do what, blah, blah, blah, blah.
By default, if you don't touch anything, which will perform well.
And then if you have expert tolerance, you might fine tune.
But we try hard to make developer experience as simple, and that's why we choose SQL also
for querying data.
So another time series database, make the trade off.
We want to perform.
We need to use a different language, which is cool because, you know, that's, I get it.
We choose SQL because we want the developers to have an easy way learning QuestDB.
For ingesting data, you can use SQL, but we also offer a different protocol, which
is faster.
That's why we have collecting libraries, so you don't have to go low level to be performant.
But that's the idea.
And we are open source, very proud about being open source.
But why we are building another database?
There are a lot of databases.
If you walk around first, then you're going to read research about every type of database
out there.
And just today here, I saw MongoDB, I saw Clickhouse, there's someone about Postgres,
there's someone about SQL, about MariaDB.
Why you need another database, another open source database?
Well, because different data looks different and can have different problems.
And in our case, we are specialized on time series.
We don't do anything else.
I mean, if you try to use QuestDB for full text search analytics, we are truly the worst
database ever for that.
If you try to use QuestDB for geospatial queries, we support some geospatial themes
kind of a bit.
We have a specific data type about geohasses, so we have a type about that.
But we are not good for geospatial unless it is part of time series plus geo.
That's kind of the idea.
So we specialize only on time series analytics, on data which is changing over time and you
want to monitor and track those changes.
That's the idea.
We are not good for anything else.
If you try to use QuestDB for everything, boy, what a disappointment we are going to
be.
But if you try for time series database, this will be one of the good ones.
That's kind of the idea.
And that's why we are building QuestDB, because there are a lot of time series data out there.
And how do you know if you have a time series tool and I have to hear a lot of things.
I want to just read a couple of them.
But basically, if most of the time you are reading data on a slice of time, tell me which
energy consumption I have over the last minute.
Tell me how is the nuclear reactor doing in the past 10 microseconds.
Tell me what is the conversion for this user in the past week.
Let me know for all the data, I have a moving vehicle, which was the last position I saw
it and which was the sensor in this particular point in time.
So if you have that, time series can be interesting.
So with time series, you have all that type of problems.
Data tends to be inserting faster than it reads.
Databases, historically, have been optimized for reads.
They try every trick in the book for making read super fast.
When you insert data, you need to define indexes and they are going to index by many different
things and they keep caching memory for a lot of things and blah, blah, blah, blah.
So reading is the key thing, because usually you read data much more than you write.
But also in time series databases, we can support heavy reads on top of that, but we
need to support heavy inserts and keep performance of that.
We don't use indexes.
The performance you're going to see today is with no indexes.
We don't need them.
We don't want them, because having an index slowed down in gestion.
It's a luxury we cannot have.
So we have some kind of indexing, but we don't have indexes, not as you know them.
That's kind of the idea here.
So it's slightly different.
You have data that you are writing very often, that data is going to grow, and it can grow
fast.
And you need to have some way of loading or deleting that data.
On a traditional database, you just don't say, oh, I have, I don't know, I'm Amazon
and I'm getting users.
It's like, oh, I already have a million users, a million one, I'm going to delete the old
users.
You don't do that.
I mean, sometimes you do, but you don't do that.
You don't really do that on your databases.
On time series database, almost all of them have some mechanism to deal with historical
data and do something with that.
In our case, you can amount partitions, you can amount to cheaper storage, those kind
of things.
But we have the commands and it is designed for that kind of thing.
That kind of the idea.
Many other things about how you have a time series storyline, but that kind of the idea.
But better than me just telling you, I'm going to show you some queries on top of demo
data sets.
I'm going to get the feeling why a time series database might be interesting and then we're
going to details about the ingesting data and about all those things.
That's in sound good so far, yeah?
Do you have any questions?
I'm happy to take them during the talk, by the way, not only at the end.
So we have a live demo, demo.questdbe.io, which is running on a large machine on AWS.
We don't need all the power, but since it's like, you know, open to the public.
Again, we have a few different data sets.
There is one.
You are in a big data room, so you are truly familiar with the taxi rise, New York City
taxi rise data set.
It's the, and the city of New York has a data set, which is very cool for machine learning
and for big data, which is taxi rides in the city of New York.
When the ride started, when it finished, also the coordinate and a few things like the
tip and the amount of the fare, how many people, blah, blah, blah.
So we took that open data set and we just put it here on questdbe, a few years of the
data set.
Yes, you know, a lot of columns here.
So let me just show you how big this is.
This is, right now, is the size okay or maybe not?
Maybe I have to make it a bit, first this a bit bigger and then, okay.
So it's 1.6 billion rows, which is not huge.
I mean, if you have a relational database, 1.6 billion rows, they don't, relational
databases today, they are great.
But 1.6 billion rows is like, yeah, I couldn't work with that, I'm not super comfortable.
For us, it's cute.
It's like, I mean, it's a data set which is respectable but not really huge, but 1.60
billion rows.
And now, what if I want to do something like, for example, I don't know, I want to calculate
the average of whichever, this for example, this number, I want to average the fair amount
over 1.6 billion trips.
How long you will expect your database to take to go do a full scan over 1.6 billion
rows and compute the average, no indexes, no anything.
How long would you say, more or less, ballpark, 1.6 billion rows, no one?
How is the size in gigabytes, megabytes?
I don't know for the whole data set, but this is a double, I mean, I really just know, it's
big, it's big.
When you download the CSV, it's CSV, it's about 600 megabytes and you have several of those.
It's in the, you know, it's largesse.
But anyway, well, actually it was slower than I thought.
It took, usually it takes half a second, this time it took 0.6 seconds.
I know it's slow, I know, but it's with a reason, sort of that.
But I told you, I told you, we are trying to see this database, we are super slow for
other things.
This is not a time series query, did you see any timestamp here, I didn't see anything.
This is just a full scan, we parallelize, we read data and we are slow.
We take almost over half a second to go over only 1.6 billion rows, unforgivable, sort
of that.
But there with me here, no, that's the thing, I mean, I'm kind of half kidding but not really.
But wait until I put a time dimension, now yes, I want only, for example, I want only
one year of data and I'm going to just also add another computation because I know that
it's just counting data which is super fast.
So I'm going to add another computation, so I'm going to count the data and only for
2016 and this is better, this is already 100 milliseconds because we are going only
over a few rows, we are going only about, yeah, it's only 146 million rows, this is
much more manageable, so only 140 million rows, that's better.
So we can go actually very fast on this and then if you keep going down, oh no, I want
only one month of data which is, I don't know, still, yeah, 12 million rows, so a month
of data is 60 milliseconds, for one day of data, of course, is way faster, this is already
50 milliseconds, if I go to one specific hour, a minute, it should be, you know, kind of,
not much faster because, oh yeah, it's under one millisecond actually, thank you for that,
but still, like, you know, we have partitions, so basically one thing we do, we only go to
the partition where the data is stored, so we only attack that part of the data, but
that's kind of the thing, for when you have like that time component, we are quite fast,
oh, fairly fast, that's kind of the beauty for a time-serious database, and we can do
also interesting, other interesting things, if I go to the same table and I show you what
this looks like, you can see that for the same second, I have many trips because this
is New York, baby, and in New York, you know, the city that never sleeps, you can't get
back in every corner, you get rich when you land in New York, I spent there one year,
it's not like that, anyway, so in every particular second, even at midnight, you have always a
few trips at least, okay, so actually you could do that, we could do something like,
I want to know the, I want to, if I want to do something like, give me the date time
and how many trips are ending where this date time is in, for example, June 21st, city,
what are you doing there, man?
I didn't even know I had city here, okay, so, I don't know, for example, in this particular
minute, in one particular day, I want to sample in one second interval and know how many trips
I have for every particular second, so that's another thing you can do in a time series
database, rather than grouping by columns that you can also do, you can group by time,
you call this sample by, so we can sample by any, we go from microsecond to year, I
guess, microsecond to year, so you can group by microsecond, millisecond, second, year,
day, whatever, so in this case, I'm saying, okay, in this particular second, I have six
trips and five trips and blah, blah, blah, you get the idea, yeah, so something I wanted
to show you, which is another cool one, it's, I have this data set with several trips every
second, I have another data set, also with data from Manhattan, is the weather data set,
so maybe it will be interesting to know, to join those two data sets, it will be cool
to know the weather that I had for a particular trip, because maybe that gives me some insight,
I don't know, the challenge is this data set, of course, is real life, it's a different
open data set, it's not at the same resolution, we don't have weather changes every second,
in my hometown sometimes that happens, and when I was living in London that was crazy,
but in real life, we don't measure, we don't store weather changes every second, in this
particular data set, we have about two or three records every hour, so now if I want
to join a data set with sub-second resolution, a data set with sub-hour resolution, and I
want to do a join, if I want to do it in other databases, I could do it, it will take me
a while, then I will think I have it and I wouldn't, and then it will be like, yeah,
this makes sense, or not really, and a week later I will be crying, I don't know, I don't
know, so you know, I should know, so one thing, one cool thing we have here, we have a demo
set, it's an example, I'm going to move on to another thing really quickly, because otherwise,
but this one I really like, we have a special type of join, which we call an ask of join,
which basically does this, I'm going to select the data from the table I told you already
for one particular day in time, and then I'm going to do what we call an ask of join, which
basically says, this table has a time stamp, we call it the designated time stamp, you
design which is the column, you have several, so we have the designated time stamp in one,
designated time stamp in the other, joined by the ones that are closer to each other,
in this case, ask of means the one which is exactly the same, or immediately before me,
the one which is closer to me, what happened before, we have also the one strictly before
me cannot be the same, but that's the idea, so in this case for joining two different
data sets, I can just do that, also I'm going to add here the time stamp for the other table,
so it's clear, so if I run this query, now here I can see for each record on the New
York taxi rides, I'm always getting the same time stamp in the weather data set, because
I have only one entry every 40 or 45 minutes, if I move to a different point in the day
to this day, but instead of at 12, at 12.55 for example, I should see already the time
matching to a different entry on this table, but that's it, I have different resolutions,
I don't care which one, we join by time, because we're about time, that's kind of the
idea, that's what I'm trying to say, I have more interesting queries, but maybe for a
different day, so that's the first thing.
So I told you, okay, now you get the idea why tensile is kind of interesting, the kind
of things we can do, down sampling, all those things, machine learning is very important,
you have data maybe every second, and then you want to do a forecasting, and it doesn't
make sense to train a model with every second data in many cases, maybe you want to down
sample to 15 minutes intervals, with this trick you can do it easily, so that's kind
of the idea.
So I was speaking about ingesting data, so ingesting over one million times per second
on a single instance, it's interesting, but ingesting over one million records per second
on a single instance, it's easy actually, I could just write to a file, a pending line,
and that will be it, the interesting bit is actually being able to ingest data while you
are able to query data in real time, the same data you ingested, that's the trick, because
just ingesting, I mean, you put it there and you're like, why ingesting a million records,
when you think about it, it's like, well, wait, but how long I have to wait to query
the data, and when I can, so the idea is you can query the data at the same time, all benchmarks
are lies, of course, on the same benchmark that I'm going to tell you, other people will
tell you the contrary, and I'm totally fine with that, but a couple of years ago we published
an article saying, hey, we can ingest now at 1.4 million, the slides are linked already
on the first page, by the way, thank you, so we, our CTO posted about, you know, how
we were ingesting 1.4 million records per second, these records were, they have like
20 columns, 10 dimensions, 10 strings, and 10 metrics, 10 numbers, so, you know, we could
ingest records of 20 columns with 10 strings and 10 numbers, 1.4 million records per second
while running queries, which is the other bit, so we were able to scan over 4 million,
4 billion records per second, you know, at the same time in relatively small machines,
relatively small, so that's kind of the, the idea, okay, and these benchmarks, we didn't
write it, it was, there is a benchmark specifically for 10 series databases, as I told you earlier,
if you load data in QuestDB, you can load relational data into QuestDB, and you can
run queries, you try to run a conventional benchmark on QuestDB, it's going to be super
slow, so we are not designed for full text search, we are not designed for, you know,
just operations, reading individual records, or doing updating data, we are not designed
for that, we can do it, but we are not designed for that, so there is, and also there are
10 series databases, so in FluxDB, another open source database, created this benchmark,
the TSBS benchmark, which is specifically about 10 series databases, so the queries
and the ingestion patterns matches what you would expect from a 10 series database, now
it's maintained by time scale, which is another open source database on top of Postgres, and
we have our own, you know, there is an adapter for running that on top of QuestDB, and with
that benchmark, it's with the one that we are getting those results, so with that particular
benchmark, it's the one giving the results, so you know, your mileage might vary, also
depending on the hardware, if you try to run the benchmark in the cloud, it's going to
be slower, always, because in the cloud, by default, you use on AWS, you use CVS, on
WorldCloud, you use the attached storage, it's networking storage, it has latency, because
they are not local disk, they are super cool, but they are not local, it's going to be always
slower, you want to get this on WorldCloud or on AWS, you can do it, you have to use
NVME disk, which are local disk, which are attached to the instance, but they disappear
when you close the instance, but with those disks, you will be getting the same benchmark,
so hardware is also important with the benchmark, but that's the idea, you know, that's how
we did it, and before, I tell you a bit about the technical decisions, that I will not have
super time, but I want to show you how we are doing this in gestion, so let me just,
if I can move this out of the way, so this is a scripting goal, I don't know any goal
at all, but I know to run this, so another developer advocate, I mean, I couldn't tell
you that I know a lot of goals, but I have no idea, so goal lang is a language, so yeah,
we have, I've been told it's pretty cool, so we have this library or package or whatever
they call it in Go, which is our official package, cargo or whatever, I don't know, so this is
my missing languages here, thank you, so yeah, this is our theme, I'm connecting to local
host to the default port in QuestDB, I'm going to be simulating data, so I'm simulating IoT
data, and I'm going to be outputting a device type, it can be red or blue or green or yellow,
I'm going to be outputting duration, latitude, longitude, speed, and time stamp in nanoseconds,
and I'm going to do this in chunks of, in batches of 50,000 records, I'm going to do
this 200 times, 50,000 records, 200 times, 10 million records, I'm going to be inserting
10 million records on a device, on a table that doesn't exist, QuestDB will create it
automatically when it starts receiving data, so if I run this scripting goal, which run
doing go run, well don't go, so go run, it's ingesting data, it should take less than
10 seconds because we are ingesting 10 million, and that's finished, so let me just go to
my local host here, let me just select, select how many records did we ingest it for, I have
to refresh the tables, okay, how many records I ingested, 10 million records, that's good,
can you tell me the interval, so I can see what happened here, sampled by one second,
and it's telling me, yeah, you know, in the first second only half a million, because
we, we then started at the top of the second, it was probably at second or something, but
after that, one million, one million, one million, ten, one, you see, you see the idea,
okay, that's not too bad, I can do this slightly better, I can run this script actually twice
ingesting in the same instant to two different tables, so now, if I refresh, I should see
I have two tables, not only one, so I have two tables here, same hardware and everything,
if I run again, I'm going to select only the last 10 rows, so we only see the latest run,
so you can see it's just lower now, I was actually ingesting to two tables, so I'm ingesting
only 700,000 per second, something like that, but if I go to the same time to the other
table, I can just do a union, if I go to the other table here, you should see that at the
same time in the, oh yeah, I cannot apply limit here, sorry, in a union, so I should
see that, you know, even if I was going slower, the other table was reading data, and in this
format you cannot see it very well, but we can do something I told you earlier, I can
just rather than do a join, I can just do something like, as of join, the first query
with the second, so I should be able to do this, now I have, in the first run, we were
running only one instance of sending data, and this one is the one in which I was running
two, so you can see, for this particular second, we were ingesting 700,000 records
in one, 700,000 records in the other same time, so about 1.4 something million in total
because we're in different tables, out of the box, if I configure the writers and how
many threads I have for processing things, I can get it slightly faster than this, okay,
but that's good enough, on a local, M1 laptop SSD, it's fast, but that's the idea, okay,
so that's the one million there, I was not lying, I was just, you know, telling you things,
I have only a few minutes, but that's cool, how we got here, first, we can do a lot of
assumptions about the data, this is time-serious, so we know people usually want to get not
individual rows, but computations over rows, we know people mostly want to group by things
that are in the data, like strings, like the country name or the device name or the brand
or whatever, so instead of storing strings, we have a special symbol, which is called
a special type, which is called a symbol, if you give me a string, we convert into a
number and we do look up automatically those things, so we can make a lot of assumptions
because we hyper-specialize on one particular use case, we optimize storage, we don't use
indexes because we store everything always in incremental order per partition, if we
get data out of order, we have to regret the partitions, but we don't need indexes because
we always have the data physically in order, so we can scan super quickly back and forth,
that's kind of the idea, we also parallelize as much as we can using different things,
this is written in Java and it's from scratch, you will see some databases which I love,
like MongoDB, excellent database for content, they have a time-serious module, we use the
same MongoDB collections for doing time-series, they cannot be as fast because they are using
exactly what they are using for content, it's very convenient, I can do everything, but
same thing with other engines that are built on top of other things, we don't have any
dependencies, everything is built for scratch, actually we are writing some of the libraries
in Java like strings and loggers and so on to avoid conversions, there are things that
we don't use, so we don't use them, we have libraries for strings, we have libraries for
memory management, we have libraries for absolutely everything, they are written in our own version,
we had our own Justintine compiler because the original Justintine compiler in Java was
not performed enough for some of the parallelization inquiries wanted to do, so we wrote everything,
our Java is kind of weird, Jeremy can tell you more about that, it's super weird Java,
but it's still Java, that's kind of the idea, we even route our own input output functions,
that's kind of a thing, why?
Because we can get nanoseconds faster, this is log4j, log4j, we don't speak about log4j,
but this is awesome, but you know this is log4j, j for log4j, and this is the nanoseconds,
the operations you can do in each nanosecond, so with log4j, login, integer, you can do
82 operations per nanosecond, we can do 800 operations per nanosecond, which is, do you
have to go down to the nanosecond, if you are doing a CRUT application, probably not,
it really depends what you are building, that's kind of why we are writing things from scratch,
so basically the approach of QuestDB to performance, you know this, this is like, I don't know
who you are, but I don't know you, but I will find you and I will kill you, that's kind
of the same approach I see on QuestDB team, they are like, I don't know, we can get faster
at some obscure thing here, so that's kind of the idea, and we try to be a good team
player, Jeremy here has contributed himself, only alone, the connectors for KafkaConnet,
connectors for Apache Flink, so we try to integrate with the rest of the ecosystem, we love it
if you try QuestDB, you are open source geeks, you like, we have stars, we like you have
stars, please contribute, please start on GitHub if you like it, we have a contributor
to the Slack channel, we are quite friendly, we are fast, we work with interesting problems,
if you like interesting problems, if you like weird Java, we would love to have you here,
so thank you very much, and I can take any questions outside.
Oh, one question for the chat, thank you, yeah, yeah, yeah, yeah, it's a, someone was
asking, is QuestDB can work with GPS data, yes, you can work with GPS data, we have
doubles that we can use for that, we don't have a lot of geospatial functions, we have
geohashes, which basically allow you to define in which, at different resolutions, in which
square in the world something is, so if you are talking about finding where a point is
in the world, at a particular point in time, QuestDB is very cool, if you need to do other
things, we support some math libraries, calls and all those things to do your own calculations,
but yeah, it can be used for GPS, and some people are, a lot of people are actually doing
asset tracking with QuestDB, thank you.