4 ms·

The author says, "I wouldn’t be able to explain from the top of my head what the ACID term means". I think there is a big difference between not knowing someth

The author says, "I wouldn’t be able to explain from the top of my head what the ACID term means". I think there is a big difference between not knowing something off the top of your head and being completely unaware of it. If you have awareness of something, you can make decisions based on that awareness without necessarily having a deep understanding. When you have no awareness is when the big issues arise. For example, with your MongoDB fuckup if you had even a vague idea about the underlying behaviour you might have been able to consider the failure scenario ahead of time and prevent it from occurring. A deep understanding of how MongoDB works probably wasn't required to prevent this incident. Same for indexes. If you're aware of them, you can trigger the thought process of "this query is pretty slow, maybe I should look into adding an index because I know that speeds things up". You don't necessarily have to understand how a B-tree works for an index to be effective.

dspillett 5y ago
> The author says, "I wouldn’t be able to explain from the top of my head what the ACID term means". > I think there is a big difference between not knowing something off the top of your head and being completely unaware of it. The next level is being aware that the “I” doesn't quite apply in most databases with their default transaction settings, true isolation/serialisability being sacrificed for performance, though the distinction rarely matters is can be significant. > You don't necessarily have to understand how a B-tree works for an index to be effective. How a b-tree operates can be safely kept as a black-box thing, the important level of detail that many miss in my experience is how compound indexes can (and more importantly, can't) be used. Others include that in most databases defining a foreign key relationship does not create a supporting index by default, and that careless use of functions in joining & filtering clauses often blocks index use. If you have a DB specialist on the team there is probably no need for other devs to be intimately aware of even these details, but they should at least be aware enough of their existence than they know to ask the local expert (or look things up elsewhere) to check their working when the issues might affect what they are working on. As a bit of a local DB specialist who has met some bad ones, I should point out that it is important that we, and other local “exports”, be approachable so the other devs feel they can run things by us for verification/improvement.
roenxi 5y ago
To some degree the more competent the person in using ACID databases, the less likely they are to remember what 4 random words are hidden behind ACID. If someone uses a database that makes ACID guarantees there is no benefit to remembering what they are. It is just how the database works. People only need to know about ACID if they are arguing about whether to use a database that doesn't implement it, or switching between databases some of which do and some of which don't provide strong ACID guarantees.
- Kranar 5y ago
  I disagree entirely with this, ACID only makes guarantees at the database level itself not the system as a whole; it's possible that using ACID in the database could render the system as a whole more vulnerable than not using it. Take for example MySQL/InnoDB which offers durability (the D in ACID). If you have a write heavy database you could end up in a situation where your server ends up stalling waiting for MySQL to commit the write to disk. In effect, your server ends up building a large buffer of queries to commit to MySQL and sits there waiting for MySQL to become available. If your server or system crashes, all of that data gets lost. You may have been much better off, both performance and correctness, to relax MySQL's durability from flushing on every write, to flushing/committing every 1 second (using innodb_flush_log_at_trx_commit). Doing so improves performance by multiple orders of magnitude (it can be the order of 10-100x) to the point that your server is no longer sitting there queuing up queries for writes to complete. The database loses some guarantees about durability but the system as a whole gains better durability. I could make similar arguments about the other properties as well, the point is that a competent person should actually know this stuff, understand it, not just memorize an acronym and boil the entire issue down to whether a database has ACID or not. It's not a binary matter, it's more of a continuum where each property can be tuned to various degrees. MySQL and Postgres both offer many options to tune the various ACID guarantees from having complete conformance to relaxing them with various upper bounds. Relaxing guarantees about a local property of the system can improve the guarantees of system as a whole.
vbezhenar 5y ago
> this query is pretty slow, maybe I should look into adding an index because I know that speeds things up On my current work database is riddled with indices over various status columns. Those indices are not selective and basically useless. Worse of all: it's not immediately obvious that those indices are bad, because inserts/updates are fast enough, but they slow system down as they accumulate and there's no easy way back.
- protomyth 5y ago
  Sadly, a good DBA would be able to give you information on these things, but the DBA like system admins is a fading "not needed" position. I know HN hates stored procedures, but having an application use stored procedures instead of on-the-fly queries allows a team to do the proper optimizations. Databases evolve with use and added records, and few teams devote resources to stay ahead of the changes.
  - bigbizisverywyz 5y ago
    I personally love stored procedures, and Postgres, MSSQL, Sybase and Oracle all provide pretty good languages to implement them in. In my experience the most reliable and long living projects treat the DB as its own separate product, whose interface to data mutation is via stored procs. The db can then be sanely designed with good data integrity rules (foreign keys, unique indexes and data column constraints). Then external callers use the stored procs as their API, and then mostly don't have to worry about the internals. You can easily create a web based client, REST calls or a native GUI app that all work directly off the same db. However they do have to worry about : -connection timeouts -query timeouts -deadlocks But that's the same for all distributed systems.
neilalexander 5y ago
> If you have awareness of something, you can make decisions based on that awareness without necessarily having a deep understanding. When you have no awareness is when the big issues arise. I think in many cases, it's that even just having a vague awareness of something prompts you to double-check more often or to do something about it when you aren't really sure. As an example, I find myself reading about the pros and cons of various PostgreSQL index types over and over again. It isn't really all that important for me to retain every detail about them for immediate recall but knowing that there's enough of a difference motivates me to read the documentation or run a quick web search before shooting myself in the foot. I tend to think that testing people on this kind of deep knowledge isn't really helpful nor is it indicative of how developers really operate. What you need to test them on is what steps they would take when they aren't really sure and how they learn from it.
baktubi 5y ago
Yeah that’s the difference between a “blank stare” in the interview and a decent response. Anyone applying for a database position should know that relates to transactions and yadda-yadda. They should know why transactions are important, etc. But… I’d like to say this is the kind of problem these quick-boot coding schools fall short on. Sure one can learn programming language X, but that doesn’t mean one has any sort of fundamentals in algorithms, data structures or general database technology.
jimbokun 5y ago
> For example, with your MongoDB fuckup if you had even a vague idea about the underlying behaviour you might have been able to consider the failure scenario ahead of time and prevent it from occurring. This is going to require being conversant with the terms in that ACID acronym. > A deep understanding of how MongoDB works probably wasn't required to prevent this incident. That deep understanding is exactly what was required. If you don't understand how Mongo works, you don't understand the ways it can fail to do what you think it will do. > If you're aware of them, you can trigger the thought process of "this query is pretty slow, maybe I should look into adding an index because I know that speeds things up". So why not add an index for everything ahead of time, then every query will be fast, right? Or maybe you need to have a sense of the cost of adding an index, then. Which may require knowing the different kinds of indexes and what the speed/cost tradeoffs are. I think in all these cases, not every developer has to understand these things, but if you don't have anyone in your organization that understands them and is thinking about them, things can blow up for you at unexpected times that make your customers very unhappy. And then you might need to hire someone who understands them to fix the mess.
theamk 5y ago
Well, the interviewer did not complain about getting answer partially wrong, they complained about getting a blank stare back. The interviews are not like school exams, they are not graded by the number of exactly correct answer. I don't remember what each letter of acronym means either. But if I was interviewee in this situation, I'd just start talking about transactions from user perspective -- how they either completeley pass or completely fail, and how even with power failure, you are not going to see partial data. If I were interviewer, I'd accept this answer. No one cares about hearing exact words (0), "user will never see only a part of transaction" is good enough even if person never said the word "atomicity" (0) According to some sources, Google runs scripted interviews where interviewer is non-technical, and grades answers against a checklist.
wizzwizz4 5y ago
Anecdotal evidence: I didn't know what ACID meant, but I know each of the things: • the transaction is the minimal (atomic) unit, and must never be split up; • all transactions (and everything else too) must map valid states to valid states; • parallel database transactions should be viewed as a directed asynchronous graph over valid states (like Git) • store stuff on the hard drive rather than RAM (i.e. the hard part of making databases; filesystems are horrible!) I have fairly deep understanding of these things; I just didn't know the cool acronym mnemonic. (Database engine devs don't tend to use it in their blog posts about the internals of SQLite or Postgres.) “If they didn't learn how I learnt, they don't know anything” is one of my pet peeves.

dspillett 5y ago

> The author says, "I wouldn’t be able to explain from the top of my head what the ACID term means". > I think there is a big difference between not knowing something off the top of your head and being completely unaware of it. The next level is being aware that the “I” doesn't quite apply in most databases with their default transaction settings, true isolation/serialisability being sacrificed for performance, though the distinction rarely matters is can be significant. > You don't necessarily have to understand how a B-tree works for an index to be effective. How a b-tree operates can be safely kept as a black-box thing, the important level of detail that many miss in my experience is how compound indexes can (and more importantly, can't) be used. Others include that in most databases defining a foreign key relationship does not create a supporting index by default, and that careless use of functions in joining & filtering clauses often blocks index use. If you have a DB specialist on the team there is probably no need for other devs to be intimately aware of even these details, but they should at least be aware enough of their existence than they know to ask the local expert (or look things up elsewhere) to check their working when the issues might affect what they are working on. As a bit of a local DB specialist who has met some bad ones, I should point out that it is important that we, and other local “exports”, be approachable so the other devs feel they can run things by us for verification/improvement.

roenxi 5y ago

To some degree the more competent the person in using ACID databases, the less likely they are to remember what 4 random words are hidden behind ACID. If someone uses a database that makes ACID guarantees there is no benefit to remembering what they are. It is just how the database works. People only need to know about ACID if they are arguing about whether to use a database that doesn't implement it, or switching between databases some of which do and some of which don't provide strong ACID guarantees.

Kranar 5y ago
I disagree entirely with this, ACID only makes guarantees at the database level itself not the system as a whole; it's possible that using ACID in the database could render the system as a whole more vulnerable than not using it. Take for example MySQL/InnoDB which offers durability (the D in ACID). If you have a write heavy database you could end up in a situation where your server ends up stalling waiting for MySQL to commit the write to disk. In effect, your server ends up building a large buffer of queries to commit to MySQL and sits there waiting for MySQL to become available. If your server or system crashes, all of that data gets lost. You may have been much better off, both performance and correctness, to relax MySQL's durability from flushing on every write, to flushing/committing every 1 second (using innodb_flush_log_at_trx_commit). Doing so improves performance by multiple orders of magnitude (it can be the order of 10-100x) to the point that your server is no longer sitting there queuing up queries for writes to complete. The database loses some guarantees about durability but the system as a whole gains better durability. I could make similar arguments about the other properties as well, the point is that a competent person should actually know this stuff, understand it, not just memorize an acronym and boil the entire issue down to whether a database has ACID or not. It's not a binary matter, it's more of a continuum where each property can be tuned to various degrees. MySQL and Postgres both offer many options to tune the various ACID guarantees from having complete conformance to relaxing them with various upper bounds. Relaxing guarantees about a local property of the system can improve the guarantees of system as a whole.

vbezhenar 5y ago

> this query is pretty slow, maybe I should look into adding an index because I know that speeds things up On my current work database is riddled with indices over various status columns. Those indices are not selective and basically useless. Worse of all: it's not immediately obvious that those indices are bad, because inserts/updates are fast enough, but they slow system down as they accumulate and there's no easy way back.

protomyth 5y ago
Sadly, a good DBA would be able to give you information on these things, but the DBA like system admins is a fading "not needed" position. I know HN hates stored procedures, but having an application use stored procedures instead of on-the-fly queries allows a team to do the proper optimizations. Databases evolve with use and added records, and few teams devote resources to stay ahead of the changes.
- bigbizisverywyz 5y ago
  I personally love stored procedures, and Postgres, MSSQL, Sybase and Oracle all provide pretty good languages to implement them in. In my experience the most reliable and long living projects treat the DB as its own separate product, whose interface to data mutation is via stored procs. The db can then be sanely designed with good data integrity rules (foreign keys, unique indexes and data column constraints). Then external callers use the stored procs as their API, and then mostly don't have to worry about the internals. You can easily create a web based client, REST calls or a native GUI app that all work directly off the same db. However they do have to worry about : -connection timeouts -query timeouts -deadlocks But that's the same for all distributed systems.

neilalexander 5y ago

> If you have awareness of something, you can make decisions based on that awareness without necessarily having a deep understanding. When you have no awareness is when the big issues arise. I think in many cases, it's that even just having a vague awareness of something prompts you to double-check more often or to do something about it when you aren't really sure. As an example, I find myself reading about the pros and cons of various PostgreSQL index types over and over again. It isn't really all that important for me to retain every detail about them for immediate recall but knowing that there's enough of a difference motivates me to read the documentation or run a quick web search before shooting myself in the foot. I tend to think that testing people on this kind of deep knowledge isn't really helpful nor is it indicative of how developers really operate. What you need to test them on is what steps they would take when they aren't really sure and how they learn from it.

baktubi 5y ago

Yeah that’s the difference between a “blank stare” in the interview and a decent response. Anyone applying for a database position should know that relates to transactions and yadda-yadda. They should know why transactions are important, etc. But… I’d like to say this is the kind of problem these quick-boot coding schools fall short on. Sure one can learn programming language X, but that doesn’t mean one has any sort of fundamentals in algorithms, data structures or general database technology.

jimbokun 5y ago

> For example, with your MongoDB fuckup if you had even a vague idea about the underlying behaviour you might have been able to consider the failure scenario ahead of time and prevent it from occurring. This is going to require being conversant with the terms in that ACID acronym. > A deep understanding of how MongoDB works probably wasn't required to prevent this incident. That deep understanding is exactly what was required. If you don't understand how Mongo works, you don't understand the ways it can fail to do what you think it will do. > If you're aware of them, you can trigger the thought process of "this query is pretty slow, maybe I should look into adding an index because I know that speeds things up". So why not add an index for everything ahead of time, then every query will be fast, right? Or maybe you need to have a sense of the cost of adding an index, then. Which may require knowing the different kinds of indexes and what the speed/cost tradeoffs are. I think in all these cases, not every developer has to understand these things, but if you don't have anyone in your organization that understands them and is thinking about them, things can blow up for you at unexpected times that make your customers very unhappy. And then you might need to hire someone who understands them to fix the mess.

theamk 5y ago

Well, the interviewer did not complain about getting answer partially wrong, they complained about getting a blank stare back. The interviews are not like school exams, they are not graded by the number of exactly correct answer. I don't remember what each letter of acronym means either. But if I was interviewee in this situation, I'd just start talking about transactions from user perspective -- how they either completeley pass or completely fail, and how even with power failure, you are not going to see partial data. If I were interviewer, I'd accept this answer. No one cares about hearing exact words (0), "user will never see only a part of transaction" is good enough even if person never said the word "atomicity" (0) According to some sources, Google runs scripted interviews where interviewer is non-technical, and grades answers against a checklist.

wizzwizz4 5y ago

Anecdotal evidence: I didn't know what ACID meant, but I know each of the things: • the transaction is the minimal (atomic) unit, and must never be split up; • all transactions (and everything else too) must map valid states to valid states; • parallel database transactions should be viewed as a directed asynchronous graph over valid states (like Git) • store stuff on the hard drive rather than RAM (i.e. the hard part of making databases; filesystems are horrible!) I have fairly deep understanding of these things; I just didn't know the cool acronym mnemonic. (Database engine devs don't tend to use it in their blog posts about the internals of SQLite or Postgres.) “If they didn't learn how I learnt, they don't know anything” is one of my pet peeves.