Open Source PolarDB Uses pg_rational Extension to Support Stern-Brocot Trees

This article describes how open source PolarDB uses pg_rational extension to support Stern-Brocot trees to efficiently customize and adjust the order.

By digoal

Background

PolarDB's is a cloud-native database that features a computing-storage separation architecture. It offers affordable data storage, efficient scalability, high-speed parallel computing capabilities, and fast data search and processing. By combining PolarDB with computing algorithms, it enables the conversion of data into productivity and drives business data output.

This article describes how open source PolarDB uses pg_rational extension to support Stern-Brocot trees to efficiently customize and adjust the order.

The test environment is macos + docker. For more information about deploying PolarDB, see:

Simple Deployment of PolarDB

pg_rational for PolarDB

The pg_rational extension uses Stern-Brocot trees to find a valid intermediate point as the score for the lowest item. It can continue to make deeper splits between scores according to the needs of any practical application. Efficiently customize and adjust the order.

pg_rational features:

• Stores fractions in exactly 64 bits (same size as float)

• Written in C for high performance

• Detects and halts arithmetic overflow for correctness

• Uses native CPU instructions for fast overflow detection

• Defers GCD calculation until requested or absolutely required

• Supports btree and hash indices

• Implements Stern-Brocot trees for finding intermediate points

• Coercion from integer/bigint/tuple

• Custom aggregate

1. Install pg_rational

git clone --depth 1 https://github.com/begriffs/pg_rational  
  
cd pg_rational/  
  
USE_PGXS=1 make  
  
USE_PGXS=1 make install  
  
export PGHOST=127.0.0.1  
  
USE_PGXS=1 make installcheck  
/home/postgres/tmp_basedir_polardb_pg_1100_bld/lib/pgxs/src/makefiles/../../src/test/regress/pg_regress --inputdir=./ --bindir='/home/postgres/tmp_basedir_polardb_pg_1100_bld/bin'      --dbname=contrib_regression pg_rational_test  
(using postmaster on 127.0.0.1, default port)  
============== dropping database "contrib_regression" ==============  
DROP DATABASE  
============== creating database "contrib_regression" ==============  
CREATE DATABASE  
ALTER DATABASE  
============== running regression test queries        ==============  
test pg_rational_test             ... ok  
  
  
==========================================================  
 All 1 tests passed.   
  
 POLARDB:  
 All 1 tests, 0 tests in ignore, 0 tests in polar ignore.   
==========================================================

2. Load the pg_rational extension

psql  
psql (11.9)  
Type "help" for help.  
  
postgres=# create extension pg_rational;  
CREATE EXTENSION

3. Basic operations: pg_rational can be converted to floating-point and integer types.

-- fractions are precise  
-- this would not work with a float type  
select 1::rational / 3 * 3 = 1;  
-- => t  
  
-- provides the usual operations, e.g.  
select '1/3'::rational + '2/7';  
-- => 13/21  
  
-- helper "ratt' type to coerce from tuples  
select 1 + (i,i+1)::ratt from generate_series(1,5) as i;  
-- => 3/2, 5/3, 7/4, 9/5, 11/6  
  
-- simplify if desired  
select rational_simplify('36/12');  
-- => 3/1  
  
-- convert float to rational  
select 0.263157894737::float::rational;  
-- => 5/19  
  
-- convert rational to float  
select '-1/2'::rational::float;  
-- => -0.5

4. Adjust the order test, do not specify the value, and only specify which two rational values you want to insert between. pg_rational extension uses the Stern-Brocot trees to find a valid intermediate point as the score of the lowest item. This enables fast order adjustment.

postgres=# create sequence todos_seq;  
CREATE SEQUENCE  
  
  
postgres=# create table todos (  
  prio rational unique  
    default nextval('todos_seq')::float8::rational,  
  what text not null  
);  
CREATE TABLE  
postgres=# insert into todos (what) values  
postgres-#   ('install extension'),  
postgres-#   ('read about it'),  
postgres-#   ('try it'),  
postgres-#   ('profit?');  
INSERT 0 4  
postgres=#   
  
-- put "try" between "install" and "read"  
  
postgres=# select * from todos order by prio asc;  
 prio |       what          
------+-------------------  
 1/1  | install extension  
 2/1  | read about it  
 3/1  | try it  
 4/1  | profit?  
(4 rows)  
  
-- put "read" back between "install" and "try"  
  
postgres=# update todos  
postgres-# set prio = rational_intermediate(1,2)   -- 1为install extension, 2为read about it. 根据Stern-Brocot 树找到1,2之间的3/2分数.   
postgres-# where prio = 3;  
UPDATE 1  
postgres=# select * from todos order by prio asc;  
 prio |       what          
------+-------------------  
 1/1  | install extension  
 3/2  | try it  
 2/1  | read about it  
 4/1  | profit?  
(4 rows)  
  
postgres=# update todos  
postgres-# set prio = rational_intermediate(1,'3/2')   -- 1为install extension, 3/2为try it. 根据Stern-Brocot 树找到1,3/2之间的4/3分数.   
postgres-# where prio = 2;  
UPDATE 1  
postgres=# select * from todos order by prio asc;  
 prio |       what          
------+-------------------  
 1/1  | install extension  
 4/3  | read about it  
 3/2  | try it  
 4/1  | profit?  
(4 rows)

References

https://github.com/begriffs/pg_rational

Community

Open Source PolarDB Uses pg_rational Extension to Support Stern-Brocot Trees

Background

pg_rational for PolarDB

References

Read previous post:

Read next post:

digoal

You may also like

Comments

digoal

Related Products

PolarDB for MySQL

PolarDB for PostgreSQL

PolarDB for Xscale

Managed Service for Prometheus