PostgREST Benchmark

[kiwicopple]

Chore

Describe the chore

As part of our move from Alpha to Beta, we will want to be clear about the limitations and performance of each of the components in Supabase. We need to do 3 things:

• Design a benchmark (if it isn't already designed)
• Perform the benchmark
• Create a blog post or documentation detailing the results

Additional context

Steve mentioned that there were some old benchmarks, it might just be a case of running these with the latest version of pgrst

[steve-chavez]

I've done the load tests with the client(k6) and the server(pg/pgrst) being on separate ec2 instances on the same VPC(over local IP addresses). This is to avoid any networking issues and to make the tests reproducible.

Setup

Database

• Chinook. Also used for the Hasura benchmark.
• For reproducible POST tests, I removed the indexes and constraints from the employee table. Large row count degrades pg insert performance(this happens on about 10 runs of the tests).

VPC

• Server: PostgreSQL 12 plus PostgREST v7.0.1 exposed directly on port 80(no Nginx). The two on the same instance.

  • Same setup on 2 instance types: t3a.nano and t2.nano.
  • pg + pgrst are running on systemd services. No containers to avoid any overhead.
  • OS: NixOS 20.09. Kernel: Linux 5.4.35.

• Client: k6 on a t3a.medium.

  • Lower t3a instances(nano, micro, small) don't have enough RAM for enough k6 Virtual Users.
  • Linux settings(ulimit, tcp) tuned according to the k6 tuning guide.
  • OS: NixOS 20.09. Kernel: Linux 5.4.35.

(The description of the VPC can be found here)

Load test scenarios

The k6 scripts are done using constant request rates for 1 min. They can be found here.

• GETSingle: GET a single row(filtered by id) /artist?select=*&artist_id=eq.3
• GETSingleEmbed: GET a single row(filtered by id) with 2 embeds /album?select=*,track(*,genre(*))&artist_id=eq.127
• GETAllEmbed: GET all rows(no filter) with 2 embeds /album?select=*,track(*,genre(*))
• POSTSingle: POST single json object /employee
• POSTBulk: POST bulk insert(json array of 20 objects) /employee

Results

Here are the summaries of the results. Full results for each test can be found here.

t3anano

	http_reqs	http_reqs(total)	http_req_duration(avg)	data_received	data_sent	vus	failed_requests
GETSingle	1372.484403/s	82435	59.21ms	24 MB	8.5 MB	230	0.00%
GETSingleEmbed	455.587386/s	27922	1.04s	341 MB	3.4 MB	600	0.00%
GETAllEmbed	28.445898/s	2091	7.47s	1.7 GB	220 kB	388	0.00%
POSTSingle	1152.393369/s	69522	258.06ms	39 MB	33 MB	580	0.00%
POSTBulk	557.423585/s	34472	52.96ms	5.1 MB	240 MB	162	0.00%

t2nano

	http_reqs	http_reqs(total)	http_req_duration(avg)	data_received	data_sent	vus	failed_requests
GETSingle	1018.047624/s	63055	292.38ms	18 MB	6.4 MB	529	0.00%
GETSingleEmbed	415.859432/s	25511	1.01s	312 MB	3.1 MB	600	0.00%
GETAllEmbed	24.2963/s	1939	10.62s	1.6 GB	220 kB	487	0.00%
POSTSingle	928.804556/s	55926	315.39ms	31 MB	26 MB	556	0.00%
POSTBulk	550.833996/s	34517	61.61ms	5.1 MB	240 MB	176	0.00%

Comments

• There's a notable performance drop from the previously advertised 2000 req/s. The Heroku free tier dyno is equivalent to the t2.nano, so it's about 1018~ req/s now.
  • I have an idea around what could be causing this drop(SET LOCALs before every query). I'm looking forward to improve this.
• If the GET request has no filter, the req/s will be very low. Might be reasonable considering around 1.7 GB is downloaded.
  • Lots of these requests can cause Linux to OOM kill PostgREST. This can be mitigated with a systemd Restart=Always(already on KPS, IIRC).
• Other scenarios to test?
• Should I test other ec2 types?

[steve-chavez]

I was not satisfied with the results I got from PostgREST. So I tried some things for increasing the req/s:

• Reduced logging: Got around ~200 req/s more. PR done.
• Batching SET LOCALs: Failed approach, got less req/s. PR done.
• More efficient concatenation of query fragments: ~220 req/s more. PR done.
• Prepared statement for GET: ~240 req/s more. PR done.

Once I'm done with the above improvements, I should be able to get around 1900 req/s on the GETSingle case. Which would be close to the previous 2000 req/s we had.

After that I'll proceed with writing the blog post. @kiwicopple What do you think.. Is that good?

Edit: Improvements done. New GETSingle results on t3a.nano: 1980.830129 req/s.

[kiwicopple]

I think that's amazing @steve-chavez - great job increasing throughput by 10% and completing the PR. got that we have this consistently running too, so that we can measure the changes to PostgREST over time

Looking forward to the blog post. Make sure you cover the changes you made to get if faster (and your failed attempts) - i'm very curious!

[awalias]

@steve-chavez could you run these on a larger instance (maybe t3a.large) and see if you can get any improvement. For KPS I can't improve on ~1200/s (have tried micro, medium, 2xlarge) even when I switch the instances to "Unlimited" mode

[steve-chavez]

@awalias Sure. For reads(GETSingle), I'm also getting similar results on t3a.nano, t3a.large and t3a.xlarge - all unlimited. However with a c5.xlarge I get a noticeable improvement.

I'll post the results in a while.

[steve-chavez]

I've done more load tests on the latest version. This time using more t3a instances and c5xlarge. The results are also on the supabase.io benchmarks project.

Latest version(7.0.1)

t3anano

benchmark_name	http_reqs_rate	http_reqs_count	http_req_duration_avg	data_received	data_sent	vus	failed_requests
GETAllEmbed	28.14130368125745	1052	4392.724907480037	803 MB	108 kB	212	0
GETSingle	1368.1003585286514	41228	54.38214947382864	11 MB	4147 kB	177	0
GETSingleEmbed	478.7065957767772	14548	246.26307021693663	170 MB	1733 kB	182	0
POSTBulk	558.278675135931	17344	41.93758273547061	2524 kB	115 MB	0	0
POSTSingle	1125.3026654185057	34124	193.6772965780991	18 MB	15 MB	411	0
RPCSimple	1461.277419102948	44068	184.8882632989707	8736 kB	4519 kB	538	0
RPCGETSingle	1357.8467358391467	41033	211.10588839750883	9497 kB	4488 kB	565	0
RPCGETSingleEmbed	563.5562193025908	16953	209.60113417967398	196 MB	2550 kB	230	0

t3amicro

benchmark_name	http_reqs_rate	http_reqs_count	http_req_duration_avg	data_received	data_sent	vus	failed_requests
GETAllEmbed	28.319923884594417	1052	4325.241133394486	803 MB	109 kB	209	0
GETSingle	1359.4629431116216	40860	66.68731558509552	11 MB	4150 kB	197	0
GETSingleEmbed	499.18213012816136	15001	5.586006356709547	175 MB	1802 kB	100	0
POSTBulk	562.2905272757457	17369	42.76031739541728	2527 kB	115 MB	128	0
POSTSingle	1130.4409066451226	34205	205.7264949658817	18 MB	15 MB	435	0

t3amedium

benchmark_name	http_reqs_rate	http_reqs_count	http_req_duration_avg	data_received	data_sent	vus	failed_requests
GETAllEmbed	28.64676017095671	1061	4368.507001370408	810 MB	111 kB	211	0
GETSingle	1337.9942439864074	40234	119.74172412412452	11 MB	4126 kB	296	0
GETSingleEmbed	478.6294803275805	14528	246.4662328864953	169 MB	1759 kB	178	0
POSTBulk	557.0108925292726	17128	52.160808355499874	2492 kB	113 MB	137	0
POSTSingle	1024.3395454045806	30903	268.5424716246939	16 MB	14 MB	519	0

t3alarge

benchmark_name	http_reqs_rate	http_reqs_count	http_req_duration_avg	data_received	data_sent	vus	failed_requests
GETAllEmbed	27.959444076327134	1046	4490.868743819312	799 MB	108 kB	213	0
GETSingle	1344.8077192588032	40748	88.26264817613146	11 MB	4138 kB	236	0
GETSingleEmbed	491.31689092671496	14857	104.77036235303191	173 MB	1785 kB	122	0
POSTBulk	554.3946223877862	17118	47.72673540501268	2491 kB	113 MB	142	0
POSTSingle	1129.0000963808495	34168	203.9136696720621	18 MB	15 MB	422	0

t3axlarge

benchmark_name	http_reqs_rate	http_reqs_count	http_req_duration_avg	data_received	data_sent	vus	failed_requests
GETAllEmbed	49.90669359634122	1613	1501.471376600127	1232 MB	169 kB	124	0
GETSingle	1540.274836106219	46298	31.654532426022385	13 MB	4747 kB	177	0
GETSingleEmbed	758.428261/s	22793	75.40341754064875	279 MB	2.8 MB	186	0
POSTBulk	560.4358393526506	17434	41.623110891706105	2537 kB	115 MB	0	0
POSTSingle	1073.0229247909144	32371	164.82871096886123	17 MB	15 MB	349	0
RPCSimple	1554.2067168921103	46726	17.767453889868605	9263 kB	4883 kB	148	0
RPCGETSingle	1575.321899074914	47336	12.293285437447201	11 MB	5270 kB	128	0
RPCGETSingleEmbed	813.5611151030155	24465	120.73675498291435	283 MB	3727 kB	211	0

c5xlarge

benchmark_name	http_reqs_rate	http_reqs_count	http_req_duration_avg	data_received	data_sent	vus	failed_requests
GETAllEmbed	64.33705786521816	2053	1092.1149330038968	1567 MB	213 kB	129	0
GETSingle	2207.0122163428027	66745	18.123930021919435	18 MB	6779 kB	184	0
GETSingleEmbed	1025.0301701378478	30810	26.454880600064858	359 MB	3701 kB	141	0
POSTBulk	615.2065824676657	18850	46.25723316201589	2743 kB	125 MB	157	0
POSTSingle	1394.0286261871204	41963	26.314313699044487	22 MB	19 MB	170	0
RPCSimple	2229.6218875590957	66996	14.15916249617884	13 MB	6935 kB	186	0
RPCGETSingle	2265.1258574916014	68219	13.350303690452941	15 MB	7528 kB	161	0
RPCGETSingleEmbed	1065.2575525253092	32015	42.555433064344946	371 MB	4846 kB	164	0

Comments

• I was expecting to see an improvement with more vCPUs, since PostgREST should be able to use all cores automatically - thanks to a GHC option.
  • c5.xlarge(4 vCPUs, 8GB RAM) holds in this regard, it gives more req/s.
  • t3a.nano to t3a.large also hold, they all have give similar req/s with 2 vCPU(though they vary in RAM).
  • t3a.xlarge(4 vCPUs, 16GB RAM) req/s increase lightly. I was expecting it to have similar numbers to c5.xlarge because of the number of vCPUs.

Edit 1: corrected results for GETSingleEmbed on t3axlarge.
Edit 2: corrected results for GETSingle on t3axlarge.
Edit 3: added RPC results for t3anano/t3axlarge/c5xlarge.

[steve-chavez]

These are load tests on the new master version(unreleased) with the above improvements:

Master version

t3anano

benchmark_name	http_reqs_rate	http_reqs_count	http_req_duration_avg	data_received	data_sent	vus	failed_requests
GETAllEmbed	28.432965198469514	1069	4455.005381366703	816 MB	110 kB	223	0
GETSingle	2067.949048556139	62161	9.746079284696217	17 MB	6253 kB	135	0
GETSingleEmbed	804.9944509323249	24281	9.240602824924855	283 MB	2893 kB	106	0
POSTBulk	607.923018497801	18859	60.058675140834275	2560 kB	125 MB	0	0
POSTSingle	1546.5607244755133	46616	20.313292865175143	6328 kB	21 MB	168	0
RPCSimple	2011.3246029355748	60638	38.478822698918314	11 MB	6218 kB	212	0
RPCGETSingle	2165.9723477785415	65083	7.496874328472832	14 MB	7118 kB	126	0
RPCGETSingleEmbed	724.2786621387023	22015	335.15800572518646	255 MB	3311 kB	426	0

t3axlarge

benchmark_name	http_reqs_rate	http_reqs_count	http_req_duration_avg	data_received	data_sent	vus	failed_requests
GETAllEmbed	52.79012209817422	1650	781.8713445618171	1260 MB	172 kB	100	0
GETSingle	2163.741375811913	65088	11.116856056615712	17 MB	6674 kB	163	0
GETSingleEmbed	1151.328495863017	34618	22.066126356230715	403 MB	4192 kB	166	0
POSTBulk	607.9391977183707	18903	59.37284083510547	2566 kB	125 MB	0	0
POSTSingle	1335.0433780553615	40225	58.09855885978874	5460 kB	18 MB	284	0
RPCSimple	2013.6575186156895	60505	15.883888903842529	11 MB	6322 kB	195	0
RPCGETSingle	2120.0409269479424	63843	16.249871573610218	14 MB	7108 kB	185	0
RPCGETSingleEmbed	1117.231833206941	33715	43.549919254901205	390 MB	5136 kB	200	0

c5xlarge

benchmark_name	http_reqs_rate	http_reqs_count	http_req_duration_avg	data_received	data_sent	vus	failed_requests
GETAllEmbed	69.83586322263953	2100	43.52809438095244	1603 MB	217 kB	100	0
GETSingle	2845.2504094666474	85673	5.991303163178547	23 MB	8701 kB	140	0
GETSingleEmbed	1522.8594526998459	45759	7.049463338512587	533 MB	5496 kB	121	0
POSTBulk	629.4452167238903	19336	38.03010694414565	2625 kB	128 MB	148	0
POSTSingle	1514.6482852887295	45618	13.3789027014557	6192 kB	21 MB	166	0
RPCSimple	2755.724864338208	82801	10.698241249151723	15 MB	8571 kB	176	0
RPCGETSingle	2793.208517310614	83929	8.848548436511885	18 MB	9262 kB	162	0
RPCGETSingleEmbed	1449.2239286992774	43549	16.945833428367923	504 MB	6592 kB	153	0

Comments

• More reads/writes than previous version but obtaining all the data without filtering(GETAllEmbed) stays the same.
• t3anano to t3alarge results stay almost the same. I've omitted the results here for brevity.
• t3axlarge holds on improving slightly on GETSingle despite having 4 vCPUs. c5xlarge(also 4 vCPUs) improves much more.

Edit 1: Corrected GETSingle t3a.xlarge results.
Edit 2: added RPC results for t3anano/t3axlarge/c5xlarge.
Edit 3: corrected RPC results(functions were not marked as STABLE and this reduced the req/s)

[kiwicopple]

First of all, amazing job with the performance improvements @steve-chavez - 51% increase (!!) on nano for GET single 😲. Wow

t3a.xlarge(4 vCPUs, 16GB RAM) doesn't hold on GETSingle

I find this strange. Perhaps the t3 architecture has something unusual - are these on standard CPU or unlimited CPU? Perhaps they are on standard, and the CPU is being capped?

Either way - this does make one thing clear:

t3a.nano (2 vCPU): $0.0047 per Hour
t3a.xlarge (4 vCPU): $0.1504 per Hour - 32x more expensive than nano, minimal increase in throughput
c5.xlarge (4 vCPU): $0.17 per Hour - 36x more expensive than nano, 38% increase in throughput

From these numbers, it seems much better to horizontally scale than vertically scale. The cost of getting a couple of extra vCPUs inside the same box is very high

[steve-chavez]

t3a.xlarge(4 vCPUs, 16GB RAM) doesn't hold on GETSingle
I find this strange. Perhaps the t3 architecture has something unusual - are these on standard CPU or unlimited CPU? Perhaps they are on standard, and the CPU is being capped?

@kiwicopple Double checked that they were on unlimited. But I made a mistake before - I didn't increase the constant request rate for GETSingle on the t3a.xlarge(fixed here).

It turns out that the req/s were improving slightly on GETSingle. I've corrected the results above.

[steve-chavez]

I've added load tests for RPC. I think with this we cover all the relevant PostgREST features. The scenarios are:

• RPCSimple: Simple addition of 5 numbers /rpc/add_them?a=1&b=2&c=3&d=4&e=5.
• RPCGETSingle: Wrapper function that gets a single row a single row(filtered by id):
  /rpc/ret_artists?select=*&artist_id=eq.3
• RPCGETSingleEmbed: Wrapper function that gets a single row(filtered by id) with 2 embeds:
  /rpc/ret_albums?select=album_id,title,artist_id,track(*,genre(*))&artist_id=eq.127

Results(only for t3a.nano, t3a.xlarge and c5.xlarge) on the above comments: v7.0.1 and new version.

(k6 scripts added on #7)

Comments

• Using wrapper functions doesn't add much overhead if they're declared as IMMUTABLE. The req/s are almost the same as GETSingle and GETSingleEmbed.

[steve-chavez]

A couple more findings here.

Unix Socket connection from PostgREST to PostgreSQL

Using Unix Socket instead of TCP Socket. This is only possible if pgrest/pg are on the same instance. Basically have this in the pgrest config:

db-uri = "postgres://postgres@/postgres"

Instead of:

db-uri = "postgres://postgres@localhost/postgres"

t3anano - Master version

benchmark_name	http_reqs_rate
GETSingle	2450.151619
POSTSingle	1716.604133
RPCGETSingle	2436.285207

Comments

• 11.6% more throughput compared to the TCP socket.