Korotkov Alexander

Abstract Reference: 30751
Identifier: O7.3
Presentation: Oral communication
Key Theme: 2 Management of Scientific and Data Analysis Projects 

Spatial indexes in PostgreSQL for astronomy

Authors:
Korotkov Alexander, Bartunov Oleg, 

PostgreSQL is the worlds's most advanced open source database, which is extensively used in astronomy to store and query large volumes of spatial data. It is a general-purpose RDBMS with strong support of extendability, so data domain experts could develop the full-fledged

custom data types with queries and operators without deep knowledge of database core. PostgreSQL was introduced to astronomical community in ADASS in 2003 year and it's worth to review the new features of PostgreSQL from the astronomical point of view. We will briefly outline the current status of PostgreSQL and its road map to inform astronomical community which important features are available right now and what to expect in the closest future. Scalability of modern PostgreSQL on high-end multi-core servers, parallel query execution, distributed transactions, JIT-compilation, support of pluggable storages (im-memory, vertical) - these features are what currently is being discussed in postgres hackers conferences.

We review the existing implementations of spherical data support available for PostgreSQL in Q3C, pgSphere (old and new versions), PostGIS etc and compare their performance in several use cases -- from bulk loading to range queries to live update. We demonstrate how different index types (B-tree in Q3C, GiST+R-tree in pgSphere and PostGIS) could be used to optimize data handling for specific problems.