There's no simple answer to your question, but here are a few things to think about.
First, scale isn't the only thing to worry about. What you do with your data is. If you have 500 tables 30 TB of data, and you are doing simple OLTP with very little reporting, I don't think you will have too many problems. There are 32TB databases on PostgreSQL out there. However, at the same time performance will degrade somewhat because it is having to hit disk on everything. Similarly if you have 50TB if data but have a commonly hit set of about 100GB, then you can build a server with enough RAM to keep that part of the db in memory and you are golden.
On the other hand if you are trying to take the mode (most common value) out of 1TB of data, it doesn't matter what system you are using, this is going to be painful with or without sharding. (Edit: Sharding may, in fact, make this problem worse.)
The major problems you will run into with huge db's on MySQL and PostgreSQL involve the fact that neither supports intraquery parallelism. In other words a query is run as a single block by a single thread, and it can't be broken down into pieces and run separately. This is most often an issue when running large analytical queries over large amounts of data. This is where Postgres-XC and Green Plum come to the rescue since they separate storage from execution, and can do this at the coordinator level. Note that Postgres-XC and Green Plum essentially use sharding internally but the coordinators enforce all consistency globally.
With intraquery parallelism you can break up the query, have different processors/disk I/O channels run parts of it, and report back pieces of the result set to be assembled and passed back to the application. Again, this is usually most helpful in analytic rather than transaction processing loads.
The second thing is that some systems, like Vertica or Greenplum store columns of information together. This makes the system harder to use from an OLTP perspective and decreases performance there, but it drastically increases performance for large analytic workloads. So this is a workload-specific tradeoff.
So the answer is that once you get above 1-2 TB in size you may find yourself faced with a number of tradeoffs between systems and workloads. Again this is specific to databases, size of working sets, etc. However at this point you really have to go with snowflake systems, i.e. ones unique and tailored to your workload.
This of course means that the limits are not generally quantifiable.
Edit: I have now worked with a 9TB database which handles a mixture of decision support and transactional processing workloads in PostgreSQL. The single largest challenge is that if you have questions that hit large portions of the data set, you will have to wait a while for the answer.
However with careful attention to the fundamentals (including indexes, autovacuum, how these work on the low level, etc) and sufficient computing resources, these are entirely manageable (and I estimate would be manageable well into the 30TB range in Pg).
Edit2: Once you head to 100TB though what works will depend on your data set. I am working on one right now that will not scale into this range because it will hit the 32TB per table limit in PostgreSQL first.