Postgres or mysql

#discuss

Oldest comments (13)

My pref is Postgres for its "good enough" text search and great JSON support. Most users nowadays expect a decent search functionality. And in PG it is pretty easy to add a ts_vector column for search purposes. I also find that JSON support alleviates the most common reason people want to use an ORM: a hierarchy of objects. Storing an object graph into tables is a real pain. Rather than using an ORM to numb that pain, I avoid it by storing those as JSON. PG supports indexes on jsonb fields as well. Not everything is best suited as JSON, but you can opt into using it where it makes sense. Otherwise PG is a pretty well-proven and robust SQL DB.

Marco Palamede • Jun 20 '18

Hey! All your description sounds like CrateDB!!

But for me, both have them pros and cons.

Daniel • Jun 19 '18

The only answer to your question is that it does not matter. Both are really good.

If your question came with a set of requirements for your app, then we could think about which specific features of each DB match better your needs.

In any case, don't worry about the DB. At this stage, is the smallest of the problems you need to solve.

And use Heroku, that comes with PG.

Dian Fay • Jun 19 '18

This is a backwards way of thinking about it: there are many important differences among even different relational databases alone as a class, and attempting to build for a lowest-common-denominator generic relational database prevents you from being able to get the most out of any actual relational database. It may be necessary to make those sacrifices if you really do see a need to deploy on multiple infrastructures but that's getting less and less common as services/subscriptions become the dominant product delivery model. If you defer thinking about what you're going to deploy on, that choice in itself is going to affect how you design and implement your application, and not for the better.

Daniel • Jun 20 '18

Please, explain me which differences are relevant for a CRUD app with low traffic, with less than 100GB of data.

Remember that OP made this quesiton in general, not asking to solve some weird corner case in terms of scalability or availablility.

Dian Fay • Jun 20 '18

I catalogued dozens of differences that matter at that level of size & complexity in the post I linked in my other comment; feel free to refer to that.

This is not about corner cases. You can't really defer the database question: if you think you just don't care what you use, you're deciding you're content with the lowest common denominator feature set and ignoring whatever additional functionality might be available to you. With Postgres, that additional functionality is quite extensive and includes many features that simplify schema design and query implementation at any scale, which in turn simplify and speed up application design and implementation. Whether to leverage these features or select another product on its specific merits is therefore an important decision and one that must be made early. Ignoring one's options in favor of the lowest common denominator is the worst possible answer unless there's an identified and justifiable requirement to deploy on multiple database platforms.

Daniel • Jun 20 '18

Most apps will never have a huge amount of traffic, or data, or any special requirement appart from basic CRUD operations. And this is by design.

Other apps could benefit from one of the few features that are exclusive of one DB but:

1 - By the time you start the app, you probably don't know what the requeriments will be.
2 - By the time your app has this requeriments, the DB may have released a couple of new versions, with a different set of features and fixed for former drawback.
3 - Some parts of your app may benefit from the features of one db, while other parts of the app may benefit from features from the other db.
4 - From time to time, there's a blogpost from a major company explaining why they switched from X to Y. And then it's time to sit back and enjoy all the cargo culters claiming how X is completelly outdated and everybody need to migrate to Y.

Dian Fay • Jun 20 '18

None of that is a compelling reason to hamstring your application development by intentionally ignoring widely useful but non-universal features like (for Postgres) check constraints, varlena, RETURNING, or array fields. These and others have a direct impact on how efficiently and effectively you can organize and access the information you're storing, which is the very foundation of "basic CRUD operations", to say nothing of anything non-trivial. Different databases are appropriate for different situations, but what you're saying boils down to "we don't know exactly what we need, so we're not going to think about it".

Daniel • Jun 20 '18 • Edited

We don't know exactly what we need, so we're not going to think about it.

If you have experience creating products, you will agree with me that, when you start developing it, you have no idea of how this product will end up looking like when it hits the market.

And just to finish this conversation. I like fotography and I use to hang out with fotographers. And I found out that there are different types of fotographers.

There are some photographers who buy extremelly expensive gear, know all the physics related to lenses, know all the features of the camera and even play with the firmware. And, still, they take shitty pictures. Basically, because they think that the camera is the most important for a fotography.

Other fotographers, they just take their iPhone out of their pocket and take wonderful photographies. Because, knowing the limitations of their iPhone, they understand that what makes a great photography is the light, the composition, the subject, the color, the intention... And not the camera.

For web development, it happens exactly the same. Some people just find the simplest solutions to create a product and put it in the market.

Other people bring their startup to a financial or technical banckrupcy while talking about kubernetes, microservices, mongodb and graphql.

Dian Fay • Jun 20 '18

I have several years of experience creating products, and I disagree emphatically. As a software architect (or at least when wearing that particular hat), it's my job to think about how a given product will end up looking when it hits the market. The picture evolves based on feedback and shifting requirements, but it's still a picture representing the same fundamental idea. And that fundamental idea, the process of thought that the product itself is supposed to embody, is an idea about information. How you organize, store, and retrieve that information is one of the first critical decisions you make in the course of bringing the product to life. Agility, minimum viability, and so forth are great ways to minimize risk and iterate safely, but they don't excuse you from thinking about what you're doing.