If only there were a way to store data where each record has the same structure...

The JSON support in postgres allows a hybrid approach. Put the common data in a tabular format, with a JSON column that stores all the extra, variable data.

Internal pseudo-schematized data storage and JSON support aren't mutually-exclusive. Nice to have a checkbox feature like this but the implementation isn't particularly exciting for large datasets.

PostgreSQL jsonb has the same problem in the actual document storage, with repeated keys. I believe it does better in the index, when you use the correct operator, but the table storage has the same type of overhead.

This email talks about how the indexes are 50% the size of the data.

http://www.postgresql.org/message-id/5323D45F.1080503@agliod...

Document stores trade off some performance and robustness to increase ease of development. When that becomes a problem you can switch to more structured tables. One possibility is to structure frequently accessed fields and stuff the remainder into a json column.

This just forces complexity onto the caller - it's not unreasonable to expect the DB to "pseudo-schematize" as most keys will be repeated.

With a power law, only a few keys occur frequently. Changing the data representation implicitly would cause unpredictable performance as columns get rewritten. If you want to do more explicit storage optimisations, maybe one of the mongo-like overlays could accommodate that.

It's more than that - virtually large all datasets I've worked with (> 20B documents) had a keyspace of under 128 keys/columns per nested level of a JSON document. Meaning an optimizing DB could express most keys as single byte (or less with packing). I don't thing column reordering would be necessary unless you exhausted the first 128 keys and you wanted to optimize the base-128 keyspace assignment. Such an operation could be done during compaction or any other operation that rewrites a table.