Update: The migration went smoothly. Please email us at our support email if you experience any issues.
We’re excited to announce that we’ll be migrating our database to a new design on Sunday, October 6, from 12:00 AM PDT to 10:00 AM PDT. This upgrade is the result of a significant development effort, and we’re eager to share the benefits with our users.
The main reason for this migration is to improve the performance of Port87. Our old database design was causing slow queries, which resulted in slower page load times, search results, and email opening times. Our new database design addresses this issue by allowing us to optimize queries and take advantage of advanced indexing capabilities. This means that Port87 will be able to operate much faster, providing a better experience for our users.
On Sunday, October 6, Port87 will be unavailable from 12:00 AM PDT to 10:00 AM PDT while we complete the migration. We apologize for any inconvenience this may cause and appreciate your patience during this time. Please note that you don’t need to take any actions to prepare for the migration. Simply be aware that the service will be unavailable during the scheduled downtime.
After the migration, you can expect to see significant improvements in the performance of Port87. Page load times, search results, and email opening times will all be much faster. In fact, our testing has shown that many queries will be 10-20 times faster, with some examples seeing improvements of up to 35 times. Specifically, searching with single keywords, like “delivery” will be significantly faster. While the product will function largely the same, the improved performance will make it feel more responsive and efficient.
We’re thrilled to be able to share these benefits with our users. We’re confident that this new database design will have a positive impact on your experience with Port87, and we’re excited to hear your feedback.
Thank you for your understanding and patience during the migration. If you have any questions or concerns, please don’t hesitate to reach out to us at our support email or on Mastodon.
Appendix: Technical Details of the Database Migration
The database’s new design is a significant undertaking that will result in substantial performance improvements. In this appendix, we’ll delve into the technical details of the old and new designs, and explain why the new design is better equipped to handle our users’ needs.
The old design was based on MySQL and used three tables for each type of data entity, including one for comparisons that was designed when the system ran in PHP. While this design was sufficient for our initial needs, it had several limitations that became apparent as our user base grew.
Limitations of the Old Design
The old design was limited by the use of MySQL and the ORM’s query translation. One of the main limitations was the lack of full-text indexes. This meant that queries that required full-text searching were slow and inefficient. Additionally, the query planner in MySQL often struggled to optimize queries, resulting in slow sorting and retrieval of data. Even when indexes were used, the query planner would sometimes choose them inefficiently, leading to slow query execution. Recently, we patched the MySQL queries to always choose a sorted index. While this improved the queries used to display mail from individual labels and the aggbox, it significantly slowed the queries used to retrieve mail for a specific thread, which are run when an email is opened.
Improvements in the New Design
Our new design is based on Postgres and uses two tables for each entity type. This change allows us to take advantage of Postgres’ more advanced indexing capabilities, including full-text (trigram) and JSON indexing. This means that every query can take advantage of these indexes, resulting in faster query execution and more efficient sorting throughout the entire app.
Performance Improvements
During testing, we saw significant performance improvements across all queries. The best examples saw a 35 times improvement in speed, with many other queries seeing improvements of 10-20 times. This is a direct result of the new design’s ability to take advantage of the advanced indexing and query optimization offered by Postgres. With the new design, we’re confident that our users will see a noticeable improvement in the performance of our product.
Conclusion
In conclusion, the new design is a significant improvement over the old design. By switching to Postgres and taking advantage of advanced indexing capabilities, we’ve been able to achieve substantial performance improvements that will allow us to continue to serve our users’ needs as our user base grows. We’re excited to bring these improvements to our users and look forward to continuing to optimize and improve our product.
Open Source
Port87’s database abstraction layer is open source! You can see all of its code, use it in your own projects, and even contribute, on the Nymph.js GitHub Page.