GitHub PullRequestReviewCommentEvent at 2014-03-01 14:29:36
Repository: reefpoints
Actor: michaeldupuisjr
Payload: {"comment":{"url":"https://api.github.com/repos/dockyard/reefpoints/pulls/comments/10190887","id":10190887,"diff_hunk":"@@ -0,0 +1,404 @@\n+---\n+layout: post\n+title: \"Avoid Rails When Generating JSON responses with PostgreSQL\"\n+comments: true\n+author: Dan McClain\n+twitter: \"_danmcclain\"\n+googleplus: 102648938707671188640\n+github: danmcclain\n+social: true\n+summary: \"Let's use PostgreSQL instead of Ruby to generate JSON responses\"\n+published: true\n+tags: ruby, ruby on rails, postgresql, postgres_ext\n+---\n+\n+What if I told you that you could generate the following JSON response\n+in PostgreSQL? \n+\n+```json\n+{\n+  \"tags\":[\n+    {\"id\":1,\"name\":\"Tag #0\",\"note_id\":1},\n+    {\"id\":1001,\"name\":\"Tag #1000\",\"note_id\":1},\n+    {\"id\":2001,\"name\":\"Tag #2000\",\"note_id\":1},\n+    ...\n+  ],\n+  \"notes\":[\n+    {\n+      \"id\":1,\n+      \"title\":\"Note #0\",\n+      \"content\":\"Lorem ipsum...\",\n+      \"tag_ids\":[9001,8001,7001,6001,5001,4001,3001,2001,1001,1]\n+    },\n+    {\n+      \"id\":2,\n+      \"title\":\"Note #1\",\n+      \"content\":\"Lorem ipsum...\",\n+      \"tag_ids\":[9002,8002,7002,6002,5002,4002,3002,2002,1002,2]\n+    }\n+  ]\n+}\n+```\n+\n+What if I told you that it is over 10X faster for small\n+data sets, and 160X faster for larger data sets?\n+\n+Typically when you have an API serving up JSON responses, your web\n+framework serializes your data after retrieving it with its ORM. We'll\n+talk about Rails specifically in this article, but this will generally\n+apply to most frameworks. So the typical Rails request will roughly\n+follow this flow (I am purposely brushing over some parts of the request\n+response cycle):\n+\n+  1. Rails receives the JSON request from the browser/client\n+  2. Rails will apply some business logic and craft a query via\n+     ActiveRecord\n+  3. ActiveRecord serializes its query and sends the query to PostgreSQL\n+  4. PostgreSQL will compile the result set and serializes the records\n+     in its protocol format\n+  5. ActiveRecord deserializes the records into a set of rows object\n+  6. ActiveRecord will convert the set of rows into a set of model\n+     object instances\n+  7. Rails will convert the set of models objects into a JSON string\n+  8. Rails will send the JSON string down to the browser\n+\n+Most of the time in this response cycle is spent in steps 6 and 7. Rails\n+has to deserializes one format, store that deserialized content in\n+memory just to serialized it in a different format. Since [PostgreSQL\n+supports JSON responses](), we can use its [JSON functions]() to\n+serialized our result set. That JSON response will still be serialized\n+in PostgreSQL's protocol format, but ActiveRecord can deserialize it as\n+a single string object, instead of a set of objects which it then\n+converts and reserializes. We end up having this response cycle instead: \n+\n+  1. Rails receives the JSON request from the browser/client\n+  2. Rails will apply some business logic and craft a query via\n+     ActiveRecord\n+  3. ActiveRecord serializes its query and sends the query to PostgreSQL\n+  4. PostgreSQL will compile the result set, serializes it as JSON then \n+     serializes the JSON in its protocol format\n+  5. ActiveRecord deserializes the protocal format into a single JSON\n+     string\n+  6. Rails will send the JSON string down to the browser\n+\n+We are only removing 2 steps, but it is the bulk of the time spent\n+generating the response. We are also limiting the number of ruby objects\n+created, so this reduces memory usage, and time spent garbage collecting\n+short lived Ruby objects used only for JSONification.\n+\n+# Massive PostgreSQL Queries\n+\n+Let's say we have an Ember application that we are generating the JSON\n+request for. The Ember app wants the list of notes, along with the tags\n+associated with the notes, and we will side load the tags. Side loading\n+allows you to specify the ids of the tags on the note, and then include\n+a list of tags, which will be used to instantiate the tags on the note.\n+The benefit of side loading is that it allows you to save bandwidth by\n+potentially duplicating tag ids, instead of embedding the tags under the\n+notes, where you would have to duplicate the tag objects. We only want\n+notes with `id < 40`, which is arbitrary in this example, but, as we\n+will see, has implications on the query we need to execute\n+\n+Here is the whole query we need to generate the JSON required, which is\n+also the example JSON at the beginning of this article:\n+\n+```sql\n+-- Note Ids\n+WITH notes_ids AS (\n+  SELECT id\n+  FROM \"notes\"\n+  WHERE \"notes\".\"id\" < 40\n+),\n+-- Tag Ids grouped by note id\n+tag_ids_by_notes AS (\n+  SELECT \"tags\".\"note_id\", array_agg(\"tags\".\"id\") AS tag_ids\n+  FROM \"tags\"\n+  GROUP BY \"tags\".\"note_id\"\n+  HAVING \"tags\".\"note_id\" IN (\n+    SELECT \"notes_ids\".\"id\"\n+    FROM \"notes_ids\"\n+  )\n+),\n+-- Tag records\n+tags_attributes_filter AS (\n+  SELECT \"tags\".\"id\", \"tags\".\"name\", \"tags\".\"note_id\"\n+  FROM \"tags\"\n+  WHERE \"tags\".\"note_id\" IN (\n+    SELECT \"notes_ids\".\"id\"\n+    FROM \"notes_ids\"\n+  )\n+),\n+-- Tag records as a JSON array\n+tags_as_json_array AS (\n+  SELECT array_to_json(array_agg(row_to_json(tags_attributes_filter)))\n+AS tags, 1 AS match\n+  FROM \"tags_attributes_filter\"\n+),\n+-- Note records\n+notes_attributes_filter AS (\n+  SELECT \"notes\".\"id\", \"notes\".\"content\", \"notes\".\"name\",\n+coalesce(\"tag_ids_by_notes\".\"tag_ids\", '{}'::int[]) AS tag_ids\n+  FROM \"notes\"\n+  LEFT OUTER JOIN \"tag_ids_by_notes\"\n+  ON \"notes\".\"id\" = \"tag_ids_by_notes\".\"note_id\"\n+  WHERE \"notes\".\"id\" < 40\n+),\n+-- Note records as a JSON array\n+notes_as_json_array AS (\n+  SELECT array_to_json(array_agg(row_to_json(notes_attributes_filter)))\n+AS notes, 1 AS match\n+  FROM \"notes_attributes_filter\"\n+),\n+-- Notes and tags together as one JSON object\n+jsons AS (\n+  SELECT \"tags_as_json_array\".\"tags\", \"notes_as_json_array\".\"notes\"\n+  FROM \"tags_as_json_array\"\n+  INNER JOIN \"notes_as_json_array\"\n+  ON \"tags_as_json_array\".\"match\" = \"notes_as_json_array\".\"match\"\n+)\n+SELECT row_to_json(jsons) FROM \"jsons\";\n+```\n+\n+Let's break it down. You'll notice is that I am making use of [Common Table\n+Expressions (CTEs)]().\n+\n+## Gathering our Note Ids\n+\n+The first important step is getting the note ids of our final result\n+set, which we do with:\n+\n+```sql\n+WITH notes_ids AS (\n+  SELECT id\n+  FROM \"notes\"\n+  WHERE \"notes\".\"id\" < 40\n+),\n+```\n+We are creating a CTE that represents the ids for our notes, we'll be\n+using this extensively to generate our tag related records.\n+\n+## Getting Tag Ids Grouped by Note Ids\n+\n+From our `note_ids`, we can assemble a list of tag ids grouped by notes.\n+This will be used to create the `tag_ids` attribute on the notes later\n+on.\n+\n+```sql\n+tag_ids_by_notes AS (\n+  SELECT \"tags\".\"note_id\", array_agg(\"tags\".\"id\") AS tag_ids\n+  FROM \"tags\"\n+  GROUP BY \"tags\".\"note_id\"\n+  HAVING \"tags\".\"note_id\" IN (\n+    SELECT \"notes_ids\".\"id\"\n+    FROM \"notes_ids\"\n+  )\n+),\n+```\n+\n+Our projection is the `note_id`, plus an [`array_agg`]() of the id of the\n+tags in our grouping. This projection will return the following\n+\n+```text\n+note_id | tag_ids\n+=================\n+      1 | [1,2]\n+      2 | [1,3]\n+```\n+\n+In this example, the tags `belong_to` note, so we are retrieving this\n+data from the `tags` table, if this was a many-to-many relation, this\n+query would execute against the join table (ie `notes_tags`).\n+\n+We group our tags by the `note_id`, and we use the `HAVING` clause to\n+only group tags which have an `note_id` contained in the `note_ids` CTE\n+that we created at the beginning.\n+\n+## Generating Our Note Records\n+\n+Most of your time, we don't want to expose all of our record data to\n+Ember, since whatever we send to the client will be accessible by the\n+user, whether we intend it to be or not. We filter down the attributes\n+sent to the Ember by limiting the columns in our projection\n+\n+```sql\n+notes_attributes_filter AS (\n+  SELECT \"notes\".\"id\", \"notes\".\"content\", \"notes\".\"name\",\n+coalesce(\"tag_ids_by_notes\".\"tag_ids\", '{}'::int[]) AS tag_ids\n+  FROM \"notes\"\n+  LEFT OUTER JOIN \"tag_ids_by_notes\"\n+  ON \"notes\".\"id\" = \"tag_ids_by_notes\".\"note_id\"\n+  WHERE \"notes\".\"id\" < 40\n+),\n+```\n+\n+Also note that in the projection, we are using [`coalesce`]() to ensure\n+that we return an empty array if a specific note has no `tag_ids`. \n+We are using a [`LEFT OUTER JOIN`]() to combine our previously generated\n+tag id groupings with our notes. We use an `OUTER JOIN` instead of an\n+[`INNER JOIN`]() so that all our notes are returned, even if no tags are\n+associated with it. An `INNER JOIN` would only return notes that have\n+tags associated with it. We also use the same `WHERE` predicate in this\n+query as we did in the `note_ids` CTE, to ensure our query only returns\n+the desired records.\n+\n+## Turning Our Note Records into a Single JSON Array\n+\n+So now that we have our notes records filtered down, we need to create a\n+JSON array of these records to use in our final query. At this point, we\n+will use two of PostgreSQL's [JSON functions]() and the `array_agg`\n+function that we used earlier. `row_to_json` takes a PostgreSQL row and\n+converts it to a JSON object, where the columns of the row converted\n+into JSON properties. \n+\n+```text\n+foo | bar\n+=========\n+  1 |   2\n+```\n+\n+Will be converted to\n+\n+```text\n+     json\n+================\n+{ foo: 1, bar 2 }\n+```\n+\n+So at this point, our result set is a series of rows with a single\n+column of JSON representing the original PostgreSQL row from our\n+`notes_attribute_filter` CTE. We then use `array_agg` to turn the series\n+of rows of single JSON objects into a single row with a single\n+PostgreSQL Array of JSON objects column.\n+```text\n+     json\n+================\n+{ foo: 1, bar 2 }\n+{ foo: 1, bar 2 }\n+{ foo: 1, bar 2 }\n+```\n+\n+will be converted to\n+\n+```text\n+                    Array\n+=======================================================\n+{{ foo: 1, bar 2 },{ foo: 1, bar 2 },{ foo: 1, bar 2 }}\n+```\n+\n+Lastly, we use `array_to_json` to convert the PostgreSQL array of JSON to a JSON array.\n+\n+After putting combining these pieces, we get the following query:\n+\n+```sql\n+notes_as_json_array AS (\n+  SELECT array_to_json(array_agg(row_to_json(notes_attributes_filter)))\n+AS notes, 1 AS match\n+  FROM \"notes_attributes_filter\"\n+),\n+```\n+\n+which yields\n+\n+```text\n+    notes    | match\n+====================\n+[{},{},{},{}]|     1\n+```\n+\n+We are using the `notes_attributes_filter` as our source for all the\n+JSON functions, and adding a column `match` with a value of `1`, which\n+we will need later.\n+\n+## Aggregating Our Tag Records\n+\n+We apply the attribute filtering and the aggregation techniques to our\n+`tags` table to generate our JSON array of tags. Note that when we\n+filter the tags attributes, we only include tags that have a `note_id`\n+of a note we are returning.\n+\n+```sql\n+tags_attributes_filter AS (\n+  SELECT \"tags\".\"id\", \"tags\".\"name\", \"tags\".\"note_id\"\n+  FROM \"tags\"\n+  WHERE \"tags\".\"note_id\" IN (\n+    SELECT \"notes_ids\".\"id\"\n+    FROM \"notes_ids\"\n+  )\n+),\n+\n+tags_as_json_array AS (\n+  SELECT array_to_json(array_agg(row_to_json(tags_attributes_filter)))\n+AS tags, 1 AS match\n+  FROM \"tags_attributes_filter\"\n+),\n+```\n+\n+which yields\n+\n+```text\n+    tags     | match\n+====================\n+[{},{},{},{}]|     1\n+```\n+\n+## Combining Our Notes and Tags\n+\n+So at this point, we have 2 CTEs that represent our notes and tags. We\n+need to combine these tables into a single row, so that we can convert\n+that row to a JSON object with a `notes` and `tags` property. This is\n+the reason we added a `match` column onto both CTEs; we join those two\n+table into our final table, which we then call `row_to_json` on to get","path":"source/posts/2014-02-15-avoid-rails-when-generating-json-repsonses-with-postgresql.md","position":350,"original_position":350,"commit_id":"dffa668ae70ad2f578b0c2de69a0c65d266246d6","original_commit_id":"dffa668ae70ad2f578b0c2de69a0c65d266246d6","user":{"login":"michaeldupuisjr","id":1060866,"avatar_url":"https://avatars.githubusercontent.com/u/1060866","gravatar_id":"a7607e593785b6d21bf229f7cdc62760","url":"https://api.github.com/users/michaeldupuisjr","html_url":"https://github.com/michaeldupuisjr","followers_url":"https://api.github.com/users/michaeldupuisjr/followers","following_url":"https://api.github.com/users/michaeldupuisjr/following{/other_user}","gists_url":"https://api.github.com/users/michaeldupuisjr/gists{/gist_id}","starred_url":"https://api.github.com/users/michaeldupuisjr/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/michaeldupuisjr/subscriptions","organizations_url":"https://api.github.com/users/michaeldupuisjr/orgs","repos_url":"https://api.github.com/users/michaeldupuisjr/repos","events_url":"https://api.github.com/users/michaeldupuisjr/events{/privacy}","received_events_url":"https://api.github.com/users/michaeldupuisjr/received_events","type":"User","site_admin":false},"body":"two **tables** into","created_at":"2014-03-01T14:29:36Z","updated_at":"2014-03-01T14:29:36Z","html_url":"https://github.com/dockyard/reefpoints/pull/117#discussion_r10190887","pull_request_url":"https://api.github.com/repos/dockyard/reefpoints/pulls/117","_links":{"self":{"href":"https://api.github.com/repos/dockyard/reefpoints/pulls/comments/10190887"},"html":{"href":"https://github.com/dockyard/reefpoints/pull/117#discussion_r10190887"},"pull_request":{"href":"https://api.github.com/repos/dockyard/reefpoints/pulls/117"}}}}