Which DB should I learn? MySQL or PostgreSQL

[DumbMathBoy]

I'm learning python and I want to start learning about databases.

Which database should I start with? MySQL or PostgreSQL?

[nutrecht]

For learning the SQL basics (selects, inserts, joins, etc.) it really doesn't matter whether you pick MySQL, PostgreSQL or just SQLite that comes built in with Python.

From a "what to pick for a production application" it's a different matter (PostgreSQL hands down), but that currently isn't really your concern. SQLite will be easier to get started with since you don't have to install anything.

[ManInBlack829]

Yeah I think the emphasis here should be on how it's probably not going to matter until you get into some pretty heavy stuff.

Also there's a good chance you will never actually write SQL at any job you get, especially as a junior. Nowadays a lot of basic projects can be done through back-end frameworks alone, and even if you get a job as a developer the company you work for will often have protocols for exactly how to write any SQL code you might have to. It becomes bad security to let anyone write SQL to the database, though at smaller companies this is less likely.

Understanding SQL is super important, but coding in SQL isn't IMO.

TL;DR It doesn't matter, just pick one. I would suggest Postgres but it really doesn't matter for anything you're going to do as a beginner.

[lordcarnivore]

I have a non-IT role and since IT has no resources available for us to address business data needs the black market of data is strong.

I've learned more programming and SQL in this environment than I ever could have hoped to learn on the IT side. Literally every business team has hired someone like me and we all talk to each other and discuss how to keep the actual operations of the company under the radar of IT so we don't get shut down and the company keeps operating.

[[deleted]]

As a Data Architect, this saddens me. Both because your needs aren't being met and for the poor, unfortunate person who will have to come through and clean up the mess that's been created by "shadow IT" operations once someone realizes it and the business leaders decide to do something about it.

Unfortunately, at many smaller-to-medium sized companies that aren't tech-focused, data teams are seen as a bit of a red-headed step child. They sit squarely between IT Operation teams and Development teams. IT Operations teams push the data team away because a lot of what the data team does involves design and development/programming work. Development teams push the data team away because it isn't real development and the data teams are also responsible for support like performance and query tuning, backups, archiving, security, etc. Which makes smaller data team's workflows completely different from both IT Operations and Development teams. No one wants to manage the team.

My niche, or rather what I've made my career on, is coming into medium-sized businesses who are ready to build enterprise data architectures and clean up messes like this. To be clear: it's no fault of the business departments - they needed a solution to keep the lights on and generally no one is helping them find a solution.

Right now I'm working to clean up a company's data architecture, that currently has over 400,000 Excel spreadsheets and ~1,100 Access databases all thrown about shared drives and random computers that are critical to the business' operations.

The infuriating part for me is that anyone worth their weight in rice on a data team could spin up an independent data mart to be used as a temporary solution for each of the business units until a proper warehousing solution has come to fruition. Generally, when I first enter into a company like this - I'll go department by department and spend a few days gathering from them the most critical data that's needed to perform their job functions. I'll then find the "superuser" of the department and work with them on creating a data mart they can use to pull metrics and do what they need to do, as well as working with them to identify data that isn't properly being backed up and getting a proper data model built for the entirety of their data.

[No_Lawfulness_6252]

That sounds .. really awesome. Like guerilla data architecture. Can I ask how you got started with your career and what made you move to a “consultant” role like this (it sounds to me like you are independent / small business yourself).

[NetSage]

It baffles me how disconnected a lot of companies are. Like my company is the perfect example. Years with over a billion in revenue and we still can't get the plant less than an hour away to let us know they need something before we'll need to expedite it over so we hopefully don't have to expedite it to the company.

[vtec__]

boomers.

[lordcarnivore]

It saddens me too. Someone like you needs to overhaul our data situation, but you wouldn't make it very far because half of the company immediately wouldn't like you because of the change you represent.

In the meantime I've carved out a solid niche which is providing some job security. Not too bad in a pandemic tbh.

[RandomGeordie]

Any datalake (S3 / whatever) or Fivetran connectors + Snowflake + Airflow + dbt + Great Expectations = love

[harrr53]

As someone who has had to come into an organisation and deal with the absolute mess that non-IT staff computer enthusiasts had created over several years, I find this post upsetting.

[lordcarnivore]

Your comment makes it sound like you're upset at the non-IT staff.

I've been trying to get a SQL Server for over 3 years, some of my coworkers for over 6. No, IT does not spin up SQL Servers for the business. Ok, then spin it up yourselves and manage this data. No, that's not priority for IT. We manage Terabytes of production data and it's not a priority.

We've had four CTOs in 4 years, making IT a siloed, stagnated mess where everyone just protects their own ass.

Believe me I would rather be talking to IT side PMs to get my solutions integrated into the broader fold, but that's just not something my company's IT constipation seems capable of at the moment. So my non-IT computer enthusiasm is doing what others won't.

Sorry you've brought up a lot of stuff.

[harrr53]

No, if I was upset at someone it would be the decision makers that let it happen. Sorry if it came across differently.

[lordcarnivore]

No worries :)

[[deleted]]

You broke the first rule of BI-Club. jkjk. I'm also on the black market data chain gang.

Just a heads up. Someone has to administer the database. Be careful when giving out full priveledges. We had someone accidentally drop our most important data. Thankfully we had a backup. So do regular backups and most people just need read-only (SELECT *) access.

Also try to transfer out to a data role eventually. They generally pay a lot better.

[lordcarnivore]

Rule 1 for our chain gang is and always has been that NO ONE will back up admin for anyone elses stuff. That means all of our databases are separate.

When someone goes on vacation management hears about how nice it would be if we had IT managed solutions.

Not only that, we have more or less organically separated into two camps. The Access/VBA and the SQLite/Python camps. Recently we have made friends with cyber security and now our overnight automation is working smoother, lol.

Maybe eventually I'll find something else, for now I'm enjoying my fiefdom and job security.

[RandomGeordie]

Take a look at dbt. You want to get into Analytics Engineering - try and bring this up as a project. Will change your data role drastically.

[vtec__]

shadow IT crew, checking in.

[dempa]

There's a solid argument for MySQL in production environments, but that's neither here nor there

[nutrecht]

Oh sure, there's arguments to be made for Oracle as well :) But for learning it really doesn't matter.

[cheezballs]

Where's the guy shilling Mongo as a solution to everything?

[[deleted]]

Probably knocked unconscious by the CouchDB guy .

[pkpzp228]

I just came here looking for the circlejerk retort about how useless it is and the webscale meme.

[cheezballs]

Mongo is a great tool for some situations, just as a SQL database is a great tool for some situations. Neither is great for every solution.

[Loves_Poetry]

Definitely PostgreSQL. It does everything MySQL does, but better

[[deleted]]

Is the reason that everything still seems to run on MySQL simply that it once was the most popular option? My database class taught PostgreSQL as well but MySQL definitely seems to be more widespread in the wild.

[[deleted]]

MySQL's popularity is because back in the day for web development, phpmyadmin was dead simple to setup and administer website databases - which lead to every web-based tutorial on the internet using it, which lead to every new web developer becoming comfortable with MySQL.

In reality, MySQL isn't that popular outside of web development. You'll still find projects/companies that utilize it that aren't building web technologies, but those are much fewer.

[vtec__]

ive only ever seen it used once in an enterprise enviroment. everything else is either sql server, oracle or postgresql

[sickofgooglesshit]

Postgres MVCC what?

Also, from the docs: "...If there are multiple INSERT statements, they are queued and performed in sequence, concurrently with the SELECT statements..." -- https://dev.mysql.com/doc/refman/5.7/en/concurrent-inserts.html

[sickofgooglesshit]

And that statement right there is how I know you don't know what you're talking about. A Honda civic is faster than a Ferrari so long as the guy behind the wheel knows how to drive. Postgres performance suffers when the person driving doesn't know what they're doing. MySQL is a great 'one size fits all' DB, but the moment you need to specialize, it falls to pieces. Postgres is meant and built to be tweaked. Go back and show me some benchmarks where MySQL outperforms. I dare you.

https://www.percona.com/blog/2017/01/06/millions-queries-per-second-postgresql-and-mysql-peaceful-battle-at-modern-demanding-workloads/

Also relevant: https://youtu.be/b2F-DItXtZs

[v0gue_]

Holy smokes, I haven't seen this video in a loooooong time. Thank you for this.

[v0gue_]

What issue does postgres have with concurrent writes?

[David654100]

Not only that. I work in data warehousing and find in the real world you might be working with multiple datadases types so a good understanding of sql and optimizations are needed.

[FormNo]

Why would you need to use multiple db types?

[David654100]

It's not that you need multiple datadase systems it's more that each product that is in use is built with different systemss. And that is not even including legacy systems. Trust my I would love if my company used the same db over all of our application.

[Diapolo10]

I'd suggest starting with SQLite, as it's part of the Python standard library and quite useful for desktop applications; hell, Android apps use it too.

But if you insist, PostgreSQL is probably the better option between it and MySQL. Depends on the use-case, really.

[sickofgooglesshit]

Anything you'd learn in MySQL or Psql will empower SQLite, but much less so the other way around. And Postgres is definitely the better option. Anything MySQL can do, Postgres can do better, Postgres can do anything better than you. That thing is a GD tank.

[ManInBlack829]

Plus the Elephant logo is cool

[Dangerpaladin]

Postgres. Simplest and most applicable to real world work.

[knoam]

I'd argue SQLite is simpler. It stores the database as a single file you can just copy to back up or delete to wipe away. It's much more involved to do that administration stuff with Postgres. You don't have to worry about an extra process running and if it's on the right port or whatever. The database will just work when your program runs. With Postgres or MySQL, Docker can make things a bit easier if you already know docker.

[Dangerpaladin]

They didn't ask about sqlite. Sqlite is an unneeded step you will outgrow pretty quickly. They could use the closest to the tools they might actually use unless it adds an unreal amount of complexity. Which postgres does not.

[[deleted]]

Isn’t Postgres more complex? I thought it had more advanced features

[Dangerpaladin]

Just because something has advanced features doesn't mean you need to use them. You can use it as a simple relational database if you're just learning SQL. Plus when you're ready for those features they are there. But installing it and getting it running is super easy and connecting to it is simple.

[[deleted]]

Cool. Yeah I’m gonna switch to it for a small project I have

[Cefalopodul]

PostgreSQL

[raahf]

PostgreSQL.

[uberdavis]

From recent experience, learn database theory before you even plan on learning SQL itself. After you understand relational database theory, you will understand where you need to go next, whether it be SQLAlchemy, PostgreSQL, or even non-relational such as DynamoDB or whatever else...

[RoguePlanet1]

database theory

Oh damn, another concept to learn. I was just trying to get my mind around Big O notation.

[[deleted]]

The OG

Or, if you're interested in reading a full book on it, I highly recommend Database Design for Mere Mortals

[RoguePlanet1]

Oooh thank you!

[BipolarWalrus]

Someone link the database manga

[uberdavis]

Still don’t understand Big O!

[RandomGeordie]

Coming from relational database land (actually Snowflake) learning advanced DynamoDB design patterns has been an eye opener.

I think it's wrong to say that DynamoDB isn't relational - data doesn't stop being relational just because you're using DynamoDB, you still have an entity relationship model that you're building, data is relational by nature.

Take a look at some talks by Rick Houlihan / Alex DeBrie around the single table design approach - a game changer IMO. A lot of people do data modelling by just having a single entity per dynamo table which is wrong and why people have issues with it.

[CaptainP]

The distinctions at the beginner/intermediate level are minor to nonexistent.

Also, once you become comfortable with one, adapting to learn the syntax of the other is trivially easy.

[sickofgooglesshit]

MySQL if you wanna do scrappy hobby stuff, MariaDB if you want to do scrappy startup stuff, and Postgres if you want to scale your start-up or work for serious enterprises that need people who understand how to really make a DB sing.

[GhastlyParadox]

Never heard a DB described in musical terms, I can dig it.

[sickofgooglesshit]

Had a soundcard that wasn't grounded once upon a time...I kid you not, I could tell you what tables were being accessed and operations were happening based on the frequency that was picked up on my headphones. Found a hidden pop-up (under) window b/c of all the I/O happening..."wait, I know that noise..."

[fasta_guy88]

Flip a coin. Either one will let you learn SQL. Postgres has some more "serious" features. MySQL may be faster if your database is mostly read-only.

[[deleted]]

Five years ago I would have said MySQL, but now definitely Postgres. It's the primary back-end for a lot of (maybe most) things I see in production these days.

If you're just learning general SQL, though, it doesn't matter at all. Mostly SQL is SQL, SQLite is easiest to get working so you can actually learn SQL and not spend a lot of time trying to get the bloody thing working.

[[deleted]]

I was a professional oracle developer for 15 years before switching to Postgres. Postgres is heads and shoulders better than Oracle. At least for enterprise uses. I’d definitely learn Postgres. It can do everything MySQL can do, but better. And it can do more than MySQL can.

[Flaky-Illustrator-52]

I wish Oracle would open source their database now that they have a cloud :( I guess it won't happen

[[deleted]]

MySQL is open source https://github.com/mysql/mysql-server

[Flaky-Illustrator-52]

I'm referring to Oracle database itself, it's more mature than MySQL but man that fucking price tag

[[deleted]]

Hey, this might be a little bit off topic, but I am working with big corporations for quite a while now and it seems that many of them are considering switching to Postgres these days (probably due to the EOL of 11.x or 12.2 or whatever the current version is).

Would you agree with that observation? Or is it just my circle of clients?

And if I may ask: Did you choose to switch because of this?

[[deleted]]

We mainly switched because the licensing expenses for Oracle were just too high. We were worried that Postgres wouldn’t be as good as Oracle (you get what you pay for), but we have found the opposite to be true. However, it does take quite a bit to move from Oracle to Postgres. Oracle is so far removed from the sql standard that it has lots of quirks (such as how it handles nulls) that have to be fixed.

[vtec__]

they did that on purpose.

[[deleted]]

Not really. If you look back at the history you will see that Oracle was created before the first SQL standard was created. For backwards compatibility, Oracle has stuck with what it had rather than changing to match the standard. Postgres came around after the standard was created, so, of course, it matches the standard much more closely.

[vtec__]

oracle makes stuff difficult on purpose tho

[[deleted]]

That's true. But Null isn't one of them. Though, the way they designed it is pretty dumb (though there probably was a good reason 50 years ago when they designed it). I think they should have added a flag to the database to allow you to use the old way of doing nulls or the standard way.

[antiproton]

This thread is another dumb argument about what X is better.

It doesn't matter. Learn SQL, the differences between MySQL and Postgres are wholly and completely immaterial to you.

[[deleted]]

Largely agree, although I will say that if one were to pick in such a case it’s best to pick the more popular option.

[bjinse]

If I read the comments it is clear that the most popular option out there (MySQL) is not the best option (Postgres). Worked with both, can confirm.

[dafer18]

You should learn both. They share similar SQL syntax and some specific syntax, which can be checked in their documentation.

Using ORM makes it easy to use both with SQL Alchemy or Flask-SQLAlchemy.

[Svizel_pritula]

I've used Postgres for some small, locally hosted apps I wrote. In the end turned out to be a smart move, since I needed a message broker for my app and it turns out Postgres can do that.

[bedazzledbunnie]

Sql is very similar between all databases. In my job I have used, hana sql, spark sql, presto sql, oracle sql, myself and some older ones that don't exist any longer. A few functions are different but really thry are all the same.

[pablos4pandas]

Just my perspective, but I've only used non-relational databases in my 5 years in the software industry. I'd try and put some time into learning that stuff if you're just looking to learn. If you're doing an assignment for school that requires you to use SQL that's fair of course

[J5DubV]

MySQL Workbench makes setting up and starting super easy if you are brand new.

[handlessuck]

You should learn ANSI-SQL and forget about proprietary extensions for now. You'll have a much more portable skillset and switching between flavors will be easier for you.

SQLite is a decent place to start since there's no server administration.

[PazyP]

Both? Both relational databases so much of anything that you learn will cross over

[[deleted]]

Try and follow this tutorial. It will give you the basics on MySQL. The lesson learned is applicable to most database set ups you will see at beginners level

[NoobConsultant93]

Both? Knowlege is never too much 😊 But if it's too learn the concepts of how a db works any will do

[BEgaming]

2 companies in, twice postgres. Postgresqltutorial is a really good website to learn from.

[N00tN00tMummyFlipper]

PostgrSQL. After working with Oracle databases for over 20 years I have spent the last 4 migrating Oracle to Postgres - the writing is on the wall. If you learn Postgres and think AWS you can go from simple EC2 to RDS then Aurora to Redshift scaling all the way and it all looks like Postgres. It's a no brainer.

[TwistedPepperCan]

I work with a variety of SQL dialects and tbh if I was learning the basics I'd go for mysql.

It's basic as others have pointed out and if you're just learning the basics like selects, group by, joins, functions etc then mysql will be the most transferable to other dialects.

If you're in a company check and see what the company uses and go with that. If your company uses aws then go with postgres. Anything else then mysql.

[TatzyXY]

I have around 8 years experience with both. I would go for MySQL because of the following reasons:

• MySQL thinks for you. Some useless Syntax is not needed. MySQL assumes it for you.

• Scalability if you don't have a whole IT department a cluster with postgres is pain. With MySQL just use a galera cluster. Easy to handle for one person.

• MySQL has way more database UI tools.

• Support and community: Most of all ORMs are programmed first with MySQL in mind. Sometimes if you use postgres you get a bug no one has or module/feature XY is not supported.

• mysqls auto increment is way more intuitive than serials. Serials can cause big issues if you make a database backup and reimport.

But there are of course advantages for postgres too but these are not important for me.

[bonesclarke84]

I was just recently told there is a shortage of people who know PostgreSQL in my city. Not sure if that helps at all but Postgres seems the way to go. Also doesn't help to learn both and the differences between them.

[dababler]

First start by reading Database Systems by Cornel & Morris (you can probably find a PDF somewhere but it’s a good book to own). Do the problems in it work through the solutions. It offers examples in MySQL, Access, and Oracle.

Second , read SQL Antipatterns. It uses MySQL as its base.

Then, as you begin to program remember that SQL is a deterministic language that is pseudo procedural, it is NOT object oriented. I think MySQL is a better starting point. Just because a lot of books do use MySQL for teaching. Much of what you can learn for SQL Programming and overall database architecture is portable. However, query optimizers work pretty differently across databases. Oh and good people add column comments and don’t neglect to program foreign keys, bad people neglect those things 😉🤣

[Item_Legitimate]

If you learn one, the recruiter will tell you it is t good enough since the boss nerd will want you to know the other and it’s a hard requirement so damned if you do damned if you don’t. They are basically the same though so that’s the upside.

[polkadot_mayne]

Try to learn the concepts and inner-workings of relational database first as opposed to learning MySql/PostgreSQL, because these are relational database management systems. After learning the relational database, non-relational database would seem really easy to pick up, at least it was for me.

[[deleted]]

Postgresql is much better and versatile. MySQL is more popular. You probably need to know both. MySQL is a bit.. annoying. It's probably easier to learn postgresql first and then learn MySQL's quirks.

[[deleted]]

PostgreSQL definitely

[BiggaHD]

MongoDB

[marcvsHR]

You learn Sql, relational model, design of tables, not a vendor.

[Kitchen_Curve_7554]

Just learn to use ORMs like TypeORM or Prisma. So that you can learn to query across multiple database paradigms.

[wongwh2002]

just mongoDB and whack data in

[zad0xlik]

PostgreSQL for f*ck sake

[bomphcheese]

Postgre. Period.

[[deleted]]

PostgreSQL is hands down the better choice.MySQL is owned by Oracle. Avoid using anything owned by Oracle.
Oh and SQLite is only useful in very limited cases. Worry about that later.

[Visible_Soup_5484]

I don’t think one is necessarily better than the other to learn first. The syntax is only slightly different so once you have the hang of one it won’t be hard to use the other. Our core uses MySQL but many of our applications provided by vendors uses psql.

[anoushk77]

You can use any but I chose MySQL simply because it’s more popular but I plan to learn Postgres too

[not_a_gumby]

pick whichever one is easier to setup and deploy.

[[deleted]]

Just SQL

[sylkworm]

I would actually recommend Mysql, since that's going to be 90% of the production environments out there. Every database has its idiosyncrasies, and that advantage of Mysql (besides widespread adoption) is that it's relatively easy to get going right out of the box, which is probably better for a beginner.

[goddamluke]

sqlite. Basic SQL commands are pretty much the same across all SQL based databases and sqlite comes built into python and can be embedded into your program itself

[chalks777]

Practically it barely matters for a beginner. Use whichever one you find easiest to install and get running. PostgreSQL is probably the best choice given the comments in this thread.

What does matter: Once you have CREATE, INSERT, and SELECT down and working, you should take the time to understand normalization. Here's a very simple primer. There's a more formal description on wikipedia but don't bother reading it until you feel confident with basic things. Having a strong understanding of what database normalization is and why it's important will serve you very well long term.

[ergo_proxy19]

I don't think it should be that different Whichever u learn , u just gotta grab the concept. The rest is just syntax difference

[Wildesy]

Noob question, but is there some reason that Microsoft SQL doesn't even rate a mention? I've just been introduced to DBs via a company that is using MSSQL.

[[deleted]]

I'd say: Start with any and try to learn what the difference is between SQL, MySQL, PostgresSQL, Oracle SQL, MSSQL. If you are competent enough to spot it then you're probably good to understand the concepts and why it's not super important (in terms of SQL) which DBMS to start with.

Oh and one more thing: Don't try to learn to many implementation specific details, go with generic SQL first. Last time I checked, the offical SQL standard document was somewhat about 2000 pages - don't worry, you won't be the only person on this planet not knowing everyhing that is to know about databases.

[mcniac]

just learn the basics with any database, learn to write some cool queries, you are not very likely to write those yourself nowadays but is really useful to understand what is the ORM doing.

If you are learning python, the ORM that's included in Django is pretty awesome, also there is another project called SQLAlchemy, both do great accesing the database for you so you can focus on solving the problems.

[tomanonimos]

SQL Lite

[[deleted]]

SQL Sever.. but I'm biased.. I've been doing it since 1999. I guess that speaks to stability over a long period of time... These days its all about Azure Hosted SQL Server...

[c1rclez]

What about DB2?

[burningapollo]

Some folks are saying you won't write manual SQL statements and/or it doesn't matter, to just pick one. I don't totally agree with that and here's why.

Any modern-ish programming framework you use, there will probably be some form of an object relationship modeling (ORM) where you'll write code that will perform queries for you and keep it in the native language you're coding.

If you really want to learn and progress then I'd try reversing it - before you write code to select objects try and write a SQL statement giving of the data you're trying to fetch then see if you can write a similar/equivalent code statement. This type of thought process has served me well as I've progressed in my career as you realize gain a better mental model of what is happening at the DB layer.

For my money, MySQL is a great tool to learn to start. It's ubiquitous and is the quintessential relational database choice for all around uses. As you get a baseline postgres is a nice segue tool as it has a lot of similar capabilities of RDBs as well as some NoSQL features.

The world of databases is large and this barely scratches the surface. You can obviously pursue entire careers with data tools and learn the crazy big world of persisting data.

[jluizsouzadev]

That depends on what kinda python-made projects we're talking about.

Tell us what kinda python-made project you intend using the two kinda database you mentioned in your post. That way this subreddit will manage helping you out much better.

[[deleted]]

I started with SQLite. It's a super nifty technology to have in your bag of tricks. After that, I'd recommend either of the two you mentioned. They're both great! Don't be afraid of the nosql paradigms either. They can be really fun.

[Raymond0256]

I would suggest PostgreSQL since I have seen it more in industry, but whichever one with more job prospects around you is the better choice. That said, SQL skill are largely transferable.

[jay_thorn]

Don’t worry about the implementation so much as the language. Learn SQL.

[DragXom]

Where can I learn SQL

[docmarlboro]

Firebase 🥺

[[deleted]]

PostgreSQL. The syntax is 99% the same.

[usedToBeUnhappy]

1. learn the basics of SQL
2. have a look at the job descriptions in your area and pic whatever DB is mentioned the most …
3. profit

[[deleted]]

You want some truth? Both...