Could 40 million be Big Data, as in too big for my desktop, and requiring Hadoop or something - or would this be manageable on a RDBMS database server?
Tens of millions of rows with up to hundreds of columns per row is pretty solidly in the relational database range. MySQL, PostgreSQL or even H2 will handle this pretty well on a single machine. However if you are doing any expensive per-row calculation (like natural language processing) or any sort of all-pairs work (like nearest neighbor) then you will see benefits from multiple machines and something like Hadooop to distribute and manage the work.
Big Data is whatever you want it to be. For the VC blowhards around here, it is happiness, wealth, a solution to world hunger, and a bikini model with DDD boobs that loves old nerds and doesn't get them thrown in jail for drug trafficking.
But for people who actually work with it regularly, it is typically a constraint that manifests itself in the form of impatience. I could process petabytes of data using AWK, but if I can't wait that long, I will end up using Cascalog on a cluster.
40 million rows of one column with 1 byte is probably fine in a reasonable desktop system. 40 Million rows of varying "blob" fields each containing a totally different structure of data data, each with a gig or so of various entries... well, I would be surprised to see any current RDBMS manipulate this the way I want as an analyst with ease, efficiency, and speed.
It's not the rows, per se, but the size, variability, and in some cases, the velocity of the data that start to tax RDBMS. Anything with a consistent and relatively simple structure, even with long-and-many rows, can be handled in many modern RDBMS. But once you start varying the data per row, and mixing different levels of atomicity, the Big Data systems tend to provide more flexibility and ease, imho.
This is very manageable on RDBMS software, 100 million row tables is considered small for data warehousing systems; at least that's been my experience with Oracle installations.
