10 ms·

I'm no fan of MongoDB, but this same advice goes for any NoSQL data store. I am an Apache Cassandra contributor and community MVP, but my advice stays the same:

I'm no fan of MongoDB, but this same advice goes for any NoSQL data store. I am an Apache Cassandra contributor and community MVP, but my advice stays the same: it's best just to start with a SQL database and go from there. Read some books and learn it well: the "SQL Cookbook" from O'Reilly is great, and so is "The Art of SQL." Premature optimization continues to be the root of all evil.

bunderbunder 14y ago
Don't forget to read a book that's specific to your particular RDBMS. Because SQL databases are only trivially interchangeable for trivial cases. There have been more than a couple times when I was ready to blame the relational model, but further investigation revealed that the real root of the problem was that the existing schema or query used an approach that was optimized for one DBMS but performed terribly on the one we were actually using.
- rbranson 14y ago
  This is why the "SQL Cookbook" is good, it has dozens and dozens of examples with permutations in Oracle, MS SQL, MySQL, and PostgreSQL.
- gregjor 14y ago
  Wrong. SQL databases (except for SQLite) are almost completely interchangeable because they are all based on the same relational model and they all implement the ANSI SQL standard with only minor deviations. If you have a lot of stored procedures and triggers -- executable code embedded in the database -- you will have to rewrite that. Oracle is in a world of its own in a lot of ways, but if you are moving to or from Oracle you should have the required resources and expertise in your budget. If you are referring to the well-known cost of COUNT(*) in PostgreSQL (or MySQL with InnoDB, for that matter), or different ways to handle full-text searching, I agree that there are differences you have to deal with, but they are not usually a big deal. Contrast the fairly easy and routine process of migrating between MySQL, PostgreSQL, or SQL Server with the huge amount of work involved changing your application code from MongoDB to anything else.
  - AlisdairO 14y ago
    Just in case you weren't already aware, the count(*) issue should be significantly improved for some queries in postgres 9.2, thanks to the addition of covering indexes.
    - einhverfr 14y ago
      There was discussion on pgsql-general a bit before the release. It appears that physically sequential scan of a table is about as fast as key-order sequential scan of a covering index. To speed up a lot, I think you'd need to be able to scan an index in physical order which is not currently supported. Now if you are using SSD's yeah, you could tweak the planner settings enough to cause it to make an index scan perhaps, and maybe you'd see a modest performance increase, but even there it appears the operating system's prefetch logic really does come to the rescue of a sequential scan of a table in physical order.
      - AlisdairO 14y ago
        ah, that's unfortunate :-(
  - bunderbunder 14y ago
    SQL databases (except for SQLite) are almost completely interchangeable because they are all based on the same relational model and they all implement the ANSI SQL standard with only minor deviations. They only all implement the ANSI SQL standard for certain values of "ANSI SQL standard." Those values being SQL-89 or perhaps SQL-99. Beyond that it's a mess. SQL Server didn't get around to doing a really good job with window functions until this year. Last I checked, MySQL still doesn't do any number of useful things, such as WITH or FULL JOIN. Upserts and bulk inserting are different for every DBMS on which I've learned how to do them. And so on and so on. There are any number of ways you can get yourself coupled to a particular DBMS if you go beyond the core set of basic SQL features. In other words, SQL databases are only trivially interchangeable for trivial cases.
    - 14y ago
stickfigure 14y ago
it's best just to start with a SQL database and go from there. This is bad advice. It's best to understand your problem domain and use the tools that are most appropriate. You see a lot of two types of posts on HN: * "I picked a NoSQL database for a problem domain with a better relational fit." Those posts look like this one. * "I picked an RDBMS for a problem domain with a better NoSQL fit." Those posts are usually titled something like "How I scaled Postgres to XYZ qps" and describe an insane amount of re-engineering and operational hell. Oddly, these posts are usually proud of the accomplishment rather than embarrassed that they picked the wrong tools in the first place. There are upsides and downsides to RDBMSes. From my experience, you should be leaning towards NoSQL systems (of which there are many, each suited to different use cases) when you have very large scaling needs (in terms of dataset and qps), heavily polymorphic data, or data that has ambiguous structure. It's been stated many times already: Use the right tool for the right job.
- rbranson 14y ago
  Given the context of the OP, a brand new project or startup, my advice still stands true.
  - gaius 14y ago
    There's maths behind RDBMS - the relational algebra and calculus. These give you enormous power when you don't know how you want to query your data in future (something you'd think the "agile" crowd would be all over). But instead, the prevalent thinking is "just stuff it all in and worry about it later". Experienced DB hands call this "painting yourself into a corner".
  - einhverfr 14y ago
    Part of the reason though is it is easier to get an idea of what you need from a NoSQL database and what you'd be giving up if you are already on an RDBMS, right? I think the advice is right on and I see NoSQL databases as very much useful adjuncts to the relational systems, not as replacements.
- gregjor 14y ago
jamesli 14y ago
I agree that relational databases are a safer bet after you study the business domains, consider the pros and cons of relational databases vs NoSQL, and there are no clear winners. Disagree to the books recommended. SQL is only a query language, not the database itself. It definitely should be part of the consideration. Understanding how database engines works under the hood is more important in terms of performance in high-concurrency, high load scenarios.
- einhverfr 14y ago
  This is true. Robert Haas ran a couple of posts a while ago about differences between InnoDB and PostgreSQL. He basically pointed out that in InnoDB, the table is contained in the primary key index, while in PostgreSQL, it is an indexed heap of pages. Moreover InnoDB does not support traversing the table in physical order, so sequential scans of a large table are ghastly performance-wise. What this means is that optimizations appropriate for InnoDB (index everything you search on) are very poor for PostgreSQL and vice versa. In PostgreSQL you index only a few columns, typically join conditions and a few select search columns, and the combination of the operating system and database will do the rest. In short, InnoDB only does index scans (and the table is a special index). This makes some things perform better (primary key lookups), but many other things perform worse. Moreover optimizing for one does not mean optimizing for the other....
egiva 14y ago
Thanks rbranson, this comment was very helpful. I also think that the logical fallacy of some of these arguments is that they´re absolutist - it´s better to follow your advice because it´s a very logical progression to start with a SQL database and then move into different things as you grow. Life is a progression and I think your quote about premature optimization is spot on - it IS the root of all evil because it´s absolutist and too rigid to fit the curve of everything that life (and tech, and work) throws at you!
Ingaz 14y ago
Funny. The same problem exists with OLAP databases. People trying to do MOLAP without building ROLAP. They are thinking that MS SASS, ESSBASE, Cognos or Qlikview will do some "magic" that eliminate the need to carefully think about data

bunderbunder 14y ago

Don't forget to read a book that's specific to your particular RDBMS. Because SQL databases are only trivially interchangeable for trivial cases. There have been more than a couple times when I was ready to blame the relational model, but further investigation revealed that the real root of the problem was that the existing schema or query used an approach that was optimized for one DBMS but performed terribly on the one we were actually using.

rbranson 14y ago
This is why the "SQL Cookbook" is good, it has dozens and dozens of examples with permutations in Oracle, MS SQL, MySQL, and PostgreSQL.
gregjor 14y ago
Wrong. SQL databases (except for SQLite) are almost completely interchangeable because they are all based on the same relational model and they all implement the ANSI SQL standard with only minor deviations. If you have a lot of stored procedures and triggers -- executable code embedded in the database -- you will have to rewrite that. Oracle is in a world of its own in a lot of ways, but if you are moving to or from Oracle you should have the required resources and expertise in your budget. If you are referring to the well-known cost of COUNT(*) in PostgreSQL (or MySQL with InnoDB, for that matter), or different ways to handle full-text searching, I agree that there are differences you have to deal with, but they are not usually a big deal. Contrast the fairly easy and routine process of migrating between MySQL, PostgreSQL, or SQL Server with the huge amount of work involved changing your application code from MongoDB to anything else.
- AlisdairO 14y ago
  Just in case you weren't already aware, the count(*) issue should be significantly improved for some queries in postgres 9.2, thanks to the addition of covering indexes.
  - einhverfr 14y ago
    There was discussion on pgsql-general a bit before the release. It appears that physically sequential scan of a table is about as fast as key-order sequential scan of a covering index. To speed up a lot, I think you'd need to be able to scan an index in physical order which is not currently supported. Now if you are using SSD's yeah, you could tweak the planner settings enough to cause it to make an index scan perhaps, and maybe you'd see a modest performance increase, but even there it appears the operating system's prefetch logic really does come to the rescue of a sequential scan of a table in physical order.
    - AlisdairO 14y ago
      ah, that's unfortunate :-(
- bunderbunder 14y ago
  SQL databases (except for SQLite) are almost completely interchangeable because they are all based on the same relational model and they all implement the ANSI SQL standard with only minor deviations. They only all implement the ANSI SQL standard for certain values of "ANSI SQL standard." Those values being SQL-89 or perhaps SQL-99. Beyond that it's a mess. SQL Server didn't get around to doing a really good job with window functions until this year. Last I checked, MySQL still doesn't do any number of useful things, such as WITH or FULL JOIN. Upserts and bulk inserting are different for every DBMS on which I've learned how to do them. And so on and so on. There are any number of ways you can get yourself coupled to a particular DBMS if you go beyond the core set of basic SQL features. In other words, SQL databases are only trivially interchangeable for trivial cases.
  - 14y ago

stickfigure 14y ago

it's best just to start with a SQL database and go from there. This is bad advice. It's best to understand your problem domain and use the tools that are most appropriate. You see a lot of two types of posts on HN: * "I picked a NoSQL database for a problem domain with a better relational fit." Those posts look like this one. * "I picked an RDBMS for a problem domain with a better NoSQL fit." Those posts are usually titled something like "How I scaled Postgres to XYZ qps" and describe an insane amount of re-engineering and operational hell. Oddly, these posts are usually proud of the accomplishment rather than embarrassed that they picked the wrong tools in the first place. There are upsides and downsides to RDBMSes. From my experience, you should be leaning towards NoSQL systems (of which there are many, each suited to different use cases) when you have very large scaling needs (in terms of dataset and qps), heavily polymorphic data, or data that has ambiguous structure. It's been stated many times already: Use the right tool for the right job.

rbranson 14y ago
Given the context of the OP, a brand new project or startup, my advice still stands true.
- gaius 14y ago
  There's maths behind RDBMS - the relational algebra and calculus. These give you enormous power when you don't know how you want to query your data in future (something you'd think the "agile" crowd would be all over). But instead, the prevalent thinking is "just stuff it all in and worry about it later". Experienced DB hands call this "painting yourself into a corner".
- einhverfr 14y ago
  Part of the reason though is it is easier to get an idea of what you need from a NoSQL database and what you'd be giving up if you are already on an RDBMS, right? I think the advice is right on and I see NoSQL databases as very much useful adjuncts to the relational systems, not as replacements.
gregjor 14y ago

jamesli 14y ago

I agree that relational databases are a safer bet after you study the business domains, consider the pros and cons of relational databases vs NoSQL, and there are no clear winners. Disagree to the books recommended. SQL is only a query language, not the database itself. It definitely should be part of the consideration. Understanding how database engines works under the hood is more important in terms of performance in high-concurrency, high load scenarios.

einhverfr 14y ago
This is true. Robert Haas ran a couple of posts a while ago about differences between InnoDB and PostgreSQL. He basically pointed out that in InnoDB, the table is contained in the primary key index, while in PostgreSQL, it is an indexed heap of pages. Moreover InnoDB does not support traversing the table in physical order, so sequential scans of a large table are ghastly performance-wise. What this means is that optimizations appropriate for InnoDB (index everything you search on) are very poor for PostgreSQL and vice versa. In PostgreSQL you index only a few columns, typically join conditions and a few select search columns, and the combination of the operating system and database will do the rest. In short, InnoDB only does index scans (and the table is a special index). This makes some things perform better (primary key lookups), but many other things perform worse. Moreover optimizing for one does not mean optimizing for the other....

egiva 14y ago

Thanks rbranson, this comment was very helpful. I also think that the logical fallacy of some of these arguments is that they´re absolutist - it´s better to follow your advice because it´s a very logical progression to start with a SQL database and then move into different things as you grow. Life is a progression and I think your quote about premature optimization is spot on - it IS the root of all evil because it´s absolutist and too rigid to fit the curve of everything that life (and tech, and work) throws at you!

Ingaz 14y ago

Funny. The same problem exists with OLAP databases. People trying to do MOLAP without building ROLAP. They are thinking that MS SASS, ESSBASE, Cognos or Qlikview will do some "magic" that eliminate the need to carefully think about data