News for hackers. Stuff that matters.
Re:Recently minted database technologies that I find intriguing by petercooper on Monday June 15, 2020 @04:18PM (#23532464)
I edit a database newsletter – https://dbweekly.com/ – so tend to always have my eyes out for new releases, what's coming along, and what not. And I thought I'd share a few more things that have jumped out at me recently in case anyone's in the mood for spelunking.

1. QuestDB – https://questdb.io/ – is a performance-focused, open-source time-series database that uses SQL. It makes heavy use of SIMD and vectorization for the performance end of things.

2. GridDB - https://griddb.net/en/ - is an in-memory NoSQL time-series database (there's a theme lately with these!) out of Toshiba that was boasting doing 5m writes per second and 60m reads per second on a 20 node cluster recently.

3. MeiliSearch - https://github.com/meilisearch/MeiliSearch – not exactly a database but basically an Elastic-esque search server written in Rust. Seems to have really taken off.

4. Dolt – https://github.com/liquidata-inc/dolt – bills itself as a 'Git for data'. It's relational, speaks SQL, but has version control on everything.

TerminusDB, KVRocks, and ImmuDB also get honorable mentions.

InfoWorld also had an article recently about 9 'offbeat' databases to check out if you want to go even further: https://www.infoworld.com/article/3533410/9-offbeat-database...

Exciting times in the database space!

Re:Recently minted database technologies that I find intriguing by LukeEF on Monday June 15, 2020 @07:12PM (#23534176)
Luke from TerminusDB here. Thanks for the honorable mention! Definitely exciting times in database land and great to be in the same list as all these projects.

We are focused on the revision control aspects of Terminus - trying to make the lives of data intensive (ML etc.) teams a little easier. We use a delta encoding approach to updates like source control systems such as git and provide the whole suite of revision control features: branch, merge, squash, rollback, blame, and time-travel. Idea is to provide continuous integration for the data layer.

Basically squash all the tools and processes mentioned here into a versioned graph: https://martinfowler.com/articles/cd4ml.html

Re:Recently minted database technologies that I find intriguing by gen220 on Monday June 15, 2020 @05:32PM (#23533223)
Has anybody tried using Dolt? It looks quite young.

I think this idea is really valuable, but I usually see it implemented as a time-series extension on top of Postgres or MySQL, like SQLAlchemy-Continuum or TimescaleDB. i.e. you caan get most of the useful git-like time-travel semantics (modulo schema migrations) out of timeseries data with a separate transaction history table.

I'm curious what Dolt's performance profile looks like (i.e. how reads and writes scale vs "branch"-count, row-count, and how they handle indices across schema migrations), since the aforementioned solutions on Postgres are building on an already-very-performant core.

edit: TerminusDB also looks very cool, although it's approaching this problem from a graph-database angle rather than a relational one. Their architecture (prolog server on top of a rust persistence core) also seems super fascinating, I'd love to read more on how they chose it.

Re:Recently minted database technologies that I find intriguing by timsehn on Monday June 15, 2020 @06:08PM (#23533637)
CEO of Dolt here.

You are indeed correct, we are quite young. We are also focused at this stage on the data distribution use case (ie. use Dolt instead of putting a CSV or JSON file on GitHub). So, we haven't spent much time on query performance or scale. The current model is that the database and all of its history have to fit on the user's hard drive. The format is easily distributed (ie. put content addressed chunks on different servers, optimize for fewer network hops) but that's not how it works today.

That being said, we think architecturally we can eventually get pretty close to parity with other RDBMS on the read path. We will be slower on the write path given the need to build the Merkle DAG on writes.

Long ways to go though, we only launched open source last August. On a suite of MySQL ~6M correctness benchmarks, we currently execute 3-4X more slowly. These aren't large sets either so we suspect we'll run into some non-linearities in performance. This is just normal SQL. We haven't really tested the limits of how many branches we can handle or how long branch creation or merge takes at scale. Not because we don't want to but because it's not the use case we're focused on.

If you are willing to take the journey with us, we'll work with you to get Dolt where it needs to be (if it can, reads should be fine, writes will be more expensive). But we're just at the beginning so there will be bumps along the road. If that's not for you, we completely understand.

Re:Recently minted database technologies that I find intriguing by bduerst on Monday June 15, 2020 @08:26PM (#23534656)
Looks great. Are users able to query commits/historical values to a cell inside their SQL query?

i.e. "Select population.master, population.branch2, population.commit123.diff from state_populations"

Re:Recently minted database technologies that I find intriguing by zachmu on Tuesday June 16, 2020 @02:23PM (#23542381)
You can do historical queries two different ways:

1) `SELECT * FROM state_populations AS OF 'master'` to get the repo as it existed at a particular timestamp / ref name 2) `SELECT * FROM dolt_diff_state_populations WHERE ...` to examine to/from column value pairs between any two commits you choose.

There are a bunch of different supported system tables that provide interesting functionality: https://www.dolthub.com/docs/reference/sql/#dolt-system-tabl...

Re:Recently minted database technologies that I find intriguing by Fiahil on Tuesday June 16, 2020 @04:07AM (#23536922)
I work as a SWE at a large AI consultancy. We've been experimenting with "git for data" products for a while, and we've been trying to get rid of them (notably Pachyderm) for -at least- 2 years.

What they all share is a) awful performances, and b) bad design.

Git semantics, ("branche", "merge", "commit") are not well suited for data, because merging dataframes and creating "branches" often leads to misunderstandings and delays. Time travel is very nice to have, but it's often the case where you would like to consume your input datasets at different point in time in the same repository (unless you do one dataset per repository, but then, what's the point ?).

Performances are bad, because all updates needs to go through some kind of coordination mechanism (etcd, zookeeper, or raft directly). In a single instance scenario, you often end-up flooding it or needing additional memory to cope with the load. However, you could deliver high throughput and high availability by using proper sharding and distributing updates to specific masters (like you would do in any actor-based architecture).

As a replacement, we're now using a custom event-sourcing framework on top of AWS S3/Azure blob. It's faster, more reliable, and most importantly, better designed.

Re:Recently minted database technologies that I find intriguing by zachmu on Tuesday June 16, 2020 @02:38PM (#23542557)
We're building a very literal "git for data" product, Dolt. (doltdb.com). I'm very curious about this criticism:

> Git semantics, ("branche", "merge", "commit") are not well suited for data, because merging dataframes and creating "branches" often leads to misunderstandings and delays.

Can you give a concrete example of what you mean? I'm wondering if this is a failing of the tool you're using or the model itself?

> Time travel is very nice to have, but it's often the case where you would like to consume your input datasets at different point in time in the same repository

Dolt supports doing exactly this. See my reply to a child comment below.

> Awful performance

It's not obvious to me why this needs to be true in the general case, unless it's caused by b) bad design. Are you mostly talking about write performance, or are reads also a problem?

Re:Recently minted database technologies that I find intriguing by Fiahil on Tuesday June 16, 2020 @04:57PM (#23544004)
I don't want you to take it personally, but, from my point of view, Dolt falls in the same bucket as DVC, Pachyderm and some others. The shortcomings are not from the tools themselves, it's a fundamental issue with the design.

First, branching and merging. In git, branching allows you to make uncoordinated parallel progress for the price of a reconciliation step. In a datastore, you want the exact opposite: A single, consistent, available source of truth. Having different branches of the same dataset bring more confusion while solving zero problem.

Then, commits. In git, a commit represent a snapshot of the entire state of your repository. This is particularly attractive because it guarantees that your code will build no matter what kind of update will follow (without incidence: editing a readme ; severely destructive: removing an src folder). In a datastore, this is nice but unnecessary. As I mentioned it in this thread, datasets move at different speeds, and attaching an new hash to something that didn't change doesn't add value. However, I have to recognize, I failed to mention earlier that datasets are often unrelated and not relational. This would be to reconsider if it were the case, of course. Most of the time, a dataset is represented as a single dataframe (or a single collection of dataframes).

There some points where git semantics make sense: immutability of commits, linearizability within branches. Both are extremely important if you want to enable reproducibility of your pipeline. These are traits coming from Event Sourcing.

Reproducibility is also claimed by DVC and Pachyderm, but their issue here is more a problem of trying to do too much things at once but not managing to do it right. Running code within Pachyderm pipelines was a recipe for disaster and the first thing we got rid of.

As for performances, the write side is where it matters, because it needs to be coordinated. Reads almost never are an issue with good caching. In any case, it should be robust enough to fill the gap between csv files sent to s3 and a full kafka cluster, eg: not complaining for a few TB. To my knowledge, the only multi-leader datastore suitable for storing sharded datasets as a continuous log is Kafka.

Re:Recently minted database technologies that I find intriguing by LukeEF on Tuesday June 16, 2020 @04:39AM (#23537059)
That's very interesting. This is why we think (Luke from TerminusDB again) designing your own full featured graph DB is the best appraoch to the problem - you can work from the ground up and ensure performance is satisfactory for the operations you want to deliver. I don't agree that Git semantics are not well suited to data, but you do have to be very careful about how you advance.

I was reading through these source code branching patterns and you can easily imagine (if design and performance is right) how they would apply to data - but as the author says 'branching is easy, merging is harder.'

https://martinfowler.com/articles/branching-patterns.html

Re:Recently minted database technologies that I find intriguing by LukeEF on Tuesday June 16, 2020 @04:54AM (#23537153)
Was discussing your point with a colleague and the comment about time travel is very interesting. Could you give a little more detail the the point? With us, everyone can be talking about a different time point in a branch whenever they like, and it has nothing to do with head. Two clients can look at different time points in our database and neither have to know about each other at all.

Also, for TerminusDB we don't use a multimaster coordination mechanism - we actually use the same sort of git approach.

Re:Recently minted database technologies that I find intriguing by Fiahil on Tuesday June 16, 2020 @06:54AM (#23537671)
First, I need to say that I never tried TerminusDB, so I can't claim having a strong opinion on your approach :)

Back to the time-travel. One of the most evident architecture when dealing with AI/ML/Optimisation is to design your application as a mesh of small, deterministic, steps (or scripts) reading input data and outputting results. As you would expect, output of one step is reusable by another one. Example:

Script A is reading Sales data from source S, Weather data from source W; writing its result to A. Script B is reading data from source A, and Calendar from C; writing its result to B. In this example, we want to be able to do two things: 1) run a previous version of A with S and W from 2 weeks ago and assert the result it produced now is exactly identical to the one it produced at the time 2) run a _newer_ version of A with S and W from 2 weeks ago and compare its result from the one it previously produced. Of course, in the real-world, S, W, C, progress at different speed : new sales could be inserted by the minute, but the weather data would likely change by the day. So, you need a system that would allow you to read S@2fabbg and W@4c4490 while being in the same "repository". That's why git semantics are not a good fit: you need to have only one "branch" to ensure consistency and limit misunderstandings, but you want to "commit" datasets in the same repository at different pace. For that purpose, event sourcing is much better :) (BTW, git at its core, is basically event-sourcing)

Kafka's architecture is actually the best solution.

Re:Recently minted database technologies that I find intriguing by zachmu on Tuesday June 16, 2020 @02:28PM (#23542448)
Dolt actually addresses this need exactly. You can query tables at two different revisions without checking out the two different branches. E.g.:

`SELECT * FROM S AS OF '2fabbg';` `SELECT * FROM W AS OF '4c4490';`

(Branch names work as well as commit hashes for the above).

You can even do joins between the tables as they existed at those revisions:

`SELECT * FROM S AS OF '2fabbg' JOIN W AS OF '4c4490' ON ...`

As long as your data is actually relational, it's a pretty good fit.

Re:Recently minted database technologies that I find intriguing by LukeEF on Tuesday June 16, 2020 @09:56AM (#23539166)
You'll have to try :)

Very interesting - thanks for the additional detail. Will have to think about how we might best represent in Terminus. We did a bunch of work for retailers in exactly the situation you describe.

Re:Recently minted database technologies that I find intriguing by buttersbrian on Tuesday June 16, 2020 @12:31PM (#23541063)
We do a similar revisioning system that just isn't performant enough.

Can you give me some more detail about what you are doing? We have a a large road ahead, and I am genuinely curious about the path you've taken.

Re:Recently minted database technologies that I find intriguing by Fiahil on Tuesday June 16, 2020 @05:03PM (#23544083)
In short, something very similar to https://www.snowflake.com/. An event-based system sitting on top of s3, indexed with a postgresql, influxDB, or anything else.
Re:Recently minted database technologies that I find intriguing by zachmu on Monday June 15, 2020 @07:53PM (#23534431)
> you caan get most of the useful git-like time-travel semantics (modulo schema migrations) out of timeseries data with a separate transaction history table

Well, not really. Dolt isn't just time travel. If all you want is time travel (or append-only data), you can do that with Postgres or MySQL pretty easily and get great performance. What Dolt brings to the table is actual Git semantics, a real commit graph you can branch, merge, fork, clone. You can inspect the historical values of each cell and examine the author and commit message for each change. Two people can clone a database, both insert / update / delete rows, and then merge each other's changes. If they touched the same rows, they'll have a merge conflict they have to resolve. If you find somebody's data they've published in the Dolt format has an error, you can fork it, correct the data, and submit them a PR to merge back, just like you do with code on Github. It's a database built for collaboration from the ground up. Instead of authorizing each person you want to make edits specific grants on particular tables / subsets of the data, you just let them clone it, then submit a PR that you can examine and approve. Or if they disagree with your policies, they can fork the data and still merge in updates from your master source as necessary. Git demonstrated what a powerful model the commit graph is for code. Dolt brings it to data, with SQL built in.

To answer your question about indexes across schema migrations, indexes are versioned with the schema of the table. This means they can be efficiently synced during a pull operation, but it means that previous versions of the data don't have indexes on them. We're considering adding the ability to add an index to the data from the beginning of the commit history as a rebase operation, but haven't implemented that yet.

Re:Recently minted database technologies that I find intriguing by gen220 on Monday June 15, 2020 @09:22PM (#23535001)
Heyo, thank you for responding! I totally agree that the commit, PR, merge semantics are powerful and valuable, and you can’t get that easily with existing databases.

I guess in my imaginary perfect world, you don’t need to use commit, PR, merge semantics to make normal edits. You can use existing bitemporal/transaction history ideas in online updates, appends, and deletes, and then you have a higher-level abstraction for offline(-only?) branching and merging.

I guess what I’m saying is that I don’t totally buy the idea that you need git “all the way down”, especially if it gets in the way of performance. But maybe I’m just used to “the old way”, and I’ll cringe reading this in 20 years. :)

Major kudos for this project, it’s super impressive. I’m excited to see how it grows!

Re:Recently minted database technologies that I find intriguing by jacques_chester on Tuesday June 16, 2020 @03:10PM (#23542878)
I have a hunch that some form of multi-temporalism, or perhaps more fancily some kind multi-lattice-ism, as the secret model underlying all of these.

But putting that probably-wrong-because-I'm-a-dilettante wankery to one side: a serious advantage of bitemporalism is that most layfolk understand how clocks and calendars work. This is less true of git commits.

Re:Recently minted database technologies that I find intriguing by gen220 on Tuesday June 16, 2020 @05:53PM (#23544726)
I think I agree with you. Git uses logical ordering rather than temporal ordering, and their concept of a primary key is rather nebulous.

We've been able to train up "regular folk" on bitemporalism at our company. The distinction between valid and transaction history takes a little while, but it sticks. Git has two or three such conceptual sticking points (staging vs committing, branching & merging, remote vs local).

Re:Recently minted database technologies that I find intriguing by zachmu on Tuesday June 16, 2020 @02:41PM (#23542592)
Hey, thanks for the kudos!

FWIW we are absolutely working on modes of using the product that "just work" like a normal database without any explicit commits / merges. In that case commits would happen automatically (maybe one for each write transaction?) and you'd get all the history "for free." We aren't sure how useful that is, though. Definitely looking for customers interested in this use case to guide us on their needs.

Re:Recently minted database technologies that I find intriguing by gen220 on Tuesday June 16, 2020 @05:48PM (#23544658)
I work at an insurance company, where it's extremely important that we have a way of persisting (1) what happened when [transaction history], and (2) how our opinions of what happened when changed over time [valid history]. Basically, you can think of (2) as a materialized view of (1). But it all lives in one database.

There is much business value to be extracted out of this idea, because most companies (1) do not have the ability to cheaply (i.e. ad-hoc) introspect this way (2) would gain a lot of value from this ability. My hunch is that most companies out there are interested in answering that set of questions (referred to as bitemporal data, in the literature).

This is the space that TimescaleDB seems to be competing in, although we don't use them (we use our own extension of Postgres, that's only a few hundred lines of PL-pgSQL, plus some helper libraries in Go/Python).

In my perspective, I think git-style semantics would be very powerful as a layer above an existing bitemporal system. We've implemented similar systems, where records get a semver-style version in their bitemporal key, so we can see how different producer logic versions minted different records over time. It's be really cool to have branching versions, and to be able to rebase them -- this is something our system doesn't currently support.

Anyways, hope our data point is useful for you all. Happy to share more if you have questions about anything.

Re:Recently minted database technologies that I find intriguing by LukeEF on Friday June 19, 2020 @07:06AM (#23573177)
Thanks for this - really interesting (Luke from Terminus here). We have engaged plenty with some reinsurance folk around some query use cases (recursive stuff to better understand risk), but that was before we did a lot of the git-like stuff. Can see how this might be powerful on a historical view in insurance.
Re:Recently minted database technologies that I find intriguing by nyanpasu64 on Tuesday June 16, 2020 @02:08AM (#23536369)
Have you heard of patch-theory-based VCS systems (Pijul is unstable, I heard Darcs is slower)? Instead of a chain of independent states (commits), each state is a set of patches (some of which depend on others). Pijul stores an internal structure that's more complex than "a series of chars/lines".

This eliminates complex rebasing topologies, and Git not recognizing that old and rebased commits are the same. But I'm not sure if it works well in practice. And it doesn't extend to SQL data sets yet.

I don't know if you're interested in this or not, but I just wanted to mention it.

Re:Recently minted database technologies that I find intriguing by xiaodai on Monday June 15, 2020 @08:49PM (#23534785)
Dolt doesn't work for large data right?
Re:Recently minted database technologies that I find intriguing by zachmu on Tuesday June 16, 2020 @02:17PM (#23542293)
Depends what you mean by "work" and "large," I guess. We have repos in the 250 GB range that definitely push the limits of functionality, but everything still works (pull, push, merge, clone, etc., and queries return the right results, if slowly).
Re:Recently minted database technologies that I find intriguing by xiaodai on Wednesday June 17, 2020 @06:38AM (#23549891)
"Slow" is the issue. I think it's going to be too slow for ppl's liking.
Re:Recently minted database technologies that I find intriguing by LukeEF on Monday June 15, 2020 @07:24PM (#23534255)
Thanks! Luke from TerminusDB here. We think a graph is the best way to approach a graphy problem!

We like Prolog for Querying, constraint checking and user interaction. Rust is great for low-level data manipulation. Prolog is a superpower - very simple but very powerful, but quite far removed from the hardware and uses abstractions not under our control so not good at nitty-gritty bit manipulation. We like Rust as a low-level memory-safe language (and it has a great community).

Much more background on why we made those choices: https://news.ycombinator.com/item?id=22867767

Re:Recently minted database technologies that I find intriguing by manigandham on Monday June 15, 2020 @05:06PM (#23532974)
There have been a lot of interesting projects released as alternatives to Elasticsearch and Solr like Sonic, Tantivy, Toshi, Xapiand, Typesense, Blast, etc.

List here: https://gist.github.com/manigandham/58320ddb24fed654b57b4ba2...

Re:Recently minted database technologies that I find intriguing by ekabod on Monday June 15, 2020 @08:01PM (#23534486)
Re:Recently minted database technologies that I find intriguing by amelius on Tuesday June 16, 2020 @05:05AM (#23537194)
> Bayard is a full-text search and indexing server written in Rust built on top of Tantivy that implements Raft Consensus Algorithm and gRPC.

Why would Raft be part of a search module when it can be written (and/or used) as a separate library? Curious about the design-choice here.

Re:Recently minted database technologies that I find intriguing by fulmicoton on Wednesday June 17, 2020 @10:01PM (#23559017)
It is using a separate library from the TiDB project.
Re:Recently minted database technologies that I find intriguing by manigandham on Monday June 15, 2020 @08:24PM (#23534642)
Thanks, already listed but updated the repo link.
Re:Recently minted database technologies that I find intriguing by rapnie on Monday June 15, 2020 @05:31PM (#23533217)
TerminusDB recently featured on HN. Also a git-for-data approach, time-travel, storing data as RDF and queries return JSON-LD.

https://news.ycombinator.com/item?id=22867767

Re:Recently minted database technologies that I find intriguing by LukeEF on Monday June 15, 2020 @07:14PM (#23534192)
Yes, the latest version of Terminus uses JSON-LD, which is a serialisation of RDF. This enables us to store queries in a database and provides a schema documentation for our datalog query language.

Release notes: https://github.com/terminusdb/terminusdb-server/blob/master/...

Re:Recently minted database technologies that I find intriguing by lhdj on Monday June 15, 2020 @08:52PM (#23534816)
Not really a database - but Parallax[0] is a data warehouse geared at private/sensitive data written in Rust - it allows you to perform data anonymisation at query-time.

Furthermore it exposes a privacy-as-code framework allowing you to version control and be very granular about how different entities interact with your datasets.

I'm on the core team - if you have any questions I would be more than happy to answer.

[0] https://github.com/openquery-io/parallax

Re:Recently minted database technologies that I find intriguing by lifty on Tuesday June 16, 2020 @03:21AM (#23536714)
Since you mentioned Dolt, I will add Noms [1], which is the original db that Dolt is based on. The folks that created Noms are working now on Replicache [2]. I started using Noms in my project and the offline first tech is really wonderful; it allows me to do some cool stuff like syncing clients only when they come online.

1. https://github.com/attic-labs/noms 2. https://replicache.dev

Re:Recently minted database technologies that I find intriguing by lessname on Tuesday June 16, 2020 @09:27AM (#23538885)
Is there also a javascript client for Dolt? I like the idea of Noms but it seems like it is not in active development
Re:Recently minted database technologies that I find intriguing by Arkanosis on Monday June 15, 2020 @06:50PM (#23533982)
I was about to ask if there was a RSS version of your newsletter, and it appears there is: https://cprss.s3.amazonaws.com/dbweekly.com.xml

Looks awesome, added to my feeds!

Re:Recently minted database technologies that I find intriguing by petercooper on Monday June 15, 2020 @07:03PM (#23534093)
Yep, you got it. We should link these from the front page really :-) The main problem is they're just an "added extra" and we can't always guarantee the formatting is 100% due to our focus on getting the emails just right. It tends to be OK 99% of the time though.
Re:Recently minted database technologies that I find intriguing by mywittyname on Monday June 15, 2020 @04:38PM (#23532691)
I'm surprised that it has taken so long for time series databases to take off. I remember in 2015 I wrote an application to wedge time-series features into MySQL because the only option I could find for a native db was paid.
Re:Recently minted database technologies that I find intriguing by mulmen on Monday June 15, 2020 @04:46PM (#23532770)
My day job relies heavily on Amazon Redshift. Most of our fact tables are sorted by date and almost always a locale.

I wrote a Python script to delete the stale records based on that time key. We have a separate vacuum service that cleans up the mess continuously.

Is this considered a time series database? Or is there something a dedicated time series database does differently?

Re:Recently minted database technologies that I find intriguing by akulkarni on Monday June 15, 2020 @05:51PM (#23533436)
Ajay from TimescaleDB here.

In general, a time-series database makes architectural decisions and focuses on capabilities for time-series data that lead to orders of magnitude higher performance and a better developer experience.

For example, TimescaleDB:

- Auto-partitions data into 100s-10,000s of chunks, resulting in faster inserts and queries, yet provides the Hypertable abstraction that allows you to treat the data as if it lives in a single table (eg full SQL, secondary indexes, etc)

- Provides Continuous Aggregates, which automatically calculate the results of a query in the background and materialize the results [1]

- Supports native compression, using a variety of best-in-class algorithms based on datatype (eg delta-of-delta encoding, Simple-8b RLE, XOR-based compression (aka "Gorilla" compression), dictionary compression [2]

- Adds other capabilities like interpolation, LOCF, data retention policies, etc, that are necessary for time-series data

- Scales up on a single node, or out across multiple nodes

There's more, but I'll stop because I don't want this to sound like marketing.

You are welcome to implement this yourself (and our docs explain how if you'd like), but most find it easier to use the database we already provide for free[3]. :-)

[1] https://docs.timescale.com/latest/using-timescaledb/continuo...

[2] https://docs.timescale.com/latest/using-timescaledb/compress...

[3] https://blog.timescale.com/blog/multi-node-petabyte-scale-ti...

Re:Recently minted database technologies that I find intriguing by mulmen on Monday June 15, 2020 @07:03PM (#23534099)
Thanks for the detailed explanation, that helps a lot.

It sounds like a time-series database does in fact include some elements of ETL at the database layer. I can see how that would be helpful.

Re:Recently minted database technologies that I find intriguing by akulkarni on Monday June 15, 2020 @07:08PM (#23534129)
Glad to hear. I'm also available via email ajay (at) timescale.com if you have additional questions

We also have a pretty active community on Slack (4,000+ members): https://slack.timescale.com/

Re:Recently minted database technologies that I find intriguing by mywittyname on Monday June 15, 2020 @05:17PM (#23533069)
I tend to think of them as databases where rows are an aggregate count of events over a period of time.

An overly simplistic example being an API that captures upvotes. Instead of storing the individual upvotes, you incoming requests in a queue and only write out a count of upvotes over, say 1 minute. That way, if you want to get a count over a larger period of time, you're setting a ceiling on the number of records involved in your operation. If you have 1 minute resolution and you're looking over a year of data, you're adding at most, 6024366 records for each post. This is really handy for analytics data where you don't necessarily care about the individual records.

The project I was specifically referencing was one that captured data streams from sensors like accelerometers and thermometers, which is inherently time-series data, because it's literally just (timestamp, sensor-reading). But to make use of that, you need a library of tools that understand that the underlying data is a time-series to do things like smoothing data, or highlight key events. For example, a torque spike had a "signature" which involved looking at the difference over time periods at a particular resolution. But a temperature spike would look different. Etc.

Re:Recently minted database technologies that I find intriguing by mulmen on Monday June 15, 2020 @05:36PM (#23533270)
I see, thanks for the explanation. Is aggregation in general a required feature of a time-series database? Or specifically the ability to calculate aggregates continuously over changing (incoming?) data?

Do time series databases store the raw underlying data for the aggregates or just enough to calculate the next window?

Re:Recently minted database technologies that I find intriguing by manigandham on Monday June 15, 2020 @05:09PM (#23533004)
Time-series databases themselves are usually key/value or relational.

The "time-series" features come from the focus around time as a primary component of the data (or some kind of increasing numeric value). Features like automatic partitioning and sharding, better compression, aggregating into time buckets, extracting and managing date values, smoothing missing values in queries, etc.

Re:Recently minted database technologies that I find intriguing by mulmen on Monday June 15, 2020 @05:34PM (#23533237)
Redshift does a lot of that. Is there something unique to a "time series" database that Redshift does not do? Is it the inclusion of some ETL capabilities? I'm not sure what makes a database truly time-series. It sounds like there is some compromise made that requires a time dimension. I'm not sure what that compromise would be beyond the general case of a columnar database.
Re:Recently minted database technologies that I find intriguing by manigandham on Monday June 15, 2020 @05:51PM (#23533447)
Nothing unique. Any database can store "time-series" data. If you look at InfluxDB (key/value) or TimescaleDB (relational), they offer things like high precision time values, higher compression algorithms that work better with timestamps, utilities to make it easier to query for time (buckets, timezones, gap-filling), automatic rollups and aggregations, data deletions based on time, etc.

You can do all that yourself on Redshift but they just offer more built-in functionality for it.

Re:Recently minted database technologies that I find intriguing by akulkarni on Monday June 15, 2020 @06:00PM (#23533558)
Exactly what mani says. For many people, Redshift, or just plain Postgres, is good enough. But if you find yourself wresting with poor performance, slow queries, high costs, or just an annoying developer experience, I'd encourage you to look at time-series databases (eg TimescaleDB, where I work).
Re:Recently minted database technologies that I find intriguing by fnord123 on Monday June 15, 2020 @05:28PM (#23533189)
You could have made a timeseries db. There's nothing magic about it. It's just a collection of engineering tradeoffs where you throw away relational features and indexes on various fields, instead focusing on performance for the huge number of events coming into an append only system (you can update but it's super rare).
Re:Recently minted database technologies that I find intriguing by cevian on Monday June 15, 2020 @06:37PM (#23533886)
To be clear, TimescaleDB does NOT throw away and indexing capabilities or ACID features. Its only limitation is that UNIQUE constraints need to include the time column. Also, its query optimizations are far from trivial to implement.
Re:Recently minted database technologies that I find intriguing by manigandham on Monday June 15, 2020 @05:31PM (#23533219)
If you build it on a modern relational data warehouse then you don't have to throw away any of that. That's more of how specific databases (like InfluxDB) choose to handle it but not necessarily a component of time-series itself.
Re:Recently minted database technologies that I find intriguing by fnord123 on Monday June 15, 2020 @05:44PM (#23533358)
Yes absolutely. However if you are aggressive in getting performance then you're likely to do things like batching the data and storing compressed blobs in the db and the rest of the row is essentially just metadata to the blob. Once you do this, relations on the data are basically thrown away because you can no longer do any joins on the blob data.

You can choose to not do this but then get ready to eat mediocre performance.

Re:Recently minted database technologies that I find intriguing by manigandham on Monday June 15, 2020 @05:59PM (#23533547)
Sure but that's because a "time-series" database is trying to get functionality that isn't already part of the underlying data store, for example if you're using RocksDB underneath and want to avoid slow scans.

You don't need to do that with a relational columnstore like Redshift, BigQuery, MemSQL, Vertica, Kdb+, or others. They're designed for massive aggregations with rows stored in highly compressed column segments that support updates and joins. And they're much faster than any custom compression scheme on top of another slower data store.

There's also the in-between options like OpenTSDB and Pinalytics that use Hbase/Bigtable. That's a sorted key/value datastore but it still applies compression on the row segments so you can leverage that in scans without a custom compression scheme on top.

Re:Recently minted database technologies that I find intriguing by fnord123 on Monday June 15, 2020 @07:08PM (#23534138)
>And they'll be much faster than any custom compression scheme on top of another data store.

As usual, it depends on the situation and what's being measured. For example, if you are able to batch a lot, then write performance of a blob will be line rate. If you need an ACK on each event then that's not possible and indeed a column store will be better.

> OpenTSDB

AFAICT, OpenTSDB compacts columns which is similar to what I've described.

https://github.com/OpenTSDB/opentsdb/blob/14ab3ef8a865816cf9...

https://github.com/OpenTSDB/opentsdb/blob/14ab3ef8a865816cf9...

> Pinalytics

This is proprietary so there's no visibility into what they do here.

FWIW, KairosDB is a timeseries db build on Cassandra and uses a row with 1,814,400,000 to side step the data management overhead via bucketing. Not a binary blob but certainly not the straight forward data layout that someone asking about 'why can't I just store data in a normal database' might expect.

https://kairosdb.github.io/docs/build/html/CassandraSchema.h...

Re:Recently minted database technologies that I find intriguing by manigandham on Monday June 15, 2020 @08:22PM (#23534627)
All relational columnstores use batch inserts and updates. In fact that's the recommended way to write columnstore data and SQL already supports various batch insert methods. The modern databases also use a row-store representation as a buffer for any single-row mutations before merging the columnstore segments. [1]

OpenTSDB compacts columns inside a column-family. This is because Hbase/cassandra/dynamo key/value stores support arbitrary columns for each row and store a verbose identifier for the column/cell/version matrix. It's a custom data format to save disk space but the underlying compaction still refers to HBase LSM-tree compaction. [2] The rows are still stored individually and compression is enabled on HDFS.

But yes, it's all highly dependent on the situation and it's the same fundamental mechanics of batching and compressing data. My point is that it's better to just use a database that is built with this architecture and can provide greater performance, usability and full querying flexibility instead of bolting it on top of a less functional data store.

1. https://docs.memsql.com/v7.1/concepts/columnstore/

2. http://opentsdb.net/docs/build/html/user_guide/definitions.h...

Re:Recently minted database technologies that I find intriguing by mulmen on Monday June 15, 2020 @05:32PM (#23533220)
Almost all of our load operations are a merge. Our ETL layer takes care of that. The vacuum then deals with re-sorting rows and freeing blocks.

I have never worked with something that bills itself as a "time-series" database. Does that involve pushing elements of the ETL layer down into the database? Or is it just optimizing for access by a time dimension?

Re:Recently minted database technologies that I find intriguing by santafen on Tuesday June 16, 2020 @04:56AM (#23537165)
Since you mentioned QuestDB ... I'm a former InfluxDB person, now at QuestDB. I'll say this about them: Their performance is off the charts good. Influx performance has been, frankly, headed in the other direction. I was looking at the NYC Taxi data the other day with QuestDB and I ran a sum over 1.6B rows of taxi data in under 150ms. 'select sum(passenger_count) from trips;'
Re:Recently minted database technologies that I find intriguing by heipei on Monday June 15, 2020 @04:38PM (#23532688)
Just came to say that I recently discovered your newsletter and am very glad for it. The database space has been seeing so much movement lately that your newsletter is the missing piece for staying on top of it.
Re:Recently minted database technologies that I find intriguing by arauhala on Tuesday June 16, 2020 @01:10AM (#23536102)
Hi Peter :-)

Antti from aito.ai here (https://aito.ai)

What do you think about Aito and the predictive databases?

We believe that the predictive databases can be a thing both in the database and the ML space, which is quite exciting.

I also feel that the 'smart databases' like Aito or BayesDB form an interesting database category, which will emerge as an everyday softwarw component once the technology matures. According to our internal benchmarks, it is already mature accuracy & performance wise for many applications.

- Antti & Aito

Re:Recently minted database technologies that I find intriguing by murkt on Tuesday June 16, 2020 @12:31AM (#23535957)
MeiliSearch is exactly not Elastic-like search server. It should work out of the box for its intended use case (fast typo-tolerant search), and it doesn’t support basically everything else. No complex queries, no aggregations, no big datasets, no clusters, etc.
Re:Recently minted database technologies that I find intriguing by willvarfar on Tuesday June 16, 2020 @01:57AM (#23536311)
thx, didn't know about that before, have just subscribed :D