pg_orrery

It's not rocket science. (It's celestial mechanics. But now it's just SQL.)

An orrery is a clockwork model of the solar system — brass gears turning planets in their courses. pg_orrery is the same idea, built from Keplerian parameters and SQL instead of wheelwork. Where a mechanical orrery approximates orbits with gear ratios, a database orrery computes them from the six orbital elements that define each trajectory.

68 functions. 7 custom types. All PARALLEL SAFE. Optional JPL DE440/441 support for sub-arcsecond accuracy; core functions have zero external dependencies at runtime.

Documentation · Source

Installation

Docker (recommended)

docker run -d --name pg_orrery \
  -e POSTGRES_PASSWORD=orbit \
  -p 5499:5432 \
  git.supported.systems/warehack.ing/pg_orrery:pg17

psql -h localhost -p 5499 -U postgres -c "CREATE EXTENSION pg_orrery;"

Build from Source

Requires PostgreSQL 17 development headers and a C/C++ toolchain.

git clone https://git.supported.systems/warehack.ing/pg_orrery.git
cd pg_orrery
git submodule update --init
make PG_CONFIG=/usr/bin/pg_config
sudo make install PG_CONFIG=/usr/bin/pg_config

CREATE EXTENSION pg_orrery;

Quick Start

Where is Jupiter right now?

SELECT topo_azimuth(t) AS az,
       topo_elevation(t) AS el,
       topo_range(t) / 149597870.7 AS distance_au
FROM planet_observe(5, '40.0N 105.3W 1655m'::observer, now()) t;

What's the entire solar system doing?

SELECT body_id,
       CASE body_id
         WHEN 1 THEN 'Mercury' WHEN 2 THEN 'Venus'
         WHEN 3 THEN 'Earth'   WHEN 4 THEN 'Mars'
         WHEN 5 THEN 'Jupiter' WHEN 6 THEN 'Saturn'
         WHEN 7 THEN 'Uranus'  WHEN 8 THEN 'Neptune'
       END AS name,
       round(helio_distance(planet_heliocentric(body_id, now()))::numeric, 4) AS distance_au
FROM generate_series(1, 8) AS body_id;

Predict ISS passes over your location:

WITH iss AS (
  SELECT '1 25544U 98067A   24001.50000000  .00016717  00000-0  10270-3 0  9025
2 25544  51.6400 208.9163 0006703  30.1694  61.7520 15.50100486 00001'::tle AS tle
)
SELECT pass_aos(p) AS rise_time,
       pass_max_el(p) AS max_elevation,
       pass_los(p) AS set_time
FROM iss, predict_passes(tle, '40.0N 105.3W 1655m'::observer,
                         now(), now() + interval '24 hours', 10.0) p;

When will Jupiter produce radio bursts tonight?

SELECT t,
       round(jupiter_burst_probability(
         io_phase_angle(t),
         jupiter_cml('40.0N 105.3W 1655m'::observer, t)
       )::numeric, 3) AS burst_prob
FROM generate_series(now(), now() + interval '12 hours', interval '10 minutes') AS t
WHERE jupiter_burst_probability(
  io_phase_angle(t),
  jupiter_cml('40.0N 105.3W 1655m'::observer, t)
) > 0.3;

Plan an Earth-Mars transfer:

SELECT round(c3_departure::numeric, 2) AS c3_depart_km2s2,
       round(tof_days::numeric, 1) AS flight_days,
       round(transfer_sma::numeric, 4) AS sma_au
FROM lambert_transfer(3, 4, '2028-10-01'::timestamptz, '2029-06-15'::timestamptz);

What It Covers

Domain	Theory	Key Functions	Accuracy
Satellites	SGP4/SDP4 (Brouwer 1959)	observe(), predict_passes()	~1 km (LEO, fresh TLE)
Planets	VSOP87 (Bretagnon 1988)	planet_observe(), planet_heliocentric()	~1 arcsecond
Sun	VSOP87 (Earth vector, inverted)	sun_observe()	~1 arcsecond
Moon	ELP2000-82B (Chapront 1988)	moon_observe()	~10 arcseconds
Planetary moons	L1.2, TASS17, GUST86, MarsSat	galilean_observe(), etc.	~1-10 arcseconds
Stars	J2000 catalog + precession	star_observe()	Limited by catalog
Comets/asteroids	Two-body Keplerian	kepler_propagate(), comet_observe()	Varies with eccentricity
Jupiter radio	Carr et al. (1983) sources	jupiter_burst_probability()	Empirical probability
Transfers	Lambert (Izzo 2015)	lambert_transfer(), lambert_c3()	Ballistic two-body

Types

Type	Bytes	Description
tle	112	Parsed mean orbital elements for SGP4/SDP4 propagation
eci_position	48	Position and velocity in TEME frame (km, km/s)
geodetic	24	Latitude, longitude, altitude on WGS-84 ellipsoid
topocentric	32	Azimuth, elevation, range, range rate relative to observer
observer	24	Ground location. Input: '40.0N 105.3W 1655m' or decimal degrees
pass_event	48	Satellite pass with AOS/TCA/LOS times and azimuths
heliocentric	24	Position in AU, ecliptic J2000 frame

All types are fixed-size with STORAGE = plain. No TOAST overhead.

Body IDs

Planets follow the VSOP87 convention. Planetary moons use per-family indexing.

ID	Planet		Galilean (0-3)	Saturn (0-7)	Uranus (0-4)	Mars (0-1)
1	Mercury		0: Io	0: Mimas	0: Miranda	0: Phobos
2	Venus		1: Europa	1: Enceladus	1: Ariel	1: Deimos
3	Earth		2: Ganymede	2: Tethys	2: Umbriel
4	Mars		3: Callisto	3: Dione	3: Titania
5	Jupiter			4: Rhea	4: Oberon
6	Saturn			5: Titan
7	Uranus			6: Iapetus
8	Neptune			7: Hyperion

GiST Indexing

The tle_ops operator class indexes TLEs by altitude band for conjunction screening:

CREATE INDEX ON satellites USING gist (tle);

-- Find objects in overlapping altitude shells
SELECT a.name, b.name
FROM satellites a, satellites b
WHERE a.tle && b.tle AND a.norad_id < b.norad_id;

-- K-nearest-neighbor by altitude separation
SELECT name, round((tle <-> iss.tle)::numeric, 0) AS alt_sep_km
FROM satellites, (SELECT tle FROM satellites WHERE norad_id = 25544) iss
ORDER BY tle <-> iss.tle
LIMIT 20;

Performance

Measured on PostgreSQL 17, single backend:

Operation	Count	Time	Rate
TLE propagation (SGP4)	12,000	17ms	706K/sec
Planet observation (VSOP87)	875	57ms	15.4K/sec
Moon observation (Galilean)	1,000	63ms	15.9K/sec
Star observation	500	0.7ms	714K/sec
Lambert transfer solve	100	0.1ms	800K/sec
Pork chop plot (150x150)	22,500	8.3s	2.7K/sec

Testing

12 regression test suites covering all domains:

make installcheck PG_CONFIG=/usr/bin/pg_config

Tests: TLE parsing, SGP4/SDP4 propagation, coordinate transforms, pass prediction, GiST indexing, convenience functions, star observation, Keplerian propagation, planet observation, moon observation, Lambert transfers, and DE ephemeris.

Documentation

Full documentation at the pg_orrery docs site, built with Starlight. Includes guides, workflow translations (from Skyfield, JPL Horizons, GMAT, Radio Jupiter Pro), complete function reference, architecture notes, and benchmarks.

What pg_orrery Is Not

Not a GUI. Use Stellarium, GPredict, or STK for visualization.

Not sub-arcsecond. VSOP87 gives ~1 arcsecond — good for observation planning, not for dish pointing at GHz frequencies. For that, use SPICE or Skyfield with DE441.

Not a TLE source. Bring your own from Space-Track, CelesTrak, or any provider.

Not a replacement for SPICE. No BSP kernels, no aberration corrections at IAU 2000A level. pg_orrery trades those last few milliarcseconds for SQL-speed computation joined with your existing data.

Not a full mission design tool. The Lambert solver handles ballistic two-body transfers. For low-thrust, gravity assists, or multi-body optimization, use GMAT.

Upgrading from v0.1.0

ALTER EXTENSION pg_orrery UPDATE TO '0.2.0';

Adds all solar system functions while preserving existing TLE data and satellite functions.

License

PostgreSQL License. Copyright (c) 2025, Ryan Malloy.

The bundled sat_code library is separately licensed under the MIT license.