I find it fascinating how almost every No/NewSQL database in almost every use case niche (document, time series, key value, etc.) gets blown out of the water by Postgres either from day one or a couple of Postgres releases later (sometimes using a plugin). It just goes to show how great their technology is. Plus, its very mature, well understood, safe, secure and stable. And its much more likely to still be around (maintained) 10 years from now than the current NoSQL flavor of the month.
After we had some pretty horrendous experiences with several NoSQL dbs (mostly MongoDB, CouchDB) on client projects, we strongly urge all clients to just use Postgres. The only exceptions are redis (for caching, queues, etc.) and elasticsearch (for ... search), which are just very convenient complements to Postgres. We have never found a single instance where the postgres performance was not as good or better than the NoSQL alternative.
I use and like Postgres, but it's crazy to say there's never a scenario where putting data somewhere else makes sense. You just named two examples, Redis and Elasticsearch.
It might be fair to say something like: if you have a general purpose OLTP workload with lots of individual reads and writes, with lots of interconnected relationships between models, where you'll need to query and index by lots of different attributes, then you'll probably see as good or better performance with Postgres vs a NoSQL alternative.
But there are other uses where you'll have a much easier time using a message queue, a streaming platform like Kafka, flat files in S3, some ingestion system to feed HDFS or Redshift, a sharded key/value store like DynamoDB, etc.
I would even go as far as to say, in my own anecdotal experience, for every case where I've seen an app struggling because they're pushing a NoSQL DB beyond its sweet spot, I've seen another app struggling due to pushing Postgres or MySQL. Things like writing all application logs to the primary Postgres DB, or using it as a message queue. To be fair, the catalyst of the problem in these cases is usually the mixed workloads in a single DB instance, and a separate instance of Postgres would work better. But still, other tools that are built for a much more specific purpose can go even further. Plus they can be easier to tune, with Postgres you'll often have to worry about configuration and things like query planner statistics, vacuuming, and XID wraparound.
I think there is a problem in that a number of current developers never went through the era where SQL (whatever DB you want) was the only choice. Specialized DBs came about - NoSQL, Graph, etc. Developers not trained in or experienced with the basics go with what they deem shiny and interesting. As experience grows, one realizes hey, maybe something like Postgres makes sense.
It's better to go with "what works" and then optimize. Sadly, too many developers don't have the breadth of experience to know the tradeoffs.
I think you're right (although it makes me feel like an old man shaking his head while talking about "the kids these days") but I also think it goes far beyond just SQL v. NoSQL and into many other areas of our field in general.
Druid still crushes Postgres, so I'm happy with our decision. However, I get a lot of sad faces when eager engineers come to me and ask which "cool" database they should use to meet the needs of Project X. "Postgres, unless you can prove otherwise" is always my answer. It is a great piece of technology, but it isn't the answer to everything ... just most everything ;)
I am missing many features in InfluxDB. Some issues are open for years with no progress.
Some highlights:
1. Create measurement and insert data. You'll insert an integer. Then, you insert a float value, because that can happen. Value won't be inserted, as Influx assumes the datatype on first insert. Ended up creating a new measurement and casting all values before inserting.
2. No Log()/exp() functions. I like to store raw values, like example from ADC or barometric devices. I can't convert a raw barometric pressure to a pressure at sea level, because there is no exp() function. I don't like to store two values...
3. Downsampling is a broken. Downsampled values must be saved to a different measurement. Displaying those data in Grafana must therefore be always for 2 (or more) measurement. One for recent data, one for old one. This is a mess.
4. Deleting values. You can "SELECT * FROM bla WHERE value < 10" for example. But you can't "DELETE FROM WHERE value < 10" because delete only supports timestamps in the WHERE clause. This is especially painfull because there are no constraints to disallow obviously false values for inserting.
I'm curious what you do for Postgres to get an HA, as the last time I worked with Postgres it was a nightmare to get even a bare bones HA working. I swore I'd never touch it again until they had a real HA solution. I ask ever year or so and keep being told it still doesn't have one.
You still need a mechanism to swap over to the standby on failure. However with libpq from postgres 10 you can provide multiple servers. This isn't true HA, but for most people it fits the requirements.
From their FAQ (http://www.pgbarman.org/faq/):
Does barman manage replication and high availability as well? How does it compare with repmgr, OmniPITR, walmgr and similar tools?
No. barman aims to be a pure disaster recovery solution. It is responsible for the sole backup of a cluster of PostgreSQL servers. If high availability is what you are looking for, we encourage you to use repmgr. barman specifically targets the DR case only
Unrelated to your point, but I will be so very happy if/when WordPress ever supports PostgreSQL. At that point, I will be able to ditch MySQL completely; WordPress is the only reason I still have to manage MySQL servers.
I totally agree, PostgreSQL has been an incredibly good experience and joy to develop with. The stuff that already comes with most distro packages is already blowing out most other RDBM systems and the stuff you can get elsewhere just solidifies it.
Though I'm also looking at some alternatives too, CockroachDB has some of my attention since they seem to have masterless HA figured out, if they could combine that with PG... (they do use the PG wire format already)
What about when you want to store documents with an unpredictable schema? That is, a collection where the field names and types could be different in every document.
Granted, I've never actually encountered that situation.
The JSON support in Postgres is in my experience much more consistent than that of e.g. MongoDB, and I understand performance is better too. Postgres really is awesome :)
No issues so far except of course the initial difficulty correctly setting up the tables and continuous queries.
The main problem for us is that clustering is available only to the enterprise version which is way too expensive for a small self funded company like ours. I wish they offered more options without any support and a big discount.
For us the problem is that we have been talking to influx sales for over 6 months to get a quote for those features, however it is just a never-ending interview process to determine how much we are willing to pay. We are looking at other options at this point.
Hey, we make clustering and high-availability part of our system for free, very openly licensed as MIT/ZLIB/Apache2. Check it out, 7.5K+ stars - https://github.com/amark/gun , we view clustering/availability as a core necessary feature of any database and not some "optional" or "enterprise" thing, and written some pretty serious articles about it back when RethinkDB and Parse shut down, which was about the same timing as when InfluxDB removed it: https://hackernoon.com/the-implications-of-rethinkdb-and-par... .
I evaluated InfluxDB at my previous company. Settled for TimescaleDB in the end due to the querying power of Postgres.
Influx had some real quirks with nested queries (wrong data being returned). TimescaleDB is probably a bit slower and less compact but with our data (a few GBs/day) it wasn't our biggest problem.
When you talked about 150M queries per day; are those inserts or reads? That's about 1,700 per second, which to be honest doesn't seem like a lot for time series metrics ingest. I would expect a single node on most TSDBs to be at least ~100 times that performance.
Can you talk about the data you were ingesting? Was it numerics, text, or something else?
Hi there -- We are currently ingesting 150M/day -- though it's not evenly distributed -- probably peaks around 3,000qps
Agreed that it's not too much yet. I think the element which kills us is needing to do rollup tables, and then query against both the raw data and rollup data for our customer analytics dashboard.
The data is a combination of date/time, strings, and ints. 22 fields.
Influx founder and CTO here. I figure I should comment because it seems this post is a bit dated on information. That being said, I think this is one of those cases where based on where InfluxDB was at the time, they simply picked the wrong tool for the job. We were looking at use cases with hundreds of thousands of series. It just wasn't designed for what they were trying to do (for whatever version they were running at the time). We have much higher aspirations now, but I'll get to that in a bit when I talk to each point raised in the post.
As I mentioned, it would be helpful to know which version of InfluxDB was tested against, but I'll try to cover each of the points.
1 - Cardinality. High cardinality has long been a known problem for InfluxDB. We started work on this almost 15 months ago to create a new feature called TSI or Time Series Index. It was significantly harder for us to develop than we thought it would be. It can be enabled on version 1.5, which was released yesterday. We'll have a detailed whitepaper on how it works and what techniques we used coming soon. It shows significantly lower memory usage for high cardinality write workloads. The other side of this is query, which I'll talk about in a bit.
2 - Rollup tables is a known issue for us. Continuous queries were originally supposed to do this, but the problem is that it falls over at scale. There are some other weird things about how it works where it won't pick up lagged data collection. It's a weak spot for us, Prometheus, OpenTSDB and a bunch of other solutions. From what I can tell, Timescale doesn't offer this feature either. See https://github.com/timescale/timescaledb/issues/350. It sounds like DNSFilter implemented this themselves using their Kafka ingest pipeline and computing them and inserting into Timescale tables (which for now is exactly what we recommend with InfluxDB). If I'm guessing wrong, I'd love to hear the detail about how this works in your solution.
3 - Ingestion performance vs. Query performance. It sounds like query load causes ingestion performance to degrade. I really don't know without more information. In the post Mike said they were working with InfluxDB in 2015 and 2016 and didn't continue upgrading. So they could have been running 0.11.0, a version that is now over two years old. We've made significant improvements since then. However, I will say that our current query engine is a known weak spot. This is why we're investing heavily into IFQL (our new language and engine). The new language also addresses the InfluxQL doesn't actually operate like SQL, which is something he mentions at the top of the post. I agree that can cause frustration, which is why we're designing our new language around this use case. I think the functional paradigm makes more sense than SQL for time series. Slides from a recent talk I gave about the engine:
4 - Resource utilization. It uses RAM, particularly on high cardinality workloads. Yes, we know this and have known it for a long time. Running tests on 1.5 with TSI enabled, the RAM utilization is significantly lower. However, TSI is only one side of the issue. The other side is querying high cardinality data. Our current query engine will eat many resources trying to do this. It's something we're addressing with the IFQL engine. You can actually use the new engine as a separate process against the InfluxDB 1.5 release. However, it's still under heavy work and we haven't begun the performance optimizations.
I'm confused how Mike talks about about their query processor and then immediately dives into Kafka, which is only relevant in the ingestion pipeline. In fact, our recommended configuration is to route writes through Kafka before sending to InfluxDB if you're operating at scale. It's how we're designing 2.0 from day 1 and that work has already started. In an analytics pipeline, you want to separate your write pipeline from production query/storage.
1 - Ease of change: schema changes in InfluxDB aren't easy. Postgres supports alter table commands. How do these work on large tables? Has Timescale (or Postgres) solved the problem of these kinds of operations taking the DB down for a while? I think that's one of the strengths of having Kafka in their ingestion pipeline to protect against it (or other DB issues). We still need to do work here and I know it's a weak spot and it's honestly just a very hard problem (for how data is stored on disk for us). In the future, at the very least we'll enable users to kick a schema change off and have it run in the background while keeping the production infrastructure up. Depending on the change it might be a long running background task that requires rewriting many things.
2 - Performance - Impossible to address without knowing their data, their queries, and really testing it on a new version. We're optimizing all the time. I guarantee you that the query performance you see in InfluxDB today won't be anything close to as good as what it'll be a year from now.
3 - No missing data - I don't know of any issue in the current release (or many previous releases) of Influx that just drops writes. This might be something to do with Continuous Queries since it sounded like it was about rollups. It's a known issue with that feature and it's being completely reworked in 2.0 to work in a guaranteed way, at scale. It's a long running problem and one that is hard to get right. Again, it sounds like Mike and team implemented it themselves through their ingestion pipeline and then attributed the gain to Timescale. You can do the same thing with InfluxDB and it's our recommended architecture. We tell customers to do their downsampling
I'm not sure what is going on with deletes. You can do deletes in Influx and you can delete by a time range, which would give you a specific point. That being said we've done significant work in the last three releases to improve how deletes work and particularly how they work for deleting large amounts of data.
For loads, I'd really like to see a comparison against the 1.5 release with TSI on compared to Timescale. A release that's 1.5 to 2 years old with InfluxDB is completely different that what is current, as many people that have been watching the project over the last 4 years has seen.
Finally, Mike does make a case at the end for Timescale just being SQL and Postgres so they found it easy to use because it's familiar. For better or worse, we're making the bet that SQL isn't the last only true API for working with time series data. Some developers will prefer SQL and some will prefer other approaches. For InfluxDB, we're making a bet that other approaches will be better for developer productivity.
I agree that in the end, InfluxDB ended up not being the right tool for the job. We're using it more-so as a backend analytics system for our customer dashboard, making it query-heavy.
Sorry, you may have read an earlier version of the post -- I'd since updated it to specify that version 1.3 is the last version we used, a few months ago.
We currently do rollups in TimescaleDB via a cronjob that runs a stored procedure. It references a rollup table for the bigid of the last data element in our raw table. It is then rolled up with an upsert. In this way, we don't miss any items while rolling up. They're working to make it more 'native' to the TimescaleDB offering, but it's been working great for us.
We use Kafka only as a queue to send data to multiple timescale instances. Kafka has allowed for us to shift to a different timescale instance when making database changes, if an alter table is blocking. Not all table or index changes are blocking though.
Thanks for the reply. Interesting that you're using crons to query and then upsert. We're going to be doing the same thing in our 2.0 work. That paired with our new query engine should make things much better on our end. So it's one of those things that we know is a weakness and we're working quickly to try to solve and make it a first class citizen in the DB. For many workloads we've had people working successfully, but we keep expanding our focus as we go.
If you tested against 1.3 we might have more work on queries and writes at the same time. Although it may be that with the 1.5 and TSI enabled, that problem would largely go away, but not sure. Can you provide more detail about how many QPS you were pushing?
For the different Timescale instances, are they share nothing and your write and query pipelines shard based on customer? Didn't think there was a clustering solution available.
Thanks again for providing more detail, much appreciated.
Answering question #1, an alter table command that adds a new column without a default value is essentially free. Even on a large table. Even if the table is busy. However altering an existing column, or inserting one with a default requires locking the whole table.
But adding a column is a much more common operation than anything else, so this gets rid of the biggest pain point.
Thanks for the answer. Sounds like mileage may vary depending on the operation. For us, we've seen people frequently want to change data types (from int to float or vice versa). Although that more frequently pops up because they started out writing the wrong data and we don't require them to define a schema up front.
In InfluxDB, adding tags, measurements or fields is a free operation and requires no downtime or even an alter operation. Just write the data in and you're good to got. It's different if you want to add that new thing, but also add it for all historical data. For that, you can do the historical backfill and then drop the old series that don't match. But I will admin that how the underlying series map to a columnar set is not made clear to the user. Maybe something we can provide in a more automated and understandable fashion in the future.
Yeah, I'd think about that for the analytics use case. It's interesting technology and I always keep an eye out for what Influx can learn from these other projects. This CloudFlare post is on my readlist to learn more about it: https://blog.cloudflare.com/http-analytics-for-6m-requests-p...
I did not. One of the benefits from TimescaleDB is the ease of development and using standard queries and indexes to deal with the data. It would be another system for us to spin up on.
The issue for an operational workload like this with Clickhouse might be the lack of direct support for UPDATE and DELETE. The workarounds required would add additional complexity, I think.
Dang, 3B records/day is very impressive - and I'm a competing distributed systems database engineer!
Our tests were doing about 100M+ (100GB+) a day (this was 2 years ago), although on significantly smaller hardware, but I don't think it would've hit 3B even on the Intel Xeon you guys used. Stats: https://www.youtube.com/watch?v=x_WqBuEA7s8&t=1s
Just wanted to say, really impressive work! Respect.
After we had some pretty horrendous experiences with several NoSQL dbs (mostly MongoDB, CouchDB) on client projects, we strongly urge all clients to just use Postgres. The only exceptions are redis (for caching, queues, etc.) and elasticsearch (for ... search), which are just very convenient complements to Postgres. We have never found a single instance where the postgres performance was not as good or better than the NoSQL alternative.