Best Practices

A Particle becomes part of your query surface — other people will call it, views will embed it, and the planner will reason about it. A few habits keep them predictable and safe.

Write it like a pure function

The best Particles are pure: same inputs → same output, no hidden state, no side effects. That is what lets the engine cache, reorder, and parallelise calls freely, and it is what makes a Particle trivial to unit-test.

Keep I/O out. No network, no files, no clocks inside the body. Network, secrets, and filesystem access are deny-by-default and rejected until capability governance ships — so a Particle that needs them will not run anyway. Do the lookup in SQL (a join) and pass the values in as arguments.
Don’t depend on call order or row order unless you declared a @window, whose contract is the frame’s ordered values.

# Good — pure, deterministic, testable.
@scalar
def haversine_km(lat1: float, lon1: float, lat2: float, lon2: float) -> float:
    ...

# Avoid — hidden clock/network make it non-deterministic and unsafe to cache.
@scalar
def enrich(ip: str) -> str:
    return requests.get(f"https://geoip/{ip}").text   # deny-by-default; won't run

Name it so it can’t clash with a built-in

A Particle whose name matches a built-in function is rejected at deploy time (SQLSTATE 42723). Pick a name that stays out of the built-in namespace:

Use a domain word: haversine_km, domain_of, slugify, sha256_hex.
Or a small prefix: pmedian (not median), py_tag, p_zscore.

Don’t try to override a built-in — the check is there precisely to keep SUM, UPPER, and friends unambiguous. Details and the exact error in Collisions & Error Handling.

Get the type hints right — they are the contract

Your Python type hints (or Rust signature) become the SQL signature, and they drive marshaling in both directions. Every parameter and the return value must be annotated; a missing or unsupported hint is a clear, source-located error at discovery time — you fix the module, not the database. See the type mapping.

Match the SQL type you actually want:

Money and exact decimals → Decimal (numeric), not float.
Whole numbers → int (bigint).
An aggregate/window over numeric columns → list[float].

Declare volatility honestly

Default volatility is immutable (result depends only on the arguments). If a Particle’s result can change over time for the same arguments — e.g. it buckets a timestamp relative to “today” — declare it stable so the planner does not constant-fold it:

@scalar(volatility="stable")
def day_bucket(ts: datetime) -> date:
    return ts.date()

Leave it immutable for genuinely pure math (the common case) — that is what lets the engine fold and cache aggressively.

Understand STRICT-null before you rely on it

Particles are STRICT by default (PostgreSQL-faithful): if any argument is NULL, the result is NULL and your body is never called. This is usually what you want — a @scalar that would crash on None is automatically safe over a nullable column. Opt out only when the body genuinely needs to see NULL:

@scalar(called_on_null=True)
def coalesce_blank(s: str) -> str:
    return s or ""

Full null semantics per kind (scalar / aggregate / window) are in Collisions & Error Handling.

Test the module directly — no database needed

Because the decorators return your function unchanged, a Particle module is ordinary Python you can import and unit-test. Test the logic before you ever deploy:

from geo import haversine_km

def test_haversine_km():
    assert round(haversine_km(40.71, -74.01, 37.77, -122.42), 2) == 4129.06

Then run particledb particles verify to confirm the SQL signatures the engine will build from your hints (Loading).

Choose Python or Rust deliberately

Next steps

Collisions & Error Handling — the naming rule, null semantics, and what a failing body does.
Loading — get your Particles into a running server.