REST Data Service on YugabyteDB / PostgreSQL
Tags: postgres, yugabytedb, rest, sql
Published: 2022-02-07 21:22:47

In this series I defined an API Rate Limiter with a simple table + function. I still have the table `rate_limiting_token_bucket` and function `rate_limiting_request` created, you can find their definitions in [Part 2](https://dev.to/yugabyte/rate-limiting-with-postgresql-yugabytedb-token-buckets-function-5dh8). To call it, I'd created a small Java program in [Part 3](https://dev.to/aws-heroes/optimistic-or-pessimistic-locking-for-token-buckets-rate-limiting-in-postgresql-4om5) but we don't need it here. I'll call my function from a **REST API** and this very simple to implement, with no code. I'll use [PostgREST](https://postgrest.org/en/stable/index.html), a _standalone web server that turns your PostgreSQL database directly into a RESTful API_. And what works on PostgreSQL can also run on YugabyteDB. I have a 3 nodes cluster setup and I'll install **PostgREST** directly on each node, connecting to the local endpoint. YugabyteDB is a distributed SQL database, you can connect to any node and see the whole database because there is a stateless PostgreSQL on top of the distributed storage and transaction layer.

## The simplest install ever
The install is easy, just check the [latest release](https://github.com/PostgREST/postgrest/releases) and it extracts to a single executable:
```bash
curl -qsL https://github.com/PostgREST/postgrest/releases/download/v9.0.0/postgrest-v9.0.0-linux-static-x64.tar.xz | \
tar -xJvf -

```
This creates a 16MB `postgrest` binary in the current directory.

## The simplest config ever

Better than an online help, the executable can generate a configuration file template ready to customize:
```bash
./postgrest -e > my_rest_configuration.conf
```
You can update this configuration, defining at least the connection url `db-uri` in the libpq format.

But I'll do even simpler for this demo, using environment variables only:
```bash
PGRST_DB_URI="postgres://yugabyte:yugabyte@$(hostname):5433/yugabyte" \
PGRST_DB_SCHEMAS="public" PGRST_DB_ANON_ROLE="yugabyte" \
PGRST_DB_CHANNEL_ENABLED=false ./postgrest &

```
This starts the PostgREST server with  a pool of connections to the local endpoint, database "yugabyte", schema "public". I disabled the "DB Channel" because LISTEN/NOTIFY is not enabled in YugabyteDB (this mecanism is not appropriate for a distributed database).

If you created the procedure after starting the PostgREST deamon, you need to refresh the cache with: `pkill -10 postgrest` to signal for cache re-load. 

I'm keeping it simple for this demo, but of course, it can be configured for performance and security. With all defaults, it listens to port 3000.

## REST calls

I can now use this REST proxy to call my rate limiting function.

There is nothing more to define because PostgREST reads the schema. The namespace of the stored procedures and functions is exposed in `http://localhost:3000/rpc` and the parameters are passed as a JSON document. Here is a call getting a token for "_user1_" with a refill rate of 1 token per second:

```bash
echo $( 
curl -qs \
http://localhost:3000/rpc/rate_limiting_request \
-X POST -H "Content-Type: application/json" -d '
{ "rate_id": "user1", "refill_per_sec": 1 }
')
```
Here is the result:
![curl result](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/k19r193inv0r0vrs9ylp.jpg)
I have 60 tokens remaining because my function has a default window of 60 seconds, filled with the rate of 1 tocken per second.

## Threads

From my Java program and with this cluster, I was able to get 1000 tokens per second from threads requesting tokens from different users and 120 per second. Here is a python script to do the same though the REST API:

```
import threading
import time
import requests
import sys

def request_tokens(id,num):
 tokens=0
 start = time.time()
 for i in range(1,num):
  response=requests.post('http://localhost:3000/rpc/rate_limiting_request', json={ "rate_id": id, "refill_per_sec": 1000000 })
  if response.status_code==200:
   tokens=tokens+1
  print("{0} tokens/s".format(tokens/(time.time()-start)))

threads=[]
for i in range(0,int(sys.argv[1])):
 threads.append(threading.Thread(target=request_tokens,args=(i,1000)))
for t in threads:
    t.start()
total=0
for t in threads:
    t.join()
```
This runs 1000 loops in 50 thread:
```
python franck.py 50
```
What is interesting is that the overhead is very small:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/536nayurb0k7pfbvp8cu.jpg)
On this node, the `postgrest` deamon uses less CPU than the calling program `python` and the database itself `yb-tserver`. You can see 10 `postgres` backends because PostgREST has a connection pool, which is the best to guarantee predictable performances.

The beauty of it is that it is easy to install one `postgrest` on each database node, opening a REST access for the database schemas.