Comparison of MySQL and PostgreSQL from a developer’s point of view

annotation

The article presents a comparative analysis of two free free database management systems (DBMS): MySQL and PostgreSQL. The analysis is carried out from the point of view of using these DBMS in low- and medium-loaded applications. The issues of scaling and optimization for projects with multi-million audiences are not considered. No performance comparison data. MySQL 5.1 and PostgreSQL 8.3 are considered.

Data types

The first thing that a developer has to face is the available data types. Let's compare the available data types.

Integers and floating point numbers

I will not specify the ranges of possible values, but I will indicate the information capacity in bytes.

  .  | MySQL |  PostgreSQL        
 TINYINT | 1 byte |                   
 SMALLINT | 2 bytes |  2 bytes           
 MEDIUMINT | 3 bytes |                   
 INTEGER, INT | 4 bytes |  4 bytes           
 BIGINT | 8 bytes |  8 bytes            
 FLOAT, DOUBLE, REAL | 4 or 8 bytes |  4 or 8 bytes      
 DECIMAL, NUMERIC | 65 decimal places |  with no restrictions   
 SERIAL, BIGSERIAL | 8 bytes |  4 or 8 bytes
 BIT | he is |  he is 

')
Note:
1) MySQL has support for UNSIGNED types, however this is not part of the SQL standard.
2) For floating point types, PostgreSQL uses the IEEE 754 standard format, so you can store the values ​​+ Inf, -Inf, and NaN, but using these values ​​in mathematical operations will not work.

As can be seen from the table, the data types are almost identical for the two DBMSs. The differences are that MySQL allows you to use available memory in more detail, but working with numbers in a symbolic representation is limited to 65 digits. I do not see any practical use of numbers with so many characters, so we can assume that the capabilities of MySQL and PostgreSQL in this section are identical.

Strings and data

Dimensions are in bytes. Do not forget that for one character UTF-8 can be used from 1 to 4 bytes.

  .  |  MySQL |  PostgreSQL
 BINARY, CHAR |  255 |  10485760
 VARCHAR, VARBINARY |  65535 * |  10485760
 TINYBLOB, TINYTEXT |  2 ^ 8 | 
 BLOB, TEXT, bytea |  2 ^ 16 |  is not limited
 MEDIUMBLOB |  2 ^ 24 | 
 LONGBLOB |  2 ^ 32 | 

* The limit is caused by a maximum string length of 65535 bytes, but in reality the maximum length is much shorter.

The table shows that in terms of capacity, the string data types in the two DBMS do not differ much, and again MySQL allows you to control the format of data storage on the hard disk in more detail. However, this flexibility of MySQL introduces two small problems at the design stage: confusion over data types and sizes.

Not to be unfounded I will give an example - the storage of user data. Suppose that we needed to store about a user not only his name, surname and patronymic, but the address and phone numbers, but what can we offer to store to the user in our profile. From a SQL point of view, the types CHAR and VARCHAR must be used for this. And here in MySQL you have to decide what is the maximum length of a surname, what is the maximum length of the name, what is the maximum length of the address, because 65535 bytes are given for everything. At the same time, in PostgreSQL, we simply specify the type for all columns in the VARCHAR table, where we can, if necessary, store much more data than MySQL allows us. (I will ask you not to suggest using TEXT in MySQL for this purpose.)

date and time

The types of date and time for the two bases are almost identical and usually do not cause problems.

Custom Types

For those who wish, PostgreSQL offers a whole group of data types for work that are completely missing in MySQL: arrays, structures, types for storing IP and MAC addresses, and even types for storing parameters of geometric figures. Those interested can independently familiarize themselves with the PostgreSQL data types .
UPD In MySQL, it turns out, there are data types for geometric shapes details in the comments

Conclusions on data types

1) The data types offered by the two DBMSs are identical from a functional point of view.
2) With the help of these types, you can store data in any of the DBMS, however, in MySQL, the developer is forced at the very initial design stage to artificially limit the length of the string data, which does not have a positive effect on the usability of the system.
3) The use of non-standard types in PostgreSQL allows you to simplify development quite a lot, but it will complicate the transition to another DBMS.
4) MySQL allows you to accurately control the structure of the stored data, but at the same time sacrificed the convenience of the developer.

Data management capabilities

Here I want to compare two DBMSs in terms of the additional functionality offered by the developer. Some of this functionality is included in the SQL standard.

Stored procedures

Let's start with the simplest - stored procedures. Roughly speaking, in MySQL there are no stored procedures functionality at all. If to be expressed more precisely, then in general they are, but rather conditional. So, for example, with replication enabled, stored procedures can only be readonly. So, the quite popular scheme of restricting user rights through stored procedures is not at all realizable in MySQL.

Indices and Keys

On this front, MySQL also does not shine with its capabilities. 1000 byte limit on key size - where does it fit? Suppose I allow my users to create accounts in any language (UTF-8). As the maximum login length, I choose 512 characters. Since logins should be unique, I come to the conclusion that I need to impose a unique key on the column, and, as it turns out, I can not, because the key does not fit into 1000 bytes. We have to make concessions and make uniqueness only on the first 333 characters. Whoever does not believe, can independently look at the result of create table t (t varchar (512), key (t)) character set = utf8.
PostgreSQL does not suffer from such complexes, but simply makes a unique key of the required size.

Verification of data at the stage of adding

The SQL standard provided for a CHECK statement that specifies an expression that the data to be added to the table must satisfy. In the MySQL manual, this instruction is said very simply, “The CHECK clause is parsed but Ignored by all storage engines.” I have nothing more to add to this, in postgre, as expected, everything is in order.

Transactions and foreign keys

By default, MySQL uses the MyISAM engine for tables, which does not support transactions or foreign keys. This happened historically and there is an excuse for such an approach, if we consider working with the database from the point of view of performance. You can simply note that if you need transactions and foreign keys, the use of the InnoDB storage engine is necessary. Naturally in PostgreSQL transactions and external keys are fully functional.

findings

MySQL and PostgreSQL are database management systems that have different tasks and clearly understand what the difference is. As a practical recommendation, I can say that MySQL shows its advantage in the HighLoad area, but it requires a more careful approach from the developer, and also imposes quite serious restrictions on the stored data and on the DBMS function.

In general, for projects not focused on multimillion-dollar attendance, as well as for academic purposes, I recommend using PostgreSQL. MySQL shows its advantage on databases with a large number of simple one-table queries and requires close attention from the developer.