Hacker News Search

espeed 15y ago
For tabular data, relational databases rock. But the relational model doesn't align well with object-orientated programming so you have an ORM layer that adds complexity to your code. And with relational databases, the complexity of your schema grows with the complexity of the data. The graph-database model simplifies much of this and makes working with the modern-day social graph so much cleaner. Graphs allow you to do powerful things like find inferences inside the data in ways that would be hard to do with relational databases. How would you calculate PageRank using a relational database? As I said, with a graph database and Gremlin, you can do it in 2 lines. To see the types of things you can do with graphs, check out Marko's short screencast on Gremlin (http://www.youtube.com/watch?v=5wpTtEBK4-E http://www.youtube.com/watch?v=5wpTtEBK4-E). And also check out Peter Neubauer's introduction to graph databases and how they compare to RDBMS' and where they stand in the NOSQL-movement (http://www.infoq.com/articles/graph-nosql-neo4j http://www.infoq.com/articles/graph-nosql-neo4j).
- jacques_chester 15y ago
  > But the relational model doesn't align well with object-orientated programming so you have an ORM layer that adds complexity to your code. The mismatch comes about for a number of reasons: * OOP has no formal basis so it can't be reliably transformed into relational terms. * OOP is identity-bound -- each object is essentially an address in memory, not a relation in a set. * The biggie: OOP mixes data with behaviour. Relational does not. > How would you calculate PageRank using a relational database? As I said, with a graph database and Gremlin, you can do it in 2 lines. For something on PageRank's scale, a custom datastore based on matrices and their multiplication makes business sense. Or MapReduce over a distributed key-value store (note that these are both OLAP approaches). Still. SQL's a bit verbose, but these days we have recursive queries. For Oracle users, I'm talking about CONNECT BY. If I find myself running the social graph every minute, I develop an ETL package that periodically moves data from my write-bound system to my read-bound system with a more query-friendly schema. Depending on how you look on it, relational systems invented "eventual consistency". More to the point, boring old database greybeards have learnt that OLTP and OLAP are very different use cases. The 3/4/5NF of the OLTP database will be very different from the star schema of the OLAP database. There's really not much about NoSQL that hasn't already been done, under a different name, by the relational crowd. I still think there is a place for NoSQL. It's just not as universal a replacement for relational systems as many make it out to be, whether we're talking about document stores, distributed key-value stores, graph stores and so on.
  - espeed 15y ago
    The "NoSQL vs SQL debate" name will eventually change. Instead of isolating SQL from the other datastores, the debate will move toward discussing which database is the right tool for the job, where SQL/relational databases will have a specific role. Putting relational databases into their own group is silly. I believe this came about because relational databases have dominated for so long, and this has caused developers to try and fit every problem into the relational database model. In the early days of the Web, choosing a database meant choosing between relational database management systems -- Oracle was king but expensive, Microsoft SQL Server if you were on a Microsoft stack, and PostgreSQL or MySQL were the primary open-source options (with one being a real RDBMS while the other was basically just an SQL interface to the file system). Ten years ago the RDBMS was the only good option so that's what everyone used even though many problems and programming languages didn't match up well with it -- it was like trying to fit a square peg in a round hole. You don't have to do that anymore so relational databases can stop being the one-size-fits-all solution, and instead we can move toward using them for the specialized cases where they're the right fit.

justlearning 15y ago

Having Oracle as your db doesn't mean that you are not working on interesting problems. Every company is not a startup. Some of us work on well established products used by huge customer base who use Oracle and in turn we are forced to. Above all, are you helping a customer? solve his problems instead of compounding it - that my friend is more important than choosing Oracle or Mongodb.

bad_user 15y ago
Having Oracle as your db doesn't mean that you are not working on interesting problems First, I consider consultancy for customers using Oracle to be OK, as long as you earn something from it. But if your company chooses Oracle, it means that you're bogged down by legacy, stupid company processes and/or clueless managers. And in most such environments, the harder you try to change it for the better, the harder it fights back, putting yourself in an awkward position in which you are considered the bad apple of the team. So you end up either adapting (not giving a shit), quitting or finding some small project with no perspective for the company (i.e. less controlled) that can bring you pleasure. Of course, some companies, like Adobe for example, use Oracle when it doesn't impact their core competencies, as it's a safe choice for corporate types. But a company like Adobe doesn't earn money from projects that are relying on Oracle and other projects inside Adobe are also using HBase and MySql and their own distributed file-system that can be queried and so forth. Either way that quote is correct. I'm not promoting the latest fads (personally I'm not into NoSql unless it makes absolute sense), but you can safely ignore companies that make decisions based on brochures and lap dances.
- brown9-2 15y ago
  I hope you realize that a company might choose to use Oracle for a reason other than "brochures and lap dances", and that a "legacy" decision to use Oracle doesn't necessarily mean you are "bogged down" or that the decision was stupid. I don't understand how using Oracle for your RDBMS is suddenly equated with all the things you mention.
  - bad_user 15y ago
    Such as? Really, I'd like to know the reason for why a company might choose Oracle.
- tedunangst 15y ago
  Amazon uses Oracle. I think to completely disregard Amazon because of this fact would be a mistake.
  - deleted 15y ago
    [deleted]
  - dillona 15y ago
    As does Facebook I believe
    - HarrisonFisk 15y ago
      Facebook uses MySQL as it's primary data store, with some hbase, cassandra, and other more minor usage storage solutions in various places.
      - tzhang 15y ago
        Facebook uses Oracle for certain things.

jamwt 15y ago

Without going too much into specifics and picking on individual databases (which I could do, boy do I have the scars...) When you hit a certain traffic level, scalability, latency and robustness become far more important than single-node ops/s. I need to be able to add nodes and repair failed nodes while under load--I need the 99.9% latency mark to stay ~100ms while doing so. I don't really care how many bajillions of ops a second your database can do in some concocted scenario, b/c you're not going to do that many in the real world anyway (trust me, we tried). The disk subsystem is going to give you a few hundred, maybe a few thousand if you're lucky, IOPS, then your latency will spike to hell and your phone will wake you up at night. Maybe in the world where 99% of ops are reads, you will put up impressive numbers, but now you're just showing you are pretty good at using the disk cache. That's a relatively easy problem. The riak guys seem to get all this better than most: http://blog.basho.com/2011/05/11/Lies-Damn-Lies-And-NoSQL/ http://blog.basho.com/2011/05/11/Lies-Damn-Lies-And-NoSQL/ So, to give you a short answer to your direct question: Use SLC SSDs + md + RAID-0. Have at least 5 nodes. Use bitcask, but realize that your keys will need to fit in memory. Also, realize that really small values aren't a great fit for Riak in some ways b/c the overhead per value is at least a few hundred bytes. Also, it's important to note this is where I'm at right now, but maybe not where you (generally) are at. Riak may not make you happy at server #1, but it will make you pretty happy at server 10 and server 100. Riak's sweet spot is people with scaling pains. If you only need a server or two to try some stuff, and you don't have any users yet, you might cause yourself more headaches than you need. Sometimes you don't need a locomotive, you need a motorcycle. (These guys have a pretty great motorcycle: http://rethinkdb.com/ http://rethinkdb.com/ )

bretthoerner 15y ago
Was your experience with Cassandra different? Happy at server 10 and 100, that is?
- jamwt 15y ago
  TBH, we didn't seriously pursue Cassandra when we considered distributed database systems b/c the vast majority of "back-reference checks" we did on the YC network and other area startups was "stay away." We got some very frank advice from some people whose opinions on databases I take very seriously to stay away, including reports from within FB. Having said that, I cannot claim to have firsthand proven or disproven anything about Cassandra.
  - espeed 15y ago
    Quora's decisions to not use Cassandra and Adam's answer regarding it lead me to the same conclusion (http://www.quora.com/Quora-Infrastructure/Why-does-Quora-use-MySQL-as-the-data-store-instead-of-NoSQLs-such-as-Cassandra-MongoDB-CouchDB-etc http://www.quora.com/Quora-Infrastructure/Why-does-Quora-use...). Evidently few from Facebook are advocating it.
    - dehora 15y ago
      Quora on MySQL failed outright when AWS EBS failed, companies on AWS using Cassandra like SimpleGeo and NetFlix did not. To their credit, Facebook were clear enough on their reasons for using HBase over MySQL and Cassandra, such as wanting to double down on their current Hadoop system/knowledge and having easily obtainable ordering guarantees on messages. It's also clear they've invested in making HBase good enough. At large loads and footprints, imvho, Riak, Cassandra and HBase present viable options. But there are some factors to consider that don't seem to get mentioned in the pop tech press - What are you able to operate in production? - What are you able/willing to debug and patch? - What hardware options do you have? - What are your workloads? - Which variable of C.A.P, when you lose it, most damages your business? - Will your company's choices be evaluated in the press? - Does your board/investors have capital tied up in business's that are using something else? - What architecture tradeoffs and styles sit well with you? - What kind of data access and consumption patterns make you money? - Can you pay for help? The right choice is context sensitive, and I'm fairly sure for this class of systems at this point in time, there's no free lunch. That means you have to do the legwork for yourself and make your own choices and commitments; doing what you heard worked for someone else is a cargo cult.
      - espeed 15y ago
        Or maybe it's wisdom. "A fool learns from his mistakes, but the truly wise learn from the mistakes of others." -- Otto von Bismarck
  - ericflo 15y ago
    Facebook for a long time didn't use the vastly (and I mean vastly) improved open source version of Cassandra, instead opting for their internal fork. Instead of choosing to do so, I believe they have now switched to HBase, mainly for its easier consistency model. So I would take their advice with a grain of salt, because it's probably based on their experiences with an old fork. There are a few people (YC companies even, alas) who are very vocally negative about Cassandra, but I also saw some of those same people ignoring direct advice given to them in #cassandra on IRC, and then turning around and bashing it when it didn't work as planned. Simply following the advice could have made for a completely different story. I suppose the lesson to learn is that you need to develop software in a way that simply won't allow developers to shoot themselves in the foot, because people never want to blame themselves for doing it, they blame the gun.
grncdr 15y ago
For everything you've said about Riak re: stability and happy scalability, that's exactly why I was excited to include it in this test. I would like to prevent myself/my team from acquiring too many of those scars, so please elaborate on what caused them ;) Especially if those scars came from Cassandra or HBase! The test hasn't been a "concocted scenario", it's measuring the performance[1] of a prototype implementations for what will be an essential piece of our infrastructure and process (bulk loads of large numbers of small records, very read heavy after the initial load). Riak's write performance was completely adequate, just nowhere near what we got out-of-the-box with the bulk insert operations available in Cassandra and HBase. I asked on #riak channel on freenode and got told to use protocol buffers (which we already were), I'd really appreciate advice beyond this. > Also, realize that really small values aren't a great fit for Riak in some ways b/c the overhead per value is at least a few hundred bytes. This is pretty much what I've chalked it up to. It's unfortunate because that is the use case for which we currently need to provide a solution for right now, and once we've got some of our data in one distributed data store, it's convenient (and considered less risky) to use that same technology for the next project. (This is really a culture thing though, it's taking us months to get the necessary buy-in and approval for a postgres 8.2 -> 9.0 upgrade rolled out for a different product, where we know it would solve a specific issue we have). [1] We've been running our tests on a 4 node cluster, each node has an 8 core 2.8ghz xeon, 32gb of ram, and a woefully inadequate disk: the machines were repurposed from a system that required them to have redundancy and didn't require write performance, so the drives are RAID1. We also need to make recommendations to IT for their hardware purchase plan after our testing.
- jamwt 15y ago
  > The test hasn't been a "concocted scenario" Btw, b/c I can totally see why you'd read it that way, that particular barb wasn't directed at you, more directed at some of the public benchmarks touted by (non-distributed) NoSQL database systems. Re: cassandra, please see my reply to the sibling on this thread. Also, feel free to email me jamie@bu.mp if I can answer any specific questions for you with things we ran into with various database systems.
  - StavrosK 15y ago

rosser 15y ago

When I first started doing serious DB work (back in the PostgreSQL 7.x/MySQL 3.x days), there were some show-stopper flaws in MySQL that made it a non-starter for my scenario. (In particular, you couldn't self-join a table, and "February 31" was treated as a valid date.) In performance terms, granted, pg was a bit of a dog in those days, but it outperformed MySQL in every benchmark I could throw at it, with minimal tuning. So, if you're experiencing it as less performant, you're most likely running with a default configuration — which is deliberately tuned for something like a 486-class box with 128MB RAM or so — and/or haven't ANALYZEd your data. (Those are complete WAGs, knowing nothing more than what you've said about your scenario, but they tend to be among the more common reasons for lackluster performance.) As for support, as a complete noob, I had a weird performance problem I couldn't make sense of, so I went to the mailing lists. Within a few hours, I was exchanging stack traces and other sundry debugging/profiling dumps with Tom Lane. (He's in the Wikipedia. Even if you've never touched PostgreSQL, you use his code every day of your life.) I don't think you can get much better support than that. Since, I've never encountered a problem that I haven't been able to have addressed, or at least get pointed in the right direction, by asking on — or searching the archives of — the relevant mailing list. From there, it was largely a matter of, "This is the one I already know how to use...", along with the better feature-set (not mentioned by any of the sibling posts thus far: transactional DDL); the lack of a known-evil corporate overlord who could pull the plug at any time; the consistent tens-of-percent performance improvements in every major release; a development community that will punt a feature to the next release if it's not 100% ready and provably correct; and, let's be honest, the fact that, as someone who's been doing pg work for as long as I have, I can command a very comfortable hourly rate on the basis of that depth of experience — particularly when it's been focused in high availability and replication. (EDIT: proofreading.)

spitfire 15y ago
I'll second this. For the longest time mysql was the quickest way on the web to lose your data. As in corrupt databases. It also didn't support transactions where postgres did. Essentially mysql was a dumb datastore with sql interface, whereas postgres was a database. That's changed now, but postgres is still a head in reliability(shit just doesn't break) and feature-set. and I hear in speed these days. But I no longer care about speed these days, as an SSD backed postgres handles anything I can possibly throw at it.
- rosser 15y ago
  Be very, very careful using SSDs under your DB, whether PostgreSQL or anything else. If you don't have supercaps on your drives, you will lose data in a power loss situation, even with a battery-backed RAID controller. That data loss could take the form of anything from silent corruption of a table or index, to unrecoverable filesystem loss. (Briefly, the drive's controller uses the on-board cache to accumulate writes into erase-block sized chunks before flushing to the NAND media. If you disable the on-board cache, performance drops through the floor — USB thumb drives look fast by comparison — and you shave at least an order of magnitude off the drive's lifetime.) At present, it looks like the best choice is the forthcoming Intel 710 series drives, but if you need an SSD now, their 320 series, the Sandforce controller based drives with supercaps (like the OCZ Vertex Pro models, though I wouldn't touch those with a competitor's database), or a FusionIO card are the only remotely safe options.
  - old-gregg 15y ago
    This is why I will be able to go back tonight and honestly say that I didn't waste time on Hacker News in the morning. :) Thanks.
- mgkimsal 15y ago
  Except if you want to do a nice pagination interface over a set of data. select count() in postgresql takes forever on large tables. select count() over myisam is speedy. select count(*) over innodb is speedy but an estimate (I'd still prefer an fast estimate over exceedingly slow accuracy for many scenarios, such as paginated web interfaces).
  - deleted 15y ago
    [deleted]
  - frankwiles 15y ago
    This is easily solved by using the statistics count rather than a count() and living with the fact your last page of data might have one or two rows +/- the actual count. Then it's fast.

yaaang 15y ago

First off - whatever you're doing, you'll probably be fine using either. That said, I'm generally more frustrated when using MySQL than when using PG. Here's a sample of the problems I've encountered from using both MySQL and PG. I haven't updated my list in a while now - please feel free to correct me on things - but hopefully it's a little more illustrative than that Wikipedia feature matrix, and a little more specific to MySQL vs. PG. (This list is a cleaned-up selection from my notes wiki at http://yz.mit.edu/notes/Hackery. http://yz.mit.edu/notes/Hackery.) No referential integrity. No constraints (CHECK). No sort merge join, let alone hash-join. http://www.dbms2.com/2008/07/10/how-is-mysqls-join-performance-these-days/ http://www.dbms2.com/2008/07/10/how-is-mysqls-join-performan..., http://www.mysqlperformanceblog.com/2006/06/09/why-mysql-could-be-slow-with-large-tables/ http://www.mysqlperformanceblog.com/2006/06/09/why-mysql-cou... Generally poor at analytical workloads, since it's designed for transactional workloads. Can't reopen TEMP table - WTF? (Still not fixed!) http://bugs.mysql.com/bug.php?id=10327 http://bugs.mysql.com/bug.php?id=10327 Multiple storage engines has always restricted progress: http://www.mysqlperformanceblog.com/2010/05/08/the-doom-of-multiple-storage-engines/ http://www.mysqlperformanceblog.com/2010/05/08/the-doom-of-m... (PG also supported multiple storage engines in 80s, then concentrated on one) No WITH clause: http://stackoverflow.com/questions/324935/mysql-with-clause http://stackoverflow.com/questions/324935/mysql-with-clause Crappy errors: “Incorrect key file for table ‘stock’; try to repair it” on “alter table stock add constraint pk_stock primary key (s_w_id, s_i_id);” where stock is in InnoDB (which has no “repair table”) means I have no /tmp space (no Google answers) Crappy EXPLAIN output - somewhat better when using the visual-explain tool from Percona. InnoDB auto-extends ibdata1 file; only way to trim (garbage collect) is dumping and loading. Scoping is broken: mysql> create table t(a int, b int); Query OK, 0 rows affected (3.30 sec) mysql> select a, (select count(*) from (select b from t where a = u.a group by b) v) from t u; ERROR 1054 (42S22): Unknown column ‘u.a’ in ‘where clause’ Optimizer leaves plenty to be desired, e.g. not pruning unnecessary joins. “InnoDB is still broken…Just last week we had to drop/re-create an InnoDB-table in one project because it would not allow to add an index anymore, no matter what we tried…Mysql::Error: Incorrect key file for table 'foo'; try to repair it: CREATE INDEX [...]” http://news.ycombinator.com/item?id=2176062 http://news.ycombinator.com/item?id=2176062 MySQL only recently got such things as per-statement triggers and procedural language support. MySQL has only its own internal auth system, whereas PG supports a wide array of auth providers. PG has more supple ALTER TABLE implementation. MySQL doesn’t support ASC/DESC clauses for indexes http://explainextended.com/2010/11/02/mixed-ascdesc-sorting-in-mysql/ http://explainextended.com/2010/11/02/mixed-ascdesc-sorting-... Optimizer only recently started working properly with certain subqueries OK documentation, but still considerably unpolished compared to PG's. Random omission: auto_increment jumps up to next power of 2 but inconsistently across versions (platforms?). (Older issue, not sure if it's still relevant) Crappy concurrency, >3 cores sucks vs PG: http://spyced.blogspot.com/2006/12/benchmark-postgresql-beats-stuffing.html http://spyced.blogspot.com/2006/12/benchmark-postgresql-beat...

lobster_johnson 15y ago

These days, Postgres is faster than MySQL+InnoDB, and scales much better across multiple CPU cores. (MyISAM is still faster, but that's not an appropriate comparison.) Some features that make Postgres awesome: * Transactional DDL. You can do "create table" in a transaction. _Everything_ is transactional, it's not a tacked-on feature, it's the basis of everything. * No legacy cruft. Compared to MySQL, which is filled to the brim with historical warts. The Postgres people have been careful to weed out obsolete functionality. There are essentially no sneaky border cases that a developer needs to be aware of, no weird special cases like "0000-00-00 00:00" having special meaning. * No need for a "strict" mode, since Postgres is always strict. Postgres doesn't allow invalid dates, doesn't allow byte sequences that violate character encodings, etc. It diligently enforces contraints and generally doesn't allow you to screw up. To Postgres, data integrity is paramount. * PostGIS. Simply awesome. (MySQL's geospatial stuff also tries to implement the OGC API, but last I looked, it was a half-hearted attempt that negelcted to provide the fast R-tree-based (actually GiST-based) indexing that makes PostGIS so super fast.) * Replication. It's late to the party, but I rather prefer how Postgres has implemented its replication, even though it has some downsides where it will abort a long-running query if some data has changed under its feet (but if you're using transactions it's easy to simply restart the query). 9.1 will be getting synchronous replication, which is pretty cool. * Extensions. Postgres can integrate languages like R and Ruby as first-class languages that can be called from SQL. It also has a module system that can extend the type system (a bit of trivia: This was originally the main reason why Michael Stonebraker invented Postgres) with new types, eg. multidimensional matrix columns, or new features, like remote tables. * The "text" type. Seriously, why should do people keep writing things like varchar(255)? Postgres' text type is an unlimited string. Unlike MySQL's text type, it can be efficiently indexed without limitations. (Varchar is internally implemented as a bounded text type.) * Cost-based planner backed by row-level statistics. This is the stuff that allows Postgres to do complex nested queries and still perform incredibly well. * Partial indexes. You can do something like "create index ... on themes (name) where color = 'blue'". Whenever you do a query that falls within the expressions's range, Postgres will use that index, potentially vastly reducing the search space. * Functional indexes. You can do something like "create index ... on (lower(name))". If you then do a query such as "select ... where lower(name) = 'xyz'", then Postgres will recognize that it's the same expression, and it will be able to use the index. * Windowing functions and recursive queries, both from ANSI SQL99 iirc. Look this up, they're great. There are some bad points, none of them significant and all of them a matter of taste: - I have never really liked Postgres' text indexing, which feels a bit creaky and antique. At least with 8.x, GIN index updating was slow as hell. - Partitioned tables are a great feature, but I will never use it because of the requirement that one does the plumbing yourself (creating partitioning rules and so on); I keep waiting for something like Oracles's automatic partitioning. - Stored procedures -- ie., running logic inside the database -- feels wrong to me, and always has. For some people this is a requirement, so I'm not really complaining. In some cases, writing a stored procedure can be essential to speed up queries/operations by saving on database roundtrips. - Still no "upsert" SQL command (aka "insert or replace", "insert or update") for asserting the existence of a row atomically.

vegai 15y ago
"Oracle remains viable, even as others continue to catch up, because Oracle builds on its solid foundation by adding additional capability and features relentlessly. I challenge you to read the new features guide for any new release of Oracle and to remember just the names of 20% of the new features. The Oracle documentation, as of 6 or 8 years ago (10g), was 40,000 pages. No telling what it is now. I can tell you this. If you have something you need to do with databases, Oracle probably figured out how to do it a long time ago." Are these good things? We can also reverse this argument: if you're using Oracle, you will never have a use for 99% of its features.
- rm-rf 15y ago
  Or - as I picked up from some long forgotten blog post: If you need Oracle, you'll know it. If you don't know that you need Oracle, you don't need Oracle. I run MySQL, Oracle SQL Server, hundreds of databases, a couple in the 'many thousands of queries per second' range. There are reasons for each database platform.
fdr 15y ago
> But when it comes to building spacial indexes on hierarchical dimensions, or whatever, Postgres just isn’t going to have a feature for that. I’ll have to figure that one out for myself. In this case, yes it is, Postgres probably has the best type system and extensibility of all SQL databases. GiST and GIN are very good technologies that also exist in Informix. If I had to choose something that is missing in Postgres and very far away for sheer want of implementation effort, it's parallel execution of queries. Oracle RAC is also quite a crazy and neat feature, except when it has problems, in which case you get even more crazy for that crazy. On the other hand, Oracle is also not a strict superset of the features in PostgreSQL. For example, 9.1 has the only credibly fast implementation of SERIALIZABLE level isolation that I am aware of. There are also interesting features like "index exclusion constraints" that have no equivalent in Oracle, and solve problems hard to solve otherwise (example constraint: there are no overlapping circles in this table)