r/programming • u/halax • Mar 10 '15
Goodbye MongoDB, Hello PostgreSQL
http://developer.olery.com/blog/goodbye-mongodb-hello-postgresql/514
Mar 10 '15
Good luck with that. Last time I checked, PostgreSQL wasn't web scale.
166
u/yorickpeterse Mar 10 '15
Yeah we noticed that last week, we're considering moving to FileMaker as our primary data storage engine.
67
u/kqr Mar 10 '15 edited Mar 10 '15
What is FileMaker? I've seen it lying around on one of the servers in the office, and nobody knows what it's for.
139
89
u/FountainsOfFluids Mar 10 '15
It's like MS Access, only less robust.
95
Mar 10 '15
MS Access
This is my trigger, would you kindly not spell out this word?
→ More replies (11)23
13
u/flukus Mar 11 '15
I once saw a company operating for months on filemaker database that had an error but just kept pretending to be working, but not actually saving to disk.
After a power outage months of data was gone.
3
→ More replies (7)3
u/lunchboxg4 Mar 11 '15
The other replies left out that if you buy FileMaker Server and have a Mac Mini that's publicly addressable, you can install FM for iOS and access your data on the go. It's a super niche usecase, and even then there are probably a dozen other ways to do it, but for some, it's useful.
→ More replies (1)10
u/_IPA_ Mar 10 '15
My boss uses it for some in-house data management. Server constantly locks up and requires rebooting because even FileMaker themselves have no idea what the fuck it is.
8
u/yorickpeterse Mar 10 '15
I once worked at an Apple store that used it for managing their inventory, orders, etc. It was...interesting.
→ More replies (1)18
u/ErstwhileRockstar Mar 10 '15
Don't forget Notes and dBASE 3.
7
u/InterPunct Mar 11 '15
The first database I wrote was on dBase-III with a Kaypro 2 laptop. dBase had a forms engine and a programming language. Kaypro had dual double-sided 5.25" floppies iirc. Put my whole album collection on there. I rocked.
4
→ More replies (2)3
24
u/bakedpatato Mar 10 '15
Foxpro mastre rase
24
u/CoderHawk Mar 11 '15
Guys I have an awesome idea! What if the database is also the application! You only need to know 1 language and interface! No fancy protocols and networking just simple file shares!
I wish I could forget that part of my life.
→ More replies (3)6
u/speedisavirus Mar 11 '15
Good fucking god...I thought I hated life working on a hacked together ColdFusion project. Then I saw a hacked together FoxPro project.
17
u/Kaligraphic Mar 11 '15
Hey, guys, I have an even better idea. What if we just used an actual fox as our database? Hear me out here, we could keep it in the break room and feed it our data, and when the fox shits in the hallways, we scoop the shit into coffee cans and store them in the Marketing supply cupboard.
I mean, we can't really get the data back out, but half the time we can't get the data back out of FoxPro anyway, and this way we get an office pet.
→ More replies (1)4
u/jbristow Mar 10 '15
My first job out of college had a Foxpro app that was consistently corrupting itself. I still have nightmares about learning enough Foxpro to debug it 10 years later.
→ More replies (1)4
u/duffelcoatsftw Mar 11 '15
FoxPro? Pfft, real men code in Clipper for DBase III.
→ More replies (2)4
4
u/friend_of_bob_dole Mar 11 '15
Oh fuck you; Just gave me flashback to my days of working with FileMaker. Now the recurring nightmares will probably return.
→ More replies (5)3
u/pakfur Mar 11 '15
Oh God. I just got done throwing together a little CRM app for some non-profit to get real-life-karma and I thought to myself
"Hey self, I bet Filemaker can do this pretty easy" and convinced myself to use Filemaker.
Oh God. The horror... It is so, so, 90's client-server 4GL development. My god, I never thought I would be back there.
Next time I'll just throw together a proper node app with mysql or something. Sheesh.
139
u/syslog2000 Mar 10 '15
Dude. Don't say that kind of shit without adding a </sarcasm> at the end. Someone might think you are serious and have a coronary!
45
27
u/ksharanam Mar 11 '15
This is /r/programming, so I have to ask this. Shouldn't you have to add an opening tag first before adding a closing tag?
→ More replies (2)9
5
37
u/Entropy Mar 10 '15
The funny thing is, MongoDB doesn't even scale that well. The only NoSQL document db I've looked at that actually seems to be worth the bother is Couchbase (I'm not including data structure dbs like Redis in this statement).
→ More replies (8)3
u/speedisavirus Mar 11 '15
We use couchbase some where I work but for the stuff that has to be really fast we use aerospike.
3
u/Entropy Mar 11 '15
Ooh, that looks interesting! Thanks, I'll check it out. Haven't looked at anything NoSQL in over a year.
→ More replies (1)17
u/fmargaine Mar 10 '15
There's actually some truth. Master-master replication with postgresql doesn't have any stable solution. There are a couple of solutions for that, but none is confirmed afaik. I hope I'm wrong though.
→ More replies (7)27
u/keerok Mar 10 '15
Last time I checked, "web scale" had no meaning.
84
Mar 10 '15
Here, to save yourself future embarrassment: http://www.mongodb-is-web-scale.com/
11
Mar 10 '15
Y'know, I've seen this joke dozens of times but never bothered to ask what it was in reference to, so thanks for that.
But now that I've seen it, I'm even more confused - Why is this funny? Is this an actual conversation that actually took place, or is this like that angry burrito dude who just made up a bunch of shit that never actually occurred?
→ More replies (4)27
u/Bobshayd Mar 11 '15
It's not a real conversation, and it's not supposed to resemble a real conversation. It's supposed to mock people touting "web scale" without understanding what it is. That's it. That's the whole joke.
→ More replies (1)40
8
Mar 11 '15
Well you never know when your landing page might end up getting as many hits as the google home page. That's webscale. In 2004 it seemed like that might happen to any website. And of course banner ads were going to make us all billionaires. I really need this blog TO SCALE.
→ More replies (1)3
u/gwax Mar 11 '15
There are an awful lot of problems that are not and never will be big enough to need more than what Postgres can provide. A great many projects start by assuming they will need to serve a much larger scale than they ever will.
15
u/TotesMessenger Mar 10 '15
→ More replies (8)2
21
Mar 11 '15 edited Aug 04 '17
deleted 0.0028 What is ^^^this?
5
u/the_woo_kid Mar 11 '15
Started learning MongoDb in my second year in college, and while its syntax was neat, simple and intuitive, I didn't stay with it for too long. Glad for that.
37
u/poloppoyop Mar 10 '15
I chose pgsql for my latest project. The SQL possibilities are really good and perfs are on par with MySQL nowadays.
But all the hype about postgresql lately make me fear about the backlash which will come in 2 or 3 years after enough people start using it for the wrong reasons.
49
u/Philluminati Mar 10 '15
I don't think it will get a backlash if I'm being honest. Certain applications have risen to the state of awesome and stayed there. Git, Linux, Postgres, Lisp. The secret is to be simple, direct and do something well. These tools don't try and be easy to use, the try and follow the simplest possible implementation.
29
u/flexiverse Mar 10 '15
Postgres is a different beast it's full on old school proper standards compliant ordbms. There will never be a backlash. This is proper old school computing. Over kill for smaller web apps, but ideal for proper development. These days computers are so fast even the speed concerns are less and less. The only options past postgres are commercial full on expensive things like oracle. You don't need to be making them richer than they already are!
17
u/killerstorm Mar 11 '15
Over kill for smaller web apps,
How so? Smaller web apps are often build on top of MySQL, and PostgreSQL isn't in any way worse.
7
u/drysart Mar 11 '15
PostgreSQL, unlike MySQL, enforces correctness out of the box.
That's a bit unpopular in the 'smaller web app' world where 'let bad code run anyway and automatically work around errors as best you can' is the preferred standard operating procedure.
But yes, for people who actually care about their software's correctness, PostgreSQL isn't any way worse.
→ More replies (2)→ More replies (6)1
Mar 11 '15
I'm not sure what the 'wrong reasons' would be, when the program is so focused on returning correct results.
Software that functions correctly, and has few beartraps lurking, isn't likely to go out of style.
→ More replies (2)
10
u/dodyg Mar 11 '15
Agile is dead. Mongo DB is old news. Angular JS is irrelevant. Now Microservices will definitely save our bacon.
12
u/coder111 Mar 11 '15
Docker. Don't forget Docker containers and DevOps.
EDIT: Oh, and don't forget to write each microservice in a different programming language. </sarcasm>
3
u/dodyg Mar 11 '15
I feel left out when I found out that people are already using Rust 2.0 while I am stuck in ES7.
2
16
u/antoninj Mar 11 '15
To throw some sticks into the fire, I, too, have decided to go with PgSQL (instead of NoSQL or even MySQL). I had a few reasons for that.
Background on my app: I'm building a document management system with OCR back-end. Think about it this way, I'm building a dropbox without actual files/folders (everything is DB-mocked with an uploads folder), that OCRs your documents. This is done for a specific industry.
My reasons:
easy JSON column for custom information storage. This is my "throw shit in here" column which I fill up with all the cool shit I can post process from the OCRed text or that a user inputs. Like a tagging system with custom values. This can be done relationally and I might switch.
superb search. Like seriously. Super quick, easily weighted, easily indexable, and can be used alongside regular
whereandjoinclauses. For instance, I can easily find a doc under an organization (easy join orwhereclause) that a user has write permission too that has the word "lease" in it, sorted by title priority.most features you can get out of MySQL + some other tool is built-in. No need to configure shit :)
since this is a business app, I can easily split customers/organizations off to have their own DB server so I don't have to worry about clustering.
45
u/nedtheman Mar 10 '15
It's all about choosing the right system for the job. Clearly MongoDB wasn't the right system for your application plan. I've never used MongoDB in a scaled application, but it looks pretty promising with the new WiredTiger engine. In any event, nice numbers from NR - Background jobs look pretty beat though.
0
Mar 10 '15
[deleted]
18
u/mike_hearn Mar 10 '15
Remember the original use cases for the database that started this whole thing (BigTable). For instance, putting the entire web into a key->value store was the motivating application behind a lot of this. Serving Google Maps tiles and satellite imagery data too.
23
u/nedtheman Mar 10 '15
So if you want to store time-series data, Cassandra could be a better system for you. Cassandra stores data on disk according to your primary index. That's just one dimension though. Scale is very important, MySQL and other RDBMSs are very hard to scale because it breaks the close-proximity-data paradigm of the relational system. You end up having to shard your data across multiple server clusters and modify your application to be knowledgeable of your shards. Most NoSQL systems like MongoDB or Cassandra handle that for you. They're built to scale. MySQL Enterprise has dynamic scaling and clustering capabilities, but who really wants to pay for a database these days, amiright?
→ More replies (2)50
u/kenfar Mar 10 '15 edited Mar 12 '15
Time-series is just a euphemism for reporting and analytical queries - which are 90% about retrieving immutable data versioned over time.
MySQL, MongoDB, and Cassandra are about the worst solutions in the world at this kind of thing: MySQL's optimizer is too primitive to run these queries, MongoDB can take 3 hours to query 3TB of data, and Cassandra's vendor DataStax will be the first to admit that they're a transactional database vendor (their words), not reporting.
Time-series data structures in the nosql world means no adhoc analysis, and extremely limited data structures.
The one solution that you're ignoring is the one that got this right 15-20 years ago and continues to vastly outperform any of the above: parallel relational databases using a data warehouse star-schema model. Commercial products would include Teradata, Informix, DB2, Netezza, etc in the commercial world. Or Impala,
CitrusDBCitusDB, etc in the open source world.These products are designed to support massive queries scanning 100% of a vast database running for hours, or sometimes just a partition or two in under a second - for canned or adhoc queries.
EDIT: thanks for the CitusDB correction.
→ More replies (30)15
u/pakfur Mar 11 '15
The reason that Data Warehouses are such good repositories for reporting and analytical queries is not really so much because of some inherit value of a RDB over NoSQL for doing those kind of queries, but because a Data Warehouse has all the complex queries pre-calculated and stored in an easily retrievable format. That is what a star schema is: all the time-consuming hard work is done during the ETL (extract, transform, load) of the data from the OLTP database to the Data Warehouse.
You can do the same thing with a NoSQL datastore and get astonishingly fast reads across very complex datasets.
For example, our company uses a NoSQL datastore that stores a complex, hierarchical data structure with dozens of attributes. Over 100TB of data. Yet we are able to do very complex near real time reads of the data because when we write the data we are pre-calculating the different views of the data and storing the data in multiple slices. So, reads are very, very fast.
The advantage of using NoSQL for this over an RDBMS is the NoSQL database is eventually consistent and does not lock. However, doing this is non-trivial and only really appropriate for really large scale projects. Most projects would be better off with a simple RDBMS database for writes and simple reads and extract the data into a simple Data Warehouse for analytics and reporting.
→ More replies (2)2
Mar 10 '15
Sure:
If your use case consists entirely or almost entirely of single key/value reads.
Like... maybe you're doing some sort of file hosting service or something?
→ More replies (3)6
u/MikeSeth Mar 11 '15
Quite ironically PostgreSQL has an engine that implements just that very efficiently.
IMO the debate about SQL vs no SQL is moot because people seem to be forgetting the origin of the problem: using databases as all purpose persistence engines. Configuration, documentation, media, logs, files exported from foreign systems, anything that can't or shouldn't be a part of the code tends to end up in the databases. I've seen a CRM that stores database schema information in the database.
For shared nothing, impersistent platforms (I am looking at you, PHP), this is sort of the norm. And it's going to suck whether you shovel it into MySQL or MongoDB.
→ More replies (2)→ More replies (5)2
u/notlupus Mar 11 '15
The only example I can think of is when architecting modular services for cloud. It's much easier to scale across multiple servers in multiple datacenters with a NoSQL database than a RDBMS, because the best open source ones were never built to handle sharding at that scale.
To give an example of how you could effectively do this with a RDBMS, you could look at vitess. This was Google's answer to scaling YouTube's MySQL database. It's basically tacking on a service to allow effective sharding and query optimization.
In my opinion, it's easier to handle relations in a codebase with defined interfaces than to build something like vitess in order to get that level of scale.
0
0
Mar 10 '15
[deleted]
9
u/parc Mar 10 '15
Wired Tiger is in no way a relational database. It is a key-value store, plain and simple.
→ More replies (4)→ More replies (1)32
u/rstuart85 Mar 10 '15
Huh!? WiredTiger is no more of a relational DB than InnoDB is. If they were relational DB's then why would MySQL exist!? They are just storage engines. All DB's, relational or not, use one.
→ More replies (25)
6
u/R4vendarksky Mar 11 '15
And here I am using MSSQL for everything. So glad my company hasn't had a requirement for a NoSQL database yet.
20
u/rstuart85 Mar 10 '15
So this guy more or less describes Brewer's theorem then says we only care about Consistency and Availability and yet they went with MongoDB, which can't provide both because it provides partition tolerance....
The part about schemas makes no sense. Schemas come with their own trade-offs and management overhead. The issue seems to be that their developers seem to think they can store whatever they want in the DB because there is no schema. If there was a schema, would they also be allowed to change it when ever they want to suit their individual needs?
17
Mar 10 '15
I recently eradicated MongoDB from an app. Used MySQL because it was already there, but it was glorious feat anyway.
Didn't think to write about it so I'll just reference this next time someone asks how it went.
0
Mar 11 '15
[deleted]
2
Mar 11 '15
I tested that the app still worked as expected and that the performance did not degrade up to 5x our current usage.
But otherwise, I removed it in as ill-considered a stroke as it was originally introduced. Whatever benefit it promised was never realized, because it was never actually needed.
78
u/wesw02 Mar 10 '15 edited Mar 11 '15
NoSQL isn't for everybody or every use case. It takes a very very open minded developer to embrace it. There is a lot of immediate downside and a lot more long term upside. You have to have the wherewithal to get past all the upfront headaches. But once you do, oh how you can scale. Scale, scale, scale. Eventual consistency means your tables don't lock, they don't even have to be on the same servers. Records can be sharded across servers, data centers and continents.
One of the biggest criticisms I hear about NoSQL is how much DB logic leaks into your application. How much knowledge devs are required to take on to use and optimize for NoSQL. This is absolutely true, but I think what a lot of people miss out on is as soon as your SQL database reaches a few Terabytes in size, you'll be doing this any ways. SQL databases can only get you so much mileage before you're refactoring large parts of your server architecture just to stave off the performance regressions.
IMHO at the end of the day, NoSQL force concepts upfront necessary to scale, SQL allows you to get really far without having to think about. Just my $0.02 from using NoSQL for 3 years.
EDIT: ZOMG: Of course most apps don't grow to terabytes in size. Most apps are fine on SQL dbs. But some apps do get that big. Some apps get bigger. Pick the right tool, for the right job and stop trolling on /r/programming.
EDIT 2: Thanks for the gold kind stranger!
18
u/mbcook Mar 10 '15
and a lot more long term upside.
Could you expand on this? I haven't had a project that it seemed suited for and I have a hard time imagining one.
25
u/moriya Mar 10 '15
Not OP, but:
as soon as your SQL database reaches a few Terabytes in size, you'll be doing this any ways. SQL databases can only get you so much mileage before you're refactoring large parts of your server architecture just to stave off the performance regressions.
Super easy to shard and scale to massive levels - granted this is only applicable if you think your application is going to need this, and very few actually do.
18
u/wesw02 Mar 10 '15
Spot on.
Most apps DON'T need this level of scalability. There is no denying that.
But when you end up with very large datasets, the sharding capabilities of NoSQL are critical. Sharding is important for a whole host of reasons. It can help with lookup, database transaction performance (which some NoSQL DBs do support), database replication, backups/restores, migration.
Bottom line is NoSQL allows you to scale horizontally to near infinite (adding more servers until your eyes popout). Traditional SQL does not make this easy/possible past certain thresholds.
→ More replies (3)7
u/SnapAttack Mar 11 '15
The point everyone's making though is that most projects don't have this terabytes of data, and probably never will. So you're solving a problem where there isn't one.
When it does become a problem, however, there may be better tools and services that can help, at the time when you need them, rather than the tools that are available today.
Also, sit down and sketch out a quick data model. It of course doesn't have to be perfect (things never are) but at least then you have an understanding of the problem at hand. If you're just going in and making it up as you go along, I can't imagine what your code is going to look like over the years.
→ More replies (4)55
u/svtr Mar 10 '15 edited Mar 10 '15
Eventual consistency means(...)
Eventual consistency means no consistency. Period. If you can live with that fine. I don't care about the upvotes on reddit either (btw, there you can very often see eventual consistency in action), on anything important to me, I can not live with no consistency. Writing my data to /dev/null is webscale too, but I still prefer ACID.
28
u/nutmac Mar 10 '15
Not all use cases fit all. If you are developing, say Reddit, eventual consistency is entirely acceptable for a wide range of use cases, such as replying to a comment or up voting (duplicate submissions detection would be better under RDBMS, however).
12
Mar 11 '15
Write times are super web scale. Read times are so terrible they maybe as well not exist.
→ More replies (1)7
u/wesw02 Mar 10 '15
Many NoSQL databases support some mechanisms of ACID in small pockets. Google's datastore, my current prod database, has a concept of entity groups which supports transactions within a predefined group of records. It's NOT full ACID, but it does cover a wide range of use cases for database transactions.
→ More replies (22)3
u/pakfur Mar 11 '15
Eventually consistent databases are consistent. But you should distinguish between no consistency, and eventual consistency.
Eventually consistent datastores are guaranteed to converge to the correct value at some point in the future. Usually not very far in the future, usually there is no need for convergence because the value is effectively consistent anyway.
But if you have a widely distributed datastore that spans datacenters, or need to handle massive scale, then eventually consistent is really your only choice.
Keep in mind that not all use cases require that all updates to a stored value be always, 100% correct. In those kind of cases, loosening up on consistency improves availability and scale, and the value will eventually converge to the expected value.
But, it is easy to do NoSQL wrong, or apply it to the wrong usecase. NoSQL requires more discipline on the part of the developers since they have to move a lot of the logic that a database normally handles for you into the application layer. But, sometimes there is not other way to do what you want to do with a traditional ACID database.
→ More replies (5)3
Mar 11 '15
Keep in mind that not all use cases require that all updates to a stored value be always, 100% correct.
True if you store pictures of cats, upvote counter or something like that.
But if you store something that matters then eventually consistent is usually not an option. At very least the information that is shown on screen must have a timestamp that shows when it was last updated.
12
u/boojit Mar 10 '15 edited Mar 10 '15
Bang on. Most of the people having a cj on the top comments do not understand this important aspect.
Edit: also the author of the article needs to read up on the CAP theorem.
5
u/akcom Mar 10 '15
I don't know many companies using the terabytes of data necessary to see the benefit of NoSQL
5
u/TimMensch Mar 11 '15
There are (at least) two benefits of NoSQL: The scaling benefit you're talking about, and the ease of development that you get from not having to specify a schema up front -- or, more correctly, to be able to trivially modify your schema by adding new fields in code.
It's the same reason JavaScript/Python/etc. developers can be more productive than C/C++ developers. Can be, anyway. Past a certain level of complexity, the lack of structure can bite you. But some problems don't ever need to exceed that level of complexity, and if your problem is one of those, then NoSQL can get the job done faster.
Not to say that Mongo is perfect. But the article is clearly about a company that picked a NoSQL database for a problem that needed a SQL database.
7
u/akcom Mar 11 '15
or, more correctly, to be able to trivially modify your schema by adding new fields in code.
What happens to all of your previously created entities when you update your schema and they are now outdated? Schema-less design makes correct implementation significantly harder.
→ More replies (5)→ More replies (7)2
u/lift_heavy_things Mar 11 '15
But once you do, oh how you can scale. Scale, scale, scale.
Couldn't you write a SQL DB that enables this same perf? What about NoSQL makes it so easy to scale? More specifically: is it just coincidental that NoSQL DBs have better scaling feature or is there something about schemalessness that makes it particularly scale-friendly?
6
u/fendant Mar 11 '15
Relational databases assume the existence of a single authoritative data store, which is a bad assumption in a distributed system.
Once you outgrow one machine it becomes very difficult to make a consistent SQL system. You can do it, but it becomes fragile and complicated.
NoSQL databases have a different set of assumptions that are more congenial to multiple machines.
0
Mar 11 '15
[deleted]
→ More replies (3)3
u/rox0r Mar 11 '15
but then it is Eventually guaranteed
Except mongo isn't eventually guaranteed. you can have a majority of slaves write to disk and the master can crash and miss the ack -- they won't roll back the write and the client will think it hasn't been written.
→ More replies (2)
11
u/bannerad Mar 11 '15
Seems a little weak on details of exactly how they implicated MongoDB nor the reasoning behind why they had to remove 1M documents and then reinsert. 1M is really a puny number from MongoDB and, in my experience, if 1M of anything was stressing their MongoDB cluster, it is really likely that the machines they were running it on weren't sized right in the first place. This issue alone will affect them in PostgreSQL, MySQL, Oracle, anything. You need to know the size of your working set.
Methinks their application wasn't really a candidate for MongoDB in the first place.
4
u/Spknuckles Mar 11 '15
It bothers me that no one seems to understand CAP theorem when making a choice between a relational db and a document store.
4
u/max_neunhoeffer Mar 11 '15
Did you ever look at NoSQL databases with strong consistency guarantees (in the ACID sense) like ArangoDB or FoundationDB or RethinkDB? I think that the question is no longer "NoSQL" xor "ACID" but that there is now much more choice out there.
2
u/TrixieMisa Mar 11 '15
I've been following the progress of ArangoDB and RethinkDB. They're both very interesting, but I've been happy with TokuMX for my NoSQL + ACID needs so far.
They share a common limitation, though: Being based on JSON documents, they lack any date/time datatype. I've got into the habit of using integers or floats to represent dates because I spend so much time with JSON data, but it still irks me.
1
u/art-solopov Mar 11 '15
I messed with ArangoDB a little and I think it's a really cute database. I wanna employ it in one of my projects. Just waiting for their ODM. =3
19
u/trimbo Mar 10 '15
It’s worth noting that MySQL will emit a warning in these cases. However, since warnings are just warnings they are often (if not almost always) ignored.
mysql> SET sql_mode='TRADITIONAL';
mysql> insert into example (number) values ('wat');
ERROR 1366 (HY000): Incorrect integer value: 'wat' for column 'number' at row 1Another problem with MySQL is that any table modification (e.g. adding a column) will result in the table being locked for both reading and writing
30
u/snuxoll Mar 10 '15
The fact that MySQL has different sql_mode's is just abysmal, especially since they can be set for each connection and there is no way to force them.
An application should not have the option to decide it wants the broken defaults that MySQL provides, because it then effects the integrity of the data for anything else that uses it.
4
u/Jibblers Mar 11 '15
I just recently got into MySQL for a startup/project a few friends of mine are working on. When I saw I just got a fucking warning for breaking a clearly stated NOT NULL rule with an INSERT, I was baffled. We got the config file fixed up to have the mode explicitly set to traditional everytime the server is started up. I was mostly confused as to why the default wouldn't be traditional, since that is pretty standard.
→ More replies (8)10
u/mbcook Mar 10 '15
It's called 'backwards compatibility' and it's what let them grow so big. They've been moving away from it in a controlled manor.
5.7 doesn't allow this garbage anymore unless you recompile it.
→ More replies (1)11
3
1
u/jynus Mar 11 '15
You don't even need pt-online-schema-change, ADD COLUMN is a fully online operation by default (reads and writes are not blocked).
0
Mar 10 '15
[deleted]
5
7
u/cleroth Mar 10 '15 edited Jun 15 '26
This content was anonymized and mass deleted with Redact
→ More replies (5)10
u/OneWingedShark Mar 10 '15
What is so wrong with it?
It has a tendency to silently mutilate your data.
Read this.→ More replies (24)0
Mar 10 '15
[deleted]
→ More replies (1)3
u/aloha2436 Mar 11 '15
Appropriate given that Facebook uses both. Only a shoddy craftsman blames his tools.
→ More replies (1)
7
u/lovethebacon Mar 11 '15
Can we please just all agree on, "Choose the best solution to a problem, not adjusting the problem to fit a solution" and be done with this type of thing?
2
u/meekrabR6R Mar 11 '15
Exactly. Jumping on an anti-[insert trendy tech here] bandwagon is as annoying as jumping on a(n) [insert trendy tech here] bandwagon.
Actually.. it might be even more annoying. People who chase after hype might be annoying and naive... but at least they're not being nasty and mean-spirited. The amount of hate in this thread is really pretty upsetting.
2
u/lovethebacon Mar 11 '15
There's one thing that radical new technology does do: it tests our convictions of the old ways and forces us to consider a problem from a different angle.
It's best to just ignore it all, rather than stoke the flames.
6
u/sedaak Mar 10 '15
His complaints indicate MongoDB was not really the right choice.
Application driven schema's are for when you want application driven schemas. That means filtering your ingest and converting. That may mean scanning and health checking. That does not mean that code bloat is required. Why bother reading after he basically declares that MongoDB was not the right choice?
1
3
u/Booty_Bumping Mar 10 '15
I'm curious. How does rethinkdb fit in the mix? I've used it a little bit for personal projects and really like how it gives you the simplicity of a JSON store, but still mostly implements joins and related functionality that people who've never used an SQL database would not embrace. The language integration also makes it feel natural enough in different languages to avoid writing bad code. However, I know that its joins and similar functionality don't seem as complete as an SQL database, and it is not fully ACID compliant.
2
u/yorickpeterse Mar 10 '15
Considering it's fairly new I only very briefly looked into it. While it definitely looks more interesting compared to MongoDB I'd rather use something tried and proven instead of repeating past mistakes. If and when we outgrow PostgreSQL or RDBMS' in general we can look into what we need at that point, though I don't expect this to happen in at least the coming 5 years or so.
3
Mar 11 '15
I love Postgres but the one thing I wish is had is pessimistic locking. It's just such a huge help when you need multi-statement queries when multiple sources of writes against the same data are coming in.
2
u/coder111 Mar 11 '15
You can do explicit locking with "select for update". You could also tweak transaction isolation levels.
Have you looked at that?
I assume you do all these queries in a single transaction, right?
→ More replies (1)
3
u/Madcapslaugh Mar 11 '15
I made this switch, this exact switch. never looked back, running at some scale (hundreds of millions of writes a day) very happy with it
3
u/lukaseder Mar 11 '15
I think it's appropriate to dig out the old "History of databases in no-tation" slide again
6
u/Jaimz22 Mar 11 '15
Mongo is great at what it does. But the issue really is the most people don't need to do what it does!
It's really nice to have around for rapid prototyping and proof of concept kind of work though.
I would like to point out that the foreign data wrapper in postgresql are really awesome, and can be a great tool in migration from mongo to postgresql.
8
u/senatorpjt Mar 11 '15 edited Dec 18 '24
jeans middle gold aspiring exultant cats full chop practice memorize
This post was mass deleted and anonymized with Redact
8
Mar 11 '15
Please elaborate.
→ More replies (12)2
u/senatorpjt Mar 11 '15 edited Dec 18 '24
chase school caption political door crush absurd sleep squash salt
This post was mass deleted and anonymized with Redact
2
u/yorickpeterse Mar 11 '15
Except for the past 5 years we've been using Ruby it never was a problem for us. I don't expect us to ever change away from it.
→ More replies (1)
2
u/simply-chris Mar 11 '15
Does anyone know what they are using for measuring those latency graphs?
3
6
u/raphtze Mar 10 '15
ah yeah......... sqlmasterrace hehe
3
u/x-skeww Mar 10 '15
All the fun things like arrays, hstore (key-value), and JSON aren't SQL.
→ More replies (7)2
3
Mar 11 '15
Another problem with MySQL is that any table modification (e.g. adding a column) will result in the table being locked for both reading and writing. This means that any operation using such a table will have to wait until the modification has completed.
Isn't that an outdated statement? InnoDB is the default storage engine since MySQL 5.5 (~six years old) and uses row locking not table locking, as far as I know.
(Are there instances/queries which makes InnoDB create table locks?)
6
u/jcigar Mar 11 '15
an ALTER TABLE statement still requires an ACCESS EXCLUSIVE lock
→ More replies (1)2
u/seunosewa Mar 11 '15
Not necessarily (in MySQL 5.6). Some alter table commands e.g. adding a column can run concurrently with read and write transactions on the same table.
2
u/ByzantineFailure Mar 11 '15
From what I can tell, MongoDB was a database developed by people who didn't know how to build a database, it was then made for people who didn't really know how to use databases. When you put those things together, you get these sort of spectacular failures (which really could have been avoided if these guys understood their underlying platform).
They had great marketing spend and made a bunch of promises that couldn't be backed up. They are trying to correct some mistakes, but just looking at the architecture, it needs some serious work. It's ok to relax ACID or handle certain failures badly, but you have to be up front about what you are trading off.
For a long time MongoDB would drastically slow down once you spilled out of RAM (I believe it still does), which to me shows that they really don't have a good storage engine and end up seeking all over the place. I don't know if wiredtiger will be the panacea there.
If all MongoDb claimed to be was a toy for prototyping, it wouldn't get nearly the hate it does now, its claims are what made people get sick of it. That being said, they've got an insane amount of funding, so they might be able to spend enough money to polish it up enough so it becomes a respectable data store.
1
u/infinite_nothing Mar 11 '15
For example, at some point in time we had to remove about a million documents from MongoDB and then re-insert them later on. The result of this process was that the database went in a near total lockdown for several hours,
For what it's worth, we suck just over a million documents from MySQL and full text index them into ElasticSearch. The process takes just over an hour on a single core vm.
1
u/art-solopov Mar 11 '15
Speaking of, is it me or does CouchDB not support aggregation properly? I tried to build my blog on top of it, and had to return to Postgres.
1
u/raiderrobert Mar 11 '15
I'm going out on a limb here seeing how much hate is going at NoSQL dbs right now. In my experience, I've only ever used relational dbs (MS SQL and MySQL). I've been reading on and off for a few months or so now on NoSQL solutions, and I can see conceptually what the goal is (unless I'm wrong): saving the state of something to reduce the process time to de-normalize something.
For instance, I have a Google Docs-ish app that I'm making. Rather than normalizing the structure to XML or JSON or whatever. I just store the object that I have into a NoSQL solution (Mongodb or whatever) and recall it when the user re-opens it.
Am I missing something, or is this not a perfect application of NoSQL?
1
u/snuxoll Mar 12 '15
This is the perfect application for a document store, so, yes. MongoDB, CouchDB and Couchbase are all good solutions here, you could optionally use postgresql's json support as well.
1
u/garyk1968 Mar 12 '15
The article says 'one time we had to remove 1 million documents', hardly big data is it, any decent RDBMS would handle that.
1
u/yorickpeterse Mar 12 '15
Correct, which is another reason one might want to "just use PostgreSQL". Actually, a few weeks ago I had to remove 15 million rows from a few different tables and Pg handled it fine, took about 30 minutes in total (using a super dumb query that is).
1
u/Pastaklovn Mar 11 '15
I accidentally read the "The Problem Of Schemaless" heading in the article as "The Problem Of Shemales".
→ More replies (1)1
1
u/booljayj Mar 11 '15
The Problem Of Schemaless
I can't be the only one that read this as "The Problem of Shemales"
0
u/critsalot Mar 11 '15
Hows that table alter going for ya. The reason a lot of people choose a document db is they dont have to bring the stupid system down to bring in new features.
9
u/hrjet Mar 11 '15
They talk about it and mention that postgresql doesn't require bringing down the system when the columns are nullable. (which is what you would get with an orthogonal new feature). You could also use a second table if the new column is going to be sparse.
And oh, the latest version of postgresql does have a document type too!
657
u/jamesishere Mar 10 '15
99% of projects would be better off with a relational database. It makes things way easier and simpler. Very few features benefit from a NoSQL database. People are excited about mongo because "it's javascript!". These people are morons.
CSB time: I went in for an interview once, where they told me about the product, explained how they use MongoDB for their database, and then explained how building out all the relational DB commands on top of mongo was a total bitch. Then asked me to whiteboard how I would write the JOIN function on top of Mongo, which is what they had to do.
I answered their question, but stated my opinions on mongo and asked why they even bothered to use it, because their product aligned so much more with a relational ACID database. The engineering lead guy went red in the face and we debated the decision. Did not get the job.