DEV Community: prestd

pRESTd release 1.1.4 - Query performance improvement

Vinicius Mesel — Tue, 08 Nov 2022 18:04:13 +0000

If you want to use your database as a data source for an API you are creating, but you are not happy with building it from scratch: pRESTd is for you!

Basically pRESTd is a software that enables you to perform queries through API calls on your whole database system, improving delivery time for your development team.

In this new release (1.1.4), we were able to work on a performance improvement on our querying system. Our base query used to use the following query:

select json_agg(s) from (select * from your_awesome_table) s

Which, on our analysis, was 5x slower than using JSONB aggregation function.

Benchmark

As we can see above, implementing the functionality in Golang gives us better performance on smaller scenarios, but as we grow our data requests, the linearity of the equation is not followed.

That's why we chose to implement with JSONB_AGG as our serializer on the database end, enabling us to save time on development and test writing.

You can see the requests data available in our PR, on the subtitles below.

With JSON_AGG()

2022/11/04 16:54:35 [warning] adapter is not set. Using the default (postgres)
2022/11/04 16:54:35 [warning] command.go:920 You are running prestd in debug mode.
[prestd] listening on 0.0.0.0:80 and serving on /
2022/11/04 16:54:41 [debug] server.go:2084 generated SQL:SELECT json_agg(s) FROM (SELECT * FROM "database"."public"."table" LIMIT 1000 OFFSET(1 - 1) * 1000) s parameters: []
[negroni] 2022-11-04T16:54:41Z | 200 |   5.11392325s | 54.186.223.54 | GET /database/public/table
2022/11/04 16:55:18 [debug] server.go:2084 generated SQL:SELECT json_agg(s) FROM (SELECT * FROM "database"."public"."table" LIMIT 100 OFFSET(1 - 1) * 100) s parameters: []
[negroni] 2022-11-04T16:55:18Z | 200 |   504.78063ms | 54.186.223.54 | GET /database/public/table

With JSONB_AGG()

[prestd] listening on 0.0.0.0:80 and serving on /
2022/11/04 16:59:26 [debug] server.go:2084 generated SQL:SELECT jsonb_agg(s) FROM (SELECT * FROM "database"."public"."table" LIMIT 100 OFFSET(1 - 1) * 100) s parameters: []
[negroni] 2022-11-04T16:59:26Z | 200 |   479.260256ms | 54.186.223.54 | GET /database/public/table
2022/11/04 17:00:05 [debug] server.go:2084 generated SQL:SELECT jsonb_agg(s) FROM (SELECT * FROM "database"."public"."table" LIMIT 1000 OFFSET(1 - 1) * 1000) s parameters: []
[negroni] 2022-11-04T17:00:05Z | 200 |   1.912713761s | 54.186.223.54 | GET /database/public/table

About the benchmark

We used a table with 56 million rows, two indexes (on the primary date and id fields) and both of the API calls (and queries) were paginated.

Constant work to onboarding new members into engineering team

Thiago Avelino — Fri, 07 Jan 2022 20:43:19 +0000

Developing the "onboarding" process for a new person in an engineering team takes a lot of dedication, and keeping this process fluid takes even more work (with as little friction as possible).

This issue is challenging for any team working full time, it is even worse for Open Source projects where contributors usually work in their spare time. We should make this process as fluid as possible so that people don't get discouraged by the complexity of getting up there and testing, until they make their first pull request.

Configure development environment

Every developer has a different way of setting up a development environment, even if it is in a popular technology (programming language) with lots of documentation, text editor extensions (emacs, vim, vscode, ...), etc.

We developers are used to “our way” of doing things, it is common for us to create resistance when someone presents a different way and I do it another way.

In the vast majority of applications they depend on external resources such as databases, APIs, tokens, etc., if we force the developer (user) to read all the project documentation before having the first contact with the project it is very likely that we will lose his engagement, and some frustrations in him, such as:

I just wanted to test
I have to read all this to see it working
What a complicated project
I have to install X, Y and Z services/software on my machine
I don't know the programming language used in the project, which plugins should I install in my editor?
... and much more.

There are some tools to assist project maintainers (open source or private) to generate an onboarding process with as little friction as possible.

Configuring editor (with all the necessary plugins and parameters), all the services the project needs to run, environment variables configured, database running with initial data load, data viewer configured (software to manage data from the database), etc.

To the point where the developer “clicks a button” and magically has the development environment ready to test the software.

In the last few months we at prestd have been working on improving our documentation (it is far from being good documentation) and removing as much friction as possible in the process of getting a new development environment up, some issues we have implemented until we got to what we have today:

Improve local tests execution — it is frustrating that someone wants to contribute and cannot run the local tests (we use e2e tests, making requests to prestd's own API), a way was implemented where the tests run inside docker using docker-compose;
Documentation: new content architecture — thinking of a person who has never had contact with prestd and wants to test or use it in a production environment, both people should get into the documentation and be able to do what they want to do (bring up the environment);
Onboarding of new contributor: using devcontainer — prepare the development environment with "1 click" using devcontainers (GitHub Codespaces support).

See prestd's development guide page here.

It is not nice to have the engineering team working in a bad environment, we need to think more about our team and make the team experience fluid.
people > technology

Focus on the developer (user)

The prestd exists open source since 2016, I particularly like the project very much and believe it is a great solution to accelerate the development of a RESTful API for existing database and especially development of a new API (project starting from scratch).

But for many years we turned to developing the software didn't look at documentation with the dedication we should, causing the contributor base to shrink (existing and new) — people going through open source project, hardly stayed for many years, so we always have to have the most rounded onboarding process possible.

Given this problem I started to look at the documentation with more dedication and every decision in prestd from now on will be thinking about the developer (user) experience, answering the following questions:

Will this improve the developer experience using the project?
Will this make the project easier to use?
Will this make it easier to maintain the development of the project?

When all 3 questions are “yes”, we will proceed with the implementation, regardless of what it is: feature, improvement, fix, etc.

When is the best time to share a "new" product?

Thiago Avelino — Fri, 09 Jul 2021 00:17:23 +0000

Is now (as soon as possible), even if it's not the way you want it

pREST exists since 2016 and today I remembered to publish it on Product Hunt, without having much expectation on the engagement.

To my surprise, has a much higher engagement than I imagined, especially in the installation of binary (software).

We will be a software for API development based on PostgreSQL database (new or existing) with low-code, there is a lot of work to be done... we are just in the beginning.

// Detect dark theme var iframe = document.getElementById('tweet-1412956727822200835-533'); if (document.body.className.includes('dark-theme')) { iframe.src = "https://platform.twitter.com/embed/Tweet.html?id=1412956727822200835&theme=dark" }

prest / prest

PostgreSQL ➕ REST, low-code, simplify and accelerate development, ⚡ instant, realtime, high-performance on any Postgres application, existing or new

pRESTd

pREST (PostgreSQL REST), is a simple production-ready API, that delivers an instant, realtime, and high-performance application on top of your existing or new Postgres database.

PostgreSQL version 9.5 or higher

Contributor License Agreement -

Problems we solve

The pREST project is the API that addresses the need for fast and efficient solution in building RESTful APIs on PostgreSQL databases. It simplifies API development by offering:

A lightweight server with easy configuration;
Direct SQL queries with templating in customizable URLs;
Optimizations for high performance;
Enhanced developer productivity;
Authentication and authorization features;
Pluggable custom routes and middlewares.

Overall, pREST simplifies the process of creating secure and performant RESTful APIs on top of your new or old PostgreSQL database.

Why we built pREST

When we built pREST, we originally intended to contribute and build with the PostgREST project, although it took a lot…

View on GitHub

https://www.producthunt.com/posts/prest

Full Text Search in PostgreSQL

Thiago Avelino — Sat, 02 Jan 2021 19:20:54 +0000

When the subject is search engine (inverted index) it is extremely common to think of solutions like Apache Solr or Elastic (former elasticsearch), but why don't we remember the Full Text Search feature we have native in PostgreSQL?

The full text search feature in PostgreSQL became native in version 9.4, but before that we had the GiST Indexes, which was used as the basis for the development of Full Text Search (tsvector).

What Is Full Text Search Anyway?

(...) full-text search refers to techniques for searching a single computer-stored document or a collection in a full text database; (...) distinguished from searches based on metadata or on parts of the original texts represented in databases (such as titles, abstracts, selected sections, or bibliographical references). Wikipedia

In other words, imagine you have a set of text documents stored in a database. These documents are not just meta-data items like an author name or a country of origin, but rather an abstract for an article, or full text articles themselves, and you want to find out if certain words are present or not in them.

E.g. you want to search all the news that has subistantive related to "dog" or "fox" (we are talking about animals) are present so if they are in their singular form, you'll find them with the ILIKE keyword...

SELECT * FROM news  
WHERE  
(body ILIKE '%fox%' OR
body ILIKE '%dog%');

... but you'll also find stuff like "foxtrot" or "Dogville", which is not quite what you intended, you had to declare in which field you would search, if you did not have the terms searched in the field will not bring record.

Another problem is that if you search for a word such as "query", and if it's present in its plural form "queries", then you won't find it if you try a simple pattern search with LIKE, even though the word is, in fact, there. Some of you might be thinking to use regular expressions, and yes, you could do that, regular expressions are incredibly powerful, but also terribly slow.

A more effective way to approach this problem is by getting a semantic vector for all of the words contained in a document, that is, a language-specific representation of such words. So, when you search for a word like "jump", you will match all instances of the word and its tenses, even if you searched for "jumped" or "jumping". Additionally, you won't be searching the full document itself (which is slow), but the vector (which is fast).

That is, in a nutshell, the principle of full text search, thinking about problems related to this was developed data type tsvector.

What do you need to know to use tsvector?

PostgreSQL has two functions that do exactly what we intend to do:

to_tsvector for creating a list of tokens (the tsvector data type, where ts stands for "text search");
to_tsquery for querying the vector for occurrences of certain words or phrases.

For example, to create a vector for the sentence "the quick brown fox jumped over the lazy dog":

SELECT to_tsvector('The quick brown fox jumped over the lazy dog.');

Which will return a vector where every token is a lexeme (unit of lexical meaning) with pointers (the positions in the document), and where words that carry little meaning, such as articles (the) and conjunctions (and, or) are conveniently omitted:

                      to_tsvector
-------------------------------------------------------
 'brown':3 'dog':9 'fox':4 'jump':5 'lazi':8 'quick':2

By default, every word is normalized as a lexeme in _English+ (e.g. "jumped" becomes "jump"), case depending on the localization settings of your PostgreSQL installation.

A common doubt is about support for other languages (e.g. Brazilian Portuguese). The good news is that we have support for several languages, see the list:

SELECT cfgname FROM pg_ts_config;

cfgname
--------
simple
arabic
danish
dutch
english
finnish
french
german
hungarian
indonesian
irish
italian
lithuanian
nepali
norwegian
portuguese
romanian
russian
spanish
swedish
tamil
turkish

Deeper into the PostgreSQL text search configuration.

Real example generating tokens

We have news table with title field, description and other meta data:

CREATE TABLE news (
    id SERIAL PRIMARY KEY,
    title text NOT NULL,
    description text NOT NULL,
    created_at timestamp without time zone NOT NULL DEFAULT now(),
    last_update timestamp without time zone NOT NULL DEFAULT now(),
    tokens tsvector
);

The way it is when creating a record will not be created the tokens to do textual search, how will we solve this?

We can create a trigger that listens to all the creation and updating, joins the text of the title and description and generates the tokens automatically:

CREATE OR REPLACE FUNCTION set_full_text_search_on_news()
RETURNS TRIGGER AS $$
BEGIN
       NEW.tokens = to_tsvector(concat(NEW.title, ", ", NEW.description))
       RETURN NEW;
END;
$$ language 'plpgsql';

CREATE TRIGGER update_new_full_text_search
       BEFORE UPDATE ON news
       FOR EACH ROW EXECUTE PROCEDURE set_full_text_search_on_news();

Inserting a Record:

INSERT INTO news (title, description)
VALUES
('dog history', 'The quick brown fox jumped over the lazy dog');

The tokens field will be generated automatically, thus remaining:

'caolha':4 'da':2 'de':5 'em':12 'fax':9 'gigant':11 'história':1 'java':6 'mandar':8 'new':13 'para':10 'querem':7 'york':14 'zebrazebra':3

It is now possible to do text search using the full text search feature (function to_tsquery):

SELECT * FROM news WHERE tokens @@ to_tsquery('fox');

Return:

 id |    title    |                 description                  |         created_at         |        last_update         | client_id |                                 tokens
----+-------------+----------------------------------------------+----------------------------+----------------------------+-----------+------------------------------------------------------------------------
  1 | dog history | The quick brown fox jumped over the lazy dog | 2020-12-11 15:58:55.298558 | 2021-01-02 16:05:21.088482 |         1 | 'brown':4 'dog':1,10 'fox':5 'historyth':2 'jump':6 'lazi':9 'quick':3
(1 row)

How to use this feature in pREST?

prest / prest

PostgreSQL ➕ REST, low-code, simplify and accelerate development, ⚡ instant, realtime, high-performance on any Postgres application, existing or new

pRESTd

pREST (PostgreSQL REST), is a simple production-ready API, that delivers an instant, realtime, and high-performance application on top of your existing or new Postgres database.

PostgreSQL version 9.5 or higher

Contributor License Agreement -

Problems we solve

The pREST project is the API that addresses the need for fast and efficient solution in building RESTful APIs on PostgreSQL databases. It simplifies API development by offering:

A lightweight server with easy configuration;
Direct SQL queries with templating in customizable URLs;
Optimizations for high performance;
Enhanced developer productivity;
Authentication and authorization features;
Pluggable custom routes and middlewares.

Overall, pREST simplifies the process of creating secure and performant RESTful APIs on top of your new or old PostgreSQL database.

Why we built pREST

When we built pREST, we originally intended to contribute and build with the PostgREST project, although it took a lot…

View on GitHub

This feature was implemented in version 1.0.5 as a search filter and works like this:

GET /{DATAVASE}/{SCHEMA}/news?tokens:tsquery=fox

[
    {
        "id": 1,
        "title": "dog history",
        "description": "The quick brown fox jumped over the lazy dog",
        "created_at": "2020-12-11T15:58:55.298558",
        "last_update": "2021-01-02T16:05:21.088482",
        "client_id": 1,
        "tokens": "'brown':4 'dog':1,10 'fox':5 'historyth':2 'jump':6 'lazi':9 'quick':3"
    }
]

Read more about pREST search filters here.

Difference between INSERT and COPY in PostgreSQL

Thiago Avelino — Wed, 30 Dec 2020 21:13:42 +0000

Quite a number of reasons, actually, but the main ones are:

Typically, client applications wait for confirmation of one INSERT's success before sending the next. So there's a round-trip delay for each INSERT, scheduling delays, etc (pREST supports pipelineing INSERTs in batches). This point is the most significant, It's all about network round-trips and rescheduling delays;
Each INSERT has to go through the whole executor. Use of a prepared statement bypasses the need to run the parser, rewriter and planner, but there's still executor state to set up and tear down for each row. COPY does some setup once, and has an extremely low overhead for each row, especially where no triggers are involved.

pREST of COPY batches "INSERT's" support?

Yes, we support the insertion operation using COPY when explained in the http protocol header.

Prest-Batch-Method: copy, when not declared pREST will use INSERT.

pREST Celebrating 2,441 GitHub Stars ✨

Thiago Avelino — Wed, 11 Nov 2020 19:44:50 +0000

pREST just received it's 2,441th star on GitHub. We'll use this milestone to recap pREST’s growth and other important milestones since its beginning as an experimental project in 2016. We've accomplished lot of great things together.

prest / prest

PostgreSQL ➕ REST, low-code, simplify and accelerate development, ⚡ instant, realtime, high-performance on any Postgres application, existing or new

prestd

pREST (PostgreSQL REST), simplify and accelerate development, instant, realtime, high-performance on any Postgres application, existing or new

PostgreSQL version 9.5 or higher

Contributor License Agreement -

Problem

There is PostgREST written in Haskell, but keeping Haskell software in production is not an easy job. With this need prestd was born. Read more.

Test using Docker

To simplify the process of bringing up the test environment we will use docker-compose

# Download docker compose file
wget https://raw.githubusercontent.com/prest/prest/main/docker-compose-prod.yml -O docker-compose.yml
# Up (run) PostgreSQL and prestd
docker-compose up
# Run data migration to create user structure for access (JWT)
docker-compose exec prest prestd migrate up auth

# Create user and password for API access (via JWT)
## user: prest
## pass: prest
docker-compose exec postgres psql -d prest -U prest -c "INSERT INTO prest_users (name, username, password) VALUES ('pREST Full Name', 'prest', MD5('prest'))

…

View on GitHub

While tracking the success of an open source project is known to be a hard problem, we've a few metrics available to us. Currently 42 developers have contributed to some of the 1239 commits of the pREST repository made available in 20 releases. And this is only data from the main pREST repo.

The number of stars of a GitHub project is yet another indicator, because it means people are adding the project to their list of favorites. However, people that starred pREST are not necessarily users and many users don’t star pREST (if that applies to you, consider giving us a star!), therefore its not a precise metric. Till date pREST has 50k+ all-time users during the span of an year. Which includes ~4k+ monthly users.

Milestones like this are proud moments for the developers & community, help motivate more work & new developments. If you'd like to support pREST open source project, consider looking at the discussion page on GitHub.

This will be added to the greatest moment's timeline.

Immense thanks to all the contributors who dedicated part of their time to contribute to the project. Felipe Oliveira for starting this project with me in 2016, his experience in software engineering and staying active until today.