Database Comparison (PostgreSQL and SQLite)

2023-07-28

When considering the choice between PostgreSQL and SQLite for my project, several factors come into play.

1. The Database System/Engine

SQLite

Initially, my project utilized SQLite, which presented some distinct advantages:

Fast Integration: SQLite allowed for swift integration into my project.
Easy Backups: Backing up the database was a breeze, simply involving copying the file from the server to my local machine.

However, it also had its drawbacks:

Concurrency Issues: I encountered difficulties when multiple operations like crawling, administrative viewing, and uploading occurred simultaneously.
Schema Alterations: Modifying column types or names was cumbersome, often requiring me to drop the database and start from scratch.
Database Maintenance: After pruning data, the “vacuum” command was necessary to reduce the file size, which could be a manual task.

PostgreSQL

In response to the limitations of SQLite, I made the switch to PostgreSQL, which addressed these issues effectively. Here are some notes on PostgreSQL:

Ease of Setup: While it may not be as straightforward to set up as SQLite, using Docker can simplify the process.
Backup Management: PostgreSQL allows me to create backups easily using tools like Makefile, and I might consider automating backups using Docker and cron.
Migration: PostgreSQL offers straightforward migration support, especially when using Jetbrains’ database tool.

2. Schema Management

Regarding schema management, I first used SQLAlchemy to quickly create table schemas. However, I later prefer Symfony’s doctrine:schema:update, which is a powerful tool but not recommended for production use due to it doesn’t give you a rollback plan.

In summary, while SQLite offers simplicity and speed in integration and backups, PostgreSQL shines in resolving concurrency issues and providing more robust schema management capabilities. The choice between the two depends on my project’s specific requirements and future scalability.