![]() I manually tested some queries on my development laptop and felt everything was faster with the PostgreSQL database, which was an encouraging first impression. Once again, SQLite happily stored my UUIDs on this column and even indexed them, but to get these across to PostgreSQL I needed to fix the schema and properly declare these keys as strings.Īt this point, with relatively low effort, I could switch between equivalent SQLite and PostgreSQL databases just by editing the database URL in a configuration file and restarting the Python server. When I made this change in SQLite I migrated the table by hand, and didn’t realize that I left the primary key column on this table defined as integer. Also related to typing, one of my tables initially had integer primary keys, but at some point I decided to change these keys to UUID strings.The solution was to grow the slug column to 512 characters and retry. PostgreSQL has strict enforcing of its column types, so these four posts could not be migrated. SQLite, with its unusual typing system, did not care and stored the longer slugs without complaining. The data migration failed for four articles that had slugs that were longer than the maximum. For the article slugs, I have defined a 256 long VARCHAR column.The initial failed attempts alerted me of a couple of potentially serious issues I had in my database design, which SQLite decided not to report or care about: Here is the one that I’ve made:Īfter a couple of tries, the import process worked without a hitch. The easiest way to import data with pgloader is to create a configuration file that defines the source, the destination and the options. Luckily, SQLite is one of the supported data sources. I used pgloader, a fairly complete open source script that imports data from several sources into a PostgreSQL database. To enable the application to fully work with PostgreSQL, I also needed to migrate the data currently stored in the SQLite database. SQLAlchemy even created all the tables and indexes on my new PostgreSQL database, exactly as they were in SQLite. The Python code did not need any modifications, as the differences between these databases were handled internally by SQLAlchemy. By making the database connection URL configurable I made it possible to switch between postgresql:// and sqlite:// URLs. So I installed PostgreSQL on my Mac laptop to get things working in development.Īdapting the code so that it is compatible with PostgreSQL was relatively easy to achieve because this application uses SQLAlchemy, an ORM library for Python that allows the developer to create schemas and queries using mostly database-independent Python constructs. The first step that I took was to make sure that I can easily reconfigure the application to use SQLite or PostgreSQL, as this would allow me to run side-by-side comparisons. What follows is a detailed account of my effort, the results of my testing (including a surprising twist!), and my analysis and final decision, which ended up being more involved than I expected. Having publicly professed my dislike of performance benchmarks, I resisted the urge to look up any comparisons online, and instead embarked on a series of experiments to accurately measure the performance of these two databases for the specific use cases of this application. But almost a year and a half later, this application tracks daily traffic for close to 8000 articles across the Twilio and SendGrid blogs, with about 6.5 million individual daily traffic records, and with a user base that grew to over 200 employees.Īt some point I realized that some queries were taking a few seconds to produce results, so I started to wonder if a more robust database such as PostgreSQL would provide better performance. I chose SQLite for the database that supports this dashboard, which in early 2021 when I built this system, seemed like a perfect choice for what I thought would be a small, niche application that my teammates and I can use to improve our blogging. Users can interactively request charts and tables while filtering and grouping the data in many different ways. To help us understand what content works well and what doesn’t on our blog, we have a dashboard that combines the metadata that we maintain for each article such as author, team, product, publication date, etc., with traffic information from Google Analytics. We take blogging very seriously at Twilio.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |