This isn’t exactly what you’re asking but if you’re in the millions of rows category I would strongly suggest using COPY instead of bulk inserts. COPY is both more performant at the postgres level, and will also bypass the query cache issue you’re having.

Here is a helper module we have for this operation:

defmodule MyApp.PostgresBulkLoader do
  require Logger

  def load(repo, table, stream, columns) do
    statement = """
    COPY #{table} (#{Enum.join(columns, ", ")})
    FROM STDIN
    WITH (FORMAT csv, HEADER false)
    """

    {:ok, :ok} =
      repo.transaction(
        fn ->
          Logger.debug(statement)

          stream
          |> Stream.chunk_every(2000, 2000, [])
          |> Stream.into(Ecto.Adapters.SQL.stream(repo, statement))
          |> Stream.run()
        end,
        timeout: 3_600_000
      )

    :ok
  end
end

It’s mildly tedious because you have to basically build CSV of the data you’re ingesting but it’s well worth it if you’re in the 100m rows world.

I am referring to the built-in Ecto query cache. It’s pretty much undocumented but it exists, and it makes my memory usage grow : ecto/lib/ecto/query/planner.ex at master · elixir-ecto/ecto · GitHub

Observer returned the table stats mentioned in my first post.

Try to rewrite the query to json_to_recordset instead of values. In this case, you’ll pass only a single parameter.

Thank you @josevalim @fuelen @benwilson512. The Ecto fix is highly appreciated. I will try json_to_recordset before the new Ecto version is out. Looks like I can get away with much larger batches when I can squeeze the entire data set into one parameter.

I will also try the COPY statement + figure out a new way to retrieve the data I previously obtained via RETURNING.

this community – it’s great that everyone is so involved.

We fixed this in Ecto to not use query cache for values .

For the record, here’s a link to the fix PR: Disable cache for values lists by greg-rychlewski · Pull Request #4471 · elixir-ecto/ecto · GitHub