#ZABBIX POSTGRESQL VS MYSQL DRIVERS#
#ZABBIX POSTGRESQL VS MYSQL MANUAL#
#ZABBIX POSTGRESQL VS MYSQL SOFTWARE#
#ZABBIX POSTGRESQL VS MYSQL CODE#

Rereading it now, after using MySQL at work this whole time, I got a couple of details wrong. Also, here's a followup I wrote shortly after when I had thought of more to write about.

#ZABBIX POSTGRESQL VS MYSQL SOFTWARE#

What's ironic is that I'm a software engineer, not a DBA, but I get called in to fix their database problems.Īs someone who is passionate about SQL/databases, and has used both Postgres and MySQL for a few years (guess I'm going on about 7 for MySQL at my current job, yikes), here's a comment I wrote on this subject a few years ago. So my contract gets extended and I get paid more. They do an inner join across tables and get no records back and assume that means there are no records. Every client I've ever worked with that doesn't use foreign key constraints has dangling records.

#ZABBIX POSTGRESQL VS MYSQL MANUAL#

Only after extensive work fixing bugs and manual testing and creating database backups and building out a complete rollback plan in case things go wrong, do we start switching production servers over to strict mode.Īnd the kicker? I've found that many companies that use MySQL dont' use foreign key constraints. The general approach is to get the test suite running on MySQL in strict mode, but leave production servers alone. More than one client deliberately uses the invalid date of 00:00:00 to signal to the software that this row has special handling. Some bugs are virtually impossible to fix. We have to go through, bug by bug, and fix them.

#ZABBIX POSTGRESQL VS MYSQL CODE#

So we start with this: SET sql_mode = STRICT_TRANS_TABLES Īnd everything falls down and goes boom for every damned client because their code has been written with the expectation that MySQL is 'friendly' to their data. First make it correct, then make it fast. That was one of many bugs that killed their I18N project. Thus, their test suite showed that the SQLite test stack was working correctly, even though their production code was not.

#ZABBIX POSTGRESQL VS MYSQL DRIVERS#

Turns out the MySQL and SQLite database drivers they were using had subtle differences in how dates were handled: the test suite assumed all dates without timezones were UTC but the MySQL used a floating time zone. The argument was 'because it's faster.' It took me months to get them to admit they were wrong. Same client: they had a large test suite, but were using SQLite (!) as their database for testing. They had to throw away two years of data.

One client had a massive data science project running for a couple of years, and I discovered that because they weren't using strict mode, the 30-character key they were using to join all of records was being stuffed into a VARCHAR(20) field and being truncated. Why the hell give me a date type if I can put a non-date in it? It's like declaring something an integer and discovering the field contains the string 'bob' or something. Hell, for the longest time, their documentation said 'it is not the job of the SQL server to validate dates'. You want division by zero to throw an exception. What I want is something closer to this (reformatted for your pleasure): STRICT_TRANS_TABLES,STRICT_ALL_TABLES,īecause they aren't in strict or traditional mode (traditional is like strict mode but offers even more safety), their database allows a lot of junk to pile up in it. Most of the time, it will show me nothing. Usually when I go into a shop using MySQL, one of the first things I do is this: mysql> SELECT | The real issue is that for older code bases, they're usually using an older version of MySQL and they can't just enable strict or traditional mode ('traditional' makes it sound like it's quaint or something). I replied from my phone, but now that I'm at my desk, I can take longer. One proc took 8 minutes, now in Postgres finishes in 3 secs.Įdit: added 7 and 8 cause I love Postgres and forgot to. We were using Python utilizing Pandas to merge and join.

All of our databases from the past 10 years are now in their individual schemas. Postgres is now serving as a data warehouse of sorts. These run faster than anything I’ve created in Postgres and are native.Ĩ. I’ve been recently doing lots of JS funcs for JSON payloads e.g. Ability to create procs/funcs in different languages like Python, JavaScript, Perl, etc. More robust data types (UUIDs, Booleans, etc.)ħ. I’ve been at my current employer for 10 months and have been transitioning them from MYSQL to Postgres for 4 months. Hands down, Postgres has everything you want and more. But I’ve used Oracle, Postgres, SQL Server, and MySQL during my career.

Zabbix postgresql vs mysql