Particles

What is a Particle?

A Particle is a function you write — in ordinary Python or Rust — that ParticleDB registers as a first-class SQL function. Once it is loaded, you call it inline in a query exactly like a built-in:

SELECT name, haversine_km(lat, lon, 37.77, -122.42) AS km_from_sf
FROM stores
ORDER BY km_from_sf;

Think of Particles as custom keywords for SQL. SQL gives you SUM, UPPER, NOW() — but the moment you need something it doesn’t have, you normally give up on the database: you pull the rows into Python/pandas, a Rust service, or your application, compute there, and push results back. Particles delete that round trip. You write the function once, and it runs inside the query — row by row, or group by group.

The functions SQL never gave you

These are things people constantly re-implement in another language because standard SQL has no keyword for them. As Particles they become one-liners you can call from any query, view, or GROUP BY:

You want…	SQL has no…	As a Particle
The median of a group	plain median() (only the verbose percentile_cont)	pmedian(amount)
Great-circle distance between two lat/lon points	any geo-distance function	haversine_km(lat1, lon1, lat2, lon2)
A per-row z-score within a window	zscore(...) OVER (...)	zscore(x) OVER (PARTITION BY g)
A URL slug from a title	slugify()	slugify(title)
A SHA-256 / HMAC hex digest	crypto hashing built in	sha256_hex(payload)

Every one of those is a real, tested example used throughout this guide — nothing here is hypothetical.

Tip

Rule of thumb: if you’d normally write it in Python, write it as a Particle instead. The math, the library, and the edge cases all stay in the language you already know — and the database calls it for you.

Synchronous and asynchronous Particles

Particles come in two flavours, depending on whether the work fits inside a query or has to run beyond it.

Synchronous — runs inside the query

These execute as part of query evaluation and return before the query does — one value per row, or per group:

Kind	SQL concept	Status
@scalar	a per-row function	Available
@aggregate	a group fold (GROUP BY)	Available
@window	a window function (OVER (...))	Available
@cast	a user-defined cast between types	Available
@type	a user-defined type	Reserved — coming

@scalar, @aggregate, @window, and @cast run today — all covered in Python. @type is declared surface with no runtime yet — the decorator exists so your modules stay forward-compatible, but a @type Particle is not executed.

Asynchronous — the AI-native tier

Some work is not a per-row function: calling an LLM, running an agent, or driving a multi-step process takes seconds to minutes and must survive restarts. Those are asynchronous Particles:

Kind	What it is	Status
@agent	one bounded AI/external step — an LLM call, an API call, a tool use	Preview
@workflow	a durable, multi-step agentic process (a DAG of steps)	Preview

This is the AI-native part of ParticleDB — see below.

Two languages, one concept

The synchronous Particles above can be authored in either Python or Rust — choose per Particle (the asynchronous @agent / @workflow tier is Python):

• Python — the fast path to write. A normal function with type hints, deployed with one command. Best for most functions, quick iteration, and anything that leans on the Python ecosystem.
• Rust — the fast path to run. Compiled, batch-at-a-time, zero-copy over Apache Arrow. Best for performance-critical Particles on a tight loop. (Preview tier.)

The concept is identical in both: your function’s parameter and return types become the SQL signature. Here is the same idea in each language.

Python

geo.py

from particledb import scalar

@scalar
def haversine_km(lat1: float, lon1: float, lat2: float, lon2: float) -> float:
    """Great-circle distance between two lat/lon points, in kilometres."""
    from math import radians, sin, cos, asin, sqrt
    r = 6371.0088
    dlat = radians(lat2 - lat1)
    dlon = radians(lon2 - lon1)
    a = sin(dlat / 2) ** 2 + cos(radians(lat1)) * cos(radians(lat2)) * sin(dlon / 2) ** 2
    return 2 * r * asin(sqrt(a))

SELECT haversine_km(40.71, -74.01, 37.77, -122.42);   -- 4129.06   (NYC → SF, km)

float -> float becomes double precision -> double precision. Load the module and it’s a SQL function. → Full Python guide

Rust

// domain_of(text) -> text — the domain part of an email.
fn domain_of(email: &str) -> &str {
    email.rsplit('@').next().unwrap_or("")
}

SELECT domain_of(email) FROM users;   -- 'kyle@jepsen.io' -> 'jepsen.io'

The same contract — the Rust signature is the SQL signature — but compiled and exchanged a whole Arrow batch at a time for hot paths. → Full Rust guide

Note

Both examples above (haversine_km, domain_of) are drawn verbatim from the in-repo example modules and their end-to-end tests, so the SQL results shown are exactly what the engine returns.

AI-native: agents & workflows

This is what makes ParticleDB AI-native: the database doesn’t just store the data your AI app reads — it can run the AI step itself, durably, right where the data lives. No separate queue, worker fleet, or orchestration service to stand up.

Author an agent as an ordinary function:

triage.py

from particledb import agent

@agent
def triage_ticket(subject: str, body: str) -> str:
    """One bounded AI step: classify a support ticket with your model."""
    # call your LLM / tool here — the engine runs this durably, off the query path
    ...
    return category

Unlike a scalar, an agent is submitted, not evaluated inline — it returns a run_id immediately and runs in the background: retry-safe, idempotent, and deadline-bounded, backed by a durable-execution engine.

-- Kick off ONE run — returns a run_id right away, does NOT block the query:
SUBMIT PARTICLE triage_ticket(subject => 'refund?', body => '…');
-- run_id: 9b1f…

-- …then track it, by run_id, whenever you like:
SELECT particle_run_status('9b1f…');   -- RUNNING | COMPLETED | FAILED | …
SELECT particle_run_result('9b1f…');   -- the answer, once COMPLETED

A workflow is the same idea for a durable multi-step process — a DAG of steps you write in Python. Both kinds — plus the two things that matter, how you invoke them and how you track the async run — are covered in Async Agents & Workflows.

Caution

The async tier is a preview: a testable skeleton exists behind a feature flag with a mock backend, but no shipped binary runs @agent / @workflow yet. The model is documented so your code needs no rewrite when it lands.

How the pieces fit

1. Write your function in Python or Rust — the type signature is the SQL signature.
2. Load it into a running server — one flag at startup or one deploy command. → Loading
3. Call it from SQL — it’s now a first-class function, everywhere a built-in works.

Where to go next

Python The full authoring guide — @scalar, @aggregate, @window, the type mapping, and tested examples.

Rust The compiled, zero-copy tier for performance-critical Particles on hot paths.

Best Practices How to think about writing a Particle — naming, determinism, volatility, and safety.

Collisions & Error Handling What happens when a name clashes with a built-in, and how NULLs and errors behave.

Loading Get your Particles into a running server — at startup or deployed durably.

Async Agents & Workflows The AI-native tier — invoke durable @agent / @workflow runs and track them by run_id.