Why use SQLite?

SQLite is optimized for situations where the workload is predominantly reads with occasional writes, making it ideal for small to medium-sized applications. SQLite is very efficient and can operate comfortably within a few megabytes of RAM.

For highly concurrent environments where many users need to write to the database simultaneously, SQLite might not be the best choice, and a more robust client-server database like MySQL or PostgreSQL would be more appropriate. Recommended RAM for light use for MySQL is 1GB and for PostgreSQL 2GB, which is about 1000 times more than SQLite.

SQLite is an embedded database that runs in the same process as the application using it. This means it doesn’t require a separate server process to manage database connections, unlike MySQL, which operates as a server and listens for incoming connections on a specific port (e.g. 3306). SQLite databases are stored as files on the local filesystem. When an application wants to interact with an SQLite database, it directly accesses the database file without needing to communicate over a network. So with SQLite, if you run multiple apps, there won't be problems like port conflicts or the need for containers.

SQLite doesn't require authentication mechanisms like usernames and passwords. The database file is accessed directly without any user credentials.

If you want to use a cheap (5$/month) VPS with 512GB RAM, SQLite is your only option. It is not just a toy, it is used in popular apps like nomadlist, see Pieter Levels video. You can optimize SQLite even further.

Music: Mike Oldfield - Sentinel

Optimizing website speed and memory usage

The web app for the online marketplace I've been maintaining experienced significant slowdowns as the number of products on the site increased. Page load times reached 20 seconds, making the site unusable. Over the past three days, I have used the Chrome browser's built-in Lighthouse tool to analyze the issue and have implemented several optimizations.

I initially aimed to reduce image sizes. On average, WebP format consumes 4x less memory than JPEG for product photos. For PNG images, I've observed file size reductions of up to 100x. In total, converting images to WebP decreased server disk and memory footprint by 7x. This also has a positive effect on client LCP.

I implemented lazy loading to load only the image data visible on the screen, rather than loading all images at once. Additionally, by using infinite scrolling to load only the HTML text of the visible portion, the initial memory usage for the client-side webpage source text was reduced from 1,386,494 bytes to 57,977 bytes, achieving a 24x reduction. These size optimizations decreased load times from 20 to 13 seconds. Almost all of the remaining 13 seconds was initial server response time.

The most significant improvement in page load speed was achieved by eliminating N+1 query issues (using a loop instead of a single query) in the database queries. In my case, the inefficiency was fetching photos for each product in separate queries within a loop instead of fetching all the necessary photos in one query and then map them to their respective products. This enhancement reduced the initial server response time from 13 seconds to 2 seconds and the site became usable again.

I still have a lot of work to do to apply these techniques to other portions of the code, but at least now I know where to focus.

Music: Beck the Monster-Денис Пакрушов

Use SQL COUNT(*) instead of PHP count()

If you need the number of items that satisfy a criteria in a SQL database table, intead of

count($db->query("SELECT * FROM customer_comments WHERE status = 1")->rows)

use

$db->query("SELECT COUNT(*) FROM customer_comments WHERE status = 1")->value

Using COUNT(*) is faster because databases are optimized for such operations. When you fetch all rows into your application and then count them using count(), you're moving the data processing to the application level, which is generally less efficient.