PostgreSQL has a module named hstore that implement the use of key/value pairs (it's something like the OpenStreetMap model, but in a single table).
What are the Pros and Cons of hstore regarding GIS compatibilities, data adding and retrieving?
We actually demonstrate this in our book (which supposedly will be going to print in the next week or so). In chapter 3. You can download the code from here: http://www.postgis.us/chapter_03
(and of course if you buy our book you get our thorough explanations of the pros and cons)
It uses the osm2pgsql hstore switch to import osm tags as hstore column.
What we do to compensate for the fact that Quantum and other things don't support hstore directly is to create a view around the key tags we need. For applications where you have complete control using a query that aliases the key->value as a column works fine too even if you database driver doesn't understand hstore type.
I have no experience with it myself, but if you are interested in storing spatial data in such a database, you might want to search for spatial nosql. On post I found was eg: http://stackoverflow.com/questions/2041622/nosql-and-spatial-data
It's a high performance and flexible key/value that can be indexed and queried.
I have a complex schemaless web GIS that uses an HStore column for storing the features properties.
Most WMS/WFS tools like Mapserver, Geoserver and similars can't understand and work with HStore columns, so you end up creating hard coded SQL Views (In Geoserver at least). You can still use it for working WMS/WFS but since you have to hardcode the keys you lose the schemaless capabilities.