Notes on Improving Speed and Latency

[irsyadpage]

After poking around pantunis and pantunis-api repositories, here are my notes and suggestions regarding the current issues and possible changes to improve the speed and latency of Pantunis website. It might not be the correct or the best suggestions, but just some thought and ideas to make Pantunis achieve faster and smoother experiences in the future.

Backend

• Search query using LIKE operator:
  • Database query using LIKE operator performs whole table scan, so every time user searching something, server must scan the whole database to find for matching pattern. The larger the database, the slower query performance will become.
  • Current FTS table use the default unicode61 tokenizer which does not useful for search query that use the LIKE operator.
  • Changing to the trigram tokenizer could help to improve the LIKE operator, but if the query word is less than 3 characters, it would not return any result. So word like "ke", "di", "ku" would be difficult for user to search.
  • Suggestion: This issue is quite tricky to give the best solution because there are several approaches with critical pro and cons. It really depends on the server environment and how we expect users to use the search function. Also, it heavily depends to the @irfanzainudin's preferences on how far you want to tackle this issue in the future (because you are the one that do the hard work) 😉. We can further discuss about this when the time comes. This discussion should includes FTS features such as search filter, faceted search, fuzzy search, stemming, autocomplete, vector search, etc.
• Search query return all results at once despite having over thousands result:
  • If a query word has a lot of matches, the result will be return much slower. It can take about 2-3 seconds to complete API request for searching for "di" or "kan".
  • Suggestion: Use pagination result via OFFSET and LIMIT clauses.
• Sqlite configuration suggestions:
  • Use schema.page_size = 65536 for faster db scan operation.
  • Consider to use UNINDEXED column option for id in pantun_fts table since id column is only storing id and not text for searching.

Frontend

• Searching feature:
  • Rendering thousands of pantun search result became too slow and heavy. Searching "kan" could take more than 20 seconds to complete, and searching "a" could crash the React app.
  • React will just try to render the whole pantun list without any optimization.
  • Suggestion: Implement pagination to list all the result. If long scrolling is preferable, try looking into React Infinite Scroll and Virtual Scrolling in React to ensure efficient dom rendering for long vertical content.
• SEO friendliness:
  • Improving the website speed and latency could benefit SEO. But, If we want to push things further, we can figure out if server-side rendering (SSR) is neccessary. SSR is beneficial for SEO, but it come with a cost where React must be used alongside with meta-framework such as Next, Remix, or Astro. Of course this might cause drastic code changes and new framework must be learn properly (if unfamiliar) for a correct integration. So my suggestion is to consider SSR if you have the resource and time to do so, and after all the above suggestion implemented.
  • For an alternative to SSR, the frontend code can be deploy on static hosting (with CDN) such as Cloudflare Pages, Vercel Edge Network, or AWS Amplify Hosting. Hosting on CDN reduce the latency for browsing the website (static assets) especially when the server is far from the user. Minor details: based on my simple testing (using Unifi), HTML response for hot request on Vercel is <50ms while Cloudflare is ~100ms.

Sorry for the lengthy writing 😅, I'm just too excited to see Pantunis becoming a great development experience.