1. Overview

In this blog post, we will guide you through the process of integrating IvorySQL, an open-source database built on PostgreSQL, into Neon Autoscaling Platform. Throughout this guide, we’ll walk you through each step, providing clear instructions and demonstrations.

2. What is IvorySQL

“IvorySQL is advanced, fully featured, open source Oracle compatible PostgreSQL with a firm commitment to always remain 100% compatible and a Drop-in replacement of the latest PostgreSQL. IvorySQL adds a “compatible_mode” toggle switch to switch between Oracle and PostgreSQL compatibility modes. ” [1]

The general architecture of IvorySQL is shown below. For more details, you can refer to here.

IvorySQL Key features:

• Powered by PostgreSQL
  Leveraging the robust foundation of PostgreSQL, IvorySQL inherits its comprehensive SQL capabilities, rock-solid reliability, and access to a vast ecosystem of tools and extensions.
  
  
• Oracle Compatibility
  IvorySQL introduces a unique “compatible_mode” toggle switch, facilitating effortless transitions between Oracle and PostgreSQL compatibility modes. It supports Oracle’s PL/SQL syntax and makes migration from Oracle databases a smooth process.
  
  
• Customization
  Users have the freedom to customize IvorySQL to suit their specific requirements. Simply download the source code and tailor it to your preferences.
  
  
• Open Source
  As an Apache 2 licensed project, IvorySQL is freely available for download and use, fostering a collaborative development environment.
  
  
• Open Community
  A creative and inclusive community that encourages collaboration and innovation.
  
  
• Backed by HighGo
  Supported by HighGo, a leading provider of PostgreSQL databases, IvorySQL benefits from expert guidance and support to ensure its continued excellence and reliability.
  

In general, IvorySQL continues to add support for Oracle-compatible SQL syntax and data types, making migration from Oracle to Postgres easier.

3. What is Neon Autoscaling

“Neon’s Autoscaling feature, available to paying users, dynamically adjusts the amount of compute resources allocated to a Neon compute endpoint in response to the current load, eliminating the need for manual intervention.” [2]

The basic architecture of Neon Autoscaling is shown below. For more details, you can refer to here.

Autoscaling Key features:

• Live Migration
  When a node becomes saturated in Kubernetes cluster, NeonVM manages the process of live migrating a VM, transferring the VM from one machine to another with minimal interruptions (typically around 100ms). The live migration process allows for the proactive reduction of node load by migrating VMs away before reaching capacity.
  
  
• Memory Scaling
  Dynamically adjusts Postgres memory usage using cgroups, efficiently requesting additional resources from the autoscaler-agent in Kubernetes cluster when needed to maintain optimal performance.
  
  
• Local File Cache
  Utilizes a Postgres extension for a local file cache, accelerating query processing by leveraging additional VM memory and dynamically adjusting cache size for seamless operation during scaling events.
  

In general, Neon Vertical Autoscaling can manage the performance of Postgres-based compute nodes built in NeonVM by dynamically allocating and deallocating vCPU and RAM.

4. Bringing IvorySQL to Neon Autoscaling Platform

In this section, we will guide you through the process of adding the IvorySQL 3.2 release to the Neon Autoscaling platform. Before we begin, run the following commands to check out the autoscaling source code from here.

git clone https://github.com/neondatabase/autoscaling.git
cd autoscaling
git checkout v0.28.1 -b IvorySQL3.2-autoscaling

Then follow the procedure below to make the changes on autoscaling release tag v0.28.1.

Create IvorySQL vm example

Create IvorySQL VM example by duplicating “pg16-disk-test” in the “vm-examples” directory, naming it “ivy3-disk-test”, and making the main changes to “image-spec.yaml”. The file after all changes is available at here.

Create IvorySQL deployment file

Create an IvorySQL deployment file by making a copy of “vm-deploy.yaml” and naming it “vm-deploy-ivy3.yaml”. Then, apply the necessary modifications by referring to the file at here.

Add ivy3-disk-test to Makefile

Add “ivy3-disk-test” to the Makefile and incorporate the necessary changes to enable building the “ivy3-disk-test” image using the command “make ivy3-disk-test”.

Load liboracle_parser library

To load the “liboracle_parser” library, add the following line to the end of “postgresql.conf“: shared_preload_libraries = ‘liboracle_parser’.

If you want to skip all the steps mentioned above, you can simply run below commands:

git clone https://github.com/HighgoSoftware/autoscaling-ivorysql.git
cd autoscaling-ivorysql
git checkout IvorySQL3.2

Setup a local Kubernetes cluster using kind

Set up a local Kubernetes cluster using kind. Below is a brief overview of the process to set up vertical autoscaling using kind; for more details, please refer to here.

1) Build NeonVM Linux kernel:
make kernel

2) Build docker images:
make docker-build

3) Start local cluster with kind:
make kind-setup

4) Deploy NeonVM and Autoscaling components:
make deploy

5) Build and load the test VM:
make pg16-disk-test

6) Start the test VM:
kubectl apply -f vm-deploy.yaml

7) Running pgbench:
VM_NAME=postgres16-disk-test scripts/run-bench.sh

Experience Autoscaling with IvorySQL

If you have successfully autoscaled “postgres16” following the above procedure, then run the following three commands to experience the autoscaling of IvorySQL 3.2:

make ivy3-disk-test
kubectl apply -f vm-deploy-ivy3.yaml 
VM_NAME=ivorysql3-disk-test scripts/run-bench.sh

After a while, you should see the transactions per second (tps) automatically increase, as shown below.

If you want to scale down the compute resource, you can use the following patch command:

kubectl patch neonvm ivorysql3-disk-test --type='json' -p='[{"op": "replace", "path": "/spec/guest/cpus/use", "value":0.25}]'

After a while, you should be able to observe the transactions per second (tps) decreasing, as shown below.

In addition to the patch command, you can also use the edit command ‘kubectl edit neonvm ivorysql3-disk-test’ to scale up or down the IvorySQL compute node. Simply change the value of ‘use’ under the ‘cpus’ section as shown below:

spec:
    enableAcceleration: true
    guest:
        cpus:
            max: 1250m
            min: 250m
            use: 250m

Experience Oracle compatibility with IvorySQL

To avoid using complicated Kubernetes commands, you can log in to the VM running IvorySQL using the serial console with screen. Below is an example of how to log into the IvorySQL VM, connect to IvorySQL, check the version, and test Oracle-compatible features.

IVY_VM=$(kubectl get neonvm ivorysql3-disk-test -ojsonpath='{.status.podName}')

kubectl exec -it $IVY_VM -- screen /dev/pts/0

The screenshot above shows that IvorySQL 3.2 is running on the Neon Autoscaling Platform and support the sysdate query using Oracle DUAL Table. You can refer to the IvorySQL Developer Guide to explore more Oracle compatible features.

5. Summary

This blog post provided a guide on integrating IvorySQL, an open-source database built on PostgreSQL, into the Neon Vertical Autoscaling Platform. Through detailed instructions and demonstrations, hopefully you learned how to incorporate a PostgreSQL-based database into Neon autoscaling environment and experience the Oracle-compatible features provided by IvorySQL.

6. Future work

In the future, we are considering:

1. Merging Postgres-16 changes from Neon to IvorySQL as a Serverless Branch.
2. Making IvorySQL3 a Submodule of the Neon repo.
3. Building the IvorySQL3 Compute Node with a Separated Storage Node.

This will allow end users to specify IvorySQL compute nodes beyond PostgreSQL 14/15/16 to enjoy the Oracle-Compatible Features.