puppetlabs/puppetdb — pull request #277 by nicklewis (2012-09-11 18:58:13+00:00)
Title: Review only: Store historical catalogs and dedup additional data

This is only a prototype, and will presumably be thrown away, so please excuse the poor commit messages and some missing details. :)

However, it seemed prudent to get some eyes on it to at least figure out which ideas are valuable.

The main changes here are storing historical catalog, and lots of additional deduplication for resources.

If a new catalog comes in for a node, we store it exactly as we did before, except without deleting the existing catalog. Simple as that. Then resource queries are restricted to look at only the most recent catalog for each node.

The meatier piece of this is the deduplication-related schema changes.

* `catalog_resources` has been torn to shreds.
* There is now a `resource_metadata` table, which contains `type`, `title`, `exported`, `sourcefile`, and `sourceline`.
* The new `resource_tags` table stores `tags`. The `resource_params` table is unchanged.
* The `catalog_resources` table serves only to join `catalogs`, `resource_metadata`, `resource_tags`, and `resource_params`, representing a unique resource in a single catalog.

This lets us dedup both metadata and tags, which are almost entirely non-unique -- 4000 and 1700 rows respectively for my database of 11 million resources. That represents a significant improvement to database size, which is important when we're storing all the data.

There are also several improvements to storage performance, which are independent of the historical data or new deduplication. Primarily, we now use a much faster catalog hash, which reduces the time for computing the hash from ~600ms to ~200ms. We also use a faster (and less intelligent) memoization technique for resource hashes, which reduces that time from several hundred ms to almost negligible. This represents somewhere around a 1/4 - 1/3 decrease in the time necessary to store a catalog, which is a 30-50% improvement to throughput.