Sorts of views might be: "show me objects that have lat xx and long yy but tagged "buttons" and created on mm/dd/yyyy" in the basic instance, but also "show me objects that my friends like" as a more complex example.
Makes me think that what I really need is a graph db- object relationship seems the key factor.
If you need lat/lng then you need an R-tree and very few databases come with that.
Sounds to me like your going to have to write your own database - or more accurately your own index.
Basically store data as append only, and handle change as delete/append. Store just the offset to the data as the key.
I really hope your index can fit on one machine.
Do it like the facebook link I gave you earlier.
Your going to have a hard time with index merges (lat/lng merge with tags).
Maybe a better idea is don't store the object in the database, but just the offset to it (plus machine ID).
Then use any commercial database that has an R-Tree.
You have 200GB to 1PT of data to store. Which needs at least 25 machines.
But if you can store just the index, but not the data, maybe you can store the index on just one machine - that will makes things easier. (Obviously you'll have multiple machines with the same index for load balancing.)
This might help also: http://www.ddj.com/184410998 (instead of an R-Tree, you might be able to a regular index).
SELECT DISTINCT obj
FROM media NATURAL JOIN mediatags
WHERE (location INTERSECTS somebox)
AND tag IN
(SELECT tag FROM usertags WHERE user = 137)
(EDIT: fighting with the formatting. Why is there no preview?)
1) http://code.google.com/p/uzaygezen/ (Hilbert)
2) http://www.h2database.com/html/features.html#multi_dimension... (Z-order)
Other space transformations could be employed like the Pyramid technique or the iDistance (http://obsearch.net). Nevertheless, if the "intrinsic dimensionality" of your objects is too high then the curse of dimensionality comes into play and sequential scan might be cheaper. In such a case the Va-file, IQ-Tree or even "sketches" might be the only way to go.
good tips. thanks. :)
I am curious about this line. MySQL is not very complicated to set up, so how could it be overkill?
Edit: seriously, "overkill" to me would be to use a sledge hammer to drive in a nail, that is, something that is too fat/heavy/complicated for the problem at hand.
If you never do updates, and you write your own index, then a much simpler storage method with far less overhead would be what you need.
Does each object have a fixed size? (Or can be a fixed size with padding?)
What kind of index do you need? Do you need advanced SQL commands, like aggregate data, and complex joins?
MySQL can handle billions http://dev.mysql.com/doc/refman/5.1/en/features.html of rows - but it may be overkill for you. Pretty much any database can.
But that's the wrong question. The right question is what do you need the database to do. Mainly what kind of index, and what kind of joins.
PS. If all you need is the aggregate data, why store the details? Just store the aggregate and update it on the fly. (Presumably store the details offline, so you can regenerate the aggregate if needed.)
Read this: http://news.ycombinator.com/item?id=594889 for updating the agregates on the fly. And this: http://news.ycombinator.com/item?id=587723 for one type of system for storing large numbers of objects.