Then we have the roles table which can contain global roles like admin and support as well as roles tied to specific resources like team_owner and team_collaborator.Ĭreating a relationship between the two is the users_roles join table. It doesn't feature strongly in the example so I'll leave it at the id column. If you want to skip over the example setup, you can jump right to the details.įirst is the users table. These roles help the system determine the access and permissions of each user. Most software systems have users and those users need to be given a variety of roles. This minimal example is also a real-world example. Without that index, we're bound to run into some really sneaky performance gotchas. I don't tend to think of foreign key constraints as impacting performance. We can combine the two to start getting the most out of our database. Much better! Toward the end of the post we'll see how to make it even faster than that.īut before I reconstruct a minimal example that reproduces the problem and get into the details, here is the.įoreign keys are essential for enforcing the shape and integrity of our data. 10,000x slower! □ That's a nope.Īfter diagnosing and addressing the issue, I got that second delete query down to about 1 second. I then had a similar query deleting the same number of records from another table that would run for ~30 minutes. I had a query deleting 50k records on one table in ~100ms. Let me give you an idea of how bad it was. And I'm paying the lesson forward with this article. I'm better equipped to diagnose the next performance skirmish that comes my way. I didn't just solve the problem, I now better understand Postgres. I got absolutely thrashed by some PostgreSQL performance issues this past week.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |