Good evening 🌙 It’s time for my weekly diary 📖
This week, I migrated my personal website to a different database. It used to be: SQLite; now it’s: PostgreSQL. The task took approximately 16 hours.
I compared the text search speed for my blog posts:
- SQLite: 60 milliseconds
- PostgreSQL: 45 milliseconds
The result was surprising. I expected SQLite to be faster. I thought about why this might have happened and concluded that the main reason is connection pooling:
- My program’s SQLite version opened and closed a database connection with every HTTP request from the client.
- In the PostgreSQL version, however, I initially enabled connection pooling via the Go library jackc/pgx/v5/pgxpool.
Anyway, I won’t dig deeper into this topic for now. It’s enough that the search works quickly.
The search is available on the page https://hinst.github.io/dynamic/#/personal-goals-search. For example, you can enter the word “Pascal” and find all my public posts mentioning “Pascal.” The response time there will be a bit longer because TailScale adds a lag of about 100 milliseconds 🐌.
I’ve already set up weekly database backups using the pg_dump command and removed all old remnants of the SQLite integration. According to my calculations, PostgreSQL should only need about 300 MB of RAM, and my Orange Pi micro-computer has 4 GB. So, for now, I have plenty of memory to spare.
I rearranged my computer desk to make it more comfortable to sit at. In the photo: before and after. The worst thing about the old setup was that my elbow would hang off the table in mid-air without support, which caused my arm to ache after 8 hours of work. However, the advantage of the old version was that daylight was shielded from the monitor. In the new version, light from the window is a distraction. At sunset, I have to turn up the brightness, and after sunset, I have to turn it down ☀️🫡
