I was learning and playing with both PostGIS and pgSphere to see what option can work better for storing geometry data in ObsCore table. Here is a brief summary what I have learned so far.
This is an advanced and well-supported project for PotgreSQL-based GIS. It is a separate PostgreSQL extension that needs to be installed as a system package and added to a database or schema. GIS mostly cares about more or less precise description of features located on the surface of Earth (or maybe other planets), and not what happens in the sky. Earth of course is not a spherical object, and a lot of effort in PostGIS is devoted to supporting the non-ideal Earth shape. PostGIS implements SQL/MM standard for spatial data management, with some extensions.
PostGIS has two distinct types for spatial data - geometry and geography. Geometry type is used for Cartesian coordinates, and is typically used for small enough patches of the surface where curvature can be ignored. Geography type is used for long distances where it is necessary to take curvature into effect for correct calculations. The shape of the Earth (or a small patch of it) is determined by a Spatial Reference System (SRS), all spatial objects are associated with some SRS (with some exceptions). PostGIS supports user-defined SRS, and potentially one can define completely spherical planet shape, project sky map on its surface, and use that projection for all calculations using geography type.
PostGIS geography uses lon/lat ranges [-180, 180] and [-90, 90], while ra range is [0, 360]. When a geography type receives data outside its allowed range it coerces the data into that range. If coercing ra/dec to lon/lat is done consistently, then PostGIS should still be able to produce correct answer for ra/dec. Even for geography type the calculations that produce distances return their result in meters, to obtain angular separation it needs to be converted to degrees. One possible workaround is to project on a surface where 1 degree makes one meter, a small "planet" with a radius of 57.29577951308232 meters.
PostGIS' set of shapes include POINT, LINE, POLYGON, and collections of those (e.g. MULTIPOLYGON), but no circle or ellipses. There is a large set of functions for all kinds of calculations and transformations, geography types only support a subset of the functions. Indexing of the spatial columns is supported with GiST, BRIN, or SP-GiSt type of index.
The support and development of pgSphere looks patchy, it is certainly a smaller project, used and supported by VO folks. The "official" repository at https://github.com/akorotkov/pgsphere had the latest commit from almost three years ago. Ubuntu 22.04 has a package for pgsphere version 1.1.5 which seem to be the latest pgSphere version number. Creating pgSphere extension for a database requires superuser privileges (true for PostGIS as well).
pgSphere is built specifically for use in astronomy, and uses lon/lat coordinate system with radians units in ranges [0, 2π] and [-π/2, π/2]. On input the coordinates can be specified as degrees (e.g. spoint '(90d, 45d)'), though functions that return distances always return radians.
Supported geometry types include POINT, LINE, PATH, CIRCLE, ELLIPSE, POLYGON, BOX, but there is no support for composite geometries (except for PATH). pgSphere supports a number of basic functions and operators, but not all of them work on every type of geometry, e.g. distance calculation only works for points and circles. Indexing of the spatial columns is supported using GiST index.
The choice between the two options may depend on what kind of spatial queries we need to support, and what needs to be done on TAP side for transforming ADQL into either PostGIS or pgSphere. If we need to be able to calculate distances between polygons and points (e.g. for cone search) then PostGIS seems to be the only answer (extending pgSphere is another option of course). (And I think I also heard that TAP already has something for pgSphere).