MySQL, MariaDB versus PostgreSQL
CC Attribution 4.0 International:
You are free to use, adapt and copy, distribute and transmit the work or content in adapted or unchanged form for any legal purpose as long as the work is attributed to the author in the manner specified by the author or licensor.
Holzgraefe, Susanne (miracee)
The talk compares the advantages from MySQL, MariaDB and PostgreSQL. The intention for the talk is helping you finding the system that fits best to you and your applications.
 So welcome to Froscon first talk in the database room. Yeah, Maya Squirrel versus Maria, Debay versus PostgreSQL. Yeah, my slides, the legal stuff, my slides belong to the talk and are not have no standalone meaning. That's very important especially on talks like this. So, who am I? My name is Susanne Holtzgräfer. I'm grown up in Northern Germany and today I'm living in Bielefeld. I worked, now I was, oh no, I'm still PostgreSQL developer since 2000. And then I got a job at Maya Squirrel. I worked as Maya Squirrel developer until they get Oracle and I left later on. I worked as senior consultant for PostgreSQL after that made my PhD. And today I'm freelance consultant trainer for Maya Squirrel, Maria, Debay and PostgreSQL and also for IT security and data privacy. Yeah, that's more for me. I'm still member of ISO SQL standard, community. Yes, so, yeah. The difference or differences between Maya, Squirrel, Maria, Debay and PostgreSQL, what do you think? What do you have in mind? What are the major differences? Any ideas? No. Yeah. PostgreSQL is an extra no SQL database. PostgreSQL is a no, not only SQL. Is it an elastic search? Yeah, but elastic search don't belong to PostgreSQL. It's an extra tool. I only will consider here. That's very important. What you can get for free and what the render is, the community only, so only the core features, because there are lots of features for all outside and most of here. The main difference is probably that the different format of the dates. Yeah, the different format. Yes. You wait for, huh? Yeah. That's what you found. So, there's a very important main difference. It's the license. And PostgreSQL is a BSD. So, that's very important because you might have to look which license do I need or if the license is compatible to my software, to the rest of my software. There are GPL and BSD sometimes. Don't like each other. When you just need the client, you might want LGPL. So, license always is the main topping when deciding which database system will I use for my software or will I use? When you just use it for your own company, the license doesn't matter, but when you want to ship software which needs it, you need to consider the license. The second is more on the render. MySQL belongs to Oracle. MariaDB, there's a MariaDB company. PostgreSQL is community only. I mean here the development. So, the most development for my SQL at the moment is done by Oracle from MariaDB and PostgreSQL. It's community only. So, they are volunteer developers on PostgreSQL. That's the most important. You have lots of developers at PostgreSQL who founded companies and offer services like support, consulting, training, offer tools for PostgreSQL and so on, but the core development is community only. When you consider which system I want to use, you may be asking for support and services. You have commercial support. You can get commercial support from my SQL from Oracle, from MariaDB, from MariaDB today, and from PostgreSQL, there are different developers offering like KrederTief, it's in Germany, like me of course, like EnterpriseDB, Second Quadrant, that are the three biggest ones, and of course people like me, freelance, former developers, who offer commercial, no, I don't offer support, I just offer services, who offer support. Commercial services like training and consulting at my SQL, okay, Oracle made an outsourcing from my SQL consulting and training, so today you have other companies for commercial services. MariaDB is the same, MariaDB offering support and training by all developers, by own people, and by PostgreSQL, that's what I already said, there are developers offering training and consulting like me and a few others. The language spoken in support in MySQL, MariaDB, it's English, they both, I'm not sure for MySQL, we weren't sure yesterday, if MySQL still offers support in Japanese. So Japanese is an exclusion, also at MariaDB they also offer it in Japanese, and yeah, in PostgreSQL because the developers offer support, it can happen that you have support in different languages, so when you have a book support from KrederTief, you will get to in German for example, also there are French companies or Italian companies offering it, but there is no whole company offering it. The service languages, so language of consultants and trainers, because MySQL, I don't know, it's unknown because it's outsourced companies. MariaDB has supporters, has consultants and trainers speaking German too, so they are German and of course in Germany they speak German to the clients. And the same with PostgreSQL, so when you book me of course, I speak German if you want it, I speak English if you want it, and you might can, he can talk about speaking lower German, but that's a question, but yeah, they are also French and Italian and what you ever want, so the language isn't plain English. Commercial services, you have other commercial services on all three with varying knowledge, so not offered from developers or from format developers, from other people, so the knowledge always is varying. Community support, you can get very good community support for all three from on ISE, on FreeNOT ISE, it's channel MySQL, or channel PostgreSQL, and there are forums and mailing lists and you get, on ISE you usually get very fast answers and there are channels also in different languages, it depends on the developers if they are online in your language or not. So releases or release cycles. PostgreSQL is releasing yearly, that can be annoying because it's very often when you are, I'm also have a client where I'm a DBA and it's annoying, oh hell it's again a new PostgreSQL major version, you need to upgrade and yearly there's still 9.6 is actual, the actual version, but usually I see it at hosters, I often see 9.2 or 9.3, mostly 9.2, still 9.2 in production, so you see this yearly can be very fast or too fast, too often, it depends, others say we want yearly, MySQL and MariaDB major releases every few years, not yearly every four years, I know MySQL in past has had yearly too, but at the moment I looked it up, it's every two or three years a new version. Minor releases, that's I counted, I just looked into the versions and counted the months of years, so from the release date, how much upgrades they had, how much minor releases they had and just made a calculation, so I figured out MySQL minor releases around about every second month, every eight weeks, MariaDB around about every six weeks and PostgreSQL around about every ten weeks, so they all just get out of minor release when needed, not really always after a month, in past MySQL has had, we always have a minor release in monthly minor releases, but not anymore also just when necessary. End of life, community end of life support is five years in all, long time support, so end of life means bug fixes for older versions, long time support by paying money, you will get from MySQL nine and more years, so there is between five years and nine years and then you can nine plus years, so it depends what you pay, the same you will get long time support on MariaDB, on their website it's just written that you need to call and ask for it, so it's possible, MariaDB isn't so old, so they not pointed out how long, at the moment it's possible for all versions, they have, so most versions not end of life at the moment, and PostgreSQL there are companies offering fixes for older versions, I know minimum up to ten years you might find, we'll find companies for more longer, but it depends, so you need to ask the single companies, but lots of have this in there, offer it. Documentation, you will find online documentation for the reported releases from all, for older releases you will find them on Oracle websites, but it's very hard to find, I need it to, I googled 30 minutes until I found the 5.1 German documentation, so it's really hard to find, as I said MariaDB not yet has an end of life release, but it's expected from who we know the developers and we know the owner, so it's expected that they will do it like Maya SQL before they get Oracle, they just let all documentation online and you have an archive. PostgreSQL you can get all documentation, it's online. Documentation on German, Maya SQL is just trying it, it's under process, MariaDB, sorry, Maya SQL just has one for 105, so 105 was released in 2008 and a full German documentation for PostgreSQL, the last one was in 7.3, it was in 2002. We tried it later on to translate the Maya SQL manual, the PostgreSQL manual, and we gave up after 3,000 pages or so because it's so much. Books from developers, so books from developers, I didn't see any books from developers after Maya SQL got Oracle from Maya SQL, there are MariaDB books from MariaDB developers and there are also PostgreSQL books from PostgreSQL developers, so that's the difference. Distributions and packages, this slide is very easy, I just listed the most important distributions. Today, MariaDB when you install Maya SQL you will get MariaDB on Debian, on CentOS, on WattEd, on Susie, on Ubuntu, and when you want the Oracle Maya SQL you need to download the package from Oracle, and Oracle don't have a package for, not a specific package for CentOS, it's a Linux package but not a specific package for CentOS, and you have a PostgreSQL you get with all versions, so it's all distributions. Windows, you can have packages for Windows here, MacOS just MariaDB not really has a package for MacOS, there's Homebrew, there's MacPorts, and when I need MariaDB on my Macbook, I install it in a Linux virtual machine because, yeah, so there is not really a simple to install package. Now we came to the technique. Just a moment. The packages which you get with the distributions, need not be the same which the vendor would build themselves. Yes. Especially the Indian packages for MariaDB. Yes, but when you look, it's just that you can install it just by installing it from the distribution, that was it. You also can download PostgreSQL packages from PostgreSQL, download PostgreSQL.org is it. So, technique. There's one huge difference between PostgreSQL, Informix DB2 and MariaDB Oracle, that's what the database. So you see it here, PostgreSQL has a database schema table, MySQL starts with database schema that's same as table. I learned that Oracle not has a schema, that in Oracle uses our schema, so that's very crazy, but you have one more hierarchy here. That's the main difference and that's always when I see a PostgreSQL guy chatting with a MySQL guy, they talking about databases, or I sometimes see that they totally talk, they have different definitions of database in their minds. So, now I have a long slide with lots of differences between, here I just placed MariaDB MySQL and PostgreSQL. MariaDB has MySQL desafe, that means when the server is down, it starts immediately, it will be restarted, can be annoying too, but also has a reason and is good. MariaDB or MySQL has different storage engines, we all know it. PostgreSQL, the main differences, DDL transactions was your always here, PostgreSQL has data definition language transactions, and also DCL also works, but MySQL just DML transactions. PostgreSQL has lots of procedure language, so you can write procedure language in PLPGSQL, in PAL, in PLP, in whatever else, in lots of languages. You can write procedures in both in C, C always works. You can write procedures and functions in MySQL and SQL, in SQL like it's given in the ISO SQL standard, it's not implemented in PostgreSQL. And when you ask me, when I write a function in PostgreSQL with PLPGSQL, it's like Murphy, I think shit, in SQL it would be three lines, and in PLPGSQL it's very complicated. When I write a function in MySQL or MariaDB, it's vice versa, I think shit in PLPGSQL, it would be three lines, in SQL it's more complicated. I know both and I think when you have a balance, it doesn't matter if you take SQL or PLPGSQL, it both has advantages and disadvantages. MySQL has auto increment, or only auto increment. PostgreSQL has sequences, MariaDB and MySQL will have sequences soon in not yet released versions, not yet GA versions. So I didn't, I just recognized what's in GA. PostgreSQL has the pghb.conf, the host-based assess, that's an assess list, which user is allowed to connect to which database coming from which host, and the pre-addresses. And MySQL, the host belongs to the role so that you have five or six roles or users for the same database or for the same schema, when you have five or six different or two different hosts from where they come. That's a huge difference. When I learned MySQL, when I started working for MySQL, I needed three days to understand it, that it not works like in PostgreSQL. So that's something you need to, it's difficult to understand. And I had colleagues at MySQL, they had advice rather because of the users. And when I have, in my trainings, when they come from Oracle, there's a difference. Users and roles, that's the difference. In PostgreSQL, each object has an owner, it has an owner, in MySQL, they don't have an owner. But when you start, no, when you install MySQL, you don't need a configuration file first. So when you want to tune or whatever it is later on, you need, of course, to write a configuration, but you don't need one. In PostgreSQL, there will be the PostgreSQL configuration, and all variables that you can, that you are able to configure, are already in this configuration file. So you don't need to look up in the documentation what the hell was the name from this variable. That's in MySQL, you started it from scratch, and you always need to know how the variable is named, and you need to look it up. Yeah, but I forgot, at the users, you have role inheritance at PostgreSQL, you don't have it at MySQL. You don't have inheritance at MySQL, and you don't really have it like PostgreSQL. You can have a master-master, you can set a master-master application on MySQL, it's not possible on PostgreSQL. You have different group by and index behavior in both systems. You have vacuum, PostgreSQL has a vacuum, and we do, while MySQL is doing the undo, we do undo like Oracle, and PostgreSQL has a cost-based planner and optimizer that are the main differences you might consider from the both features. Just what I see used most often outside. GIN and GIST indexes, index container, just belong to PostgreSQL. Comment table expressions, MariaDB has comment table expressions, MySQL don't has comment table expressions. Check constraints, yeah, it's always asked, check constraints, my DRDB now has check constraints, MySQL not yet, window functions. I was surprised when I looked it up, that MySQL still has no window functions. I thought they are there, but similar. It's not of MariaDB hazard already, but MySQL still under development, will come with the next release. As far as I know, MySQL 8 has... As I said, but it's not released yet, not GA. There's another, it's Pool of Swats, it's new in MariaDB, PostgreSQL not working with Swats anyway, process best, totally different. And Pool of Swats, that's why it's in here. MySQL has it, but only for commercial, as a so in the enterprise release, not in the community release. Back at Restore, you can use dump on all. I think you all know one of the systems, so the dump is existing, it's working quite similar on both. You sometimes need to look up an option, but dump is working. You have great hot backup, and here I said, okay, MySQL and MariaDB, there's Xer backup from Pacona, which is quite similar to the hot backup from PostgreSQL. There was the InnoHot backup, but that's commercial on MySQL. So you need to pay for it. And Costfree is just extra backup from Pacona, but it exists. Restore data to different platforms. That's the feature. MySQL and PostgreSQL is no problem to take your data, the firm windows system, and install it, restore it on a Linux system. That's not working on PostgreSQL. You have Restore single tables. In PostgreSQL, it just works with a dump. In MySQL and MariaDB, it also works with a physical backup. I'm not really sure if there are tools outside. I heard there's underdevelopment on PostgreSQL from other companies. Restoring tables from physical dump too, but it's still under development. So transaction lock archiving. And here you have an advantage. In PostgreSQL, you just need to configure it. In MySQL and MariaDB, you need to manually think about how to archiving my transaction locks. That means you need to write a share script. So it's not so difficult, but anyway you need to think of something. In PostgreSQL, you just need to configure it. Yeah, you're excited already. Cluster. Cluster. Community-based. I just considered community-based here. MySQL is MySQL cluster, but you only get the package. As soon as you want to get support or services or something else, you need to buy MySQL cluster enterprise. Galera from MariaDB. What I found is PostgreSQL XC developed a cluster, similar cluster system for PostgreSQL, but it looks like it's very under development. So cluster still is a problem. But I also have to say, cluster, in my opinion, NoSQL means not only SQL, and then you look to the features on the lower levels, it's not SQL anymore, it's no SQL. So it's a question if it belongs to the relational database systems or not, or not really on the SQL. So they all speak SQL, but it's not only SQL. You are visiting the Galera with SQL combination, because you also get those packages from Galera. Ah, yeah. So Galera is working for MariaDB, and MySQL, I just wanted here to show, there is MySQL cluster, that was my... But you are right, Galera is working with both. Yeah, replication. We have physical replication. MySQL calls it row-based, same MariaDB, and PostgreSQL row-based is default. So it's the binary log transfer. Then you have the logical replication. MySQL calls it statement-based. You can configure it in PostgreSQL. Ah, then the Synchronos replication, my husband said yesterday, what do you want to say with it? And he read the slides. And yeah, MySQL just has semi-Synchronos replication. In PostgreSQL, you can configure from Asynchronos to Total Synchronos replication. That means when you have a replication of two servers, two servers is, okay, usually you have more, ah, they always will wait until the data are stored on the disk on both servers. So that means when the network failed, it waits forever until the network is back. So it's not enough that the second server have the information in the RACMAM, in the RAM. It really needs to be stored on the server, on the machine, on the disk. So that's Synchronos, that's full Synchronos, and you can configure it in PostgreSQL. I think there are nine different levels of Synchronos replication in PostgreSQL. Set up with master downtime, that's also a feature that you always want, and all three, you can do it on all three by using hot backups. So the most was when you set up a client, a server, a replication, or a new slave, that is that you need the data directory, and you can do it from hot backup, so that your master won't, that you don't have downtime on your master. Another replication, replication from or to different platforms. There are really people outside, and you want to replicate from Linux to Windows, or vice versa. On MySQL and MariaDB, you can do it physical and logical, on PostgreSQL you just can do it logical. Yeah, replicate to higher versions, to lower versions with luck, but usually won't work, so because of new features, it can work, but not really, be careful. So replicate to higher versions. The typical scenario you want to upgrade, so you upgrade, first upgrade your slave, it also has the higher major release as your master, and you need to replicate. So, and that's working on MySQL and MariaDB, both row-based and statement-based, no problem. On PostgreSQL, it only works in the logical setup. Upgrade without all server down. Yeah, that was the typical, what PostgreSQL always got. You can't upgrade a replication setting without all server down. And that's why I have it in here. Today it's possible also in PostgreSQL to upgrade, make this upgrade scenario, I just told, so that you always have one or two servers up and can upgrade the other by using logical replication in MySQL. It works with physical and logical replication. Yeah, and the master bypassing master, so you have two masters, a setup as master-master replication, having a co-sync and then just switch the IP from one to the other. That's not possible in PostgreSQL because you can't set up by the core features, a master-master replication. There are, outside, there are, you can get master-master replication also for PostgreSQL, but not shipped from PostgreSQL. So, that's all. So the summary is no system is perfect. It's up to you to choose the system that fits best to you and your application because when you balance the advantages and disadvantages, you are almost on the same level. It depends what you want to need. If you really want to have master-master replication, you might look, if you will, find a tool for others. If you want, what was it? Yeah, Galera cluster. Do we have it? Gin and Gist are, the OpenStreetMap guys also make lots of stuff with MariaDB, for example. So without this index stuff. And OO, we still have, what's that? We still have, yeah, questions. In the features of Postgres, there's JSON and JSON support, which allows you to just camera in random JSON, have it in next, and basically, we change more and again all this stuff, something safe. What's really awesome is notified this one. We can just have an event listener and it's something else. So, we have an external process and with that, you can set up three configure setups. The events you have in MySQL, MariaDB 2, and I should, I just, he also said just for the three, said, he said that Postgres has a little bit more features for JSON and JSON. So, that's right, but MariaDB also is very fast and very quick and very good on this JSON stuff already. So, MariaDB is very fast in getting all those features missed. I don't know about MySQL yet. Do you know it, JSON and MySQL? Yeah. So, MySQL also, Oracle also has JSON and so that's what, yeah. Ah, sorry, I didn't saw you. Thank you. So, yeah, 5.7, since MySQL 5.7 already has JSON and JSON. So, other questions? Yeah? How are the full text search capabilities? How, what? The full text search. Full text search, okay. That was the feature. So, you have a great full text search on MariaDB and MySQL. That's the reason why you have Google and Facebook and whoever else is using MySQL, MariaDB. On Postgres SQL, it has full text search too, but they also, there are lots of other tools for Postgres SQL using full text search together with Postgres SQL. So, one of it is, I had the name today morning, somebody asked me. Something with E. The name. Yeah, T search, you have T search of course, but there is this extension, something with E. Somebody mentioned it today morning, I forgot. Elastic search. Yes. That's a totally different set because then you have to use the events to synchronize the data. Yes, exactly. So, and that's why I said, I won't talk about full text search because it also works excellent on Postgres SQL, but it's a little bit fully different. Yeah. Do you have any questions? Did I forget anything? MySQL. No. No. Yeah. Hot backups. Oh, hot backups and my eSum. Okay, when I talked about my SQL, I thought about the transactional search engines and not the not storage engines. I need to see my as well, which is. Yeah. Yeah. Yes. So my eSum and hot backup in my, it's also in my read be so it's. Yeah. So I have that my as we get rid of my eSum all already in 2008. So seven. Yeah. Yeah. Okay. Discussion here about my eSum hot backup. Yeah. More questions. Any more questions? No. Okay. Yeah. I didn't have had more slides. We thought about more and. Yeah. I think I have, I still have time. Yeah. But we are done. So you have. No time. Yeah.
 So welcome to Froscon first talk in the database room.  Yeah, Maya Squirrel versus Maria, Debay versus PostgreSQL.  Yeah, my slides, the legal stuff, my slides belong to the talk and are not have no standalone  meaning.  That's very important especially on talks like this.  So, who am I?  My name is Susanne Holtzgräfer.  I'm grown up in Northern Germany and today I'm living in Bielefeld.  I worked, now I was, oh no, I'm still PostgreSQL developer since 2000.  And then I got a job at Maya Squirrel.  I worked as Maya Squirrel developer until they get Oracle and I left later on.  I worked as senior consultant for PostgreSQL after that made my PhD.  And today I'm freelance consultant trainer for Maya Squirrel, Maria, Debay and PostgreSQL  and also for IT security and data privacy.  Yeah, that's more for me.  I'm still member of ISO SQL standard, community.  Yes, so, yeah.  The difference or differences between Maya, Squirrel, Maria, Debay and PostgreSQL,  what do you think? What do you have in mind?  What are the major differences?  Any ideas?  No.  Yeah.  PostgreSQL is an extra no SQL database.  PostgreSQL is a no, not only SQL.  Is it an elastic search?  Yeah, but elastic search don't belong to PostgreSQL.  It's an extra tool.  I only will consider here.  That's very important.  What you can get for free and what the render is, the community only,  so only the core features, because there are lots of features for all outside and most of here.  The main difference is probably that the different format of the dates.  Yeah, the different format.  Yes.  You wait for, huh?  Yeah.  That's what you found.  So, there's a very important main difference.  It's the license.  And PostgreSQL is a BSD.  So, that's very important because you might have to look which license do I need or if the license  is compatible to my software, to the rest of my software.  There are GPL and BSD sometimes.  Don't like each other.  When you just need the client, you might want LGPL.  So, license always is the main topping when deciding which database system will I use for my software or will I use?  When you just use it for your own company, the license doesn't matter,  but when you want to ship software which needs it, you need to consider the license.  The second is more on the render.  MySQL belongs to Oracle.  MariaDB, there's a MariaDB company.  PostgreSQL is community only.  I mean here the development.  So, the most development for my SQL at the moment is done by Oracle from MariaDB and PostgreSQL.  It's community only.  So, they are volunteer developers on PostgreSQL.  That's the most important.  You have lots of developers at PostgreSQL who founded companies and offer services like support,  consulting, training, offer tools for PostgreSQL and so on, but the core development is community only.  When you consider which system I want to use, you may be asking for support and services.  You have commercial support.  You can get commercial support from my SQL from Oracle, from MariaDB, from MariaDB today,  and from PostgreSQL, there are different developers offering like KrederTief, it's in Germany,  like me of course, like EnterpriseDB, Second Quadrant,  that are the three biggest ones, and of course people like me, freelance, former developers,  who offer commercial, no, I don't offer support, I just offer services, who offer support.  Commercial services like training and consulting at my SQL, okay, Oracle made an outsourcing  from my SQL consulting and training, so today you have other companies for commercial services.  MariaDB is the same, MariaDB offering support and training by all developers, by own people,  and by PostgreSQL, that's what I already said, there are developers offering training and consulting  like me and a few others.  The language spoken in support in MySQL, MariaDB, it's English, they both, I'm not sure for MySQL,  we weren't sure yesterday, if MySQL still offers support in Japanese.  So Japanese is an exclusion, also at MariaDB they also offer it in Japanese, and yeah,  in PostgreSQL because the developers offer support, it can happen that you have support  in different languages, so when you have a book support from KrederTief, you will get  to in German for example, also there are French companies or Italian companies offering it,  but there is no whole company offering it.  The service languages, so language of consultants and trainers, because MySQL, I don't know,  it's unknown because it's outsourced companies.  MariaDB has supporters, has consultants and trainers speaking German too, so they are German  and of course in Germany they speak German to the clients.  And the same with PostgreSQL, so when you book me of course, I speak German if you want it,  I speak English if you want it, and you might can, he can talk about speaking lower German,  but that's a question, but yeah, they are also French and Italian and what you ever want,  so the language isn't plain English.  Commercial services, you have other commercial services on all three with varying knowledge,  so not offered from developers or from format developers, from other people,  so the knowledge always is varying.  Community support, you can get very good community support for all three from on ISE,  on FreeNOT ISE, it's channel MySQL, or channel PostgreSQL,  and there are forums and mailing lists and you get, on ISE you usually get very fast answers  and there are channels also in different languages, it depends on the developers  if they are online in your language or not.  So releases or release cycles.  PostgreSQL is releasing yearly, that can be annoying because it's very often when you are,  I'm also have a client where I'm a DBA and it's annoying, oh hell it's again a new PostgreSQL  major version, you need to upgrade and yearly there's still 9.6 is actual, the actual version,  but usually I see it at hosters, I often see 9.2 or 9.3, mostly 9.2, still 9.2 in production,  so you see this yearly can be very fast or too fast, too often, it depends, others say  we want yearly, MySQL and MariaDB major releases every few years,  not yearly every four years, I know MySQL in past has had yearly too,  but at the moment I looked it up, it's every two or three years a new version.  Minor releases, that's I counted, I just looked into the versions  and counted the months of years, so from the release date, how much upgrades they had,  how much minor releases they had and just made a calculation,  so I figured out MySQL minor releases around about every second month, every eight weeks,  MariaDB around about every six weeks and PostgreSQL around about every ten weeks,  so they all just get out of minor release when needed, not really always after a month,  in past MySQL has had, we always have a minor release in monthly minor releases,  but not anymore also just when necessary.  End of life, community end of life support is five years in all,  long time support, so end of life means bug fixes for older versions,  long time support by paying money, you will get from MySQL nine and more years,  so there is between five years and nine years and then you can nine plus years,  so it depends what you pay, the same you will get long time support on MariaDB,  on their website it's just written that you need to call and ask for it,  so it's possible, MariaDB isn't so old, so they not pointed out how long,  at the moment it's possible for all versions, they have,  so most versions not end of life at the moment,  and PostgreSQL there are companies offering fixes for older versions,  I know minimum up to ten years you might find,  we'll find companies for more longer, but it depends, so you need to ask the  single companies, but lots of have this in there, offer it.  Documentation, you will find online documentation for the reported releases from all,  for older releases you will find them on Oracle websites,  but it's very hard to find, I need it to, I googled 30 minutes until I found  the 5.1 German documentation, so it's really hard to find,  as I said MariaDB not yet has an end of life release,  but it's expected from who we know the developers and we know the owner,  so it's expected that they will do it like Maya SQL before they get  Oracle, they just let all documentation online and you have an archive.  PostgreSQL you can get all documentation, it's online.  Documentation on German, Maya SQL is just trying it,  it's under process, MariaDB, sorry, Maya SQL just has one for 105,  so 105 was released in 2008 and a full German documentation for PostgreSQL,  the last one was in 7.3, it was in 2002.  We tried it later on to translate the Maya SQL manual, the PostgreSQL manual,  and we gave up after 3,000 pages or so because it's so much.  Books from developers, so books from developers,  I didn't see any books from developers after Maya SQL got Oracle from Maya  SQL, there are MariaDB books from MariaDB developers and there are  also PostgreSQL books from PostgreSQL developers, so that's the difference.  Distributions and packages, this slide is very easy,  I just listed the most important distributions.  Today, MariaDB when you install Maya SQL you will get MariaDB on Debian,  on CentOS, on WattEd, on Susie, on Ubuntu,  and when you want the Oracle Maya SQL you need to download the package from Oracle,  and Oracle don't have a package for, not a specific package for CentOS,  it's a Linux package but not a specific package for CentOS,  and you have a PostgreSQL you get with all versions, so it's all distributions.  Windows, you can have packages for Windows here,  MacOS just MariaDB not really has a package for MacOS,  there's Homebrew, there's MacPorts, and when I need MariaDB on my Macbook,  I install it in a Linux virtual machine because, yeah,  so there is not really a simple to install package.  Now we came to the technique.  Just a moment.  The packages which you get with the distributions,  need not be the same which the vendor would build themselves.  Yes.  Especially the Indian packages for MariaDB.  Yes, but when you look, it's just that you can install it just by installing it from the distribution,  that was it.  You also can download PostgreSQL packages from PostgreSQL,  download PostgreSQL.org is it.  So, technique.  There's one huge difference between PostgreSQL,  Informix DB2 and MariaDB Oracle,  that's what the database.  So you see it here, PostgreSQL has a database schema table,  MySQL starts with database schema that's same as table.  I learned that Oracle not has a schema,  that in Oracle uses our schema, so that's very crazy,  but you have one more hierarchy here.  That's the main difference and that's always when I see a PostgreSQL guy  chatting with a MySQL guy, they talking about databases,  or I sometimes see that they totally talk,  they have different definitions of database in their minds.  So, now I have a long slide with lots of differences between,  here I just placed MariaDB MySQL and PostgreSQL.  MariaDB has MySQL desafe,  that means when the server is down, it starts immediately,  it will be restarted, can be annoying too,  but also has a reason and is good.  MariaDB or MySQL has different storage engines,  we all know it.  PostgreSQL, the main differences,  DDL transactions was your always here,  PostgreSQL has data definition language transactions,  and also DCL also works, but MySQL just DML transactions.  PostgreSQL has lots of procedure language,  so you can write procedure language in PLPGSQL,  in PAL, in PLP, in whatever else,  in lots of languages.  You can write procedures in both in C,  C always works.  You can write procedures and functions in MySQL and SQL,  in SQL like it's given in the ISO SQL standard,  it's not implemented in PostgreSQL.  And when you ask me,  when I write a function in PostgreSQL with PLPGSQL,  it's like Murphy, I think shit, in SQL it would be three lines,  and in PLPGSQL it's very complicated.  When I write a function in MySQL or MariaDB,  it's vice versa, I think shit in PLPGSQL,  it would be three lines, in SQL it's more complicated.  I know both and I think when you have a balance,  it doesn't matter if you take SQL or PLPGSQL,  it both has advantages and disadvantages.  MySQL has auto increment, or only auto increment.  PostgreSQL has sequences, MariaDB and MySQL  will have sequences soon in not yet released versions,  not yet GA versions.  So I didn't, I just recognized what's in GA.  PostgreSQL has the pghb.conf, the host-based assess,  that's an assess list, which user is allowed to connect  to which database coming from which host,  and the pre-addresses.  And MySQL, the host belongs to the role  so that you have five or six roles or users  for the same database or for the same schema,  when you have five or six different or two different hosts  from where they come.  That's a huge difference.  When I learned MySQL, when I started working for MySQL,  I needed three days to understand it,  that it not works like in PostgreSQL.  So that's something you need to, it's difficult to understand.  And I had colleagues at MySQL, they had advice rather  because of the users.  And when I have, in my trainings,  when they come from Oracle, there's a difference.  Users and roles, that's the difference.  In PostgreSQL, each object has an owner,  it has an owner, in MySQL, they don't have an owner.  But when you start, no, when you install MySQL,  you don't need a configuration file first.  So when you want to tune or whatever it is later on,  you need, of course, to write a configuration,  but you don't need one.  In PostgreSQL, there will be the PostgreSQL configuration,  and all variables that you can, that you are able to configure,  are already in this configuration file.  So you don't need to look up in the documentation  what the hell was the name from this variable.  That's in MySQL, you started it from scratch,  and you always need to know how the variable is named,  and you need to look it up.  Yeah, but I forgot, at the users, you have role inheritance  at PostgreSQL, you don't have it at MySQL.  You don't have inheritance at MySQL,  and you don't really have it like PostgreSQL.  You can have a master-master, you can set a master-master  application on MySQL, it's not possible on PostgreSQL.  You have different group by and index behavior in both systems.  You have vacuum, PostgreSQL has a vacuum,  and we do, while MySQL is doing the undo,  we do undo like Oracle,  and PostgreSQL has a cost-based planner and optimizer  that are the main differences you might consider  from the both features.  Just what I see used most often outside.  GIN and GIST indexes, index container,  just belong to PostgreSQL.  Comment table expressions,  MariaDB has comment table expressions,  MySQL don't has comment table expressions.  Check constraints, yeah, it's always asked,  check constraints, my DRDB now has check constraints,  MySQL not yet, window functions.  I was surprised when I looked it up,  that MySQL still has no window functions.  I thought they are there, but similar.  It's not of MariaDB hazard already,  but MySQL still under development,  will come with the next release.  As far as I know, MySQL 8 has...  As I said, but it's not released yet, not GA.  There's another, it's Pool of Swats,  it's new in MariaDB,  PostgreSQL not working with Swats anyway,  process best, totally different.  And Pool of Swats, that's why it's in here.  MySQL has it, but only for commercial,  as a so in the enterprise release,  not in the community release.  Back at Restore, you can use dump on all.  I think you all know one of the systems,  so the dump is existing,  it's working quite similar on both.  You sometimes need to look up an option,  but dump is working.  You have great hot backup,  and here I said, okay,  MySQL and MariaDB,  there's Xer backup from Pacona,  which is quite similar to the hot backup  from PostgreSQL.  There was the InnoHot backup,  but that's commercial on MySQL.  So you need to pay for it.  And Costfree is just extra backup from Pacona,  but it exists.  Restore data to different platforms.  That's the feature.  MySQL and PostgreSQL is no problem  to take your data, the firm windows system,  and install it,  restore it on a Linux system.  That's not working on PostgreSQL.  You have Restore single tables.  In PostgreSQL, it just works with a dump.  In MySQL and MariaDB,  it also works with a physical backup.  I'm not really sure if there are tools outside.  I heard there's underdevelopment on PostgreSQL  from other companies.  Restoring tables from physical dump too,  but it's still under development.  So transaction lock archiving.  And here you have an advantage.  In PostgreSQL, you just need to configure it.  In MySQL and MariaDB,  you need to manually think about how to archiving  my transaction locks.  That means you need to write a share script.  So it's not so difficult,  but anyway you need to think of something.  In PostgreSQL, you just need to configure it.  Yeah, you're excited already.  Cluster.  Cluster.  Community-based.  I just considered community-based here.  MySQL is MySQL cluster,  but you only get the package.  As soon as you want to get support or services  or something else,  you need to buy MySQL cluster enterprise.  Galera from MariaDB.  What I found is PostgreSQL XC developed a cluster,  similar cluster system for PostgreSQL,  but it looks like it's very under development.  So cluster still is a problem.  But I also have to say, cluster, in my opinion,  NoSQL means not only SQL,  and then you look to the features on the lower levels,  it's not SQL anymore, it's no SQL.  So it's a question if it belongs to the relational database systems  or not, or not really on the SQL.  So they all speak SQL, but it's not only SQL.  You are visiting the Galera with SQL combination,  because you also get those packages from Galera.  Ah, yeah.  So Galera is working for MariaDB,  and MySQL, I just wanted here to show,  there is MySQL cluster, that was my...  But you are right, Galera is working with both.  Yeah, replication.  We have physical replication.  MySQL calls it row-based, same MariaDB,  and PostgreSQL row-based is default.  So it's the binary log transfer.  Then you have the logical replication.  MySQL calls it statement-based.  You can configure it in PostgreSQL.  Ah, then the Synchronos replication,  my husband said yesterday, what do you want to say with it?  And he read the slides.  And yeah, MySQL just has semi-Synchronos replication.  In PostgreSQL, you can configure from Asynchronos  to Total Synchronos replication.  That means when you have a replication of two servers,  two servers is, okay, usually you have more,  ah, they always will wait  until the data are stored on the disk on both servers.  So that means when the network failed,  it waits forever until the network is back.  So it's not enough that the second server  have the information in the RACMAM, in the RAM.  It really needs to be stored on the server,  on the machine, on the disk.  So that's Synchronos, that's full Synchronos,  and you can configure it in PostgreSQL.  I think there are nine different levels of Synchronos replication  in PostgreSQL.  Set up with master downtime, that's also a feature  that you always want, and all three,  you can do it on all three by using hot backups.  So the most was when you set up a client,  a server, a replication, or a new slave,  that is that you need the data directory,  and you can do it from hot backup,  so that your master won't,  that you don't have downtime on your master.  Another replication, replication from or to different platforms.  There are really people outside,  and you want to replicate from Linux to Windows, or vice versa.  On MySQL and MariaDB, you can do it physical and logical,  on PostgreSQL you just can do it logical.  Yeah, replicate to higher versions,  to lower versions with luck,  but usually won't work, so because of new features,  it can work, but not really, be careful.  So replicate to higher versions.  The typical scenario you want to upgrade,  so you upgrade, first upgrade your slave,  it also has the higher major release as your master,  and you need to replicate.  So, and that's working on MySQL and MariaDB,  both row-based and statement-based, no problem.  On PostgreSQL, it only works in the logical setup.  Upgrade without all server down.  Yeah, that was the typical, what PostgreSQL always got.  You can't upgrade a replication setting without all server down.  And that's why I have it in here.  Today it's possible also in PostgreSQL to upgrade,  make this upgrade scenario, I just told,  so that you always have one or two servers up and can upgrade  the other by using logical replication in MySQL.  It works with physical and logical replication.  Yeah, and the master bypassing master,  so you have two masters, a setup as master-master replication,  having a co-sync and then just switch the IP from one to the other.  That's not possible in PostgreSQL because you can't set up  by the core features, a master-master replication.  There are, outside, there are, you can get master-master replication  also for PostgreSQL, but not shipped from PostgreSQL.  So, that's all.  So the summary is no system is perfect.  It's up to you to choose the system that fits best to you  and your application because when you balance the advantages  and disadvantages, you are almost on the same level.  It depends what you want to need.  If you really want to have master-master replication,  you might look, if you will, find a tool for others.  If you want, what was it?  Yeah, Galera cluster.  Do we have it?  Gin and Gist are, the OpenStreetMap guys also make lots of stuff  with MariaDB, for example.  So without this index stuff.  And OO, we still have, what's that?  We still have, yeah, questions.  In the features of Postgres, there's JSON and JSON support, which allows you to just camera in random JSON, have it in next, and basically, we change more and again all this stuff, something safe.  What's really awesome is notified this one.  We can just have an event listener and it's something else.  So, we have an external process and with that, you can set up three configure setups.  The events you have in MySQL, MariaDB 2, and I should, I just, he also said just for the three, said, he said that Postgres has a little bit more features for JSON and JSON.  So, that's right, but MariaDB also is very fast and very quick and very good on this JSON stuff already.  So, MariaDB is very fast in getting all those features missed.  I don't know about MySQL yet.  Do you know it, JSON and MySQL?  Yeah.  So, MySQL also, Oracle also has JSON and so that's what, yeah.  Ah, sorry, I didn't saw you.  Thank you. So, yeah, 5.7, since MySQL 5.7 already has JSON and JSON.  So, other questions?  Yeah?  How are the full text search capabilities?  How, what?  The full text search.  Full text search, okay.  That was the feature.  So, you have a great full text search on MariaDB and MySQL.  That's the reason why you have Google and Facebook and whoever else is using MySQL, MariaDB.  On Postgres SQL, it has full text search too, but they also, there are lots of other tools for Postgres SQL using full text search together with Postgres SQL.  So, one of it is, I had the name today morning, somebody asked me.  Something with E.  The name.  Yeah, T search, you have T search of course, but there is this extension, something with E.  Somebody mentioned it today morning, I forgot.  Elastic search.  Yes.  That's a totally different set because then you have to use the events to synchronize the data.  Yes, exactly.  So, and that's why I said, I won't talk about full text search because it also works excellent on Postgres SQL, but it's a little bit fully different.  Yeah.  Do you have any questions? Did I forget anything?  MySQL.  No.  No.  Yeah.  Hot backups.  Oh, hot backups and my eSum.  Okay, when I talked about my SQL, I thought about the transactional search engines and not the not storage engines.  I need to see my as well, which is.  Yeah.  Yeah.  Yes.  So my eSum and hot backup in my, it's also in my read be so it's.  Yeah.  So I have that my as we get rid of my eSum all already in 2008.  So seven.  Yeah.  Yeah.  Okay.  Discussion here about my eSum hot backup.  Yeah.  More questions.  Any more questions?  No.  Okay.  Yeah.  I didn't have had more slides.  We thought about more and.  Yeah.  I think I have, I still have time.  Yeah.  But we are done.  So you have.  No time.  Yeah.
froscon2017-2032-mysql_mariadb_versus_postgresql_0_59.png froscon2017-2032-mysql_mariadb_versus_postgresql_60_167.png froscon2017-2032-mysql_mariadb_versus_postgresql_168_1223.png froscon2017-2032-mysql_mariadb_versus_postgresql_1224_4955.png froscon2017-2032-mysql_mariadb_versus_postgresql_4956_14183.png froscon2017-2032-mysql_mariadb_versus_postgresql_14184_16283.png froscon2017-2032-mysql_mariadb_versus_postgresql_16284_18551.png froscon2017-2032-mysql_mariadb_versus_postgresql_18552_21875.png froscon2017-2032-mysql_mariadb_versus_postgresql_21876_27335.png froscon2017-2032-mysql_mariadb_versus_postgresql_27336_28367.png froscon2017-2032-mysql_mariadb_versus_postgresql_28368_28691.png froscon2017-2032-mysql_mariadb_versus_postgresql_28692_29351.png froscon2017-2032-mysql_mariadb_versus_postgresql_29352_31343.png froscon2017-2032-mysql_mariadb_versus_postgresql_31344_41315.png froscon2017-2032-mysql_mariadb_versus_postgresql_41316_41507.png froscon2017-2032-mysql_mariadb_versus_postgresql_41508_42851.png froscon2017-2032-mysql_mariadb_versus_postgresql_42852_43703.png froscon2017-2032-mysql_mariadb_versus_postgresql_43704_43847.png froscon2017-2032-mysql_mariadb_versus_postgresql_43848_44255.png froscon2017-2032-mysql_mariadb_versus_postgresql_44256_44783.png froscon2017-2032-mysql_mariadb_versus_postgresql_44784_45539.png froscon2017-2032-mysql_mariadb_versus_postgresql_45540_45707.png froscon2017-2032-mysql_mariadb_versus_postgresql_45708_46379.png froscon2017-2032-mysql_mariadb_versus_postgresql_46380_47027.png froscon2017-2032-mysql_mariadb_versus_postgresql_47028_48527.png froscon2017-2032-mysql_mariadb_versus_postgresql_48528_48959.png froscon2017-2032-mysql_mariadb_versus_postgresql_48960_49907.png froscon2017-2032-mysql_mariadb_versus_postgresql_49908_49955.png froscon2017-2032-mysql_mariadb_versus_postgresql_49956_50423.png froscon2017-2032-mysql_mariadb_versus_postgresql_50424_50939.png froscon2017-2032-mysql_mariadb_versus_postgresql_50940_51287.png froscon2017-2032-mysql_mariadb_versus_postgresql_51288_51419.png froscon2017-2032-mysql_mariadb_versus_postgresql_51420_51659.png froscon2017-2032-mysql_mariadb_versus_postgresql_51660_52091.png froscon2017-2032-mysql_mariadb_versus_postgresql_52092_52427.png froscon2017-2032-mysql_mariadb_versus_postgresql_52428_54839.png froscon2017-2032-mysql_mariadb_versus_postgresql_54840_61552.png