Async & Concurrency

PormG is async-first internally: every terminal — list(), count(), create(), a DataFrame pipe — runs through the asynchronous core (fetch_async → await_result) and yields to the Julia scheduler while the database round-trip is in flight. The "synchronous" API parks the task, never the thread.

That has a powerful consequence: there is no separate async query API to learn. Julia functions are not colored — any query can be made concurrent by wrapping the ordinary call in a task:

t = Threads.@spawn M.Driver.objects.filter("nationality" => "Brazilian").list()
# ... other work overlaps the database round-trip ...
rows = fetch(t)          # Base.fetch on a Task — same Vector{PormGRow} as calling list() directly

This is the same contract Elixir's Ecto (Task.async) and Go's database/sql (goroutines) chose, and it is guaranteed by regression tests on PostgreSQL, SQLite and MySQL: a task-wrapped query returns exactly what the sync call returns.

How it works

list() → fetch() → fetch_async() → await_result(). On PostgreSQL the round-trip is real non-blocking I/O (LibPQ.async_execute on the LibPQ driver, a task per statement on Postgres.jl); on MySQL each statement runs on a task of its own; on SQLite every statement is funnelled through one serialized worker task. Every path yields while waiting, so concurrency is always safe — but only PostgreSQL and MySQL execute queries in parallel across connections. See the Architecture guide's "Async-first" is backend-specific note for the full picture.

@async vs Threads.@spawn

Both overlap database waits, because the wait is a scheduler yield either way:

• Threads.@spawn (recommended default) — the task may run on any thread, so CPU-bound work after the query (row processing, aggregation) also parallelizes when Julia has threads (julia -t auto). Under julia -t 1 it simply schedules on the sole thread and still overlaps I/O.
• @async — pins the task to the current thread. Fine for pure I/O overlap; no CPU parallelism.

Fan-out patterns

Run independent queries concurrently and collect results in input order:

years = 2010:2019

# One task per season — all round-trips in flight together
tasks = [Threads.@spawn M.Race.objects.filter("year" => y).count() for y in years]
counts = fetch.(tasks)   # [19, 19, 20, 19, 19, 19, 21, 20, 21, 21]

The same shape as a one-liner with asyncmap:

counts = asyncmap(y -> M.Race.objects.filter("year" => y).count(), collect(years))

For side-effecting fan-out where you only need completion, use a @sync block:

@sync for y in years
    Threads.@spawn begin
        n = M.Race.objects.filter("year" => y).count()
        @info "season loaded" year = y races = n
    end
end

Results arrive in input order with fetch.(tasks) / asyncmap — tasks may complete in any order.

Connection-pool interplay

Each in-flight query leases one pooled connection for its duration; a 10-way fan-out briefly holds up to 10 connections. The pool starts at pool_size and grows lazily to a pool_size × 10 ceiling; beyond that, callers park until a connection frees, and a genuine saturation raises a catchable PoolTimeoutError (tune with pool_timeout). Diagnose with pool_stats("db"). Details and YAML examples live in Advanced Configuration.

A task-wrapped terminal cannot leak a connection: the query acquires and releases its connection inside the task, even if you never fetch the task.

Under a web server's load

A web server runs each request as a task, and Julia does not preempt a task that computes. So what matters under load is where each driver waits for the database. The numbers below come from test/performance/async_concurrency_probe.jl (#1280): Julia 1.13.3, PostgreSQL 18 on the same machine, two runs each, on a machine shared with other work. They show the shape, not a benchmark. MySQL 8.4 was measured only for the :interactive caller (#1577).

• On PostgreSQL, a waiting query frees its thread. On 8 threads, 32 tasks each waiting on a 200 ms statement completed about 158 statements per second. If each wait held a thread, the most would be 40. Meanwhile a 10 ms heartbeat on the same threads woke with a p99 lateness of at most 2.3 ms, against 1.2 ms when idle. Its single worst wake was 4.5 ms late. A server can have many more queries in flight than it has threads.

• A statement runs in the caller's threadpool, except on SQLite. LibPQ runs it on the caller's thread, and Postgres.jl and MySQL run it in a task in the caller's pool (#1577). So an :interactive task, such as a heartbeat or a lease renewal, reaches the database while every :default thread computes: its primary-key lookups ran 59–60 times in 6 s with a median under 1 ms, on Postgres.jl and on MySQL alike. Before #1577, both drivers ran the statement on :default and managed one lookup in 6 s.

  • SQLite still needs a free :default thread. Its single worker task lives there, so with every :default thread computing, an :interactive task's lookup waits until the computation ends: one lookup in 6 s instead of 60. With one :default thread free, the lookups ran at their normal pace. On SQLite, keep at least one :default thread out of long CPU-bound work if an :interactive task must reach the database on time.
  • The driver decodes the rows on the caller's pool too. A statement from an :interactive task uses that thread for as long as its rows take to decode. On Julia 1.13 the main task (a script's top level, the REPL) runs on the interactive thread, unless Julia was started without one (-t 1, -t N,0), so its statements decode there. That was already so on LibPQ. To keep the interactive thread free during a large read, run the query from a :default task (Threads.@spawn).

• SQLite runs one statement at a time per process. Every statement goes through one worker task, so once a few tasks keep that worker busy, neither more tasks nor more threads raise the statement rate:

  • Raw statements: primary-key lookups stayed between about 9 and 31 thousand per second at -t 1, -t 4 and -t 8. Going from 1 task to 8 raised the rate, because a single task leaves the worker idle between its own statements. Beyond 8 tasks, nothing raised it.
  • ORM lookups: they gain from threads, up to that ceiling, because the query is built on the caller's thread before it reaches the worker. That was about 3–4.5 thousand per second at -t 1, and 4–15 thousand (most runs above 7) at -t 4 and -t 8 with 8 or more tasks.
  • A long statement delays every other: with one 200 ms statement always in flight, the median concurrent lookup took as long as that statement (200–260 ms) at every thread count, against under 2 ms without it.

  Serve concurrent web traffic from PostgreSQL or MySQL. On SQLite, keep long statements out of a process that serves requests.

Raw SQL: fetch_async, await_result, FetchTask

The only PormG-specific async API is the low-level escape hatch, and it takes raw SQL only — not query-builder objects:

settings = PormG.Configuration.get_settings("db")

task = fetch_async(settings, "SELECT count(*) FROM driver")   # returns a FetchTask
# ... do other work ...
result = await_result(task)                                   # rows; releases the pooled connection

await_result is idempotent — a second call returns the cached result without touching the pool.

Binding values — a manual-params array

The hatch does bind parameters: pass a plain values array (or tuple) and write the placeholder your backend uses. PormG performs no placeholder translation — you write $1, $2, … on PostgreSQL and ? on SQLite and MySQL — the same low-level convention as Go's database/sql, Python's DB-API, and Julia's DBInterface:

# PostgreSQL — numbered placeholders
task = fetch_async(settings, "SELECT count(*) FROM driver WHERE nationality = \$1", ["Brazilian"])
n    = await_result(task)

# SQLite and MySQL — positional placeholders (same values array, backend-native marker)
rows = fetch(settings, "SELECT * FROM driver WHERE nationality = ?", ["Brazilian"])

The driver binds each value as data, so a user value can never change the statement's structure. Write a NULL as missing (a bare nothing is normalized to it). The array bypasses the ORM's field formatters (datetime canonicalization, float precision, bool→int), which is expected for a raw hatch — pre-format any such values yourself.

The one check the hatch does apply: a string containing a NUL character ('\0') — anywhere in the array, nested or not, or in the SQL text itself — raises InvalidValueError before the statement is sent, on every backend, exactly as an ORM filter or write does. PostgreSQL text cannot store a NUL, and the drivers would otherwise truncate the value at it or fail with a driver error; binary values (Vector{UInt8}) are not affected.

Because the placeholder style is backend-native, a manual-params string is not portable across backends. For portable queries — and for row post-processing and connection management — prefer the ORM surface (list() / count() / filter(...) wrapped in a task, as above). Reach for the values-array hatch only for static SQL you own that the ORM can't express.

Cancelling a query with Ctrl+C

Interrupting a running query in the REPL is safe: PormG asks the database to abandon the statement, waits for the driver to let go of the connection, and only then decides what to do with the pool slot. A connection that comes back clean is reused; one that does not is renewed or dropped, and a fresh connection takes its place. Either way the next query gets a healthy connection — the pool cannot be left in a state where every later query fails.

An interrupt that lands while the query is still waiting for a connection (every connection is leased, or the pool is retrying a connect while the server is down) also leaves the pool whole. The waiting caller leaves the queue, and a connection handed to it at that moment goes to the next waiting caller or back to the pool (#1535).

On PostgreSQL under either driver and on SQLite, the interrupt reaches a query while its statement runs, so a statement interrupted outside a transaction block is cancelled rather than left to finish, and an interrupted autocommit write normally does not commit (a cancel that arrives after the server committed is too late). Inside a transaction block, the warning below applies instead. The limits come from how Julia delivers Ctrl+C:

• It interrupts only in the REPL, or in a script that calls Base.exit_on_sigint(false). A plain julia script.jl exits on Ctrl+C, so nothing is sent to the database.
• It goes to Julia's main thread, so it interrupts a query the REPL awaits, not one awaited in a task spawned on :default. The main thread is the interactive one (plain julia, -t N, -t auto), and a statement runs in its caller's threadpool, so a query awaited by another :interactive task, such as a heartbeat, runs there too and can take the interrupt instead (#1577).
• On Postgres.jl the interrupt usually lands inside the driver's read, which closes the connection. PormG still asks the driver to cancel the statement, and since Postgres.jl 2.6 the driver sends that cancel itself, with the backend's cancel key on a separate connection, so the statement still stops on the server (#1577, JuliaDatabases/Postgres.jl#45).
• On SQLite the statement runs on a worker thread, and the interrupt needs a second thread to arrive while it runs. Plain julia (one worker thread plus the REPL's), -t 2,1 and -t auto all have one: the interrupt arrives within about 0.1 s of Ctrl+C, the statement is interrupted, and the next query runs at once (measured 5 times per layout, #1534). With one thread (julia -t 1) the interrupt arrives only when the statement finishes, and the next query then runs normally.
• bulk_copy and fetch_copy send the COPY from your own task and are not covered.
• On MySQL (MySQL.jl 2.2.2 or newer), the interrupt reaches the query while its statement runs, and PormG sends KILL QUERY for it on a second connection: under -t 2,1 the query raised about 1.3 s after Ctrl+C, the statement left the server at once, and the next query ran (4 of 4 rounds, #1520). It needs an interactive thread, as plain julia, -t N, -t N,1 and -t auto give. With none (julia -t 1, -t N,0), one of MySQL.jl's or Julia's own background tasks can take the first Ctrl+C instead, and the query runs to its end. On macOS about 1 in 3 interrupts is dropped inside Julia's runtime, with or without MySQL (MySQL Guide).
• When the driver reports that a cancel did not reach the server or was refused, on PostgreSQL under either driver or on MySQL, PormG logs a warning (PostgreSQL refused the cancel request; the statement may still be running on the server, holding its locks, and the same for MySQL) with the driver's error: the statement you abandoned may run to its end. A MySQL KILL QUERY that finds the session already gone is not one of these, since its statement has ended too (#1719).
• All of this needs the driver floors, Postgres.jl 2.6 and MySQL.jl 2.2.2. Julia does not check an extension's [compat] when it loads it, so a manifest resolved before PormG raised a floor still loads the older driver. PormG then warns once at using, naming the driver, the floor and what goes wrong below it; Pkg.update() resolves the newer driver.

The same recovery covers an interrupt while a transaction is being opened (run_in_transaction / atomic), and an interrupt during a with_advisory_lock lock or unlock query, with one difference: those connections are renewed rather than reused, even when they come back looking clean. PormG cannot tell from the connection whether the BEGIN landed before the interrupt, and an open transaction or a held advisory lock is invisible to the check that a plain query's connection passes. Replacing the connection settles both — a PostgreSQL advisory lock or a MySQL named lock is bound to the session, so reconnecting is what releases it. The cost is one reconnect per interrupted transaction; the alternative is a pooled connection that silently belongs to a transaction nobody will ever finish.

Transactions and concurrency

The transaction context is a ScopedValue, so tasks spawned inside run_in_transaction inherit it — every query they run on that database targets the transaction's single pinned connection (see Async Context Propagation). That is exactly what you want for correctness: child tasks participate in the same transaction.

It is not a way to speed anything up:

• All statements on one connection execute one at a time — fan-out inside a transaction serializes and buys zero concurrency.
• The serialization currently relies on driver-internal locking (LibPQ's per-connection lock, MySQL.jl's per-connection lock, SQLite's global worker). It is safe today on every backend, but it is a property of the drivers, not a PormG contract — concurrent statements on one connection are a driver error class (e.g. PostgreSQL's "another command is already in progress") that PormG does not promise to absorb.

# Recompute each season's driver standings in its own transaction. The seasons
# are independent, so the transactions run concurrently — each on its own pooled
# connection with its own BEGIN/COMMIT.
@sync for year in 2010:2019
    Threads.@spawn PormG.run_in_transaction(settings) do
        new_standings = compute_driver_standings(year)   # your domain logic → DataFrame
        M.Driver_standings.objects.filter("raceid__year" => year).delete()
        bulk_insert(M.Driver_standings.objects, new_standings)
    end
end

Also remember the retry rule from Transactions: a connection error inside a transaction propagates — retry the whole transaction, never a single statement.