The Pan-STARRS1 Database and Data Products

2. BACKGROUND

• PSPS extends SDSS database software for Pan-STARRS1’s larger sky coverage and measurements from stacks, single exposures, and mean properties.The greater data volume and complexity required major changes rather than direct reuse of the SDSS design.Source: §2 · p. 1
• A monolithic database was unsuitable, so PSPS uses SQL Server features, distributed partition views, and slices to distribute data across multiple server machines.Partitioning into smaller databases addresses the scale of the Pan-STARRS1 data.Source: §2 · p. 1
• The distributed system still presents partitioned information as unified tables to users while retaining SDSS-derived tools, including Hierarchical Triangular Mesh (Szalay et al. 2007).The architecture preserves a unified user-facing view despite underlying distribution.Source: §2 · p. 2

• DR1 (December 2016) provides deep-stacked 3π images with ancillary signal, mask, variance, and number maps, plus static-sky catalog tables without time-domain information.The releases cover the 3π survey sky north of −30°; the catalog products are organized into several source-property tables.Source: §3 · p. 2
• DR2 adds single-epoch warp images, Detection and Forced* tables, and improvements that supersede DR1, while future releases will provide 3π difference images and Medium Deep Survey products.The added tables contain single-epoch source detections and forced photometry.Source: §3 · p. 2

• MAST provides Pan-STARRS pixel products, metadata, image access, and a CasJobs interface for synchronous or asynchronous SQL queries over calibrated catalogs.CasJobs supports complex large queries whose results are saved to each registered user’s private space; catalogs cover single exposures, stacks, difference images, and forced photometry.Source: §4 · p. 2
• The schema contains Fundamental Data Products, whose recalibrated catalog fluxes, magnitudes, and coordinates are preferred over new measurements from released pixels.Recalibrated astrometry should be used cautiously with the original images.Source: §4 · p. 2
• Derived Data Products provide higher-order measurements such as proper motions and photometric redshifts, but were not yet available and were deferred to later releases.Source: §4 · p. 2
• Observational Metadata describe exposures, image combinations, and detection efficiencies, while System Metadata document flags, bit definitions, filters, and other database information.Source: §4 · p. 2
• Database views simplify common queries by joining tables and sky slices; for example, the “Detection” view combines 32 Detection tables into a full-sky interface.The paper covers the 3π data releases, with most details also applying to the Medium Deep fields and additional documentation provided through MAST.Source: §4 · p. 2

5. FLOW OF DATA FROM PIPELINE TO THE PAN-STARRS CATALOG DATABASE

• Summit exposures pass through IPP processing, DVO ingestion and calibration, and IppToPsps batch generation before becoming publicly accessible in the Pan-STARRS catalog database.The flow includes camera, stacks, difference images, and forced-photometry products that supply the database construction.Source: §5 · p. 3, Figure 1 · p. 4

5.1. Processing Stages

• All raw images were reprocessed with one IPP revision as Processing Version 3, producing a consistent public DR1 and DR2 data set from the 2010–2014 3π observations.Nightly science processing generated detrended and warped images, stacks, difference images, catalogs, and forced photometry for time-sensitive science access.Source: §5.1 · p. 3

5.1.1. Download and registry of images

• More than 1.3 million summit images were transferred and registered in metadata systems, while IPP tracking recorded processing times, flags, quality metrics, and image types.The internal tracking database is called GPC1.Source: §5.1.1 · p. 3

5.1.2. Chip and Camera Stages

• Chip processing detrends individual OTA FITS files and performs source photometry, while camera processing mosaics chips, derives basic astrometry, and creates smf catalogs later ingested into DVO.Camera products are available as Detection tables starting with DR2.Source: §5.1.2 · p. 3

5.1.3. Warp Stage

• Warp processing projects images onto common tangential RA/Dec skycells with 0.25′′ pixels; all later image products use this skycell layout, and warps are available through MAST for DR2.The 3π Rings.V3 tessellation uses 4.0×4.0-degree projection cells divided into 10×10 skycells with 60′′ overlap per side.Source: §5.1.3 · p. 3

5.1.4. Stack, StaticSky, Skycal Stages

• Deep stacks combine good-quality warps by skycell and filter, while staticsky and skycal generate matched, calibrated catalogs; skycell overlap can duplicate sources, requiring BestDetection or PrimaryDetection.The 3π database contains one deep stack per skycell and filter, with associated masks, weights, variance, and number maps.Source: §5.1.4 · p. 3, Figure 1 · p. 4

5.1.5. Diff Stage / 5.1.6. Forced Photometry Stage

• Difference processing creates WARP STACK catalogs by subtracting 3π deep stacks from corresponding good-quality warps, while forced photometry measures stack-positioned sources on warps and fits extended-source galaxy models.Difference catalogs are planned for DR3; ForcedWarp* tables are available from DR2, and ForcedGalaxy* tables store galaxy-model results.Source: §5.1.5 · p. 4, §5.1.6 · p. 4

5.2.1. Building and calibrating a DVO Database / 5.3. IppToPsps Steps

• DVO links detections to objects and performs astrometric and photometric calibration, after which IppToPsps combines DVO data with catalog files, converts them into PSPS schemas, and ingests recoverable batches.IppToPsps supports multiple batch types for objects, detections, stacks, forced measurements, Gaia recalibration, and difference products; Gaia recalibration was added through GO batches without rebuilding the database.Source: §5.2.1 · p. 5, §5.3 · pp. 5–6

6.1. Introduction

• PSPS integrates the DXLayer, ODM, WMD, DRL, and PSI to transform, load, manage, retrieve, and provide access to survey databases.Users access PSPS through scripts, STScI CasJobs, or the consortium-only PSI, while Fig. 3 depicts data flow from IPP through loading and slicing to queries.Source: §6.1 · p. 7, Figure 3 · p. 8

6.2. Partitioning the PSPS

• Distributed Partitioned Views divide tables into declination-based slices of similar data volume, with configurations customized for the 3π and MD databases.Fig. 4 shows the distribution of partitioned data across a subset of machines.Source: §6.2 · p. 7, Figure 4 · p. 8

6.3. The DXLayer

• The DXLayer polls for new batches, validates manifests, converts FITS data to CSV, and prepares batches for ODM loading, with each batch limited to two PSPS slices.Batches represent processing-stage products or DVO sky-area files and include type, ObjID range, checksum, and table metadata; Figs. 5 and 6 illustrate loading and verification.Source: §6.3 · p. 7, Figure 5 · p. 9, Figure 6 · p. 9

6.4. The ODM

• The ODM executes load, merge, copy, and flip workflows, while the WMD records administrative logs and databases use role-based hot, warm, cold, and load configurations.MS-SQL databases use four volumes with 96 file partitions each, and selected metadata and object tables are merged into a head database for faster queries.Source: §6.4 · p. 7

6.5. The DRL

• The DRL manages user queries through a CasJobs-based API, tracks progress securely, and terminates queries that exceed resource or time limits.SOAP access supports command-line scripts and other clients, while Fig. 9 depicts the DRL workflow.Source: §6.5 · p. 7, Figure 9 · p. 11

6.6. PSI

• The consortium-only PSI provides web tools for submitting and monitoring queries, managing MyDB tables, browsing schemas, building joins, generating flags, plotting results, and accessing pixel products.Users can query 3pi, MD, or MyDB databases and export MyDB tables as CSV, FITS, or XML.Source: §6.6 · p. 7

6. PSPS

• The PSPS separates cold, warm, and hot database copies, with hot data serving fast queries, warm data serving slow queries, and cold data inaccessible to users.The fast queue targets queries completing in less than one minute, while slice machines hold hot and warm copies.Source: Figure 4 · p. 8

Schema overview

• The PSPS comprises over 50 tables and indexes intended to help users select appropriate tables for database queries.Objects are sources with measurable flux at specified R.A. and Dec., and multiple detections generally share the same ObjID.Source: §7 · p. 12, schema · p. 12
• A unique ObjID identifies each Pan-STARRS1 object and serves as the core index joining object and detection tables.ObjectThin provides astrometric information, while Detection supplies individual photometric attributes from single exposures at a given R.A. and Dec.Source: schema · p. 12

7.1. The Main Categories of Tables

• The database has four table categories: Fundamental Data Products, Observational Metadata, Derived Data Products, and System Metadata, with Fundamental Products split into object and detection tables.Object tables summarize sources, detection tables record individual, stacked, forced-warp, and difference-image measurements, and Derived Data Products are not described in this paper.Source: §7.1 · p. 12

7.2. System Metadata Tables

• System metadata tables provide static Pan-STARRS1 information on flags, filters, surveys, photometric calibration, stack types, difference types, and sky tessellations.For the 3π Survey, all stacks are DEEP STACK, all differences are WARP STACK, and the sky tessellation is RINGS.V3.Source: §7.2 · pp. 12, 15

7.2.1. Flag Tables

• The schema contains 45 flag columns organized into eight flag types that identify object quality, calibration, image defects, detection properties, fitting outcomes, and processing associations.The flag tables map bit definitions to columns such as ObjectThin.objectInfoFlag, ImageMeta.qaFlags, Detection.infoFlag, and related fields.Source: §7.2.1 · p. 15

7.3. Object type tables

• Object tables originate from DVO and provide unique or filter-specific identifiers, positions, mean photometry, detection statistics, difference-object properties, forced measurements, lensing parameters, and galaxy shapes.ObjectThin associates single-epoch and stacked detections within one arcsecond, MeanObject stores mean single-epoch photometry, and other object tables extend coverage to difference detections, forced photometry, lensing, and galaxy morphology.Source: §7.3 · p. 16

7.4. Individual Exposure Detection Type Tables

• The database organizes most detections into camera, stack, forced-photometry, and difference-image tables, each representing a distinct IPP processing stage.Stage-specific identifiers and metadata connect measurements to images, exposures, stacks, and object associations.Source: §7.4 · p. 16, §7.4.1 · p. 16, §7.4.3 · p. 18

7.4.1. Tables based on the ‘camera’ stage of IPP

• Camera tables provide exposure and OTA metadata, single-epoch PSF, aperture, and Kron photometry, image-linked detections, and detection-efficiency measurements.FrameMeta, ImageMeta, Detection, and ImageDetEffMeta connect image properties and individual detections through imageID; ObjID is not unique in Detection.Source: §7.4.1 · pp. 16–17

7.4.2. Tables based on the ‘stack’ stage of IPP

• Stack tables contain positional and photometric measurements, extended-source models, aperture and Petrosian quantities, stack metadata, input mappings, and detection efficiencies.StackObjectThin is the principal point-source table; stack joins generally use uniquePspsSTID, while primary and best detections are defined using sky coverage and psfQf criteria.Source: §7.4.2 · pp. 17–18

7.4.3. Tables from the ‘forced photometry’ stage of IPP

• Forced photometry measures stack-selected objects on individual warp images, adding extended and lensing measurements alongside metadata, masking information, and image mappings.Joins among ForcedWarpMeasurement, ForcedWarpMasked, ForcedWarpExtended, and ForcedWarpLensing use uniquePspsFWid; ForcedWarpMeta and ForcedWarpToImage connect warps to exposures.Source: §7.4.3 · p. 18

7.4.4. Tables based on the ‘diff’ stage of IPP

• Difference-image tables describe stack-minus-single-epoch processing, provide difference-image photometry, map inputs, and record detection efficiencies using diffImageId.The 3π database contains DiffMeta, DiffToImage, DiffDetEffMeta, and DiffDetection for this processing stage.Source: §7.4.4 · p. 18

7.5. Description of ObjID and its relation to R.A. and Dec.

• ObjID is an indexing identifier derived from RA and Dec but is not a definitive coordinate; use calibrated coordinate columns instead, and join same-stage detection tables with uniquePspsXXId.ObjectThin supplies 2MASS-calibrated coordinates, while GaiaFrameCoordinate is used for DR1 and ObjectThin for DR2; randomID and random(stage)ID are not join keys.Source: §7.5 · p. 19, §7.7 · pp. 19–20, Figure 14 · p. 19

Which RA and Dec to use?

• For DR1, use GaiaFrameCoordinate for the best general-purpose RA and Dec; for DR2, use ObjectThin’s raMean and decMean because Gaia-calibrated coordinates are integrated there.For high-proper-motion or moving objects, use ra and dec from Detection for single-epoch photometry.Source: GaiaFrameCoordinate · p. 16, §7.6 · p. 19

7.8. NULLS as −999

• PSPS represents NULL values as -999 and provides virtual SQL views that reduce common joins and can accelerate query results.These conventions and views support consistent database navigation and querying.Source: §7.8 · p. 20, §7.9 · p. 20

8.0.1. IPP Processing stages

• IPP processing began with 388,177 raw exposures, queued 381,279 (98.2%) for chip processing, and completed 375,573 (98.5%) with good quality.Camera processing completed for 374,521 exposures (99.7%); losses reflected observer flags, observational conditions, and astrometric or quality failures.Source: §8.0.1 · pp. 20–21
• 379,551 of 379,973 fake/warp exposures (99.9%) had good quality, yielding 206,177 distinct skycells before stacking and later processing.Stack processing produced 200,725 good-quality skycells, while skycal produced 200,684 good-quality stacks; forced warps covered 373,743 exposures in 199,151 skycells.Source: §8.0.1 · pp. 21–22

8.1. Building the 3π DVO Database

• The DVO database successfully ingested all 998,101 expected skycal cmfs, while 374,446 of 374,521 camera smfs were ingested without faults.Forced-warp ingestion comprised 19,266,450 cmfs from 373,743 exposures across 994,890 skycells, and forced-warp summaries contained 994,890 cmfs in 199,151 skycells.Source: §8.1 · p. 22

8.2. IppToPsps Stage

• IppToPsps generated all expected DR1 ObjectThin/MeanObject and GaiaFrameCoordinate batches, but generated 374,344 of 374,446 expected Detection batches.It generated 994,826 of 994,890 expected ForcedWarp batches; missing Detection batches were not science quality and would not enter the database.Source: §8.2 · p. 22

8.3. Loading into PSPS

• PSPS DR1 loading left 2.5% of ObjectThin/MeanObject batches partially ingested, representing 1.2% of objects, while 7,086 Forced Object batches were partially loaded.Missing stack and forced-object data were scheduled for release shortly after DR1; 32 declination-based slices partition the database across machines.Source: §8.3 · pp. 22–23, Figure 15 · p. 23

8.3.1. Queries to find holes

• GaiaFrameCoordinate provides a complete reference for integrity checks, enabling FULL OUTER JOIN queries to identify missing ObjectThin, ForcedMeanObject, and StackObjectThin records.Stack-object checks additionally require valid ObjectThin.raStack and ObjectThin.decStack values with NULL StackObjectThin.objID because not all objects have stack photometry.Source: §8.3.1 · p. 23

9. CONCLUSION

• 10,723,304,629 objects distilled from 1.6 petabytes of images and tables are accessible to the astronomical community through MAST.The work describes the database’s primary tables and quantities, together with example queries, to make this large release easier to use.Source: §9 · p. 23
• Pan-STARRS data has supported studies of moving solar-system objects, high-energy transients, Galactic dust structure, and the large-scale structure of the Universe.The paper presents these applications as only an initial sample of the database’s scientific potential.Source: §9 · p. 23
• Pan-STARRS data products will be essential for identifying host galaxies and electromagnetic counterparts of events detected by gravitational-wave, high-energy-particle, neutrino, and radio observatories.This role is framed within the emerging era of multi-messenger astrophysics.Source: §9 · p. 23
• Combining Pan-STARRS with GALEX, 2MASS, and Gaia will create a rich, high-dimensional dataset for new scientific studies and possible undiscovered astronomical results.The authors also anticipate new interfaces and working methods for high-dimensional datasets.Source: §9 · p. 24
• The database work is critical to science with future surveys such as LSST.The release is intended to support continued development of interfaces and analysis approaches for large, high-dimensional datasets.Source: §9 · p. 24

APPENDIX QUERY EXAMPLES

• The examples progress from simple to complex queries for the Pan-STARRS1 database, focusing on DR1 while deferring DR2 queries.The section is intended to demonstrate query usage across increasing levels of complexity.Source: APPENDIX QUERY EXAMPLES · p. 24
• The PSPS schema uses uppercase SQL keywords and functions and CamelCase for tables and columns, although SQL itself is case-insensitive.This follows a standard naming convention for the database examples.Source: APPENDIX QUERY EXAMPLES · p. 24

• Large-table row-count queries must be run in the slow queue.Source: §1 · p. 24
• COUNT BIG() returns a BIGINT, whereas COUNT() returns an INT limited to 2.14 billion.Source: §1 · p. 24
• COUNT() is recommended unless the query covers large tables and large areas of sky.Source: §1 · p. 24

SELECT COUNT BIG(objID) FROM ObjectThin

• The query joins ObjectThin and MeanObject on objID to retrieve object identifiers, detection counts, coordinates, and mean PSF magnitudes with errors in grizy.It restricts the region to 100.0 < raMean < 100.1 and 0.0 < decMean < 0.1.Source: SELECT COUNT BIG(objID) FROM ObjectThin · p. 25
• 3869 objects are returned by the query.Source: SELECT COUNT BIG(objID) FROM ObjectThin · p. 25
• Most returned objects have been detected only once.Source: SELECT COUNT BIG(objID) FROM ObjectThin · p. 25

• Most objects have 0–2 detections; zero-detection objects appear only in stacks, while objects with few detections may be spurious, moving, or faint.For well-measured astrophysical objects, restrict nDetections; for static-sky stack photometry, join StackObjectThin.Source: §3 · p. 25
• 748 objects remain after selecting griyz mean PSF magnitudes and errors with nDetections > 10 in a 0.1° × 0.1° sky patch, versus 3869 previously.The threshold of 10 detections is adjustable and primarily removes objects with only a few detections.Source: §3 · p. 25

5. Select stack PSF magnitudes for all filters for a rectangular patch of sky

• The rectangular-patch stack query joins ObjectThin and StackObjectThin to retrieve PSF magnitudes and errors in all filters, returning 1808 objects.The example restricts mean coordinates to 100.0 < RA < 100.1 and 0.0 < Dec, without requiring nDetections.Source: §5 · pp. 25–26

6. An example of finding rows with NULL values, using TOP to limit results

• PSPS uses -999 for NULL values, and TOP 10 limits a query selecting objects detected in single exposures but absent from stacks.The query identifies such rows through raStack = -999 while returning positional, detection-count, and mean-photometry fields.Source: §6 · p. 26

7. Basic search using BETWEEN to limit ranges

• A BETWEEN-based query limits RA, Dec, and i-band PSF magnitude while retrieving all-filter stack PSF photometry and errors.It searches 100.0–100.1 in RA, 0.0–0.1 in Dec, and 18.0–21.0 in iPSFMag.Source: §7 · p. 26

8. Using built in functions to do a box search

• ObjectThin’s hierarchical triangular mesh information enables rectangular and radial searches through dbo.fGetObjFromRectEq and dbo.fGetNearbyObjEq.The examples use these functions to select objects in a coordinate box or within 0.2 degrees of a specified position.Source: §8 · p. 26, §9 · p. 26

10. Cone search of high fidelity stellar-like objects

• A high-fidelity stellar query combines a 0.2-degree cone, detections in griz, QfPerfect > 0.85 in every band, primary detections, and a Kron–PSF difference below 0.05.The query stores selected astrometric, photometric, quality, detection, and flag fields in a HighFidelityStarsDR2 table.Source: §10 · p. 26

11. Galaxy Candidates for K2 SN Search

• The KEGS Campaign 14 galaxy query selects nearby candidates with r <= 19.5, QFperfect >= 0.95 in all filters, sufficient g, r, and i detections, and Kron–PSF differences above 0.05.It excludes stars, joins mean, object, Petrosian, and detection-attribute data, and retrieves Petrosian radii for size selection.Source: §11 · p. 27

12. Find the objID of a single object

• A coordinate-based cone query identifies a specific RR Lyrae’s ObjID, after which joins to Detection or ForcedWarpMeasurement provide unforced or forced light curves.The example identifies ObjID 109850463414820867; the individual-detection light curve includes time, flux, quality, calibration, and flag fields, while forced photometry supplies corresponding forced measurements.Source: §12 · p. 27, §13 · p. 27, §14 · pp. 27–28

Object / Mean Object Tables

• The Object/Mean Object tables provide positions, detection counts, and stack associations, while MeanObject stores per-filter PSF, Kron, and aperture statistics for individually detected sources.ObjectThin supplies stack and single-epoch coordinates; MeanObject requires at least one individual-exposure detection.Source: Table 22 · p. 34, Object / Mean Object Tables · p. 34, Table 23 · p. 35

Stack Tables

• Stack tables provide extended-source photometry, Petrosian measurements, stack-input mappings, processing metadata, and stacked-image detection efficiencies.Aperture fluxes use SDSS radii after convolution to 6 or 8 sky pixels and are limited to extragalactic fields; other tables record Petrosian quantities and stack construction or efficiency information.Source: Table 33 · p. 43, Table 34 · p. 44, Table 35 · p. 45

Single Exposure Detection Tables

• Single-exposure detection tables contain frame and image metadata, individual-detection photometry, and image-level detection-efficiency measurements.Detection records include PSF, aperture, and Kron photometry with sky and detector coordinates, while metadata describes calibration, image quality, and processing.Source: Table 39 · p. 47, Table 40 · p. 48, Table 41 · p. 49

Forced Mean Object Tables

• Forced-mean object tables provide mean lensing parameters from forced photometry and metadata describing the calibrated warp images used for those measurements.The warp metadata includes astrometric and photometric calibration, while the lensing table records Kaiser et al. (1995) parameters.Source: Table 44 · p. 52, Table 45 · p. 53, Forced Mean Object Tables · p. 53

Forced warp Exposure Tables

• Forced-warp exposure tables record single-epoch forced measurements, masked cases, extended-source aperture fluxes, lensing parameters, and mappings to input images.Forced measurements include PSF, aperture, and Kron photometry, while extended-source fluxes use SDSS R5, R6, and R7 apertures.Source: Table 46 · p. 54, Table 47 · p. 55, Table 48 · p. 55

Forced Galaxy Tables

• Forced galaxy tables provide per-warp extended-source shape parameters with all filters matched into a single row.Positions, magnitudes, fluxes, and Sersic indices come from StackModelFit measurements, while axes and orientation are recalculated for each warp.Source: Table 51 · p. 57

Diff Object Tables

• Difference-object tables associate difference detections within one arcsecond and list positional information, per-filter detection counts, and coverage fractions.Difference metadata describes images formed by subtracting stacks from single exposures or nightly stacks and records the reference-stack astrometric calibration.Source: Table 52 · p. 58, Table 53 · p. 59, Diff Object Tables · p. 59

Diff Detection Tables

• Difference-detection tables contain individual difference-image photometry, source associations, coordinate positions, positive-flux diagnostics, sky measurements, and processing flags.The table includes PSF, aperture, and Kron photometry plus positive/negative-source distances and signal-to-noise values.Source: Table 54 · p. 60, Diff Detection Tables · p. 60, Table 55 · p. 61

IPPTOPSPS TRANSLATION TABLES

• IPPTOPSPS translation tables document the source columns and formulas for ObjectThin, MeanObject, and StackObjectThin.Their provenance includes DVO cpt or cps files, IppToPsps, PSPS, and selected currently unused columns.Source: Table 57 · p. 62, Table 58 · p. 63, Table 59 · p. 63