pyacid 0.2.0a3

Astronomical Catalog Inference Driver: XMATCH SQL over HATS-partitioned Parquet via native Polars

ACID — Astronomical Catalog Inference Driver

Copyright (c) 2026, Mario Juric. BSD 3-Clause License.

SQL-driven crossmatching and analysis of HEALPix-partitioned astronomical catalogs. ACID extends SQL with an XMATCH(...) predicate for spherical-distance joins, runs each anchor partition independently against a boundary-safe margin cache, and aggregates the results.

Reads and writes the HATS format used by LINCC Frameworks (LSDB, hats-import) and by published catalogs such as Gaia DR3 and Rubin DP1.

---

Quick start

Open a connection, run a query

acid is built around one explicit object: a Connection. Open it with acid.connect(...), use it as a context manager so the worker pool is torn down cleanly, then either ask for catalog handles and compose with verbs, or drop into SQL:

import acid
import astropy.units as u

with acid.connect("catalogs.yaml", workers=8) as db:
    gaia = db.open("gaia_dr3")
    twomass = db.open("twomass_psc")

    # Fluent: composable verbs, lazy until materialized.
    matches = (gaia
               .crossmatch(twomass, radius=1*u.arcsec)
               .where("phot_g_mean_mag < 16")
               .select("source_id, designation"))

    matches.head(10).show()            # pretty-print to stdout
    df = matches.head(10).to_pandas()  # also: .to_astropy(), .to_polars(), .to_arrow()

    # SQL escape hatch for aggregates / HAVING / windows / DISTINCT.
    r = db.sql("""
        SELECT g.source_id, t.designation, d
        FROM   gaia_dr3 AS g
        JOIN   twomass_psc AS t ON XMATCH(radius_arcsec => 1.0, dist_col => 'd')
        ORDER BY d
        LIMIT  20
    """)
    print(r)

Result is a thin wrapper around an Arrow table; .show() prints, .to_pandas() / .to_astropy() / .to_polars() / .to_arrow() convert, and .write("results.parquet") / .write_parquet() / .write_csv() / .write_fits() save to disk.

Restrict to a region while you iterate

db.in_cone(...) is a context manager that scopes a spatial cone to every query inside the with block — both the fluent surface and db.sql(...). Use it for a "debug small, run big" workflow: keep the block in while you iterate, remove it for the production run.

with acid.connect("catalogs.yaml", workers=8) as db:
    gaia = db.open("gaia_dr3")

    with db.in_cone((180, 0), radius=1*u.deg):
        small = gaia.where("phot_g_mean_mag < 16").to_pandas()

    # Same query, full sky, no edits:
    big = gaia.where("phot_g_mean_mag < 16").to_pandas()

Cones do not nest; one in_cone block at a time.

Materialize an intermediate

Catalog.save(...) writes a query result as a HATS catalog and registers it on the connection so later queries can reference it by name. This is the canonical EDA pattern: run a heavy crossmatch once, save it, iterate cheaply on the cached output.

with acid.connect("catalogs.yaml", workers=8) as db:
    nearby = (db.open("gaia_dr3")
                .crossmatch(db.open("twomass_psc"), radius=1*u.arcsec)
                .save("./out/gaia_x_2mass", name="nearby"))

    # `nearby` is a normal Catalog; "nearby" is also resolvable by name.
    r = db.sql("SELECT COUNT(*) FROM nearby")
    print(r)

CLI

# Query execution. The SQL query is required (use '-' to read stdin, or -f).
# --db is a ':'-separated list of HATS dirs / registry YAMLs; it's optional,
# falling back to $ACID_PATH, the acid.conf 'path' setting, then ~/datasets.
acid query "SELECT COUNT(*) FROM object" --db datasets/ --out /tmp/result
acid query -f query.sql --db catalogs.yaml --out results/ --workers 32
echo "SELECT ..." | acid query - --db datasets/ --out results/   # '-' reads stdin
# --format is optional: it's inferred from the --out extension
# (.parquet/.pq, .csv, .fits/.fit, .hats); no extension → HATS tree.
acid query "SELECT ..." --db datasets/ --out results.csv
acid validate "SELECT ..." --db datasets/

# Download catalogs (HTTP, SSH, or local; full or spatial subset)
acid download https://data.lsdb.io/hats/two_mass/two_mass /data/two_mass
acid download https://data.lsdb.io/hats/two_mass/two_mass /data/two_mass --cone 50,-50,10
acid download user@server:/hats/gaia /data/gaia --columns ra,dec,mag --cone 180,0,5

# Inspect catalogs (local or remote)
acid inspect /data/two_mass                          # summary
acid inspect schema /data/two_mass                   # column schema
acid inspect https://data.lsdb.io/hats/two_mass/two_mass  # remote

# Build margin caches locally
acid hats build-margin /data/two_mass --margin-arcsec 5.0 --workers 16

results/ is itself a valid HATS catalog (lsdb.open_catalog(...) and hats.read_hats(...) will read it). Downloaded subsets are also valid HATS catalogs with rebuilt _metadata.

Catalog registry

The simplest way: point --db (or acid.connect(...)) at a directory of HATS catalogs. Each subdirectory with a properties file becomes a table named after the directory. Margin caches (dataproduct_type=margin) are auto-skipped.

For more control, use a YAML file:

catalogs:
  dia_source:
    path: /data/dia_source      # HATS root, or CatalogCollection root
    # Auto-detected from <path>/properties when present:
    #   ra_col            (from hats_col_ra)
    #   dec_col           (from hats_col_dec)
    #   hpix_order        (from <path>/partition_info.csv)
    #   neighbor_path     (from collection.properties or sibling '_margin' dir)
    #   neighbor_margin_arcsec  (from hats_margin_threshold)
    #   npix_suffix       (from hats_npix_suffix; default '.parquet')
    # Any auto-detected value can be overridden here.

  object:
    path: /data/object_collection    # a CatalogCollection root works too

  lightcurve:
    path: /data/lightcurve
    hpix_order: 5                    # explicit when partition_info.csv is absent

# Named MOC footprints for IN_MOC() filtering.
# Each entry is a path to a FITS file (HEALPix image or MOC FITS).
mocs:
  des_dr2: /data/mocs/des_dr2.fits
  known_artifacts: /data/mocs/artifacts.fits
  # If a catalog has a point_map.fits at its root, IN_MOC(<alias>, '<catalog_name>')
  # auto-loads it — no explicit entry needed.

Configuration (acid.conf)

So you don't re-type --db/--workers on every invocation, acid reads an INI config. The first existing file wins, searched highest-priority first: --config FILE / $ACID_CONFIG, then ~/.config/acid/acid.conf ($XDG_CONFIG_HOME), /sdf/data/rubin/user/mjuric/etc/acid.conf, /sdf/home/m/mjuric/etc/acid.conf, $XDG_CONFIG_DIRS, /etc/acid/acid.conf.

# ~/.config/acid/acid.conf
[acid]
path = /data/hats:~/datasets        # ':'-separated HATS dirs / registry YAMLs
workers = 32                        # query worker pool ("auto" = cgroup-aware)
mem_per_worker_gb = 4               # RAM/worker bounding "auto" (CPU and memory)
tmpdir = /scratch/$USER             # base temp dir (a per-run subdir is made + cleaned)
inmem_row_limit = 50_000_000        # spill threshold

Each setting resolves explicit flag/arg → env var → config → built-in. Env overrides: ACID_PATH, ACID_WORKERS, ACID_MEM_PER_WORKER_GB, ACID_TMPDIR, ACID_INMEM_ROW_LIMIT. Inspect and edit with acid config:

acid config show                 # values set in the file (--effective: resolved + provenance)
acid config get workers          # file value; exits 1 (prints nothing) if unset
acid config set path /data/hats:~/datasets

With a config in place, --db becomes optional (falls back to the path setting, then ~/datasets). See docs/archive/CONFIG-SYSTEM.md for the full design.

---

What XMATCH does

JOIN  b ON XMATCH(radius_arcsec => 1.0)                   -- nearest, inner
JOIN  b ON XMATCH(r => 1.0)                               -- 'r' is an alias
JOIN  b ON XMATCH(r => 1.0, mode => 'all')                -- every match within r
LEFT JOIN b ON XMATCH(r => 1.0)                           -- keep unmatched anchors

-- Distance is exposed as a named column via dist_col on the XMATCH call.
SELECT a.id, d FROM a JOIN b ON XMATCH(r => 1.0, dist_col => 'd')
WHERE  d < 0.5

-- Ordinary joins, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT/OFFSET,
-- DISTINCT all work; cross-partition reduction is handled internally.
SELECT a.id, COUNT(*) AS n, AVG(d) AS avg_d
FROM a
JOIN  b ON XMATCH(r => 1.0, dist_col => 'd')
JOIN  lightcurve AS lc ON a.id = lc.object_id
GROUP BY a.id
ORDER BY n DESC LIMIT 100

-- Footprint filtering via MOC (Multi-Order Coverage maps):
-- Restrict rows to a survey footprint or sky region.
SELECT a.id, a.ra, a.dec
FROM a JOIN b ON XMATCH(r => 1.0)
WHERE IN_MOC(a, 'des_dr2')              -- anchor inside DES footprint
  AND NOT IN_MOC(b, 'known_artifacts')  -- exclude artifact regions

-- IN_MOC also works in SELECT projections (per-row boolean):
SELECT a.id, IN_MOC(a, 'des_dr2') AS in_des FROM a

The fluent equivalent of the simple shapes:

a.crossmatch(b, radius=1*u.arcsec)                          # nearest, inner
a.crossmatch(b, radius=1*u.arcsec, how="all")               # every match within r
a.crossmatch(b, radius=1*u.arcsec, how="left")              # LEFT XMATCH
a.in_region("des_dr2")                                      # IN_MOC mask, per-receiver

Semantics, in short:

• All XMATCHes in a query use the anchor (first FROM) table's coordinates, even after a mode => 'all' expansion.
• A right-table radius must be ≤ that catalog's neighbor_margin_arcsec. Otherwise we'd silently miss boundary pairs; the analyzer rejects the query.
• ORDER BY ... LIMIT K pushes the top-K to each partition first; the reducer re-sorts the union and applies the global LIMIT/OFFSET.
• Aggregates / GROUP BY / DISTINCT / HAVING run in a phase-2 reducer over the per-partition Parquet output.

---

Python API surface

# Connection (the only entry point)
db = acid.connect(source, *, workers="auto", threads=None,
                  inmem_row_limit=50_000_000,
                  cache_dir=None, progress="auto") -> Connection

db.open(name_or_path, *, alias=None, columns=None) -> Catalog
db.add_catalog(name, **spec_kwargs)                -> Catalog
db.list_catalogs()                                  -> list[str]
db.register_moc(name, source)                       # FITS path, mocpy.MOC, or (N,2) ranges
db.sql(query, *, output=None)                       -> Result
db.map_partitions_sql(query, *, output=None)        -> ExecutionResult
db.in_cone(center, *, radius)                       # ctx manager
db.status() / db.validate(q) / db.explain(q)
db.close()    # or use as a context manager

# Catalog (composable, lazy)
cat.where(predicate)        -> Catalog
cat.select(*cols)           -> Catalog
cat.limit(n)                -> Catalog
cat.in_region(moc_or_cat)   -> Catalog
cat.crossmatch(other, *, radius, how="nearest"|"all"|"left") -> Catalog
cat.join(other, *, on, how="inner"|"left")                   -> Catalog
cat.columns / cat.alias / cat.describe() / cat.explain()
cat.head(n=10)              -> Result
cat.execute()               -> Result
cat.to_pandas() / cat.to_astropy() / cat.to_polars() / cat.to_arrow()
cat.save(path, *, name=None, overwrite=False) -> Catalog

# Result
r.num_rows, r.column_names, r.schema
r.column(name)         -> pa.ChunkedArray
r.show(n=20)           # pretty-print to stdout
print(r)               # same formatter via __str__
r.arrow()              -> pa.Table
r.df() / r.to_pandas() -> pandas.DataFrame
r.to_polars()          -> polars.DataFrame
r.to_pylist()          -> list[dict]
r.batches(batch_size=None) -> Iterator[pa.RecordBatch]
r.head(n=10)           -> Result
r.write_parquet(path, layout="hats"|"single") -> Path
r.write_csv(path)      -> Path
r.write_fits(path)     -> Path
r.write(path, format=None) -> Path   # format inferred from extension when omitted
len(r), for batch in r: ...

# Errors (all inherit from acid.AcidError)
acid.RegistryError           # catalog config (missing path, mixed Norder, ...)
acid.ParseError              # SQL parse failures
acid.ValidationError         # unsupported XMATCH constructs
acid.ExecutionError          # per-partition execution failures
acid.ConnectionClosedError   # method called on a closed Connection
acid.StaleCatalogError       # Catalog used outside its captured cone block

---

Layout assumptions

• Catalogs follow the HATS layout: <root>/dataset/Norder=N/Dir=D/Npix=P.parquet (or Npix=P/*.parquet when hats_npix_suffix='/').
• Margin caches live as sibling catalogs (HATS canonical), at <root>/margin_cache/..., or any sibling dir matching <name>_margin*. collection.properties is consumed if present.
• Adaptive (per-pixel) Norder is supported: a catalog's partition_info.csv may list pixels at any orders, and XMATCH/ordinary joins across mixed-Norder catalogs are run via a refinement-tree enumeration that emits one work unit per coarsest cursor pixel where every joined catalog has ≤ 1 partition. Output is itself a valid HATS catalog whose partition_info.csv reflects the refinement.

---

What's the speed story?

• Each partition is independent → embarrassingly parallel across HEALPix pixels.
• Top-K queries push the LIMIT to each partition. Aggregates write partial data to disk and reduce centrally.
• Column pruning: the anchor and right relations are lazy Polars LazyFrames over scan_parquet(), so the final projection only pulls referenced columns from disk. Wide catalogs (150+ columns) don't slow down narrow SELECTs.
• Auto-spill: when output is unset and the running result exceeds inmem_row_limit (default 50M rows), acid spills to a tempdir rather than OOM-ing the parent.
• Allocator tuning: acid ships a jemalloc default that avoids page-purge contention at high worker counts (~2× faster wall, ~20% more RSS). It's a single overridable env var — see MEMORY-TUNING.md if you're memory-constrained or scaling workers on a large machine.

See bench/match_all.py and bench/session_vs_oneshot.py for microbenchmarks.

---

Install

With uv (recommended for development)

uv sync --dev          # creates .venv, installs all deps + test + hats
uv run pytest          # run tests

With pip

pip install -e .
# extras:  pip install -e .[hats,dev]

Requires Python 3.10+, Polars ≥ 1.41, SQLGlot ≥ 27, PyArrow ≥ 14, NumPy ≥ 1.24, SciPy ≥ 1.10, cdshealpix, mocpy, PyYAML ≥ 6.

---

Status

• v0 (correctness): XMATCH inner/left, mode 'nearest'/'all', chains, ordinary joins, distance via XMATCH(..., dist_col => '<name>').
• v1 (scale): views + narrow side-tables, vectorized matcher, worker initializer, auto-spill, top-K pushdown, manifest.
• v1.1 (HATS spec): writes valid HATS catalogs, reads canonical property keys, supports hats_npix_suffix='/', auto-discovers margin siblings via collection.properties.
• v2 (EDA): persistent Connection, per-worker Polars engine, Result wrapper, Catalog.save() for materialization.
• v3 (adaptive Norder): per-catalog PartitionIndex, refinement-tree tuple enumeration, integer _healpix_29 range filtering for per-pixel row pruning, LEFT-XMATCH/JOIN over partitions without coverage.
• v4 (Polars-native): single native-Polars engine; DuckDB, the SQL rewriter/reducer, the engine abstraction, and the QueryPlan IR removed (see CHANGELOG.md / ARCHITECTURE.md).
• v4 (MOC footprint filtering): IN_MOC(<alias>, '<name>') in WHERE restricts rows to a named sky region (Multi-Order Coverage map). Supports NOT IN_MOC, multiple predicates (AND-combined via mocpy set ops), and catalog auto-resolution from point_map.fits. IN_MOC is a footprint restriction only — it must sit in conjunctive WHERE position (top-level AND-chain, optionally negated); use in SELECT/ORDER BY/CASE/JOIN ON or inside a disjunction is rejected (see Known limitations). Three-level optimization: catalog-footprint scoping, cursor-pixel intersection, and partition-level pruning — all via the existing _healpix_29 row-group pushdown fast path.
• v5 (catalog ops): acid hats build-margin builds HATS margin caches locally (validated against hats-import). acid download generates point_map.fits, auto-includes HATS RA/Dec/healpix columns. acid query accepts --db <directory> for zero-config usage, fails fast on errors, shows tqdm progress, shuffles work for load balancing. Bare column resolution via schema introspection. LocalFetcher for local I/O.
• v6 (fluent Catalog API): acid.connect() returns an explicit Connection; db.open(name) returns a lazy Catalog; verbs (where, select, crossmatch, join, in_region, save) compose without writing SQL. db.in_cone(...) scopes a cone to every query in a with block. db.sql(...) remains the escape hatch for decomposable aggregates, HAVING, and top-K (ORDER BY ... LIMIT). Window functions, DISTINCT, COUNT(DISTINCT), bare GROUP BY, and unbounded ORDER BY are rejected with a ValidationError.

Tests: ~545 passing (~60s parallel via pytest-xdist) on the native Polars engine. Fixtures cached across runs.

Known limitations

• XMATCH must be the entire ON predicate. Compound predicates like XMATCH(...) AND b.mag < 20 are rejected.
• No CTEs / subqueries in the anchor position.
• RIGHT / FULL / CROSS JOIN XMATCH not supported.
• IN_MOC must be in conjunctive WHERE position (top-level AND-chain, optionally negated). Disjunctive use (IN_MOC(...) OR ...) and IN_MOC in JOIN ON are rejected.
• No nested db.in_cone(...) blocks. The true intersection of two non-concentric cones is not a cone; we refuse rather than silently approximate.