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?