MySQL v PostgreSQL

12th July 2019 | Comments Off on MySQL v PostgreSQL

For nearly 40 years, Relational Database Management Systems (RDBMS) have been known as the go-to option for the storage of information in databases, mainly for personal data, financial information, manufacturing records and more.

The relational model for database management uses table format for storing data.

MySQL

Overview

MySQL is an open-source relational database management system (RDBMS). Just like PostgreSQL, and all other relational databases for that matter, MySQL uses tables as a core component and has more-less the same feature set as PostgreSQL.

  • MySQL project has its source code open-sourced to the public under the GNU license, along with multiple proprietary agreements. It is now owned by Oracle Corp
  • Most MySQL indexes (PRIMARY KEY, UNIQUE, INDEX, and FULLTEXT) are stored in B-trees
  • Customers can choose to use the Oracle Corporation or the open-source build
  • MySQL has a lot of available documentation, both official and from the community
  • ACID compliance (Atomicity, Consistency, Isolation, Durability)
  • MySQL is partially SQL compliant as it does not implement the full SQL standard
  • Popularity and ease of use: As one of the world’s most popular database systems, there’s no shortage of database administrators who have experience working with MySQL. Likewise, there’s an abundance of documentation in print and online on how to install and manage a MySQL database, as well as a number of third-party tools — such as phpMyAdmin — that aim to simplify the process of getting started with the database.
  • Security: MySQL comes installed with a script that helps you to improve the security of your database by setting the installation’s password security level, defining a password for the root user, removing anonymous accounts, and removing test databases that are, by default, accessible to all users.
  • Speed: By choosing not to implement certain features of SQL, the MySQL developers were able to prioritize speed. MySQL still holds a reputation as an exceedingly fast database solution.
  • Although MySQL generally performs well with read-heavy operations, concurrent read-writes can be problematic
  • Very Low overhead with MyISAM storage engine when used for read-mostly applications
  • Query Cache for repeatedly used statements
  • Some hosting services only support MySQL/MariaDB and it’s okay, specially if your application doesn’t do anything fancy with the database. Since they’re the most used, migrating to another hosting its a breeze.
  • For most web applications choosing MySQL is a no-brainier, it is very well supported (especially in shared host environments), it has a lot of sleek GUI managing tools, the syntax is ‘easier’ to learn (it’s just usually the one we learn first) and the speed in read operations is only beat by SQLite.
  • a bit hard for my company to find developers that “know” postgres, while virtually everyone knows how to develop with mysql (search for php database, there are only mysql results lol)

Integrations

Conclusion

MySQL will be ideal for your project if you require a high-security RDBMS for web applications or custom solutions, but not if you need a fully SQL compliant RDBMS capable of performing complex tasks swiftly. 

MySQL powers many websites and applications across the internet. This is, in large part, thanks to how easy it is to install and set up a MySQL database, as well as its overall speed and scalability in the long run.

PostgreSQL

Overview

PostgreSQL uses tables, constraints, triggers, roles, stored procedures and views as the core components that you work with. A table consists of rows, and each row contains a same set of columns. PostgreSQL uses primary keys to uniquely identify each row (a.k.a. record) in a table, and foreign keys to assure the referential integrity between two related tables.

  • PostgreSQL is developed by the PostgreSQL Global development group and is available under an open-source license, similar to the MIT license.
  • PostgreSQL includes built-in support for regular B-tree and hash indexes
  • newer versions of MySQL (5.7+) also support some NoSQL features as well
  • There is a REST API for any Postgres database (PostgREST is a standalone web server that turns your PostgreSQL database directly into a RESTful API)
  • There is a wealth of PostgreSQL information available online.
  • uses multi-version concurrency control (MVCC) which allows several writers and readers to work on the system at once
  • ACID compliance (Atomicity, Consistency, Isolation, Durability)
  • PostgreSQL is most SQL compliant as it meets 160 of the 179 core features of the SQL standard
  • Memory performance: For every new client connection, PostgreSQL forks a new process. Each new process is allocated about 10MB of memory, which can add up quickly for databases with lots of connections. Accordingly, for simple read-heavy operations, PostgreSQL is typically less performant than other RDBMSs, like MySQL.
  • Popularity: Although more widely used in recent years, PostgreSQL historically lagged behind MySQL in terms of popularity. One consequence of this is that there are still fewer third-party tools that can help to manage a PostgreSQL database. Similarly, there aren’t as many database administrators with experience managing a Postgres database compared to those with MySQL experience.
  • Speed: PostgreSQL was designed with extensibility and compatibility in mind. If your project requires the fastest read operations possible, PostgreSQL may not be the best choice of DBMS.
  • Simple setups: Because of its large feature set and strong adherence to standard SQL, Postgres can be overkill for simple database setups. For read-heavy operations where speed is required, MySQL is typically a more practical choice.
  •  it is harder to come by hosts or service providers that offer managed PostgreSQL instances.
  • AWS RDS Serverless and Digital Ocean managed DBs supporting Postgres
  • full text search (like NoSQL)
  • postgres is best, but Laravel support for it is .. mediocre. I usually just pick mysql because unless you have billions and billions of records, it doesn’t matter
  • It is much, much more strict than MySQL. If your app started with MySQL, you’re in for a rude awakening.
  • if you want to store critical data, full ACID compliance and have access to advanced features (like stored procedures and other things you usually require in enterprise environments) PostgreSQL is the way to go
  •  learning curve can be very steep if we come from the MySQL world

Integrations

Conclusion

PostgreSQL will be ideal for your project if your requirements revolve around complex procedures, integration, intricate designs and data integrity, and not around high-speed and ease of setting up.

Posted in Development