By 2001 Jim Gray had already won the Turing Award for transaction processing. Butler Lampson’s introduction describes TP as “pixie dust”: sprinkle it on a boring, non-fault-tolerant program and it becomes fault-tolerant and load-balanced. Gray then spends an hour on a different subject, astronomy. Working with Alex Szalay’s group at Johns Hopkins and with Caltech, he had been loading the Sloan Digital Sky Survey (SDSS) into SQL Server, and this lecture is an early report on that project, the SkyServer.
It’s this week’s classic because a lot of later data engineering traces back to it: the idea that data analysis is its own branch of science, that you design a scientific database from a set of real questions, and that you publish methods on objects rather than raw bytes. Gray made the first of these his “fourth paradigm” argument later in the decade. The slides are scanned overheads and the camera work is from 2001, but the reasoning still holds up.
Science grows a data branch
Gray explains why a database researcher was doing astronomy at all. The advice he credits to Mike Stonebraker is that the database field had been “doing the same thing” for years, and the way to broaden its agenda was to go into application areas, which would supply new problems. Astronomy was his application area.
The framing slide sets out a sequence. Science started empirical, then grew a theoretical branch, then a computational one, and until then “computational” mostly meant simulation. Gray says a second computational branch was emerging. Instruments capture data, or simulators generate it. Software processes it into objects, the objects go into a database, and the scientist analyses the database rather than the sky. “In essence, the universe is a database and you’re getting the data out.” There is far too much of it for anyone to look at, so “you need a robot to” look for you.
The data avalanche
CCD detectors were tracking Moore’s law, so astronomy data was doubling about every two years. Gray points out that this means the next two years of data will equal everything collected so far. Astronomers had roughly a petabyte of good data then. Imaging the whole sky at one pixel per square arcsecond comes to about 4 TB per band, before multiplying by wavelengths and repeat visits.
Two properties made astronomy a good testbed for database work. The data has no commercial value, so you can actually get terabytes of it, unlike from Walmart or a hospital. And it’s public: surveys typically release their data after about two years, so roughly half of everything is open to anyone, which Gray calls hugely democratising. But publishing data is harder than it sounds. Using the Hitchhiker’s Guide’s answer of 42, he says a bare number is useless without units, precision and provenance, which is the metadata problem in miniature.
The characteristics slide lists the difficulties: petabytes, hundreds of attributes per object, cross-correlation complicated by multi-resolution data, time variation, and dirt such as cosmic rays and passing airplanes. The Crab Nebula, shown in X-ray, optical, infrared and radio, illustrates why you can’t study an object from one instrument’s view.
The Internet as the world’s best telescope
Gray’s proposed architecture is the Virtual Observatory. Each survey team curates its own archive, since they know the data best and want to be publishers, not database administrators. Federating those archives creates a telescope that covers every part of the sky, in every band, at the quality of the best instrument, and “it’s up when you’re up”. No flying to Hawaii and hoping for clear skies. The hard parts he lists are plumbing (managing and indexing a petabyte), unsolved metadata, analysis tools that aren’t good enough, and the fact that this work doesn’t get you a job in either an astronomy or a computer science department.
On federation he makes a point that later web-services and data-mesh arguments repeated: “send XML” is a non-answer, equivalent to “send Unicode”. “Bytes” is the wrong abstraction. Archives should publish methods on objects. He demonstrates that with TerraService, his aerial-imagery project rebuilt as a WSDL/SOAP web service, and sketches the SkyServer equivalent (ss.GetObjects(ra, dec, radius)). The open questions are which objects and methods to standardise, and how much can be fixed at compile time.
The 20 queries
At the time SDSS had about 15 million photometric objects with around 400 attributes each, plus spectra. The processed catalog fit on an 80 GB disk. The astronomers worked with C programs and Tcl scripts over flat files. Gray’s premise was to load it into SQL, add indexes, and see whether that worked better.
To find out what “better” meant, Szalay gave him 20 queries that astronomers actually ask, each of which would take about a week to write as a C program. This is the method Gray is remembered for: collect the real questions first and design the database to answer them. Many of the queries were spatial and SQL Server had no spatial support, so the team implemented a Hierarchical Triangular Mesh as a table-valued function. Every object got a 20-level mesh ID, and spatial joins became index lookups.
Query 15 is his worked example. SDSS images each patch of sky in five colours a few minutes apart as the Earth turns, so a moving object appears at slightly different positions in each band. The image pipeline already computed velocities, and a first query found 2,167 moving objects in seven minutes at 70 MB/s. The astronomer wanted fast movers, which the pipeline treats as separate objects, so the real query looks for single-band detections near each other with no other spectrum. Gray calls the SQL “ugly, but consider the alternatives”. It found three candidates in 11 minutes, two of them genuine near-Earth asteroids.
What the queries taught
Everything ran on a roughly $3,000 PC with two CPUs, four disks and 256 MB of RAM. Some queries took ten minutes, some one minute, most about 15 seconds. The workload needed about 200 instructions per byte of I/O, far more CPU-heavy than TPC-style benchmarks, and Gray’s conclusion was that “GHz processors are fast!” Most queries were one or two sequential passes over the data, so covering indexes that make scans cheap mattered most. Counting was very common and binning was common. Nobody asked for a data cube, which Gray suspects may just be cultural.
His reflections slide covers what went wrong too. Data loading and scrubbing was the real cost (“AUTOMATE!!!”), because the data was dirty and writing queries kept exposing pipeline bugs. The setup wasn’t tuned and was disk-bound, every query benefited from parallelism, and SQL got parallelism for free in a way the astronomers’ C programs didn’t. The call to action is aimed at each research community. Visualization people are needed, nobody had good tools. Database people get real data to practise on, and distributed-systems people get a federation.
SQL against the tarpit
Two other parts are worth watching. The SkyServer was built for K-12 education, with lessons where students rediscover the Hubble diagram, and Gray found that professional astronomers used it too because their own tools were worse. In Q&A he describes how the survey team replaced a decade-old plate-drilling target-selection program that nobody wanted to touch with one SQL query. That collected spectra for rare galaxy types at 99% success, and data ingest went from weeks to much faster. His summary of why the old code was hard to change is the Turing tarpit: everything is possible, but some things are much easier than others.
Key takeaways
- Gray named data analysis as a branch of computational science separate from simulation, where software turns instrument data into objects in a database and scientists query that database.
- When data doubles every two years, the next two years bring as much data as all of history, so analysis has to be automated.
- Collecting about 20 real user questions before designing the schema is a cheap way to find out what the database must do well.
- Spatial, scientific queries mostly turned into scans plus counting and binning, so covering indexes and parallel scans mattered more than clever operators.
- Loading and cleaning the data, not running queries, was the dominant cost, and writing queries kept exposing bugs in the upstream pipeline.
- To federate archives, publish typed methods on objects with defined units and provenance, not raw files or XML dumps.
- A $3,000 PC running SQL could replace week-long C programs, because declarative queries get indexing and parallelism without anyone writing them.
Source
- Talk: Jim N. Gray on Databases & Astronomy (slides titled “Databases Meet Astronomy: a db view of astronomy data”)
- Speaker: Jim Gray, Microsoft Research; introduced by Butler Lampson
- Event: MIT Laboratory for Computer Science (LCS) Distinguished Lecture, 2001
- Length: 1 h 06 min
- Archive: MIT Video Productions (uploaded 2018)
- Video: https://www.youtube.com/watch?v=w4j9C6GNas4