I'm not a big proponent of schemaless designs, but they have their place. One particular place where I think they are useful is for archiving of data
where even though the underlying table structure of the data you need to archive is changing, you want the archived record to have the same fields as it did back then.
This is a case where I think Hstore and the way PostgreSQL has it implemented works pretty nicely.
Side note: one of the new features of PostgreSQL 9.4 is improved GIN indexes (faster and smaller) which
is very often used with hstore data (and the new jsonb type). We're really looking forward to the GIN improvements more so than the jsonb feature. We're hoping to test out this improved index functionality with OpenStreetMap data soon and compare with our existing PostgreSQL 9.3. OpenStreetMap pbf and osm extract loaders (osm2pgsql, imposm) provide option for loading tagged data into PostgreSQL hstore fields, in addition to PostGIS geometry and other attribute fields. So 9.4 enhancements should be a nice gift for OSM data users. More on that later.
Lets suppose we had a contacts table that we wanted to record whenever we delete or update:
We create a catch-all archive table that can archive most anything.
--archivingtable--CREATE EXTENSION hstore;
CREATETABLEarchive(table_name text, row_id text
, archive_type char(1), archive_dt timestamptz DEFAULTCURRENT_TIMESTAMP, data hstore);
For archiving especially if you are going to do crazy things like dump PostGIS geometries/geographies in your hstore (a storelocken), you should use GIST instead of GIN since GIN is lossless, will fail for large sizes. For this example we'll at most just have tiny points in this exercise.
CREATEINDEX idx_archive_data ON archive USING gin (data);
For Archiving we could use triggers or just build into our delete/update logic using a writeable CTE like
Note the output tells us that the first time we updated Forrest, we did not have a geography column and second time we updated Forrest and when we deleted Joey, we had a geography column.
Of course you can do more interesting queries like each() etc to get values of every single field as needed.