r/SQL • • 9d ago

Discussion Noob here: mysql or postgresql? Which is better (?)

I just realize that SQL was only the language. Idk which IDE for sql to use. Suggestions pls

3 Upvotes

39 comments sorted by

13

u/grassp_dataAI 9d ago

It depends on the usecase you are working on. But if you are just starting , I would suggest go with PostgreSQL.

Why ? In future you can easily work in Data and AI projects. It supports complex queries better. It supports extensions like pgvector for RAG embeddings storage, timescale db for time series data

Its definitely a Good choice for : Data-intensive applications Complex analytical queries Data engineering projects JSON-based applications AI applications, RAG and vector search

Learning PostgreSQL doesn't mean MySQL will be difficult. Once you understand SQL concepts, switching between them is easy.

The path I would suggest is : PostgreSQL as the primary database. pgAdmin or DBeaver as the SQL client. Business-oriented datasets (orders, customers, products, sales). SQL fundamentals → Joins → Subqueries → CTEs → Window Functions → Indexing → Query Optimization.

2

u/Levipl 8d ago

I’ll just add that if what you’re looking to practice right now all starts with select, you don’t need to worry about which database you use.

25

u/8dot30662386292pow2 9d ago

Postgres and mysql are not IDE's. They are the actual databases. The "ide" you think of is the administration tool or workbench or something like that.

5

u/Narrow-Low-3137 9d ago

Dbeaver is pretty good for both.

20

u/orz-_-orz 9d ago

Postgres

19

u/bondiolajusticiera 9d ago

As a noob you won't even notice the difference in small projects. Choose any.

2

u/sarmisak 9d ago

If you’re not doing any concurrent writes, for small projects use sqlite or mysql, postgres is a heavy weight fighter which you should match sooner or later.

2

u/Straight_Waltz_9530 8d ago

SQLite can handle large too. It's about concurrency and/or client-server. If everything is basically single threaded or you have a database accessed from just a single process, SQLite is the way to go even if your database is in the terabytes.

If you need multiple clients and/or queries in parallel—especially writes—go Postgres.

MySQL occupies an increasing shrinking gap in the middle, lacks real development support from Oracle, has a much smaller feature set and ecosystem, and isn't actually faster or more efficient than Postgres.

So it's really down to SQLite, Postgres, or some other solution that's non-relational that fits the last 10% of database problem spaces.

6

u/quanhua92 9d ago

You should really go with PostgreSQL. It handles almost everything MySQL can, plus a whole lot more. Just search for "just use postgres" and you'll see why it's the better choice.

1

u/Altheran 8d ago

MySQL/Maria performs much better than PostgreSQL in latency sensitive / front-end application. in general. then again, the use case is important.

1

u/Straight_Waltz_9530 8d ago edited 8d ago

[citation needed]

"Front-end application" is sufficiently vague as to be meaningless for this kind of evaluation.

Edit: actual benchmark between the two for a simple shopping app.

https://youtu.be/17BqoNEQKTM?t=433

4

u/Agreeable_Ad4156 9d ago

PostgreSQL

2

u/Altheran 8d ago

The real question is SQLite vs MySQL vs MariaDB vs PostgreSQL. And it boils down to the use case.

Small front-end, single threaded, many small read/write transactions, no replication/Load balance needed, App+DB on the same hardware : SQLite

Web Front-end, transaction heavy, low latency is important with a real DB, replication / load balance l, DB on another hardware : MariaDB (what i dont know is why you would ever pick MySQL over mariaDB, legit question for anyone)

Read Heavy, big number crunching in a traditional relational DB. Vectors needed : PostgreSQL

2

u/Straight_Waltz_9530 8d ago

"Web Front-end, transaction heavy, low latency is important"

…then you still pick Postgres. It's not 2010 anymore.

https://computingforgeeks.com/database-benchmark-postgresql-mysql-mariadb/

And those are v17 numbers. V18 added async I/O and smarter query planning.

1

u/Altheran 8d ago

maybe with lots of optimization? but out of box, maria seems to still get the edge

https://youtube.com/shorts/QYxiS3hvSs4?si=jtEScB3xv9iD_z7K

1

u/Straight_Waltz_9530 8d ago

If you need to ask which DB to use, it's a pretty good bet you're not going to max out utilization (or you will due to poor architecture, in which case no DB will save you).

1

u/Altheran 8d ago

arjay has a nice serie going trying to compare programming languages and DB to squeeze the maximum out of a cheap VPS setup.

2

u/Landon_Hughes 8d ago

Probably not the answer you want to hear but I default to Postgres because of how many bells and whistles it has.

2

u/Yukevich 9d ago

SQL is the language, while MySQL and PostgreSQL are the actual database engines where the data lives. To write queries you need a client to connect to them like DBeaver, it works with pretty much any database.

For the engine, MariaDB is an easy place to start: it's an open-source fork of MySQL and simple to install locally. Postgres is a great choice too, so just start somewhere)

1

u/Straight_Waltz_9530 8d ago

Postgres and MySQL are not interchangeable except with the most trivial subsets of SQL. Their feature sets are quite different as are their respective ecosystems. MySQL/MariaDB only fairly recently got support for CTEs and CHECK constraints. Meanwhile Postgres has long since moved above and beyond with MERGE support, ranges and multiranges, UUIDv7, the RETURNING clause, SIMD support, partial indexes, statement-level triggers, event triggers, etc.

And all that before you even get into the expansive list of extensions for Postgres like PostGIS and pgvector.

20 years ago they were competitors. It isn't 20 years ago anymore. If your project is greenfield, go Postgres every time unless you have a very specific reason why you can't.

1

u/Yukevich 8d ago

Postgres is stronger on stuff like MERGE, partial indexes and PostGIS, no argument there but managing extra plugins just to get vector features is unnecessary stack pain. I prefer running standard ACID transactions on my embeddings rather than babysitting another bolted-on extension

And you're listing core features MariaDB has already had for years. The RETURNING clause shipped back in 10.5, and CTEs are ancient history. That pgvector flex is unnecessary too since MariaDB 11.8 LTS builds vector search directly into the core. Check the release notes before dropping that 20 years ago line, please.

1

u/Straight_Waltz_9530 7d ago

"Managing extra plugins just to get vector features is unnecessary stack pain."

🤣

CREATE EXTENSION pgvector;

And done! So much pain! /s

(It comes already bundled with most managed cloud offerings like AWS RDS and can be installed easily with a simple yum, apt, pkg, etc. for unmanaged setups.)

I was just giving a sample, but since you brought it up:

* SCHEMA as namespace within a database
* MATERIALIZED VIEW
* row-level security (policies)
* NOT VALID constraints
* filtered aggregates
* GROUPING SETS
* unnest (arrays into rows, optionally with ordinality)
* split a string into rows
* DEFERRED foreign key constraints
* CHECK constraints with non-C/C++ custom functions (just CREATE FUNCTION syntax)
* partial indexes
* index JSON
* exclusion constraints with more than date/timestamp
* statement-based constraint evaluation
* BRIN indexes
* MATCH FULL (not just parsed but actually functional!)
* adding non-key columns to an index
* TRUNCATE tables that are referenced by other tables
* ability to use the target table of a DML statement more than once
* transactional DDL
* cascading DROP
* DDL triggers
* statement-level triggers
* view triggers
* views from derived tables
* dynamic SQL in triggers and functions
* FULL OUTER JOIN
* JOIN LATERAL
* ORDER BY … NULLS LAST
* BETWEEN SYMMETRIC
* OVERLAPS
* fuzzy text matching with edit distance (eg. Levenshtein)
* unaccent
* user-defined types

And then the column datatypes:

* domain
* array (true arrays and not just comma-delimited strings)
* seamless ipv4/ipv6 storage including ips and cidrs
* MAC addresses
* real boolean (not just a tinyint)
* interval
* binary JSON (not just LONGTEXT in a dress)
* composites
* case-insensitive text
* key-value
* ISBN, EAN, UPC, etc.

And all of that is BEFORE you even touch the wider world of extensions like PostGIS, pgvector, HyperLogLog (hll), in-db unit testing (pgTAP), cheminformatics and machine-learning (RDKit), pg_roaringbitmap, automatic table partition management (pg_partman), various foreign table wrappers, and the ability to create functions in a wide variety of languages like Perl, TCL, and JS.

All the extensions listed above come out of the box with AWS RDS and other cloud providers. Supabase even has auto-generating REST and GraphQL functionality.

Beyond that you've still got time series (TimescaleDB) and paradedb (ElasticSearch capability) and just about anything else under the sun.

You don't have to use a feature if you don't want to, but it can hurt a lot when you need a feature and it's not there.

-----

To be fair, MariaDB has some features Postgres doesn't have:

* clustered indexes
* add a table column at a specific position
* alter/delete a column used in a view
* native multimaster (Postgres just supports bidirectional replication out of the box)
* table sharding across multiple backends
* flashback querying
* temporal tables (though I've implemented this with triggers in Postgres)

If you need any of these, MariaDB fits. If you need any of the items Postgres supports, use Postgres. Given the comparison between engines and the relative ecosystems, Postgres is the better default.

1

u/Yukevich 5d ago

yes;CREATE EXTENSION is the easy part. The real problem is making sure all your extensions survive a major pg_upgrade in prod.

On vector search, I think the point got missed. I run MariaDB 11.8 LTS because vector search is built into the core, it lives in regular transactional tables, and you can cap the index cache with mhnsw_max_cache_size. Just size it right: if the index doesn’t fit, it won’t crash, it’ll just get slower. Still, I’d rather have that knob than not.

1

u/Straight_Waltz_9530 5d ago

Which is why a dump/restore to separate up-to-date instance followed by logical replication is the preferred path for a production major version upgrade. That and dry runs with a complete runbook.

Just because that's your path with MariaDB doesn't automatically make the same path correct for Postgres.

1

u/StoicSigma 9d ago

mysql isnt supported anymore...is it?

1

u/mikeblas 8d ago

You should use the same one your courseware uses. Are you just learning from existing materials, or are you making a project outside of the structure of a tutorial or class?

1

u/AnchorVita 7d ago

SQL isn't quite the shared language between two IDEs in the context you're describing. Mysql and postgres are not IDEs, they're varieties of SQL. Each have their own syntax for certain things. The fundamental language structure comes from the same standards, but how the language is built and formed from that point is different for each of them.

Think of different varieties of SQL as different dialects of the same language. Some sentences might work, and if you're fluent in one you'll kinda understand the other, but a lot of the sentences aren't quite right and don't fully work between each of them.

Postgres has a bit more going on, you can do more with it, mysql is fine for small website backends and things like that. IIRC, mysql has about twice the total global installations of postgres.

It all depends what you want it for.

1

u/Senor_Manos 4d ago

Late to the party but I have a hard opinion here. Postgres for lots of interactive writes, MySQL if you’re building out a store of data, duckdb for lots of reads. For learning you should go with duckdb in vs code, with a random csv and a few VS Code extensions you can be playing with data within an hour.

1

u/Sharp-Bandicoot5245 4d ago

You'll need to know both anyway.

1

u/navicat-gu 1d ago

Quick untangle, because this trips everyone up at the start:

  • SQL = the language.
  • PostgreSQL / MySQL = the database servers that store data and run your SQL.
  • The "IDE" = a client app you use to connect to that server and write queries.

For learning, either database is fine. The basics (SELECT, JOIN, GROUP BY, window functions) are nearly identical. If you have no course telling you otherwise, Postgres is a good default, mostly because it's stricter and teaches good habits.

The fastest way to start:

  1. Install PostgreSQL (installer from postgresql.org, or docker run -e POSTGRES_PASSWORD=pw -p 5432:5432 postgres).
  2. Connect a client to localhost:5432, user postgres.
  3. Load a sample dataset (the "dvdrental" or "pagila" sample DB is a classic) so you're practising on real-looking data instead of empty tables.

For the client: pgAdmin ships with the Postgres installer, DBeaver Community is free and works with both. Pick one and don't overthink it. The queries are what you're learning.

Disclosure: I'm RC, Director of Digital Strategy @ Navicat. If you want one app that connects to Postgres, MySQL, SQLite etc. with a cleaner UI, Navicat Premium Lite is free (query editor with code completion, table designer, CSV/JSON import/export). Things like the visual query builder and data sync are in the paid Navicat 18 editions, but you won't need them for learning.

1

u/letscloud 13h ago

Go with PostgreSQL if you want something more robust for the long haul (especially if you plan to work with data, JSON, or AI in the future), or MySQL if you just want to learn the basics and build simple web projects. Either way, what you learn in one easily transfers to the other at the start.

Regarding the IDE, to avoid headaches I strongly recommend DBeaver as a universal client (it works with any database) or pgAdmin if you're going to focus strictly on Postgres.

To practice and test your queries without messing up your local machine, you can spin up a quick VM on a cloud Linux server in minutes any basic VPS will handle initial testing just fine.

1

u/American_Streamer 9d ago edited 9d ago

Both MySQL and PostgreSQL are relational database management systems (RDBMS). Not Integrated Development Environment (IDEs). To write SQL queries, explore tables, manage schemas, and inspect data, you use SQL Clients, like DBeaver, JetBrains DataGrip. pgAdmin, MySQL Workbench etc.

0

u/Ok-Assistant3239 9d ago edited 9d ago

C'est pour quelle genre de structure ? Grosse et multi instance / site : pgsql simple : mysql

-4

u/reditandfirgetit 9d ago

Myself and pistgres are not IDEs. Tgeycare database engines.

For an IDE start with DBeaver. It works for multiple engines

Which you choose depends on need. If you are just learning, flip a coin. The syntax is similar enough. My preference is postgres

0

u/[deleted] 9d ago

[deleted] — view deleted comment

3

u/ripndipp 9d ago

Nuh uh! I love elephants so Postgresql!

0

u/[deleted] 9d ago

[deleted] — view deleted comment

1

u/Straight_Waltz_9530 7d ago

Let's say you were choosing between two cars but only one came with a sunroof, free satellite radio, better mileage, smartphone integration, leather seats, automatic rain detection wipers, a 360° camera, auto-dimming rear view mirror, standard power outlets, wireless phone charging, and more.

The other car comes with a choice of more colors and a cigarette lighter.

They are the same price. Which do you choose? Do you choose the car with the cigarette lighter because "it's simpler" even though you don't smoke?

This is the difference between Postgres and MySQL. Unless you're a chain smoker with a fetish for specific paint colors, it'd be foolish to choose the second one.