MariaDB [wut]> create table wut (stuff varchar(17));
Query OK, 0 rows affected (0.09 sec)
MariaDB [wut]> insert into wut values ('really important stuff here');
Query OK, 1 row affected, 1 warning (0.02 sec)
MariaDB [wut]> select * from wut;
| stuff |
| really important |
1 row in set (0.01 sec)
I know that it showed me a warning… I know it told me it truncated the data.. maybe there is a way to make it error vs truncating then inserting it.. all I know is I set it to varchar17 for a reason (all be it, a contrived reason).
wut=# create table wut (stuff varchar(17));
wut=# insert into wut values ('really important stuff here');
ERROR: value too long for type character varying(17)
Ahhh, much better!
Postgres is definitely the 80% (90? 95?) use case database with a nicer experience and great features. A lot of people who use a mysql variant, if they were choosing their storage technology today, would be better off with postgres.
There are still some performance, operational and reliability (esp at high throughput) issues to work through that keep some of the largest rdbms users on mysql, but hopefully one day they close the gap in that area. That said, I don’t see facebook throwing away their myrocks investment. Would google throw away their mysql expertise? Youtube still runs on vitess, right?
The oracle ownership of mysql core is a huge concern for everyone. When google, facebook, alibaba, linkedin and twitter teamed up for the webscalesql project, they were careful to market their work as a “patch set” rather than a “distribution” to keep oracle at bay. Mariadb has licensing issues, but has some cool features like the ability to slave from multiple masters, which can be a super useful feature for merging shards. Myrocks isn’t a great fit for workloads which access lots of old data (its lsm shines on hot recent data). Percona is carrying things forward, and xtradb or the toku fractal tree engine are great general purpose engines. But this fragmentation sucks.
Oracle isn’t helping anyone out. They close-sourced the mysql tests and over time are modularizing parts of the system that they can then EOL and replace with paid-only solutions.
Postgres is mostly the present and even more the future, but mysql still holds court for the most demanding workloads, and it’s a damn shame how oracle is handling things.
I feel about MySQL the way I feel about something like Cassandra or, less charitably, Mongo – it has its uses, but it is not a good general purpose data store. The cavalier attitude that MySQL has to data is IMHO disqualifying unless you a) absolutely know how to get around it b) have the resources to maintain good data integrity checking outside the data store layer and c) need something that MySQL does that Postgres doesn’t.
I’ve been kinda on the PG > MySQL bandwagon for a while, considering how much more full-featured the PG SQL is. But I’ve actually started to rethink that - MySQL seems very popular for webapps, works fine for the type of ORM-generated queries webapps mostly use, and actually has some better features for replication. Most of the webapps I work on are MySQL and only a few are PG, and I very rarely need to run the kind of SQL queries that PG is better at.
It’s been a while since I started a new webapp completely from scratch and could pick any database I wanted, but if I had to do that again today, I’d think a lot harder about using MySQL.
Can you expand on the ORM bit?
I’ve used Django professionally and near exclusively for about a decade, with a longer history of SQL. In my experience, the ORM is really easy to use as a gun to shoot yourself in the foot with.
So do you mean MySQL is better at handling badly-made queries?
For replication, unless you’re really huge (and maybe even then), repmgr should make things good enough on Postgres.
What I mean there is that Postgres has a ton of awesome SQL features, such as regex replaces, a ton of JSON manipulation stuff, CTEs, LATERAL joins, etc. I love those features when I’m writing queries against large Postgres databases by hand to do various things, and if I have to do things like that against MySQL or SQL Server, I groan to myself how much I wish they had whatever features they don’t.
But I also noticed that I only run queries like that by hand. I’ve never used Django, but the ORMs that I have used, mostly ActiveRecord, EntityFramework, and NHibernate, only issue simple queries with basic SQL. Those cool Postgres features aren’t getting used. ORM queries do tend to be a little dumb sometimes, but in certain predictable ways, mostly lots of N+1 queries, which no DB engine I’ve ever seen helps with.
As for the validation issues highlighted in the article here, it looks weird when you test explicitly for it, but I just haven’t worked with any apps that use those types of DB validations. It seems much more common for the apps I’ve used to use varchar(255) fields for things that are clearly always integers and text for json and do the validation in application code. It may not be the neatest, but there isn’t often that much business purpose in doing strict validations in the database as well as application code.
I don’t know about the full set of tools available for Postgres replication, but tools for handling it in MySql do seem to be a lot more common and widely deployed.
Not saying that MySql > Postgres by any means, just that the choice between them isn’t nearly as clear-cut as some would have you think.
TL;DR PostgreSQL is much less likely to destroy your data.
“Will it reliably and correctly store and query my data?” is the first question to ask about a database, IMO.
If not, speed and scalability just tell you how bad your problems will get and how fast.