5 million individual daily traffic
Posted: Sun Dec 22, 2024 8:24 am
—with traffic information from Google Analytics. Users can interactively request charts and tables while filtering and grouping the data in different ways. Dashboard screenshot I chose SQLite for the database that powers this dashboard. Back in early 2021, when I built this system, it seemed like the perfect choice for what I thought would be a small, niche application that my colleagues and I could use to enhance our blogs. But nearly a year and a half later, this application tracks daily traffic for nearly 8,000 posts on Twilio and SendGrid blogs , with about 6.
5 million individual daily traffic records, and a user base that now includes over 200 employees. At whatsapp philippines number some point, I realized that some queries were taking a few seconds to produce results. So I started wondering if a more robust database like PostgreSQL would provide better performance. Having publicly confessed my aversion to performance testing , I resisted the urge to search for comparisons online and decided to undertake a series of experiments myself to accurately measure the performance of these two databases for the specific use cases of this application. What follows is a detailed account of my efforts, the results of my tests (including a surprising turnaround!), my analysis, and my final decision, which turned out to be more complex than I expected.
Porting the application to PostgreSQL The first step I took was to make sure I could easily reconfigure the application to use SQLite or PostgreSQL and do side-by-side comparisons. So I installed PostgreSQL on my Mac laptop to make it work in development. Adapting the code to be compatible with PostgreSQL was relatively easy to accomplish, as this application uses SQLAlchemy , an ORM library for Python that allows the developer to create schemas and queries using mostly database-independent Python constructs. By making the database connection URL configurable, I was able to switch between postgresql:// and sqlite:// URLs .
5 million individual daily traffic records, and a user base that now includes over 200 employees. At whatsapp philippines number some point, I realized that some queries were taking a few seconds to produce results. So I started wondering if a more robust database like PostgreSQL would provide better performance. Having publicly confessed my aversion to performance testing , I resisted the urge to search for comparisons online and decided to undertake a series of experiments myself to accurately measure the performance of these two databases for the specific use cases of this application. What follows is a detailed account of my efforts, the results of my tests (including a surprising turnaround!), my analysis, and my final decision, which turned out to be more complex than I expected.
Porting the application to PostgreSQL The first step I took was to make sure I could easily reconfigure the application to use SQLite or PostgreSQL and do side-by-side comparisons. So I installed PostgreSQL on my Mac laptop to make it work in development. Adapting the code to be compatible with PostgreSQL was relatively easy to accomplish, as this application uses SQLAlchemy , an ORM library for Python that allows the developer to create schemas and queries using mostly database-independent Python constructs. By making the database connection URL configurable, I was able to switch between postgresql:// and sqlite:// URLs .