3

Given:

  • The volume of data is expected to run into petabytes over a couple of years.

  • The full set of record types, applications and queries is not known in advance. It is expected that stakeholders will keep finding new uses for the data, as normally happens with a large database.

  • The system will run on commodity hardware distributed over several data centers.

  • Open source solutions are strongly preferred if at all possible, for pragmatic reasons of wanting to be able to modify the code if necessary, and not wanting to get into a weak bargaining position with a vendor.

What is the best database engine to use?

If we were just talking terabytes, I would simply specify Postgres and be done, but my understanding is that an off-the-shelf SQL database cannot be expected to scale to petabytes.

I am given to understand that Yahoo did modify Postgres to so scale. It seems to me this would basically entail transferring load from the programmers writing application code (who now get the usual benefits that they don't have to worry so much about never letting any errors slip through, because a relational database does a lot to enforce consistency) to those maintaining the database engine (transparently providing those guarantees together with fast SQL queries on such a scale is a hard problem).

An alternative would be to take a good NoSQL database engine and tweak as needed. This puts more onus on the application programmers to never make a mistake but makes it easier to be confident that any given application can be made fast enough given enough effort.

Is the first option considered reliably viable these days?

Is the second option typical practice? If so, which NoSQL engine is the best for this scenario?

Is there a third option I am missing?

  • 2
    A great question! I don't have an answer, but I think that "distributed over several data centers" is key there. Personally, I would, by instinct, stick with a Relational Database, because indexing & efficiency are going to be very important as you scale, and I just don't feel that NoSql can handle that (but would be happy to be proved wrong). Personally, I would got for MySQL over Postgres, but for Postgres over NoSql. This is the correct site for Software Recommendations, but if you don't get any, try dba.stackexchange.com – Mawg Sep 5 '18 at 13:20
  • 1
    Are you talking about OLTP (high rate of inserts/updates for individual rows) or analytical (few, heavy, mostly read-only) workloads? "Petabytes" implies historical data, while "programmers writing application code" and "never letting any errors slip through" suggests you're thinking of OLTP (which is usually sized in terms of the number of concurrent requests). Using the same instance (I'd say even tool) for both is a bad idea. – Nickolay Sep 6 '18 at 21:43
  • @Nickolay Both are required, but it might be possible to set up two different databases. In that case, which tool would you recommend for each? – rwallace Sep 7 '18 at 10:51
1

I feel it would be irresponsible to recommend something based on the little details provided, but since my thoughts didn't fit in a comment, I'll post it here.

For the OLTP use-case: if you think Postgres is a good fit feature-wise and for your development team, and the only concern you have is scaling, the metrics you should consider are the operational data size (the amount of data queried by the OLTP requests) and the number of TPS (transactions-per-second) and their read/write rate), rather than the total amount of data the system will accumulate.

I don't have first-hand experience with scaling Postgres, but a 1,000,000 read queries / s is said to be achievable if the data fits in memory, as are 10,000s writes / s. You can put caches in front of the DB to improve read performance and implement sharding (via extensions) to scale writes.

I'm trying to avoid the NoSQL vs RDBMS debates, but for those whose main concern is scalability, the former could be considered a typical practice...

For data warehouse/reporting use-cases (run this SQL on terabytes of data in a minute) there's a class of solutions called "MPP databases" (if you're into Postgres, you might have heard of Greenplum). They shard the data and run the query on multiple (relatively high-performant) nodes in parallel, but they're not optimized to process a large number of lightweight queries.

If you need a cost-effective way to store the data for analytics and/or you're not willing to be locked into a single tool, the Hadoop ecosystem might be interesting. You lose some efficiency (and spend resources building up the expertise), but gain the ability to run arbitrary "big data" solutions (ML, streaming, plethora of DB engines) or custom code on your cluster.

2

Take a look at CockroachDB. You're talking big data and the platform needs to support serious scaling.

https://www.cockroachlabs.com/

It's probably good out of the box and it has a good open source community.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.