The European Space Agency (ESA) is an intergovernmental organization of 22 member states dedicated to the exploration of space for the citizens of the world. A crucial part of ESA’s efforts is collecting and putting massive amounts of data to good use.
ESA’s Science Data Center (ESDC) is tasked with archiving the data from ESA missions into a Digital Library of the Universe. This universal archive serves as a repository of every mission history, hosting data for all research needs with the main goal of serving the public and scientific communities. ESDC makes this archive available by providing scientific teams worldwide privileged access to this data.
In the past, ESA used legacy database systems like Oracle, Sybase, DB3, and SQL Server. The Science Data Center team wanted to use an open-source database to take advantage of the collective knowledge and experience, cross-reference datasets between missions via SQL, and leverage a broad ecosystem of tools, connectors, and libraries. At first glance, PostgreSQL was an obvious candidate. It can handle relational and unstructured data really well. But the team had to look to the larger PostgreSQL ecosystem for geospatial and time-series data for help.
ESA found Timescale more performant and also easier to use than vanilla PostgreSQL. Timescale transparently and automatically partitions time-series data but then hides all that partition complexity behind an abstraction layer called a hypertable. This removes the need to implement table inheritance or declarative partitioning manually and lets one effectively store billions of rows of data within one virtual table.