MVCC Misconceptions: The Indie Hacker's Database Choice Dilemma

Database selection is a critical decision, especially for indie hackers developing their own projects. Often, technology choices are driven by PR, and the daily impact of fundamental mechanisms like MVCC (Multi-Version Concurrency Control) can be overlooked. However, this can lead to unexpected performance issues and surprises during the development process. In this post, I will share my experiences on what MVCC means in practice, how databases like PostgreSQL and SQLite behave in scenarios commonly faced by indie hackers, and the long-term implications of these choices.

For indie hackers, database selection is usually made based on immediate needs. Concerns like project scalability, maintenance costs, and potential performance bottlenecks might take a backseat in the initial stages. However, over time, as the application grows, these "small" technical details can determine the fate of a project. Understanding what MVCC is and why it's important is the first step to minimizing such risks.

What is MVCC and Why is it Important?

MVCC is a mechanism used in databases to manage concurrent access. Its core idea is to maintain different versions of data for each transaction. This allows one transaction to read or write data without blocking other transactions. In traditional locking-based systems, a read operation can block a write operation, and a write operation can block a read operation. MVCC reduces these blockages, enabling the database to achieve higher levels of concurrency.

This mechanism is vital, especially for high-traffic web applications and systems requiring intensive read/write operations. For example, in an e-commerce site, there might be transactions simultaneously updating product stock and user-facing transactions displaying products. Thanks to MVCC, product listing operations are not affected while stock updates occur, which positively impacts the user experience.

ℹ️ Key Benefits of MVCC

• Increased concurrency
• Reduced locking
• Better read performance
• More consistent read results

The operating principle of MVCC relies on tagging each row with timestamps or visibility maps. When a transaction reads a row, it sees the version of the row valid from the moment that transaction started. If another transaction updates the same row, a new version is created, and the old version is not immediately deleted. When these old versions are deleted depends on the database's garbage collection mechanism. This management of "old versions" is one of the most crucial practical aspects of MVCC and has a direct impact on performance.

PostgreSQL: A Robust MVCC Implementation

PostgreSQL is one of the databases that implements MVCC in the most advanced way. Every update or delete operation creates a new version of the row and does not immediately remove the old version. This is managed by structures like "transaction ID" and "visibility map". A transaction determines which row versions it can see based on its own transaction ID.

The most significant result of this approach is that PostgreSQL performs relatively well even in high-concurrency scenarios. While one user views a report (intensive read), another user can simultaneously place an order (intensive write). PostgreSQL, thanks to MVCC, isolates these two operations as much as possible. However, this is not a perfect solution; especially long-running transactions or frequently updated data increase the importance of the VACUUM operation.

💡 The Role of VACUUM in PostgreSQL

PostgreSQL uses the VACUUM command to clean up old data versions, called "dead tuples," to support MVCC. When VACUUM is not run or not run frequently enough, the database size increases, query performance decreases, and the statistics from the ANALYZE command become outdated. Even if autovacuum is enabled by default, manual adjustments or more frequent triggers might be necessary for heavily loaded systems.

While working on an ERP system for a manufacturing company, I encountered a scenario where the VACUUM operation was not running in a timely manner. Daily stock movements amounted to millions, and each movement created a new row version. autovacuum was running with default settings, but "dead tuples" were accumulating due to the heavy load. As a result, SELECT query performance dropped by up to 30%. To resolve the issue, I optimized database size and query times by reducing autovacuum parameters (especially autovacuum_vacuum_threshold and autovacuum_analyze_threshold) and using the VACUUM FULL command (with caution regarding disk space). This experience showed me how critical the "cleanup" operations behind the power of MVCC truly are.

SQLite: Simplicity and MVCC Limitations

SQLite is one of the first names that come to mind when talking about embedded and lightweight databases. Stored typically as a single file, this database is popular among indie hackers due to its extreme simplicity in setup and management. However, SQLite's MVCC implementation differs considerably from PostgreSQL's and has some limitations.

SQLite's default WAL (Write-Ahead Logging) mode helps it implement MVCC more effectively. In WAL mode, write operations are recorded in a separate file called sqlite-wal. This allows read and write operations to occur concurrently. That is, while one transaction writes data, other transactions can read data simultaneously. This offers a sufficient level of concurrency for many indie hacker projects.

However, due to SQLite's fundamental architecture, only one write operation can occur at a time. When multiple threads try to write to the same database file, one thread will lock the database file, and the others will have to wait. This can create a significant bottleneck, especially when the application's concurrent write load increases.

⚠️ Concurrent Write Limitations in SQLite

Because SQLite operates through a single database file, it supports only one write operation at a time. If multiple threads attempt to write concurrently, deadlocks and wait times become inevitable. This situation is a major disadvantage for applications with high write traffic.

I encountered this limitation in a project where I used SQLite as the local database for a mobile application I developed. The application was continuously recording sensor data in the background, and simultaneously, there were operations changing some settings from the user interface. Initially, everything seemed fine, but as concurrency increased, the database started locking frequently for write operations. The user interface would freeze, and recordings were delayed. As a solution, I tried queuing write operations and writing them to the database serially through a single thread. This largely resolved the problem but increased the application's complexity. This experience concretely showed me the concurrency limits underlying SQLite's simplicity.

MVCC and Trade-offs for Indie Hackers

While MVCC has become a cornerstone feature for many databases, it may not perfectly suit every application's needs. When choosing a database for indie hackers, the advantages and disadvantages of MVCC should be carefully evaluated.

Performance: MVCC can improve overall performance by increasing the concurrency of read and write operations. However, this improvement depends on how the database is structured, data volume, and query patterns. Advanced MVCC implementations like PostgreSQL offer excellent performance in complex scenarios, while in simpler systems like SQLite, locking mechanisms might be more prominent.

Storage Space: MVCC keeps multiple versions of data, which can increase storage space requirements, especially for frequently updated data. Regular execution of the VACUUM operation in PostgreSQL is critical for managing this issue. In SQLite, WAL mode also creates additional files, but this impact is generally not as pronounced as in PostgreSQL.

Complexity: The complex mechanisms of MVCC can make understanding and managing the database more challenging. Especially for beginners, these details can be confusing. PostgreSQL's MVCC requires more configuration and maintenance compared to SQLite's simplicity.

💡 Database Selection Guide for Indie Hackers

• Simple, single-user projects: SQLite is usually sufficient. It's easy to set up and performs well in most scenarios.
• Web applications requiring high concurrency: PostgreSQL is a more suitable option. It offers better performance due to its advanced MVCC implementation and scalability.
• Data integrity and complex queries: PostgreSQL stands out with its ACID compliance and richer feature set.
• Mobile applications (local data storage): SQLite is preferred for its small footprint and simplicity. However, consider concurrent write needs.

At one point, I was developing a web service that powered my financial calculators. Initially, I used SQLite as the database because I wanted to quickly build a prototype. However, as the project grew and more users started performing calculations simultaneously, SQLite's concurrent write limitations became evident. Queries slowed down, and sometimes I even encountered errors. At this point, I decided to switch to PostgreSQL. Although the migration process took some time, the concurrency and performance improvements provided by MVCC proved critical for the project's long-term success. This transition once again showed me how important it is to correctly evaluate trade-offs.

Real-World Scenarios and Performance Analyses

To better understand the practical implications of MVCC, let's look at examples from real-world scenarios. These scenarios will illustrate how different databases' MVCC mechanisms behave and their effects on performance.

Scenario 1: E-commerce Site Stock Management

On an e-commerce site, thousands of users are simultaneously viewing products while stocks are being updated in the background.

• PostgreSQL: Thanks to MVCC, stock update operations (writes) do not largely block product viewing operations (reads). Users can smoothly view products while stocks are updated in the background. However, very frequent and large-scale stock updates might require VACUUM to run more often. For instance, if there are hundreds of stock updates per second during a sale campaign, the n_dead_tup (number of dead tuples) in the pg_stat_user_tables table can increase rapidly.
• SQLite (WAL Mode): WAL mode enables concurrent reading and writing. However, if multiple stock update operations arrive simultaneously, these operations become serialized. If the system needs to perform 500 stock updates per second and each update takes an average of 10ms, SQLite might struggle to handle this load alone, and wait times could increase. Although settings like PRAGMA busy_timeout = 5000; exist, the fundamental locking mechanism will be limiting.

Scenario 2: Blog Comment System

On a blog site, readers comment on articles while editors add new articles or update existing ones.

• PostgreSQL: MVCC seamlessly separates comment submission and article addition operations. When a user submits a comment, an editor adding a new article does not directly affect their transaction. pg_locks table typically shows only short-lived and specific locks.
• SQLite: Similarly, with WAL mode, comment and article addition operations can run concurrently. However, if a large number of comments arrive simultaneously and attempt to be written to the database at the same time, SQLite's single write lock can come into play. This situation can lead to delayed comments or users receiving "database is locked" errors.

Scenario 3: Mobile Application Synchronization

A mobile application stores user data locally and synchronizes it with a server periodically.

• SQLite: This is one of SQLite's most common use cases. While the application reads data, new data from the server can be synchronized in the background. WAL mode ensures this synchronization occurs without degrading the application's overall performance. However, if a large amount of data is being written during synchronization and, at the same time, data is being changed from the user interface, deadlocks can occur. In one project, I tried to manage these deadlocks using sqlite3_busy_handler during synchronization, but this increased the complexity of the code.

These scenarios demonstrate how MVCC can yield different results in various databases. For indie hackers, choosing the right database by considering the project's current and future needs is key to avoiding such performance bottlenecks.

Conclusion: A Pragmatic Approach

MVCC is a cornerstone of modern databases and a powerful mechanism for resolving concurrency issues. However, MVCC itself is not a magic wand. Application architecture, database selection, and configuration determine the extent to which the benefits of MVCC will be realized.

As indie hackers, our resources are often limited, and our time is valuable. Therefore, when choosing a database, it's essential to evaluate how well it fits the project's real needs, rather than just how "cool" the technology is. PostgreSQL, with its advanced MVCC capabilities and scalability, is a strong option for many scenarios, while SQLite's simplicity and lightweight nature can make it more suitable for specific use cases.

In my own experiences, I've seen that understanding the details of a database's MVCC implementation is critical for predicting and resolving performance issues. Topics like the importance of VACUUM in PostgreSQL or concurrent write limitations in SQLite, when overlooked early in a project, can become the source of major headaches later on.

In conclusion, understanding what MVCC is and what it means in practice will help indie hackers make more informed and strategic database choices. This not only optimizes the performance of current projects but also lays a solid foundation for future growth and scalability.