Why I Replaced PostgreSQL with SQLite for Croatian Blog Comments

For years, I ran Croatian blog comments on PostgreSQL. It felt like the responsible choice, the grown-up database for a site that might one day grow big. But after a particularly painful server migration for a small travel blog covering the Dalmatian coast, I realized I was over-engineering a solution for a problem I didn't have. That's when I made the switch to SQLite, and I haven't looked back.

The Real Cost of Running PostgreSQL for a Small Blog

PostgreSQL is an incredible database, but its power comes with a maintenance tax that most Croatian bloggers simply don't need to pay. When you're running a site that gets a few hundred comments a month, you're paying for features you never use.

Server Resources and Memory Pressure

On a typical Croatian VPS with 1 or 2 GB of RAM, PostgreSQL consumes a noticeable chunk of memory just to keep its background processes alive. You have to tune shared_buffers, work_mem, and maintenance_work_mem to avoid running out of memory during peak hours. SQLite, by contrast, runs in-process with your web application and uses almost no additional memory beyond what your app already consumes.

I remember the exact moment I decided to switch. I was migrating a blog about Istrian wine tours from one DigitalOcean droplet to another, and the PostgreSQL dump and restore took over forty minutes for a database that was barely 50 MB. The downtime felt absurd for such a small dataset. With SQLite, that same migration is a simple file copy.

Connection Overhead That Doesn't Matter

PostgreSQL requires a separate server process, connection pooling, and careful management of max_connections. For a Croatian blog with modest traffic, you're managing complexity that adds zero value. SQLite handles concurrent reads just fine for low-traffic scenarios, and you can use WAL mode to allow reads during writes without blocking.

Why SQLite Is a Better Fit for Comment Systems

Comment systems are write-heavy during bursts but read-heavy overall. SQLite's architecture actually aligns well with this pattern when you configure it properly.

ACID Compliance Without the Server

SQLite is fully ACID-compliant, which means your comment data is safe even if the power goes out mid-write. You don't need a separate database server process to get transactional integrity. For a comment system, this is critical because you don't want to lose user contributions during a server crash.

The key configuration change is enabling WAL (Write-Ahead Logging) mode. With PRAGMA journal_mode=WAL;, SQLite allows concurrent reads while a write is in progress. This is exactly what you need for a blog comment section where one user submits a comment while ten others are reading the page.

Simpler Backups and Disaster Recovery

Backing up a PostgreSQL database involves pg_dump or complex replication setups. Backing up SQLite is a cp command. For a Croatian blogger who might not have 24/7 DevOps support, this simplicity is a lifesaver.

Here's a concrete example: I once had a client whose PostgreSQL server crashed on a Sunday afternoon. Restoring from a backup required reinstalling PostgreSQL, importing the dump, and fixing permissions. With SQLite, I could have restored the entire site by uploading a single file via SCP. The time difference was hours versus minutes.

Addressing the Scaling Concerns

The most common objection I hear is "But SQLite doesn't scale!" That's true in the general case, but it's irrelevant for the vast majority of Croatian blogs.

Understanding Your Actual Traffic Patterns

Most Croatian blogs receive between 50 and 500 daily visitors. Even a popular post might generate 20-30 comments in a day. SQLite can handle thousands of writes per second on modern hardware. The bottleneck is almost never the database; it's the web server or the application code.

I run a Split-based food blog that gets around 2000 visitors a day, and SQLite handles the comment section without any issues. The page load times actually improved because we eliminated the network round-trip to a separate database server.

When You Should Actually Worry

If you're running a site like Index.hr or Net.hr with millions of monthly visitors, you should stick with PostgreSQL. But if you're a Croatian blogger, a small business owner, or running a niche community site, SQLite will serve you well for years. You can always migrate to PostgreSQL later if your traffic explodes.

The migration path is straightforward: export your SQLite data to a CSV or SQL dump and import it into PostgreSQL. The schema differences are minimal for simple comment tables. You're not locking yourself into a dead end.

Practical Implementation Tips for Croatian Bloggers

If you're convinced and want to make the switch, here's how to do it right.

Setting Up SQLite for Your Comment System

Start by enabling WAL mode and setting a reasonable cache size. These two settings alone will make SQLite perform like a champ for comment workloads.

PRAGMA journal_mode=WAL;
PRAGMA cache_size=-8000;  -- 8 MB cache
PRAGMA synchronous=NORMAL;

For the table structure, keep it simple. A comments table with id, post_id, author, email, body, created_at, and status fields is all you need. Don't over-normalize for a comment system.

Handling Concurrent Writes

SQLite serializes writes at the database level, which means only one write can happen at a time. For a comment system, this is actually fine because comments arrive one at a time from individual users. The write lock is released in milliseconds.

If you're worried about slow writes blocking reads, set a busy timeout:

PRAGMA busy_timeout=5000;  -- Wait up to 5 seconds before giving up

This ensures that read operations wait patiently for a write to complete rather than failing immediately.

Deployment Considerations

Store your SQLite database file outside the web root to prevent direct downloads. A good location is /var/data/comments.db with restricted permissions. Your web application user should be the only one with read and write access.

Use a cron job for regular backups. A simple script that copies the database file to a backup location every hour is more than enough for most blogs. You can also use sqlite3 .backup for hot backups without downtime.

The Forward-Looking Takeaway

The database world is moving toward specialization, not consolidation. SQLite is carving out a legitimate space in production environments where simplicity and reliability matter more than raw concurrent throughput. For Croatian bloggers, this means you can run a fast, reliable comment system without the overhead of a separate database server. Your time is better spent writing great content about local beaches, restaurants, and culture than tuning PostgreSQL parameters. Make the switch, simplify your stack, and focus on what actually matters to your readers.