puppetlabs/puppetdb — pull request #612 by grimradical (2013-08-16 16:27:49+00:00)
Title: Streaming resource queries (2)

(trying this again, as Github isn't recognizing the force push I did on the
previous branch)

Previously, we'd load all rows from a resource query into RAM, then do a
bunch of sorting and aggregation to transform them into a format that
clients expect. That has obvious problems involving RAM usage for large
result sets. Furthermore, this does all the work for querying
up-front...if a client disconnects, the query continues to tax the
database until it completes. And lastly, we'd have to wait until all the
query results have been paged into RAM before we could send anything to
the client.

This patchset adds streaming capabilities to resource queries. This is
accomplished through a few key additions:

1) HTTP functions for streaming a response. Ring can handle an
InputStream as a response object, and it will pass that to Jetty who
just reads from the stream and sends output to the client using a
Chunked Transfer encoding. I've created a macro that will wrap the body,
binding a Writer object to local scope. A PipedInputStream is then
hooked up to that Writer, and is returned as the Ring response. This
way, the body can stream content to the Writer object and Ring takes
care of the rest. No muss, no fuss!

2) Functions for streaming a JSON response. While Jackson does streaming
output, my testing seemed to indicate that it still needs to buffer up
into RAM the next object to be streamed. That doesn't really help us for
very large JSON arrays. I've written a simple function that streams an
array one object at a time, thus reducing resident memory usage.

3) Functions for streaming a JDBC query. We can't use the stock
clojure.java.jdbc querying code, as that doesn't open up a proper
database cursor. So I've written a function that will do the proper
initialization of a database cursor, and invoke a callback on the
resultset. By using a callback-based approach instead of a lazy-sequence
of rows, we can more precisely control the lifecycle of the cursor. To
wit, if the callback fails for whatever reason, we can simply cancel the
query.

4) A table for caching all the parameters for a resource, in a single row. This
allows us to avoid costly ORDER BY clauses just to get rows from the db in
a particular order so we can reconstitute the set of parameters for a resource.
This makes resource queries much faster, and as a bonus it provides for
some additional referential integrity in the db between resources and their
parameters.

Taken together, we can now issue a query for, say, all resources we know
about. As soon as the database can produce the first complete resource,
the client can receive it. And if the client disconnects prematurely,
the query is cancelled, sparing the database from doing too much work.
This is all done asynchronously, with low memory usage, and with better
overall performance (between 2x for small queries and 3-4x for large result
sets).