pgSphere and Q3C on Distributed SQL

To explore YugabyteDB’s capabilities in handling spherical and astronomical datasets, I recently tested two PostgreSQL extensions: pgSphere and Q3C. These extensions are designed for advanced spherical geometry and spatial queries, particularly in astronomy. YugabyteDB’s distributed architecture adds resilience and scalability to PostgreSQL and remains Open Source.

Here’s a breakdown of my steps to install, configure, and test these extensions on YugabyteDB. I use a Docker container for this lab, and the same can be used to download the extension files that were built and install them into a productive environment.

Step 1: Launching a YugabyteDB Docker Container

To start, I ran a Docker container using the YugabyteDB image. This isolated environment was used to build and test the necessary extensions.

cd /var/tmp
mkdir build
docker run --rm -it -v ./build:/build yugabytedb/yugabyte:2.23.0.0-b710 bash

I'll package the extension's files into the /build external directory to ship it to another YugabyteDB deployment.

Step 2: Installing Development Tools and Dependencies

The container runs AlmaLinux OS 8. Since compiling PostgreSQL, pgSphere, and Q3C requires development tools, I installed the necessary packages within the container using the following commands:

dnf update -y
dnf groupinstall -y 'Development Tools'
dnf install -y git unzip wget libicu-devel openssl-devel readline-devel make gcc zlib-devel

Step 3: Installing PostgreSQL 11

YugabyteDB’s extensions are PostgreSQL-compatible, so I downloaded and compiled PostgreSQL 11 to build pgSphere and Q3C. This process was faster than building them with the YugabyteDB sources.

cd /var/tmp
wget https://ftp.postgresql.org/pub/source/v11.22/postgresql-11.22.tar.gz
tar -zxvf postgresql-11.22.tar.gz
cd postgresql-11.22
./configure
make
make install
export PATH=$PATH:/usr/local/pgsql/bin

Step 4: Compiling and Installing Q3C

Next, I installed Q3C, which allows for fast, efficient querying of celestial objects using Right Ascension (RA) and Declination (DEC). I cloned the Q3C repository and compiled the extension.

git clone https://github.com/segasai/q3c.git
cd q3c
make
make install

Step 5: Compiling and Installing pgSphere

pgSphere adds spherical data types and geometric functions to PostgreSQL. I cloned the pgSphere repository and compiled it to install it, ensuring the correct environment was in place. I disabled HEALPix as I didn't install those libraries.

cd /var/tmp
git clone https://github.com/postgrespro/pgsphere.git
cd pgsphere
make USE_HEALPIX=0  
make install

Step 6: Packaging the Extensions

After compiling the extensions, I packaged the files for installation into the YugabyteDB environment. I compared the new files with those existing ones under the YugabyteDB deployment to add only the new ones.

cd /usr/local/pgsql
rm -rf bin include ./share/timezone  # Remove unneeded files
tar -cvf /build/extensions.tar $(
 for f in $( find . ); do [ -r $YB_HOME/postgres/"$f" ] || echo "$f"; done 
)

Step 7: Installing the Extensions in YugabyteDB

I then extracted the extension files into the YugabyteDB directory to integrate them with YugabyteDB’s PostgreSQL-compatible layer.

cd $YB_HOME/postgres
tar -xvf /build/extensions.tar

This can be installed on any YugabyteDB node. I'm using my container to test a few queries.

Step 8: Testing the Setup

With the extensions installed, I tested the setup by starting YugabyteDB and verifying that both pgSphere and Q3C were successfully loaded.

yugabyted start
yugabyted status
yugabyted connect ysql

  CREATE EXTENSION pg_sphere;
  CREATE EXTENSION q3c;
  SELECT pg_sphere_version(), q3c_version() ;

The creation of the extension confirms the presence of the files, while the execution of functions demonstrates the usability of the libraries. Here is my output:

Step 9: Running Sample Queries with Q3C and pgSphere

I generated some code with ChatGPT to test some random functionalities of Q3C and pgSphere. If you test it with your application, I'll be happy to have some feedback.

Testing Q3C

I created a table to store stars' Right Ascension (RA) and Declination (DEC) and inserted a set of stars. Then, using Q3C's efficient spatial functions, I ran a query to find stars within a specific angular distance.

CREATE TABLE star_catalog (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    ra DOUBLE PRECISION,       
    dec DOUBLE PRECISION       
);

INSERT INTO star_catalog (name, ra, dec) VALUES
('Sirius', 101.28715533, -16.71611586),
('Canopus', 95.9878775, -52.6956617);

SELECT name, ra, dec
FROM star_catalog
WHERE q3c_radial_query(ra, dec, 101.28715533, -16.71611586, 10);

CREATE INDEX ON star_catalog ( q3c_ang2ipix(ra, dec) ASC)
 INCLUDE (id, name, ra, dec);

SELECT * FROM star_catalog order by q3c_ang2ipix(ra,dec);

I can check the execution plan to be sure that the index is used:

Testing pgSphere

I also tested pgSphere by creating a table with spherical points and regions representing celestial objects. Using pgSphere functions, I queried objects based on their position within a spherical region.

CREATE TABLE celestial_objects (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    pos spoint,           
    region sbox           
);

INSERT INTO celestial_objects (name, pos, region) VALUES
('New York', spoint(-1.2915436, 0.7105724), sbox(spoint(-1.4, 0.6), spoint(-1.1, 0.8)));

SELECT name, pos
FROM celestial_objects
WHERE scircle(spoint(2.438080, 0.622446), 0.5) @> pos;

PgSphere can use GiST indexes, but YugabyteDB doesn't support them yet. This is tracked in #1337

Conclusion

I have installed and tested pgSphere and Q3C on YugabyteDB, and have successfully validated its ability to handle spherical data and astronomical datasets. Once it is operational, YugabyteDB can distribute the tables and indexes, allowing it to scale. With a replication factor of three, it is resilient to node failure.