When we develop a program, or an app, it is important to keep our data on some place. If you learn basic web app programming from Youtube or from other learning platform, you will be guided to choose MySQL as a starter database. MySQL is beginner friendly, easy to use, and versatile when it comes to intermediate level query. But there is other SQL version, which is PostgreSQL. So what is the difference between those two?
What is SQL?
SQL is Structured Query Language. It is a programming language of which we can use to manage MySQL and PostgreSQL. MySQL and PostgreSQL is a database management system. Those two are the places we can store the database, and SQL is the language we can use to access them.
The learning curve of SQL is quite lean. The core concept of the query is simple. But when it comes to complexes database which have tons of tables, it is quite challenging to optimize the syntax of the query. SQL language is quite a lot to explore, but we can grasp the core of SQL quite easy.
For example, the JOIN syntax. There are many kind of JOIN syntax like INNER JOIN or OUTER JOIN which have different purposes, but the concept is the same, to join the columns of the tables. Same as INSERT, UPDATE, and DELETE, quite explanatory each of those syntax for the SQL.
About MySQL and PostgreSQL
MySQL is commonly used with web application, mostly with PHP and Javascript. Web applications store their user data, config data, and application data within MySQL. In PHP, they have mysqli extension which for some PHP installation is enabled by default. It is the interface for the web application to execute SQL Query to MySQL databases. The query which executed then returns output to the web application or changes data in the database.
But when you familiar with enterprise application like ERP or HRIS, you will heard PostgreSQL much more often. PostgreSQL is a database, just like MySQL. They have quite similar query syntax on INSERT, UPDATE, or DELETE, but on some cases like incremental key, upsert, or concatenate, they have different syntax. PostgreSQL much optimized for bigger application for which they have optimized for larger databases and more complex queries.
So That's It? More optimized?
Well, yes basically. PostgreSQL is more optimized to larger and complex databases.
The truth of deciding of which database to choose is based on what exactly the specifications do you need?
If you only need database for simple web application for about 100 users which not concurrently using, it is better to use MySQL. The installation is simple and will fulfill your app's need. MySQL interface on some programming language mostly pre-installed so it won't be too much of a hassle to install your database.
But if you need a more heavy load transaction and computation, most likely because your app used by a lot of concurrent users and maybe you will be using pgvector to use LLMs, you will need PostgreSQL. It is more optimized for larger JOINs for much more relational tables and even can be setup as Vector Database for your LLMs.
The more we know the capacity of those two database system, the better our app will perform.
Top comments (0)