Good evening 🌙 It's time for my weekly diary 📖
This week I migrated my personal website to a new database. From: SQLite, to: PostgreSQL. The task took approximately 16 hours.
I compared the speed of text search in my blog posts:
- SQLite: 60 milliseconds
- PostgreSQL: 45 milliseconds
The result surprised me. I expected SQLite to be faster. I wondered why this might be and concluded that the main reason was the connection pool.
- My SQLite version of the program opened and closed a connection to the database for each HTTP request from the client.
- In contrast, the PostgreSQL version initially enabled connection pooling through the Go library jackc/pgx/v5/pgxpool.
I won't delve deeper into this topic for now. It's enough to know that the search is working quickly.
The search feature is available on the page https://hinst.github.io/dynamic/#/personal-goals-s.... For example, typing in the word "Pascal" will find all my public posts mentioning Pascal. The response time for queries might be slightly longer due to a 100-millisecond lag added by TailScale 🐌
I've already set up weekly database backups using the pg_dump command, and I've removed all old remnants of the SQLite integration. According to my calculations, PostgreSQL should require around 300 MB of RAM, which is well within the 4 GB available on my Orange Pi.
I rearranged my computer desk for better comfort. The photo shows both configurations: old and new. The worst part of the old setup was that my elbow hung in the air without support, causing a sore hand after 8 hours of work. However, the old desk's advantage was that it blocked out daylight from the monitor.
In the new setup, sunlight from the window is a problem. During sunset, I have to increase screen brightness, and afterward, I need to decrease it ☀️🫡
