PocketBase Request Performance Question

[anubus298]

I’ve been testing PocketBase performance and noticed something interesting.
When I fetch around 250 records (with some expand fields), the database queries themselves are extremely fast — usually between 0–8 ms based on debug logs — but the overall HTTP request takes about 140 ms to complete.

Context

• PocketBase version: 0.28.2
• Running locally on: Windows 11 + SSD + Intel 11th Gen

Steps to Reproduce

• clone this repo: https://github.com/anubus298/est-pb
• run go build -o pocketbase.exe
• run pocketbase serve
• go to the http://127.0.0.1:8090/_/
• download this backup and restore it in the dashboard (the data are encrypted): https://drive.google.com/file/d/1H_gRzqeAJZgGHnjFSlTDjG0tLFqnJbet/view?usp=sharing
• the admin email is test@gmail.com and password: 123456789
• generate a Berear token in the dashboard
• open a http client like postman
• add the bearer token in the authorization header and execute GET request on this url:

http://127.0.0.1:8090/api/collections/commands/records?page=1&perPage=250&filter=trash = false && is_ramassed = true&sort=-created,code&expand=city,delivery_man,client_source,client_source.city,packaging,status&fields=id,code,trash,product_name,quantity,delivery_man,client_source,client_name,client_phone,receiver_full_name,receiver_phone_number,receiver_address,city,price,extra_fees,packaging,status,reported_date,note,is_invoiced,is_ramassed,is_dlm_invoiced,is_echange,package_opened,delivered_date,created,updated,expand.client_source.store_name,expand.client_source.phone,expand.client_source.full_name,expand.client_source.full_name,expand.client_source.expand.city.name,expand.delivery_man.full_name,expand.delivery_man.phone,expand.client_source.store_name,expand.packaging.id,expand.packaging.title,expand.packaging.price,expand.status.is_reported_reference,expand.status.name,expand.status.allowed_roles,expand.status.is_delivered,expand.status.bg_color,expand.status.text_color,expand.status.border_color,expand.city.name

• debug what causing the long execution time

[ganigeorgiev]

If you can't provide a minimal reproducible example, then I don't think anyone will be really able to assist you but this time I'll not delete your post since at least you've provided some details, maybe somebody else will be able to help you.

Unfortunately I don't think I'll be much of help as I don't have a sandboxed environment to download and run random files, so I can't test your backup, but the main culprits for the "slow" response times in your case could be because your collections have a lot of fields.

Even ignoring the expands, just the single main queried collection has ~30 fields on its own and that is a lot. While the SQLite query itself could execute very fast, the data from that query still needs to be loaded in the appropriate Go structs, normalized and then JSON serialized and transferred over the wire to the client (this includes localhost). All of these operations could take some time.

Counter intuitively, the fields parameter in your request may also make the matter worst because it currently executes AFTER the data is fetched, right before returning the response and it will perform twice the JSON unmarshal/marshal operations (and this is know to be slow; with the recent Go encoding/json/v2 the performance when operating with unstructured data has been improved almost 2x times but PocketBase hasn't upgraded to it yet).

So, if 140ms is not acceptable as response time for your use case, my suggestions would be:

• try to run your request without the fields query param and compare the response times
• try to limit the number of fields in a single collection by splitting them across multiple collections and using relations