Brazilian "Rinha de Backend" challenge #2 - The Improvement
Tags: rinhadebackend, javascript, node, postgres
Published: 2023-08-30 12:27:22

> Part #2 of a small series `Brazilian "Rinha de Backend" challenge`. Click [here](https://dev.to/lukas8219/brazilian-rinha-de-backend-challenge-1-how-to-fail-13n4) to part 1

15th of August, 7AM.

I woke up, took a long breakfast, sitting at my office and started tackling the _ghost_ that spent the night before haunting me. (In this case, my brain + overthinking)

Some of his tips, thou, did really help!
- Fixing these two queries([1](https://github.com/lukas8219/rinha-be-2023-q3/commit/7e0d606bcc5550a07976d15dae8e71d85b011a1e#diff-c79e7bf3fe4949f3acfa89d636f82f6e794a2348c0c25763fd5fcb05a3380bb9R52),[2](https://github.com/lukas8219/rinha-be-2023-q3/commit/7e0d606bcc5550a07976d15dae8e71d85b011a1e#diff-c79e7bf3fe4949f3acfa89d636f82f6e794a2348c0c25763fd5fcb05a3380bb9R97)) business rules
- [Adding more replicas](https://github.com/lukas8219/rinha-be-2023-q3/commit/f16c2fc9d4b5d8c44edd50dd578fb70688491aa8#diff-1f91f64bfa3fb0a1ece881887af0a2356b8c529a96fb91c1894745b2a1a009aaR12) into NGINX and DockerCompose
- Fine tuning [resources](https://github.com/lukas8219/rinha-be-2023-q3/commit/f16c2fc9d4b5d8c44edd50dd578fb70688491aa8#diff-3fde9d1a396e140fefc7676e1bd237d67b6864552b6f45af1ebcc27bcd0bb6e9R14) on DockerCompose
- Fixing PINO [async logging](https://github.com/lukas8219/rinha-be-2023-q3/commit/7e0d606bcc5550a07976d15dae8e71d85b011a1e#diff-ed4a110a094ac014f4bea8d6d93719d89d5e5a2d1974dc2f79e41faf30af470aR4)
- Configuring NGINX [worker connections](https://github.com/lukas8219/rinha-be-2023-q3/commit/7e0d606bcc5550a07976d15dae8e71d85b011a1e#diff-1f91f64bfa3fb0a1ece881887af0a2356b8c529a96fb91c1894745b2a1a009aaR4)


![68 failure](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/7v4j81s36e2afo7ucyzu.png)

I was able to get some good improvements reaching ~31% of `success` which really sounded way better than 10%. But man, something was really off. 

## _**Why did my application performed so bad?**_

Well. I had forgotten to address yesterday's finding. The _database bottleneck_.

First thing I did was to copy [the database query](https://github.com/lukas8219/rinha-be-2023-q3/commit/b374c30cc4d0f0c302378a21ead5debf1ec6fcbd#diff-c79e7bf3fe4949f3acfa89d636f82f6e794a2348c0c25763fd5fcb05a3380bb9R86) and ran it against at least 40K patients. Surprisingly, it took _50 seconds_ even thou I was **certain** I had indexes setup.

_But before I explain the database optimization I did I need to explain the structure I had before the improvement._

```sql
CREATE TABLE IF NOT EXISTS pessoas (
     id SERIAL PRIMARY KEY,
     apelido VARCHAR(32) UNIQUE NOT NULL,
     nome VARCHAR(100) NOT NULL,
     nascimento DATE NOT NULL,
     stack VARCHAR(32)[]
);
    
CREATE INDEX IF NOT EXISTS term_search_index_apelido ON pessoas USING gin(to_tsvector('english', apelido));
          
CREATE INDEX IF NOT EXISTS term_search_index_nome ON pessoas USING gin(to_tsvector('english', nome));
```

The best tool to analyze what steps the database took to run the query (and much more) is the [EXPLAIN](https://www.postgresql.org/docs/current/sql-explain.html).
And of course I had _forgotten_ a index. But how I would index a ARRAY field for FTS? That was a bad design choice. Arrays can't be text indexed, the performance is kinda bad for this use-case.

Well, let's ship this [responsibility to the client](https://github.com/lukas8219/rinha-be-2023-q3/commit/f1a3c2c8aac01bf30bac38f23cc9335f8752d5e8#diff-c79e7bf3fe4949f3acfa89d636f82f6e794a2348c0c25763fd5fcb05a3380bb9R71). The `stack` field [became a JSON field](https://github.com/lukas8219/rinha-be-2023-q3/commit/f1a3c2c8aac01bf30bac38f23cc9335f8752d5e8#diff-c79e7bf3fe4949f3acfa89d636f82f6e794a2348c0c25763fd5fcb05a3380bb9R21) which the client deserializes and serializes. The JSON field is text-indexed and voilá, we now have a index for stack field.

Run the query again: Less than 20ms. Cool. That's what's expected.


![after indexes](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/trewp8ub58ijgleyk9le.png)

Not surprisingly I reached 45% of `success` and 27% of `>1200ms` requests. My database lowered CPU usage and errors like premature connection, timeouts and connection losses only appeared by the middle-to-end of the stress test.
Database-wise I thought that _It was as far as I could get_. 


![mid to end failover](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/0hbpf8mgiqulnavoui48.png)

## It's time for cache.

I decided to use Redis instead of LRU memory caches since I had distributed applications and I was aiming to have at least 4-5 replicas which would make the same resource not to frequently requested on the same pod/container.

### The caching strategy

- /pessoas?t
 - Cached the response.
- POST: /pessoas
 - Cached the the entire resource by ID after creation
 - Cached the `apelido` field since it had a unique constraint on the database.
- GET: /pessoas/:id
 - Checked cache before hitting database.
- On the validation middleware I had setup([middleware.js](https://github.com/lukas8219/rinha-be-2023-q3/commit/f1a3c2c8aac01bf30bac38f23cc9335f8752d5e8#diff-e7145ea3e4f5db1f4bc1c5be397238ee0c522c81afa6a7b8228261f54aa4e704R79))
 - Checked if person already existed on Redis SET and if not checked database. If it did exists, we updated the cache and returned the response back to the client

That is a rather simple Redis caching setup.
_(Which I would never ship to production environments at least)_


![caching results](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/1nk82we9dgcqc3c5jv3t.png)

Finally an acceptable `success` rate! 92% of `success` being 4% of requests above 1200ms.

But still...That database CPU usage _**too**_ high even with caching and the connection closes was still a thing.

## Back Into DB Optimizations

I figured I had a way over the top [PG connection pool configured](https://github.com/lukas8219/rinha-be-2023-q3/commit/05302873d38cce4f7417bc56de1a2bf9617a5462#diff-c79e7bf3fe4949f3acfa89d636f82f6e794a2348c0c25763fd5fcb05a3380bb9R8).

And it was kinda reasonable given that we were running 3-4 replicas, each one with 8 connection on a database that had less than 1 CPU allocated.

(I've also reduce to [3 replicas](https://github.com/lukas8219/rinha-be-2023-q3/commit/05302873d38cce4f7417bc56de1a2bf9617a5462#diff-1f91f64bfa3fb0a1ece881887af0a2356b8c529a96fb91c1894745b2a1a009aaR12) of the application)

With this _small_ improvement I was able to reach 100% of `success` rate

![pg optimization_1](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/1hqx7wtu1ow7odtry3ne.png)


![pg optimization_2](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ry74gtimzefsinxdnz1h.png)

Finally I had the same benchmarks from my fellow colleagues but more than so I realized how much the `attention to detail` needs to be taken seriously when dealing with scale. This was a sandbox experiment and I had a lot of small issues, small `legacy-type` code (1 day legacy. I'll call it), small premature optimizations and some over the top gimmicks trying to figure out simple stuff.

I had accomplished what I proposed to myself. Although I knew It had way more possible optimization to be done but I decided to step-way from competition.

But this is for the next talk...