The problem is more about constrains required to scale and achieve high-availabily and low-latency, rather than a database choice. But classical DBMSes lack those constrains and you may and probably will end up with a design that just cannot be scaled without removing and redesigning a lot of things that users rely on, and also with engineers that never learned how to do that, so you may end up in an urgent need of a distributed systems specialist too. Scary. You can avoid that by using systems designed for high-availability from the very beginning, like Riak. Same goes for blobs and filesystems, using POSIX API instead of an object storage is going to haunt you later on, because in your designs you may end up relying on things that ultimately cannot scale well and guaranty latency, as gitlab learned the hard way.
Schemas can be straight jackets, but in many situations they're actually closer to life jackets.
Poorly structured data, on the other hand, where the schema and consistency is enforced in code is much harder to migrate.
Maybe our problem is that we write systems with a disposable point of view? We don't even think the system will be around long enough to require migration.
On the other hand, it gets more and more operationally complex as you add shards and failovers and such. If you KNOW you are going to hit that scale (say you are an established company with 100 million customers), it may not be the right choice.
But thanks for pointing out sharding. It can be powerful strategy.
There are the 80% bottom of companies who are just toying around doing small products and MVP, that should stick to what's easy.
There are the 5% at the top who are guaranteed to get 1 million customers no matter what they ship, that should stick to doing things right and sustainable from the start.
Contrary to what the other comment state: sharding and failover are ALWAYS the EASIER choice at scale. The alternative is to be unable to change hardware/systems and to be forced into 15 hours downtime window for every maintenance/upgrade operation, which is a huge liability when you have real users.
The 2 types of companies have opposite priorities.
I absolutely agree. RDBMSs are the kings of one-size-fits-most. In the NoSQL ecosystem, MongoDB starts growing into this role. Of course, many performance problems are not caused by the backend but rather the network (an average website makes 108 HTTP requests) or the frontend (Angular2 without AOT, I'm looking at you). We took an end-to-end approach to tackle this problem:
1. As objects, files and queries are fetched via a REST API, we can make them cachable at the HTTP level to transparently use browser caches, forward proxies, ISP interception proxies, CDNs and reverse proxies.
2. In order to guarantee cache coherence, we invalidate queries and objects in our CDNs and reverse proxy caches when the change using an Apache Storm-based real-time query matching pipeline.
3. To keep browser up-to-date we ship a Bloom filter to the client indicating any data that has a TTL that is non-expired but was recently written. This happens inside the SDK. Optionally client can subscribe to queries via websockets (as in Firebase or Meteor).
The above scheme is the core of Baqend and quite effective in optimizing end-to-end web performance. Coupled with HTTP/2 this gives us a performance edge of >10x compared to Firebase and others. The central lesson learned for us is that it's not enough to focus on optimizing one thing (e.g. database performance) when the actual user-perceived problems arise at a different level of the stack.
Baqend has streaming queries whose results stay up-to-date over time; purely push-based, no pulling involved. You just have to register your query as streaming query and receive relevant events or the updated result every time anything of interest is happening. In our opinion, this is not only comparable, but goes beyond the synchronization feature of Firebase, because our real-time queries can be pretty complex. A Baqend streaming query can look something like this: SELECT * FROM table WHERE forename LIKE 'Joh%' AND age > 23 ORDER BY surname, job DESC
We are going to publish this feature during the next weeks. But you can already read the API docs here to get a feeling for the expressiveness of the feature: https://www.baqend.com/guide/#streaming-queries
We also do have some benchmarks comparing real-time performance, which are under submission at a scientific conference, so we cannot share them yet.
Unfortunately, testing against Firebase using established benchmarks such as YCSB is not very useful, as it's unclear which limits are enforced on purpose and which limits are inherent to the database design. Would you be open to providing a Firebase setup for heavy load testing w.r.t. to throughput, latency and consistency?
For any readers, the test AFAICT is time to last byte for a single reader. It's a bit unfortunate that it seems local caching is affecting the bagend result by the time you switch the comparison to Firebase (though in full disclosure, Firebase's local cache isn't available on JS so that's a bit on us too).
I could give a few perf tips on the data layout for Firebase but it isn't clear whether the toy app has intentional indirection to model something more real.
hi, this comment was timely. I have been asking myself what to use to persist a 10 million node graph. There has been a lot of marketing around using Hbase (obscene to setup and maintain) and Cassandra (much better.. but paywalled).
My main db is postgresql and we use jsonb quite extensively. The problem is that there is a lot of statements that "a single huge jsonb table in postgresql will never scale. you will have made a horrible mistake".
Seems you have ample experience on this front - whats your take?
(Sorry for the pedantry, but I both work on a cloud document-oriented database and recently watched a bunch of collegiate developers struggle to set up MySQL when their queries could easily have been handled by a simpler system.)
- When properly normalized, future modifications to the relational model will often be much simpler. Transforming hierarchical data stores or document-based ones often involves changing much of the code which calls the database
- Query optimizers are often performing work which is difficult to implement yourself.
- When properly called, query composition allows more computation to be done close to the data.
- Knowledge of RDBMs is widespread allowing a low barrier to entry (is also a huge disadvantage, since it brings inexperienced people to use a sophisticated tool, leading to overgeneralized bad press)
- Index management in relational model is often simpler and logical
- Many RDBMs have a proven track-record of stability, code quality and feature completeness. Take a look at PostgreSQL, for example: GIN / GIST / PostGIS / date-time / windowing functions / streaming results. Also, the extensibility of postgres is astonishing, the community is warm and welcoming to beginners.
Obviously, I could make a similar list of disadvantages of RDBMs. It's just my experience that the advantages mostly outweigh the disadvantages.
Of course, if all you need is to look up values by keys, a key-value store is a much simpler solution. But the moment you need more complicated or just different kinds of queries than you expected, NoSQL can be rather difficult. You can get a lot of complicated queries done with mediocre SQL knowledge and a general-purpose RDBMS, but trying to use a more specialized database outside the intended use can be pretty painful.
I don't have all that much experience with NoSQL databases, but my impression was always that it is more important there to understand the limitations of the database than with something like Postgres that is more forgiving. They might be simpler to get started with, but much harder to use correctly.
Another thing (which often overlooked) is that because relational database typically matches data model better your data will take less of space than when stored in NoSQL. With Postgres there's also the extensibility.
I learned this myself, we had a database that stored mapping of IP to a zip code and latitude/longitude to a zip code. The database was on MongoDB, but it wasn't too big (~30GB) the thing is that to maintain a performance all of the data needed to fit in RAM.
I was a bit curious and decided to see how Postgres would deal with this. So I loaded ip4r extension (to store IP ranges) and PostGIS to enable lookups on latitude/longitude. All the data fit in ~600MB, and with proper indexes postgres returned all queries instantly (sub milisecond).
The Mongo was running on 3 c3.4xlarge instances, but postgres would be fine on smallest available one and would still be able to cache whole database in RAM.
The big relational DBs scale well (near-terabyte with thousands of queries per second) with little maintenance. They scale ridiculously well (terabyte+ with a million+ queries per second) with more maintenance. Most loads simply don't require the scale at which RDBs start to actually falter at.
Just curious, does this kind of performance require dedicated hardware or is it possible on platforms like AWS?
q)a:(200000000?200000000),-42 /pick some random numbers
q)\t a?last a
303
We have tables with > billion records In RDS / SQLServer and return results in <1 second...
That'd be very impressive. Does SQLServer support parallel execution? Or how come it be so fast?
For a long time as far as I know?
Anyway, is the performance you mentioned for a scan over the entire table? E.g. to do an aggregate?
I know a count of the whole table takes about a minute tho. But the filter > group by was ~1s as the grouping was done on about < 50 rows of the filtered result.
Without knowing the original query above was using it's speculation on why it was slow and mine was fast. My argument is just that SQL Server isn't magically slow. MySQL/PostgreSQL/SQL Server are super fast. And if you don't massage the database it can be super slow too.
> I know a count of the whole table takes about a minute tho. But the filter > group by was ~1s as the grouping was done on about < 50 rows of the filtered result.
Yeah, that makes sense.
I'm running some queries in production that take a 150 million row table, filter it down to ~100-300k rows which are then aggregated/grouped. This usually takes < 1s. However, if I'd try to do a count(*) on the table, that'd be around a minute as well.
> Without knowing the original query above was using it's speculation on why it was slow and mine was fast. My argument is just that SQL Server isn't magically slow. MySQL/PostgreSQL/SQL Server are super fast. And if you don't massage the database it can be super slow too.
Yeah. The query / query plan will be needed to go more in-depth on these kind of discussions. The amount of disk vs memory hits during execution obviously as well.
Anyway, I just wanted to understand if SQL Server was e.g. an order of magnitude faster than Postgres when scanning a large number of tuples. But I guess the answer to that is: probably not.
I work with a customer where every application has to have a dedicated environment, running on VMs in a seperate VPC on a private cloud. The default DB is a galera cluster with 3 nodes, even for the most simple application.
The argument for it is that cloud VMs are expected to fail, so everything has to be setup to tolerate instance failure without downtime.
All I have to counter it is the supposedly higher risk of data loss in a complicated split brain, the unspecific "more complicated" etc. While the risk of instance failure is very real, that feels a bit theoretical and vague.
0 downtime and 0 data loss is impossible, both theoretically and practically. Even a Galara cluster is going to have downtime if a node fails (timeouts expiring, etc.).
Really, it comes down to what trade-offs is the company willing to accept: the potential for stale reads for high availability, slow/aborted writes for high consistency, downtime for reduced complexity, etc.
A few notes about Galara: its performance going to become severely degraded if one of the nodes decides it needs to be rebuilt - both that node and the donor node will effectively be out of the loop, with the network link between them saturated for the duration. That degraded state doesn't require the node to go down, it can happen spontaneously.
Galara also imposes limits on write frequency and location - if you're doing many writes, you don't want to split those writes between nodes, since every write has to look at every other pending transaction on every node before it can occur.
An automated RDB master failover can occur in under 30 seconds, easily. You can also typically run master-master without a risk of split brain by specifying even and odd primary keys.
> cloud VMs are expected to fail
Yup, but that timeframe is usually on the order of months or years. And if you use externally backed disk stores (at the cost of read/write speed) you can be back up in a matter of minutes with a single master. Even a full restore from backups can typically be done in 10-15 minutes; constrained mostly by your network speed.
--
That's cool and all, but for some real-time applications, retrieving in the order of seconds is unacceptable.
DBMS has their earned place in the backend stack but I found that statement of covering 98% of the cases a bit of a stretch.
Many engineers I have worked with like to throw around terms like: "CQRS", "Event sourcing", "no schema's", "document-based storage", "denormalize everything" and more. However, when pushed, I often see that they lack a basic understanding of DBMSes, and fill up this gap by basically running away from it. For 95% of the jobs, a simple, non-replicated (but backed-up) DBMS will do just fine. The remaining 3% might benefit from some sharding and the final 2% are for specialized databases, such as column-oriented DBMSes, document-oriented DBMSes or graph-based databases.
At this very moment, I'm working on a classical DBMS, searching through around 200 million tuples within the order of 2 seconds (partially stored columnar using psql array types and GIN indexes). Not for a second did I consider using any kind of specialized database, nor optimized algorithms, except for some application-level caching. Some problems are caused by DBMS to backend and backend-to-frontend network utilization, which is generally not something a specialized DBMS can solve.