The application currently uses SQLite as its database. While SQLite works well for an application of this size, its limitations may hinder our growth and ability to scale effectively.
Primary pros and cons to consider
Complexity: PostgreSQL is more complex to set up and maintain compared to SQLite, which is essentially just a file. If the role of this application remains as essentially, a test for potential job applicants, there is an argument here to keep SQLite for simplicity and portability. Of course, if the role of the applicant includes DBA-like or PostgreSQL-specific skills then moving to PG may have merit.
Resource usage: PostgreSQL requires more system resources and needs to run as a separate service, unlike SQLite which is embedded in the application. This includes running something like PGadmin or dBeaver in order to manage the added complexity/capabilities of PG.
Migration effort: Converting from SQLite to PostgreSQL requires changes in the codebase and potentially in the data structure, which can be time-consuming. Given our use of SQLAlchemy to abstract away the underlying database to a significant degree, this may be a small issue.
Cost: If we use a managed PostgreSQL service in the cloud, it would incur additional costs compared to SQLite. However, given that PG is free and fairly straightforward to install on most machines, cost may be a non-issue.
Some more reasons to migrate: improve scalability, concurrency, and feature set of our application.
Finally, if a version of this application is planned to go live and be used in a larger context than to screen applicants, the benefits mentioned above have more value.
Justification
Assuming the pros and cons above come out in favor of exploring the pro-migration choice.
Current limitations with SQLite
Concurrency: SQLite locks the entire database on writes, which can lead to performance issues as our user base grows.
Scalability: SQLite is not designed for high-volume applications or large datasets.
Feature set: We're limited from using advanced database features that could optimize our application.
Benefits of PostgreSQL
Improved concurrency: PostgreSQL allows multiple simultaneous reads and writes, essential for web applications.
Better scalability: Can handle larger datasets and more users efficiently.
Advanced features: Offers full-text search, JSON support, and complex queries that can enhance the application's capabilities.
Robustness: Better crash recovery and data integrity checks.
Remote access: Allows for distributed setups and easier management in cloud environments.
Implementation overview
Update dependencies to include PostgreSQL adapter.
Modify database configuration to use PostgreSQL.
Adjust models and migrations if necessary.
Set up PostgreSQL in dev and production environments.
Migrate existing data.
Update CI/CD pipelines (like GitHub Actions).
Testing to ensure functionality.
Risks and mitigation
Data Loss: Ensure thorough backups before migration. As our database currently stores a negligable amount of data (which is purely test data at this point), this should be a non-issue.
Downtime: Again, potentially, a non-issue due to the nature of this application.
Performance: Monitor and optimize queries post-migration.
Learning curve: Depending on team's current PostgreSQL expertise, provide team training on PostgreSQL management. But I'm getting that PG is in regular use at Victory, so probably a non-issue.
Estimated effort
Development: 1-2 days
Testing: 1-2 days
Deployment and Monitoring: 1-2 days
Success criteria
All existing functionality works with PostgreSQL.
No data loss during migration.
Improved performance under concurrent user load.
All tests passing with new database backend.
Additional considerations
Cost implications of hosting PostgreSQL (if using cloud services).
Long-term maintenance and backup strategies.
Potential for leveraging new PostgreSQL features in future development.
Conclusion
While this migration requires initial effort, the long-term benefits in terms of scalability, feature set, and robustness make a compelling case for transitioning to PostgreSQL. This change will position our application for future growth and more advanced feature development.
Background
The application currently uses SQLite as its database. While SQLite works well for an application of this size, its limitations may hinder our growth and ability to scale effectively.
Primary pros and cons to consider
Finally, if a version of this application is planned to go live and be used in a larger context than to screen applicants, the benefits mentioned above have more value.
Justification
Assuming the pros and cons above come out in favor of exploring the pro-migration choice.
Current limitations with SQLite
Benefits of PostgreSQL
Implementation overview
Risks and mitigation
Estimated effort
Success criteria
Additional considerations
Conclusion
While this migration requires initial effort, the long-term benefits in terms of scalability, feature set, and robustness make a compelling case for transitioning to PostgreSQL. This change will position our application for future growth and more advanced feature development.