Mistakes Beginners Make When Working with Databases • Hacker News Board

Re: Mistakes Beginners Make When Working with Databases

#161

Post by dragonwriter » Wed, Jun 08, 2016, 6:14 PM UTC

combatentropy wrote: post #131

davidw wrote: post #72

Earlier quoted context omitted.

Oh yes! We ran into precisely this problem when we had a number of machines that mostly run independently, that needed to communicate with a central server. Integer ID's don't work well for that, and especially in light of the fact that Mysql reuses them in certain circumstances. I couldn't do anything about Mysql being on the machines, but used Postgres on the server, of course. The second generation of machines, wh…

Postgres's concurrency control ( https://www.postgresql.org/docs/9.5/static/mvcc.html ) does fine with many clients adding rows with numeric IDs derived from a sequence. In Postgres a sequence is a database object that always returns the number 1 more than the number it last gave someone (This is customizable too, https://www.postgresql.org/docs/9.5/static/sql-createsequenc... ). With 64-bit integers (in Postgres, "b…

> Postgres's concurrency control (https://www.postgresql.org/docs/9.5/static/mvcc.html) does fine with many clients adding rows with numeric IDs derived from a sequence.

Which is fine for circumstances where a database round trip is acceptable at that point; there's situations where assigning IDs to items is something you want to have happen without database interaction at all.

Re: Mistakes Beginners Make When Working with Databases

#162

Post by mercer » Wed, Jun 08, 2016, 6:16 PM UTC

muxxa wrote: post #113

davidw wrote: post #72

Earlier quoted context omitted.

Oh yes! We ran into precisely this problem when we had a number of machines that mostly run independently, that needed to communicate with a central server. Integer ID's don't work well for that, and especially in light of the fact that Mysql reuses them in certain circumstances. I couldn't do anything about Mysql being on the machines, but used Postgres on the server, of course. The second generation of machines, wh…

Am I the only one who thinks that primary keys should be derived from the actual data? That way it's impossible for two processes to accidentally create the same conceptual piece of data (which is still possible with uuids). It also makes it much easier to recover from situations when you have to quickly promote a slave to a master role without first verifying that the slave is up to date. The main bonus though is th…

Isn't the timestamp pretty much the most likely thing to be unique in the data, generally?

Re: Mistakes Beginners Make When Working with Databases

#163

Post by mathattack » Wed, Jun 08, 2016, 6:18 PM UTC

lloyd-christmas wrote: post #157

developer2 wrote: post #126

Earlier quoted context omitted.

>> how your app functions defines how you should store your data I think this is the fallacy the parent you are replying to is attacking. Your data should stand apart from your application. Applications change over time. Many databases also serve multiple applications (web, mobile, api, stats, etc.). Your data store should make sense on its own without being tied to an application's design. If you build your database…

> Many databases also serve multiple applications... Your data store should make sense on its own without being tied to an application's design. While your point is valid, I think it's theory vs. practice. I think most databases don't get used for wildly different applications. I'd rather design my database for something that I know is performance-sensitive than design it for an imaginary application that might exist…

While your point is valid, I think it's theory vs. practice. I think most databases don't get used for wildly different applications. I'd rather design my database for something that I know is performance-sensitive than design it for an imaginary application that might exist in the future.

Reminds me of the cynical saying for data warehouses, "Data in, but never out." :-)

What I have seen frequently enough is myopic data designs hurting flexibility later. For example - assuming 2 level customer relationships (Corporate parent and individual store) with things like regions appearing at tags, rather than flexible hierarchies. The assumptions behind this then gets built into the code base, and fixing it requires more than just a database update. (And even if you fix the database, you are missing the historical hierarchies)

Re: Mistakes Beginners Make When Working with Databases

#164

Post by VLM » Wed, Jun 08, 2016, 6:19 PM UTC

sshykes wrote: post #78

drumdance wrote: post #74

Earlier quoted context omitted.

Integer IDs are definitely easier, but I've seen it cause so many security issues -- "hmm, I wonder what happens if I manually type in /user/239?" IME It's easier to teach junior devs not to use integers than it is to get them to think holistically about security. This relates to the "sometimes security by obscurity is okay" post from yesterday.

Vehemently disagree. What you are proposing is everything wrong with security-by-obscurity. In this case the security hole of /user/123 just needs to be properly locked down. That is all.

password reset email #123 gets url /user/123

password reset email #124 gets url /user/124

password reset email #125 gets url /user/125 but that doesn't work because someone predicted it and got there before the requestor. no idea what account they'll get, but they'll get an account of some type.

This also comes up in shipping records. OK where do we go to steal an XYZ delivered today and sitting on a front porch? Well lets check

/shippinglabel/345

/shippinglabel/346

/shippinglabel/347 oh look delivered today, sitting on back porch step, and the address is right there

Another fun one is online financial documents with sequential accounts.

Re: Mistakes Beginners Make When Working with Databases

#165

Post by davidw » Wed, Jun 08, 2016, 6:22 PM UTC

VLM wrote: post #158

ryanbrunner wrote: post #127

Earlier quoted context omitted.

The problem is there's very few examples of real-world data that actually matches a primary key - i.e. is guaranteed to be unique and never changes. I've been burned by this so many times. In reality, everything needs to at least have some capability to change.

Unique and never changes can be two things concatenated, not one thing. If that concatenation is too long you can ram anything thru a hash to get a constant length smoothly distributed key. Its also fun to use "weak" hashes for this because it trolls wanna be security types who don't understand the application. Given the above, some important things concatenated and hashed works. Can always add an application ID or i…

This is a lot of thinking and work to replace an auto-incremented integer, which is simple and works fine in most cases, though.

Re: Mistakes Beginners Make When Working with Databases

#166

Post by dllthomas » Wed, Jun 08, 2016, 6:33 PM UTC

brianwawok wrote: post #139

muxxa wrote: post #113

Earlier quoted context omitted.

Am I the only one who thinks that primary keys should be derived from the actual data? That way it's impossible for two processes to accidentally create the same conceptual piece of data (which is still possible with uuids). It also makes it much easier to recover from situations when you have to quickly promote a slave to a master role without first verifying that the slave is up to date. The main bonus though is th…

Okay so you run a school and want to make a students database. What do you use a a PK? 1) Social security #? Fails when you have international student. 2) Last Name, First Name, Middle Name? Fails when you have a repeat name. 3) Last Name, First Name, Middle Name, Home Town, Start Year? I guess this works for most of the time. Now you need to join to this table from the classes table. So now you need all 5 keys dupli…

Also, names can change!

Re: Mistakes Beginners Make When Working with Databases

#167

Post by VLM » Wed, Jun 08, 2016, 6:36 PM UTC

Illniyar wrote: post #6

mjw_byrne wrote: post #4

Not 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.

That brings back the title of the article.

Beginners aren't making distributed systems on legacy 32 bit hardware (microcontrollers?) that max out a 32 bit space.

Really the article needs to bifurcate into

1) Actual mistakes beginners really make, like sucking entire tables over the network into local arrays and then hand writing a (slow and buggy) emulation of the DB server's JOIN. Bonus points for not memoizing/caching those giant tables. Another beginner comedy is NIH reinventing of the concept of having an index for speed, written entirely in slow application code. Turing complete being what it is, beginners sometimes try to write a rational database manager in their application code, not knowing if its really handy and could cut down on round trips and bandwidth, someone probably added that to the RDBMS code back in 1990. Another mistake beginners make is scaling, designing a 1E9 system for a 1E3 problem or vice versa. Another mistake beginners make is thinking some web post from '96 is relevant because nothing has changed since then (like storage and speed and application load) vs ignoring other posts from '96 because this time really nothing has changes since then (like basic computer science concepts), or rephrased 90% of the web should be ignored and beginners will ignore the wrong 90%.

2) Things beginners do that sounded like a great idea like not normalizing their data that turn into a 3-ring circus of writing convoluted application layer code to work around it. Sometimes you can write hundreds of LoC to avoid each line of SQL. Or getting into a habit of writing "baby's first todo app" using small 32 bit ints and then blowing it when they move up in the world into giant distributed systems.

Re: Mistakes Beginners Make When Working with Databases

#168

Post by kafkaesq » Wed, Jun 08, 2016, 6:36 PM UTC

edw519 wrote: post #69

Nice starter list. Here's more, off the top of my head: - failure to lock rows with pessimistic locking - failure to consider optimistic locking when viable - data sets within a table (undernormalization) - too much duplicate app code that should be stored procedure - overuse of stored procedures that should be in app code - appending with insert - appending to datasets with variable length - "smart" keys that can ne…

appending with insert

As opposed to?

Re: Mistakes Beginners Make When Working with Databases

#169

Post by dllthomas » Wed, Jun 08, 2016, 6:38 PM UTC

mercer wrote: post #162

muxxa wrote: post #113

Earlier quoted context omitted.

Am I the only one who thinks that primary keys should be derived from the actual data? That way it's impossible for two processes to accidentally create the same conceptual piece of data (which is still possible with uuids). It also makes it much easier to recover from situations when you have to quickly promote a slave to a master role without first verifying that the slave is up to date. The main bonus though is th…

Isn't the timestamp pretty much the most likely thing to be unique in the data, generally?

Including timestamp may be appropriate for log data, where otherwise identical records differing by time should be considered different entities. Otherwise it loses the property asked for upthread that our primary key detects/eliminates duplication.

Re: Mistakes Beginners Make When Working with Databases

#170

Post by dllthomas » Wed, Jun 08, 2016, 6:43 PM UTC

VLM wrote: post #158

ryanbrunner wrote: post #127

Earlier quoted context omitted.

The problem is there's very few examples of real-world data that actually matches a primary key - i.e. is guaranteed to be unique and never changes. I've been burned by this so many times. In reality, everything needs to at least have some capability to change.

Unique and never changes can be two things concatenated, not one thing. If that concatenation is too long you can ram anything thru a hash to get a constant length smoothly distributed key. Its also fun to use "weak" hashes for this because it trolls wanna be security types who don't understand the application. Given the above, some important things concatenated and hashed works. Can always add an application ID or i…

"Hash of the data defining the entity identified" is how version 3 and 5 UUIDs work (plus a namespace). If you really want a weak hash use version 3 - it uses MD5.