Streaming json from Postgresql through Phoenix

I wanted to explore the fastest way to have json generated in postgresql delivered through phoenix to the browser. With this, I wanted to completely avoid any decoding/encoding happening in ecto/phoenix since I already had perfectly valid json coming out of pg.

It took some trial and error, and some source code reading, and some help from the list but I've got it working with materialized views and returning them in less than 1ms on my laptop.

Here's the short how-to. I'm using pg 9.4 and this should work with both json and jsonb results. I've also used the generic app/App nomenclature, you'll need to change that to your app's name.

Let's take a really, really simple query that returns some json:

SELECT '{"bar": "baz", "balance": 7.77, "active": false}'::json;

Next in your Phoenix app your going to need a repo that doesn't use the standard json extensions for the postgrex adapter. I created a second repo, NoDecodeRepo, that removes the decoding from the extension (see the last two decode/1, that's the only thing I've changed from the normal Extensions.Json).

Note: Eric Meadows-Jönsson says: A planned feature in postgrex is to allow you to bring in extensions on each call which means you can change postgrex' encoding/decoding behaviour depending on the the type of query you are actually doing. [This would eliminate the need for a second repo.]

defmodule Extensions.JsonNoDecode do
  alias Postgrex.TypeInfo

  @behaviour Postgrex.Extension

  def init(_parameters, opts),
    do: Keyword.fetch!(opts, :library)

  def matching(_library),
    do: [type: "json", type: "jsonb"]

  def format(_library),
    do: :binary

  def encode(%TypeInfo{type: "json"}, map, _state, library),
    do: library.encode!(map)
  def encode(%TypeInfo{type: "jsonb"}, map, _state, library),
    do: <<1, library.encode!(map)::binary>>

  def decode(%TypeInfo{type: "json"}, json, _state, library),
    do: json
  def decode(%TypeInfo{type: "jsonb"}, <<1, json::binary>>, _state, library),
    do: json
end

Next add a second repo:

defmodule App.NoDecodeRepo do
  use Ecto.Repo, otp_app: :search_api
end

Make sure and add the new repo to the supervisor and you've added it to the confi config. In lib app.ex:

    children = [
      # Start the endpoint when the application starts
      supervisor(App.Endpoint, []),
      # Start the Ecto repository
      worker(App.Repo, []),
      worker(App.NoDecodeRepo,[]),
    ]

config/dev.exs

 config :app, App.NoDecodeRepo,
  adapter: Ecto.Adapters.Postgres,
  username: "username",
  password: "password",
  database: "database",
  hostname: "host",
  extensions: [{Extensions.JsonNoDecode, library: Poison}]

Lastly I modified the controller method to send the results directly via plug. The rows are a list of tuples, we take the head of the list and the first element and send that back with send_resp/3.

def show(conn, params) do
   {:ok, %{rows: [[json | _]]} } = Ecto.Adapters.SQL.query(App.NoDecodeRepo, "SELECT '{\"bar\": \"baz\", \"balance\": 7.77, \"active\": false}'::json",[])
   put_resp_content_type(conn, "application/json") |> send_resp( 200, json)
 end

Cleaning this up, I'd probably create a send_query_resp/4 method that took the conn, repo, query and query parameters. But this gives you the idea.

plamb/streaming_pg_elixir.md

Streaming json from Postgresql through Phoenix