Mistakes Beginners Make When Working with Databases
craigkerstiens.com
Mistakes Beginners Make When Working with Databases
1–10 of 209 posts
Re: Mistakes Beginners Make When Working with Databases
#2If only developers learned more about the DB engines they use and their capabilities.
I've seen so many bone headed decisions (EAV "schemas" being a recurring one) because it was easier to program around the DB than learn how to use it proper. It's not because a few classes insulate you from the bone headed SQL that it's a good idea.
Re: Mistakes Beginners Make When Working with Databases
#3Great post. I find that many front end developers start with the User Design first, ignoring how the data should be stored or accessed. While user centricity is a great value, if you worry about screens needed rather than data needed, you cause trouble down the road. I've found that a data-first mental model is much more scalable and supportable. Many times the details of the databases get abstracted away, and performance suffers.
Re: Mistakes Beginners Make When Working with Databases
#4Not sure about point 3 ("use UUIDs instead of integer PKs"). Exhausting a 32-bit int takes a lot of usage; exhausting a 64-bit one is completely out of reach for almost everyone. 128-bit UUIDs will take more space to index than ints or bigints and are less human-readable. Depending on what UUID version you use you may or may not lose ordering, which can be a nice-to-have.
I think this isn't a question of ints being a mistake and UUIDs being better, it's about using the most appropriate type based on requirements.
Re: Mistakes Beginners Make When Working with Databases
#5point one may make a huge difference in page load time.... s3 is fast but it isn't no connection instant and cached like b64
Re: Mistakes Beginners Make When Working with Databases
#6Not sure about point 3 ("use UUIDs instead of integer PKs"). Exhausting a 32-bit int takes a lot of usage; exhausting a 64-bit one is completely out of reach for almost everyone. 128-bit UUIDs will take more space to index than ints or bigints and are less human-readable. Depending on what UUID version you use you may or may not lose ordering, which can be a nice-to-have. I think this isn't a question of ints being a…
With scale its not about key exhustation, its the ability to be able to create a key in multiple servers without having to consult a central location.
Re: Mistakes Beginners Make When Working with Databases
#7Not sure about point 3 ("use UUIDs instead of integer PKs"). Exhausting a 32-bit int takes a lot of usage; exhausting a 64-bit one is completely out of reach for almost everyone. 128-bit UUIDs will take more space to index than ints or bigints and are less human-readable. Depending on what UUID version you use you may or may not lose ordering, which can be a nice-to-have. I think this isn't a question of ints being a…
This was probably the weakest suggestion in the article.
That said, the default SERIAL type in postgres is 32-bit signed integer so it can be surprising when you exceed 2B items.
Re: Mistakes Beginners Make When Working with Databases
#8IMO normalizing up front is rarely ever a mistake. The primary advantage of normalization is it makes your schema amenable to changes in requirements. What if category ends up needing a description, and an id for a permalink-able page with links to each post in a category, etc.? Normalize up front and then denormalize where you need to when the requirements crystalize and your focus becomes responsiveness.
Re: Mistakes Beginners Make When Working with Databases
#9Not sure about point 3 ("use UUIDs instead of integer PKs"). Exhausting a 32-bit int takes a lot of usage; exhausting a 64-bit one is completely out of reach for almost everyone. 128-bit UUIDs will take more space to index than ints or bigints and are less human-readable. Depending on what UUID version you use you may or may not lose ordering, which can be a nice-to-have. I think this isn't a question of ints being a…
Actually, uuid4 as a pkey will cause pathological insertion performance on a b-tree since you are randomly inserting across the tree (having to constantly split pages) rather than appending to a far extent.
If you want to use uuids, uuid1 may be a better bet.
Re: Mistakes Beginners Make When Working with Databases
#10The first point is strange, borderline wrong.
What they really MEAN is don't store images in your general purpose database, in particular as long strings.
There are however databases with first party support for image storage, which is useful because now you can store the image and metadata about the image together (as well re-using existing solutions like replication, authentication, etc).
Additionally their "solution" is bizarre, they've jumped from using a database to a paid service by a third party, which is itself backed by a database. That seems very "apples & oranges" to me, I mean it would obviously work, but is a big jump from in-house development using a general purpose database.
To give one specific example have they not heard about Oracle Multimedia? That's exactly what it is designed to offer.