6 ms·

Just for fun: does anyone have any stories about initially using a SQL database for a project, later hitting problems, then switching to (or augmenting with) a

Just for fun: does anyone have any stories about initially using a SQL database for a project, later hitting problems, then switching to (or augmenting with) a NoSQL solution that solved those problems?

fs111 10y ago
I think most people in that group start out with a normalized schema in a SQL db and end up in a highly denormalized or even key/value style setup in said DB. Technically it is still a SQL database, but not used in that way.
- jerven 10y ago
  public UniProt.org went from SQL normalized -> SQL primary key/blob to proper K/V (BDBje) to custom K/V in the space of ten years. However, data production is still a mix of batch and sql systems.
sergiosgc 10y ago
I've reached a point where my default design is relational database augmented with memcache/redis. The relational DB is the authoritative source of truth, redis absorbs all (most) reads and memcache is, well a distributed cache. Writes go to both redis and relational. Redis data can always be rebuilt, and is regularly rebuilt, from relational data. At project onset, usually only redis or memcache are in use. It's a model that has served me well, apart from bugs that cause divergence between redis and relational. These are usually the class of bugs that would corrupt data were I relying on redis alone, so I just think of them as a lesser evil. There's an obvious bottleneck on writes to the relational DB but I've so far managed to keep from hitting that ceiling.
- scarface74 10y ago
  I've reached a point where my default design is relational database augmented with memcache/redis. The relational DB is the authoritative source of truth, redis absorbs all (most) reads and memcache is, well a distributed cache. Writes go to both redis and relational. Redis data can always be rebuilt, and is regularly rebuilt, from relational data. I'm really becoming a big fan of CQRS/event sourcing where we have two read models - one NoSql for live data and one relational for BI/Reporting. Events are stored separately in JSON because that's how they come in anyway through the API.
  - remotehack 10y ago
    Wow. Let the maintenance begin.
    - scarface74 10y ago
      CQRS is not rocket science. In any case, your schema for your reporting database is usually different from your online database.
johnchristopher 10y ago
I suppose they'd look first into MySQL's new JSON support. (Or other SQL database that have some kind of JSON support)
- Klathmon 10y ago
  PostgreSQL has fantastic JSON support and i've used it a few times. Not only is it fairly fast, but it lets us tie JSON blobs that really don't need schema enforcement to be still tagged with the rest of the data and laid out relationally to still allow complex queries.
lr4444lr 10y ago
Yeah, I use MongoDB whenever my tables get too big. It's web scale! </s>
pinouchon 10y ago
Most of the stories I have heard are from people choosing a noSQL store for the wrong reasons. They end up going back to a relational DB because now they see the value of ACID. The typical case is Mongo => Postgres/Redshift.
Kiro 10y ago
Kind of. I started building my game using SQL but realized everything became so much easier if I didn't have to follow a schema. For example I like to add arbitrary properties to the player objects all the time. With MongoDB it's a no-brainer. I just add the property and save.
- elmigranto 10y ago
  > no schema Do you read them back? Does your code expect property "X" to be on a "Player" object? Do you put a check everywhere for when it doesn't? Do you have default value for objects that were created before new property was added? All that work could've been done by your database (which is probably has some kind of JSON implementation anyways), but somehow it's a "no-brainer" to implement data consistency at app-level. Okay.
  - scarface74 10y ago
    Does your code expect property "X" to be on a "Player" object? Do you put a check everywhere for when it doesn't? Do you have default value for objects that were created before new property was added Why would you put checks everywhere a properly written code base would have one module/service that handles the player object and every consumer would use that one module.
    - aidos 10y ago
      There have to be some checks, somewhere. Even though it's limited to one module, you can get temporal inconsistencies. e.g. you add a new field and an application level default, then later that default is changed. Now there's no notion of which default the docs that never had the property should be using. Sure, you just need to patch the old docs to set the property where it's missing. But the GPs point was that by not keeping a consistent schema you end up moving the definition of the data into the application, and you need to be very aware of the trade-off. Personally I'm really happy using postgres and dropping into jsonb when it's the correct model to use. It's the best of both worlds as far as I'm concerned. I've built and worked on systems using nosql solutions that have been migrated back to sql and the data inconsistencies we found have made me very wary of having a primary datastore that doesn't enforce strict constraints. Though, everybody's use case is different.
FLUX-YOU 10y ago
Logging
danso 10y ago
I had assumed Facebook started off with MySQL and now is NoSQL but that was just ignorant of me: Zuckerberg may have had to rely on a typical LAMP stack but FB's newer user-facing features still use SQL. For example, the Timeline feature released in 2012 was built on MySQL/InnoDB: https://www.facebook.com/note.php?note_id=10150468255628920 https://www.facebook.com/note.php?note_id=10150468255628920 And last year Facebook released MyRocks, which is a space/write optimized replacement for InnoDB, and is being used for their "user database tier" https://code.facebook.com/posts/190251048047090/myrocks-a-space-and-write-optimized-mysql-database/ https://code.facebook.com/posts/190251048047090/myrocks-a-sp... I guess the things I've read about FB using NoSQL [1] is for other parts of their infrastructure, particularly Messages. It sounds like they had considered using MySQL, even though Cassandra was built for the Inbox feature. They ended up building a new system for Messages [2]. Now that I've spent a little time in that rabbit hole, it looks like one answer to your question is Facebook's Inbox Search, which used MySQL originally to store inbox data (7TB for over 100M users, which seems laughably small with respect to Facebook's scale today): http://docs.datastax.com/en/articles/cassandra/cassandrathenandnow.html http://docs.datastax.com/en/articles/cassandra/cassandrathen... > Before launching the Inbox Search application we had to index 7TB of inbox data for over 100M users, then stored in our MySQL[1] infrastructure, and load it into the Cassandra system. The whole process involved running Map/Reduce[7] jobs against the MySQL data files, indexing them and then storing the reverse-index in Cassandra. The M/R process actually behaves as the client of Cassandra. We exposed some background channels for the M/R process to aggregate the reverse index per user and send over the serialized data over to the Cassandra instance, to avoid the serialization/deserialization overhead. This way the Cassandra instance is only bottlenecked by network bandwidth. The acknowledgments section makes it more clear that the MySQL data was indeed migrated over to Cassandra: > Cassandra system has benefited greatly from feedback from many individuals within Facebook. In addition we thank Karthik Ranganathan who indexed all the existing data in MySQL and moved it into Cassandra for our first production deployment. [1] https://news.ycombinator.com/item?id=7891316 https://news.ycombinator.com/item?id=7891316 [2] https://www.facebook.com/notes/facebook-engineering/the-underlying-technology-of-messages/454991608919/ https://www.facebook.com/notes/facebook-engineering/the-unde...
33degrees 10y ago
If you consider ElasticSearch a NoSQL solution, then yes, many stories. A properly normalized database can be very slow to search, and throwing ElasticSearch in front of it is a very common solution.
zzzcpan 10y ago
Switching a project from SQL to NoSQL is usually too expensive. Traditional relational databases lack a lot of necessary constraints to even have architectures necessary to make the switch to a distributed database, it can only happen with a complete redesign of everything. I think it's more common for those who had problems with SQL databases to drop them for their next projects and move on. For example, I consider a highly-available multi-site distributed database as a minimum viable database and haven't used an RDBMS in like a decade.
kalleboo 10y ago
Does using memcache for caching count as "augumenting with a NoSQL solution"? Because that's pretty popular...
prashnts 10y ago
We used to use Postgres for almost everything, including full-text search. Later when we needed a bit more control over search, we started using Elasticsearch. The way we do it now is to perform joins and get a nice serialized object with all the possible search/filter/sort fields, and put it in ES. All the data still remains in postgres, though.

fs111 10y ago

I think most people in that group start out with a normalized schema in a SQL db and end up in a highly denormalized or even key/value style setup in said DB. Technically it is still a SQL database, but not used in that way.

jerven 10y ago
public UniProt.org went from SQL normalized -> SQL primary key/blob to proper K/V (BDBje) to custom K/V in the space of ten years. However, data production is still a mix of batch and sql systems.

sergiosgc 10y ago

I've reached a point where my default design is relational database augmented with memcache/redis. The relational DB is the authoritative source of truth, redis absorbs all (most) reads and memcache is, well a distributed cache. Writes go to both redis and relational. Redis data can always be rebuilt, and is regularly rebuilt, from relational data. At project onset, usually only redis or memcache are in use. It's a model that has served me well, apart from bugs that cause divergence between redis and relational. These are usually the class of bugs that would corrupt data were I relying on redis alone, so I just think of them as a lesser evil. There's an obvious bottleneck on writes to the relational DB but I've so far managed to keep from hitting that ceiling.

scarface74 10y ago
I've reached a point where my default design is relational database augmented with memcache/redis. The relational DB is the authoritative source of truth, redis absorbs all (most) reads and memcache is, well a distributed cache. Writes go to both redis and relational. Redis data can always be rebuilt, and is regularly rebuilt, from relational data. I'm really becoming a big fan of CQRS/event sourcing where we have two read models - one NoSql for live data and one relational for BI/Reporting. Events are stored separately in JSON because that's how they come in anyway through the API.
- remotehack 10y ago
  Wow. Let the maintenance begin.
  - scarface74 10y ago
    CQRS is not rocket science. In any case, your schema for your reporting database is usually different from your online database.

johnchristopher 10y ago

I suppose they'd look first into MySQL's new JSON support. (Or other SQL database that have some kind of JSON support)

Klathmon 10y ago
PostgreSQL has fantastic JSON support and i've used it a few times. Not only is it fairly fast, but it lets us tie JSON blobs that really don't need schema enforcement to be still tagged with the rest of the data and laid out relationally to still allow complex queries.

lr4444lr 10y ago

Yeah, I use MongoDB whenever my tables get too big. It's web scale! </s>

pinouchon 10y ago

Most of the stories I have heard are from people choosing a noSQL store for the wrong reasons. They end up going back to a relational DB because now they see the value of ACID. The typical case is Mongo => Postgres/Redshift.

Kiro 10y ago

Kind of. I started building my game using SQL but realized everything became so much easier if I didn't have to follow a schema. For example I like to add arbitrary properties to the player objects all the time. With MongoDB it's a no-brainer. I just add the property and save.

elmigranto 10y ago
> no schema Do you read them back? Does your code expect property "X" to be on a "Player" object? Do you put a check everywhere for when it doesn't? Do you have default value for objects that were created before new property was added? All that work could've been done by your database (which is probably has some kind of JSON implementation anyways), but somehow it's a "no-brainer" to implement data consistency at app-level. Okay.
- scarface74 10y ago
  Does your code expect property "X" to be on a "Player" object? Do you put a check everywhere for when it doesn't? Do you have default value for objects that were created before new property was added Why would you put checks everywhere a properly written code base would have one module/service that handles the player object and every consumer would use that one module.
  - aidos 10y ago
    There have to be some checks, somewhere. Even though it's limited to one module, you can get temporal inconsistencies. e.g. you add a new field and an application level default, then later that default is changed. Now there's no notion of which default the docs that never had the property should be using. Sure, you just need to patch the old docs to set the property where it's missing. But the GPs point was that by not keeping a consistent schema you end up moving the definition of the data into the application, and you need to be very aware of the trade-off. Personally I'm really happy using postgres and dropping into jsonb when it's the correct model to use. It's the best of both worlds as far as I'm concerned. I've built and worked on systems using nosql solutions that have been migrated back to sql and the data inconsistencies we found have made me very wary of having a primary datastore that doesn't enforce strict constraints. Though, everybody's use case is different.

FLUX-YOU 10y ago

Logging

danso 10y ago

I had assumed Facebook started off with MySQL and now is NoSQL but that was just ignorant of me: Zuckerberg may have had to rely on a typical LAMP stack but FB's newer user-facing features still use SQL. For example, the Timeline feature released in 2012 was built on MySQL/InnoDB: https://www.facebook.com/note.php?note_id=10150468255628920 https://www.facebook.com/note.php?note_id=10150468255628920 And last year Facebook released MyRocks, which is a space/write optimized replacement for InnoDB, and is being used for their "user database tier" https://code.facebook.com/posts/190251048047090/myrocks-a-space-and-write-optimized-mysql-database/ https://code.facebook.com/posts/190251048047090/myrocks-a-sp... I guess the things I've read about FB using NoSQL [1] is for other parts of their infrastructure, particularly Messages. It sounds like they had considered using MySQL, even though Cassandra was built for the Inbox feature. They ended up building a new system for Messages [2]. Now that I've spent a little time in that rabbit hole, it looks like one answer to your question is Facebook's Inbox Search, which used MySQL originally to store inbox data (7TB for over 100M users, which seems laughably small with respect to Facebook's scale today): http://docs.datastax.com/en/articles/cassandra/cassandrathenandnow.html http://docs.datastax.com/en/articles/cassandra/cassandrathen... > Before launching the Inbox Search application we had to index 7TB of inbox data for over 100M users, then stored in our MySQL[1] infrastructure, and load it into the Cassandra system. The whole process involved running Map/Reduce[7] jobs against the MySQL data files, indexing them and then storing the reverse-index in Cassandra. The M/R process actually behaves as the client of Cassandra. We exposed some background channels for the M/R process to aggregate the reverse index per user and send over the serialized data over to the Cassandra instance, to avoid the serialization/deserialization overhead. This way the Cassandra instance is only bottlenecked by network bandwidth. The acknowledgments section makes it more clear that the MySQL data was indeed migrated over to Cassandra: > Cassandra system has benefited greatly from feedback from many individuals within Facebook. In addition we thank Karthik Ranganathan who indexed all the existing data in MySQL and moved it into Cassandra for our first production deployment. [1] https://news.ycombinator.com/item?id=7891316 https://news.ycombinator.com/item?id=7891316 [2] https://www.facebook.com/notes/facebook-engineering/the-underlying-technology-of-messages/454991608919/ https://www.facebook.com/notes/facebook-engineering/the-unde...

33degrees 10y ago

If you consider ElasticSearch a NoSQL solution, then yes, many stories. A properly normalized database can be very slow to search, and throwing ElasticSearch in front of it is a very common solution.

zzzcpan 10y ago

Switching a project from SQL to NoSQL is usually too expensive. Traditional relational databases lack a lot of necessary constraints to even have architectures necessary to make the switch to a distributed database, it can only happen with a complete redesign of everything. I think it's more common for those who had problems with SQL databases to drop them for their next projects and move on. For example, I consider a highly-available multi-site distributed database as a minimum viable database and haven't used an RDBMS in like a decade.

kalleboo 10y ago

Does using memcache for caching count as "augumenting with a NoSQL solution"? Because that's pretty popular...

prashnts 10y ago

We used to use Postgres for almost everything, including full-text search. Later when we needed a bit more control over search, we started using Elasticsearch. The way we do it now is to perform joins and get a nice serialized object with all the possible search/filter/sort fields, and put it in ES. All the data still remains in postgres, though.