[Question] Postgrest overhead w.r.t direct DB query

[unarmedcivilian]

Environment

• PostgreSQL version: AWS RDS 13.3 (2 vCPU, 8GB Mem)
• PostgREST version: Docker (postgrest/postgrest:v8.0.0)

  # 4 Pods
  resources:
  limits:
      cpu: 1500m
      ephemeral-storage: 1Gi
      memory: 256Mi
  requests:
      cpu: 750m
      ephemeral-storage: 250Mi
      memory: 256Mi

• Operating system: AWS EKS (1.15)

Description of issue

We ran some load tests comparing Postgrest against direct DB queries, and saw that the overhead is significant. The setup is described below.

create table if not exists perf_test (
    id         bigserial,
    name       text,
    value      jsonb,
    created_at timestamp with time zone default now() not null
);

create index if not exists idx_id
    on perf_test (id);

Table has ~ 2M rows.

Tests were executed from K8s using Locust.

Benchmark DB query

def execute_query(self):
    id =  random.randint(1, 2000000)
    id2 = id + 20
    self.client.execute_query("select * from perf_test where id between {0} and {1}".format(id, id2))

Benchmark Postgrest request

def get_perf_test(self):
    id = random.randint(1, 2000000)
    id2 = id + 20
    headers = {'Accept-Encoding': 'gzip', 'Connection': 'Keep-Alive', 'Keep-Alive': 'timeout=5, max=2000'}
    self.client.get(f"/perf_test?id=geq.{id}&id=le.{id2}", name="/perf_test", headers=headers)

The DB queries are able to scale up to 2K TPS with a latency (P95) of ~ 2ms. The corresponding Postgrest requests are topping off at ~ 10ms (P95) with a TPS of ~50.

Is this amount of overhead expected? Are there any recommended configurations / best practices to optimize throughput and latency of Postgrest? Are there any benchmarks that you have done with this kind of comparison?

[wolfgangwalther]

I suggest you run another benchmark with the same query that PostgREST uses. Just enable statement logging on the database, make one of those requests, then take the SQL statement that shows up in the log and use that.

This should give you a better idea of whether we're looking at differences between the query that PostgREST runs and the very simple query you ran - or whether the differences in performance come from the overhead that PostgREST has regarding opening the HTTP connection, parsing the request and constructing the query.

[unarmedcivilian]

Hi @wolfgangwalther , I will definitely try this out. Meanwhile do you have any OOTB suggestions for users who want to run the most optimized setup to maximize performance (especially latency)?

[shaikidris]

+1 for the above comment - any OOTP benchmark numbers on PostgRest throughput and latency?

[wolfgangwalther]

The DB queries are able to scale up to 2K TPS with a latency (P95) of ~ 2ms. The corresponding Postgrest requests are topping off at ~ 10ms (P95) with a TPS of ~50.

2k vs 50? That's a big difference. I didn't realize it was that big, when I looked at this the first time.

Let's look at your query + request:

select * from perf_test where id between {0} and {1}

between ... and ... is inclusive on both ends, afaik. According to our docs, this maps to gte and lte operators for PostgREST.

But:

GET /perf_test?id=geq.{id}&id=le.{id2}

You have neither of those, but geq and le. Those requests would not even succeed.

This makes me wonder what we're looking at here really? Maybe the connection is never made, because some host/port whatever are wrong and we're looking at requests that are timing out or so?

[unarmedcivilian]

Hi @wolfgangwalther sorry that was a typo on my part while submitting the issue. The requests definitely succeeded since we got 200 OK, and any other errors are explicitly reported by Locust. In fact, even with simple primary key lookup, the performance difference was bigger.

select * from perf_test where id = {0} RPS - 4500 Latency - 1ms

id=eq.{id} RPS - 13 Latency - 8ms

[wolfgangwalther]

Ah, ok. I guess we're back to https://github.com/PostgREST/postgrest/issues/2005#issuecomment-958742289, then.

[rsmirnov90]

@unarmedcivilian Does this still happen for you? We are evaluating PostgREST for a large project principally because of performance reasons (reduce the gap between the DB and our SPA as much as possible), and this type of issue would be a major revelation...

I've been searching for postgREST benchmarks (or ANY kind of numbers!) for the last several days and am unable find anything, which does not make any sense. With this kind of project, performance would usually be the first thing advertised on the front page.

[wolfgangwalther]

With this kind of project, performance would usually be the first thing advertised on the front page.

It is: https://github.com/PostgREST/postgrest/tree/main?tab=readme-ov-file#performance