Few would argue that the IT of the future is inextricably linked with the use of huge databases. The world is already coming up with new languages, new algorithms, just to simplify and speed up the use of huge flows of information. Even the relational approach familiar to many modern users is slowly but surely becoming a thing of the past. Why and what happens next? However, let's talk about everything in order.

From past to present

There is no point in covering the history of databases, clinging to any similarity, so the moment of the appearance of databases will not be ancient times, but the 60s of the 20th century. It was then that computers became an effective tool for commercial companies, and the COBASYL (CONference on DAta SYstems Language) organization, which created the COBOL language in 1959 and subsequently endowed it with database management capabilities, helped them manage the dramatically increased flows of information.

By the end of the 60s the first network model data, the concept of a DBMS arose, and in 1974 IBM began working on a language for System R. This is how SEQUEL (Structured English QUEry Language) was born. However, later, when it became known that this name was used by a British aircraft manufacturer, it was decided to shorten it a little to the usual SQL.

With the increasing availability of computers, computer-oriented ordinary users Database (Paradox, RBASE 5000, RIM, Dbase III), API (ODBC, Excel, Access) and development tools (VB, Oracle Developer, PowerBuilder). Of course, the trend has also spread to the Internet; today, effective interaction with the database is an unspoken requirement for any resource with more or less dynamic information.

If we talk about companies, a trinity of power has been established in the market: almost all power in the field of databases is distributed between IBM, Microsoft and Oracle.

Present and future

Before the start of the new millennium, the relational approach to databases dominated in IT, but the need to improve performance inevitably led to the development of the idea of ​​NoSQL (not only SQL). If you have a hard time understanding what it is and what the difference is, then by clicking on the link you will receive comprehensive answers to all your questions.

To put it simply, the relational approach describes data in table format, that is, all information is inextricably linked by relationships and structure (remember Excel with columns and rows, where each new object written using the same template). This inevitably leads to performance and scaling limitations, but from a creation and management point of view, it is simple and convenient.

The NoSQL approach avoids these problems due to the absence of strict information connections. But here another problem arises - the organization of access. It is solved in 4 main ways: using document orientation, expandable records (sparse matrices), access keys and graph theory. Naturally, the NoSQL approach requires more knowledge and skills from the developer, but the results are much more effective. This is why it is believed that SQL is already becoming a thing of history, and NoSQL is the future of all databases.

However, this prediction rests on the fact that using the relational approach for small databases is much more effective. Therefore, instead of a meaningless argument, let’s talk about more practical things, namely the most popular databases.

Rating

1. Oracle;
2. MySQL;
3. Microsoft SQL Server;
4. PostgreSQL;
5. MongoDB;
6. Cassandra;
7. Microsoft Access;
8. Redis;
9. SQLite.

In total, 7 out of 10 representatives of the rating are relational databases, as well as one instance each of a document-oriented database (MongoDB), with distributed values ​​(Cassandra) and using the key-value approach (Redis). Thus, today the dominance of relational databases is undeniable, but what will happen tomorrow?

To answer this question, let’s turn to the trends section on the same resource. If we take time stamps of more than 2 or 4 years, then the greatest growth is demonstrated by the approach using graph theory. At the same time, over the past year, databases based on time data have shown the greatest increase in popularity. It's relative new approach, it is also considered NoSQL, the benefit comes down to creating a structure based on dates or time ranges. At the moment, the most popular representative of the Time Series database is InfluxDB.

What databases do you use? And what do you think is the most promising NoSQL database?

Databases are logically modeled repositories of any type of data. Every database that is not schemaless follows a model that defines a specific data processing structure. DBMS are applications (or libraries) that manage databases of various shapes, sizes and types.

To better understand the DBMS, check out.

Relational database management systems

Relational systems implement a relational data model, which defines all stored information as a set of related records and attributes in a table.

DBMS of this type use structures (tables) to store and work with data. Each column (attribute) contains its own type of information. Each record in the database that has a unique key is passed into a table row, and its attributes are displayed in the table columns.

Relationships and Data Types

Relations can be defined as mathematical sets containing sets of attributes that represent stored information.

Each element that forms a record must satisfy a certain data type (integer, date, etc.). Various RDBMSs use different types data that is not always interchangeable.

These kinds of restrictions are common in relational databases. In fact, they form the essence of the relationship.

Popular RDBMS

In this article we will talk about the 3 most popular RDBMSs:

• SQLite: very powerful embedded RDBMS.
• MySQL: the most popular and frequently used RDBMS.
• PostgreSQL: the most advanced and flexible RDBMS.

SQLite

SQLite is an amazing library that is built into the application that uses it. Being a file database, it provides an excellent set of tools for easier (compared to server databases) processing of any type of data.

When an application uses SQLite, their communication is done through function calls and direct calls to files containing data (for example, databases). SQL data ite), rather than some kind of interface, which increases the speed and performance of operations.

Supported data types

• NULL: NULL value.

• INTEGER: a signed integer stored in 1, 2, 3, 4, 6, or 8 bytes.

• REAL: A floating point number stored in IEEE 8-byte format.

• TEXT: text string with UTF-8, UTF-16BE, or UTF-16LE encoding.

• BLOB: a type of data stored exactly in the same form in which it was received.

Note: to get more detailed information check out the documentation.

Advantages

• File: the entire database is stored in one file, making it easy to move.

• Standardized: SQLite uses SQL; Some functions are omitted (RIGHT OUTER JOIN or FOR EACH STATEMENT), however, there are some new ones.

• Great for development and even testing: During the development phase, most require a scalable solution. SQLite, with its rich feature set, can provide more than enough functionality while being simple enough to work with a single file and associated C library.

Flaws

• Lack of user control: advanced databases provide users with the ability to manage relationships in tables according to privileges, but SQLite does not have such a feature.

• Inability to further configure: again, SQLite cannot be made more productive by fiddling with the settings - that’s just the way it is designed.

When to use SQLite

• Built-in applications: all ported applications that are not designed to scale - for example, local single-user applications, mobile applications or games.

• Disk access system: In most cases, applications that frequently perform direct read/write operations to disk can be migrated to SQLite to improve performance.

• Testing: Perfect for most applications, part of the functionality of which is testing business logic.

When not to use SQLite

• Multi-user applications: If you are working on an application in which several people will simultaneously access the database, it is better to choose a full-featured RDBMS - for example, MySQL.

• Applications that record large amounts of data: One of the limitations of SQLite is write operations. This RDBMS allows only one write operation to be executed at a time.

MySQL

MySQL is the most popular of all major server databases. It’s very easy to understand, and you can find a lot of information about it online. Although MySQL does not attempt to fully implement SQL standards, it does offer a lot of functionality. Applications communicate with the database through a daemon process.

Supported data types

• TINYINT: very small whole.

• SMALLINT: small whole.

• MEDIUMINT: medium sized whole.

• INT or INTEGER: the whole is normal size.

• BIGINT: big whole.

• FLOAT: signed single-precision floating-point number.

• DOUBLE, DOUBLE PRECISION, REAL: signed double precision floating point number.

• DECIMAL, NUMERIC: signed floating point number.

• DATE: date of.

• DATETIME: combination of date and time.

• TIMESTAMP: timestamp.

• TIME: time.

• YEAR: year in YY or YYYY format.

• CHAR: a fixed-size string, padded on the right with spaces to a maximum length.

• VARCHAR: variable length string.

• TINYBLOB, TINYTEXT: A BLOB or TEXT column with a maximum length of 255 (2^8 – 1) characters.

• BLOB, TEXT: A BLOB or TEXT column with a maximum length of 65535 (2^16 – 1) characters.

• MEDIUMBLOB, MEDIUMTEXT: A BLOB or TEXT column with a maximum length of 16777215 (2^24 – 1) characters.

• LONGBLOB, LONGTEXT: A BLOB or TEXT column with a maximum length of 4294967295 (2^32 – 1) characters.

• ENUM: transfer.

• SET: multitudes.

Advantages

• Simplicity: MySQL is easy to install. There are many third-party tools, including visual ones, that make it easier to get started with the database.

• Many functions: MySQL supports most SQL functionality.

• Safety: MySQL has many security features built into it.

• Power and Scalability: MySQL can handle really large amounts of data and is well suited for scalable applications.

• Speed: Neglect of some standards allows MySQL to work more efficiently, sometimes cutting corners.

Flaws

• Known Limitations: By definition, MySQL can't do everything, and it has certain functionality limitations.

• Reliability issues: some operations are implemented less reliably than in other RDBMSs.

• Stagnation in development: Although MySQL is an open-source product, work on it is very slow. However, there are several databases that are completely based on MySQL (for example, MariaDB). By the way, you can learn more about the relationship between MariaDB and MySQL from our conversation with the creator of both RDBMSs, James Bottomley.

When to use MySQL

• Distributed operations: When you need more functionality than SQLite can provide, you should use MySQL.

• High security: MySQL security features provide strong protection for data access and use.

• Websites and applications: Most web resources can work well with MySQL, despite the limitations. This tool is very flexible and easy to use, which is beneficial in the long run.

• Custom solutions: If you're working on a very specific product, MySQL will adapt to your needs with a wide range of settings and operating modes.

When not to use MySQL

• SQL Compatibility: Because MySQL does not attempt to fully implement SQL standards, it is not fully SQL compliant. Because of this, problems may arise when integrating with other RDBMSs.

• Competitiveness: Although MySQL handles read operations well, simultaneous read-write operations can cause problems.

• Lack of features: Depending on the choice of MySQL engine, some features may be missing.

PostgreSQL

PostgreSQL is the most advanced RDBMS, focusing primarily on full standards compliance and extensibility. PostgreSQL, or Postgres, attempts to fully comply with ANSI/ISO SQL standards.

PostgreSQL differs from other RDBMSs in that it has object-oriented functionality, including full support for the ACID (Atomicity, Consistency, Isolation, Durability) concept.

Being based on powerful technology, Postgres excels at processing multiple jobs simultaneously. Concurrency support is implemented using MVCC (Multiversion Concurrency Control), which also ensures ACID compatibility.

Although this RDBMS is not as popular as MySQL, there are many third-party tools and libraries to make working with PostgreSQL easier.

Supported data types

• bigint: signed 8-byte integer.

• bigserial: an automatically incremented 8-bit integer.

• bit [(n)]: fixed length bit string.

• bit varying [(n)]: variable length bit string.

• boolean: Boolean value.

• box: rectangle on a plane.

• bytea: binary data.

• character varying [(n)]: a fixed-length character string.

• character [(n)]:

• cidr: IPv4 or IPv6 network address.

• circle: circle on a plane.

• date: calendar date.

• double precision: double precision floating point number.

• inet: IPv4 or IPv6 host address.

• integer: signed 4-byte integer.

• interval [(p)]: period.

• line: an infinite straight line on a plane.

• lseg: a segment on a plane.

• macaddr: MAC address.

• money: monetary value.

• path: geometric path on a plane.

• point: geometric point on a plane.

• polygon: polygon on a plane.

• real: single precision floating point number.

• smallint: signed 2-byte integer.

• serial: an automatically incremented 4-bit integer.

• text: variable length character string.

• time [(p)] : time of day (no time zone).

• time [(p)] with time zone: time of day (with time zone).

• timestamp [(p)] : date and time (no time zone).

• timestamp [(p)] with time zone: date and time (with time zone).

• questions: text search query.

• tsvector: text search document.

• txid_snapshot: snapshot user transaction ID.

• uuid: unique identificator.

• xml: XML data.

Advantages

• Full SQL compatibility.

• Community: PostgreSQL is supported 24/7 by an experienced community.

• Third party support: Despite its very advanced features, PostgreSQL is used in many RDBMS related tools.

• Extensibility: PostgreSQL can be programmatically extended with stored procedures.

• Object Orientation: PostgreSQL is not only a relational, but also an object-oriented DBMS.

Flaws

• Performance: IN simple operations PostgreSQL's reading performance may be inferior to its rivals.

• Popularity: Due to its complexity, the tool is not very popular.

• Hosting: Due to the above factors, it is difficult to find a suitable provider.

When to use PostgreSQL

• Data integrity: If reliability and data integrity are a priority, PostgreSQL is the best choice.

• Complex procedures: if your database needs to perform complex procedures, you should choose PostgreSQL due to its extensibility.

• Integration: If in the future you need to move the entire database to another solution, PostgreSQL will have the least problems.

When not to use PostgreSQL

• Speed: If all you need is fast read operations, you shouldn't use PostgreSQL.

• Simple situations: unless you need increased reliability, ACID support and all that, using PostgreSQL is shooting flies with a cannon.

Currently there are a large number of DBMS various manufacturers, while the most common DBMSs are universal, i.e. can be used in various subject areas and to solve various problems. Systems vary in performance, resource requirements, and maintenance costs.

The most common industrial DBMSs are still the systems of the American company Oracle, manufacturer of the first commercial industrial DBMS. These systems are distinguished by high reliability, the ability fine tuning for tasks to be solved, flexible management RAM, high performance, multi-platform – i.e. the ability to work under the control of both computers of different architectures and different operating systems. Along with the multi-user version of the Oracle DBMS, there is a “light” version of it - Personal Oracle or Oracle Lite. This version It is usually used in small AIS with a limited number of users (usually no more than 5–10 people).

Microsoft's industrial DBMS, called SQL Server, has no less capabilities than the Oracle DBMS. However, its disadvantage is that it can only function under the operating systems of Microsoft itself, which makes it impossible to use it on servers running popular operating systems such as Unix, Linux, Solaris and others. Another popular DBMS from Microsoft is MS Access, which is included in the software package Microsoft Office. This DBMS is single-user and is intended for maintaining small databases.

Popular and fairly common DBMSs are also DB2 from IBM, Sybase, Informix, PostgreSQL (“ Post-Gres-Q-El", "postgres"), Interbase and some others. Separately, it is worth mentioning freely distributed DBMSs, many of which are suitable for solving quite complex problems. While not fully possessing the functionality inherent in commercial DBMSs, they, at the same time, have a significant advantage - zero cost. As a rule, such DBMSs are supported by independent groups of developers and distributed in the form of source codes of program modules. The most popular of these DBMSs in our country are MySQL and the so-called “clones” of the commercial DBMS Interbase – FireBird and Yaffil.

The choice of DBMS should take into account the customer’s needs and the possibility of further expansion information system, acquisition cost factors and other parameters.

The concept of a database has become so firmly established in our lives that it has become taken for granted and does not deserve special attention. About IT startups, algorithms , hacker attacks, cryptocurrency, (what can we say, and about clouds too) in the specialized media, much more articles have been written than about “ordinary”, but such important and necessary databases and their management systems. So we, having repeatedly conducted our mini-studies in different areas of the IT world, have never addressed this topic. Well, we will immediately correct ourselves and share with you interesting news and the latest statistics.

And among DBMSs there are favorites

A database management system (DBMS) is a software tool (typically an interface between the end user/application and the database itself) that makes it easier and more convenient to work with information. For example, create, update, search, delete and restore data in a database, as well as determine relationships between its components (tables).

Typically, a DBMS includes three main components: the data itself, the database engine, and a schema that defines the logical structure of the data. It is these three components that help ensure safe management anddatabase protection,integrity of the information stored in it and unified administration procedures - change management, configuration and performance control, backup, disaster recovery etc.

Of course, there are an incredible number of database management systems themselves, but the ones that everyone knows about are hardly a dozen. To determine which of the existing DBMSs is rightfully included in the list of the most popular, DB-Engines compiled a kind of rating of favorites. To do this, experts analyzed a number of factors: the number of mentions of systems on websites (we used Google search engines, Yandex and Bing), general user interest in Google Trends, mentions in discussions on specialized sites Stack Overflow and DBA Stack Exchange, on job aggregator sites Indeed and Simply Hired, in professional profiles of specialists on Linkedin and Upwork, and, finally, the number relevant tweets. However, we note that the total number of DBMS installations was not counted, so this top, although interesting, is still relative. According to DB-Engines, the top three popular management systems are: Oracle, MySQL, Microsoft SQL Server.

It is interesting that among programmers, the most in demand skills were working with DBMSs such as MySQL, MongoDB and PostgreSQL, and developers generally put Oracle in last place, since this system was noted by only 12% of respondents (source: Stack Exchange (Stack Overflow Talent)) .

Thus, based on the results of two studies, we see that so far users are most interested in the MySQL system. Perhaps not the least important role here is played by the fact that this is an open source product. source code. By the way, it is for this reason that in 2016 MySQL had the largest number of vulnerabilities eliminated - 133. And this fact does not mean that the system itself is poorly protected, but on the contrary indicates that its performance was checked by a larger number of specialists, which increased chances of identifying its “weak points” in information security.

Common Security Threats

It is noteworthy that it is the number of patches that allows us to indirectly determine the degreeinformation protection in database management systemsand identify the most common vulnerabilities. Thus, a Trustwave study showed that in 2016, DBMS users most often encountered the following categories of cyber threats:

• Unauthorized expansion of privileges.These vulnerabilities allowed unauthorized persons to use administrator rights to gain access to database tables and configurations.
• Buffer overflow.This caused the server to crash, which underminedhardware database protection,and also caused a denial of service and could lead to the execution of someone else’s malicious code.
• Default permissions.Accounting admin records, left with the default password could provide additional scope for fraudulent activities by cybercriminals.

These were the results of recent studies. We really hope that in the future database management systems will become more secure, and new names will appear in the top DBMS popularity charts. We promise to follow the news and keep you up to date with the latest trends.

Relational databases have been used in programming for a long time. At one time, they gained popularity due to the simplicity and convenience of the relational model of working with data.

This article analyzes the differences between the most popular relational database management systems (DBMS): SQLite, MySQL and PostgreSQL.

Database management systems

Databases are logically modeled repositories of various information (data) of all types. Every SQL database is based on a model that provides a structure for the data it stores. Database management systems are applications (or libraries) that manage databases of various shapes, sizes, and types.

Relational database management systems

Relational DBMSs use a relational model to work with data. This model stores any information in tables in the form of related records with attributes.

This type of DBMS requires table structures. The columns (attributes) of such a table contain Various types data. Each database record is treated as a row in a table, the attributes of which are represented as columns.

Relationships and Data Types

Relationships can be thought of as mathematical sets containing a number of attributes that collectively represent the database and the information stored within it.

When adding a record to a table, you need to distribute all its components (attributes) by data type. Different relational DBMSs use different data types, and they are not always interchangeable.

Such restrictions (as, for example, with data types) are typical for relational DBMSs, because, in fact, relationships between data are built on the basis of restrictions.

Note: NoSQL databases do not have such strict restrictions because they do not build such relationships between data. To learn more about NoSQL, read.

Popular relational databases

In this article we will look at three of the most important and popular open source DBMSs.

• SQLite: Built-in powerful database management system.
• MySQL: the most popular and widely used database.
• PostgreSQL: An advanced, open-source, SQL-compatible object DBMS.

Note: Open source applications almost always give users the right to freely use and change the code. By forking code, you can create a completely new application. One fork of MySQL, for example, is MariaDB.

SQLite

SQLite is a performance library that can be embedded into applications. A full-fledged SQLite file-based database offers a wide range of tools for processing all types of data and imposes far fewer restrictions than other relational databases.

Applications using SQLite do not communicate using an interface (ports, sockets), but send direct requests to the file in which the data is stored (for example, a SQLite database). This makes the SQLite application very fast and performant.

SQLite data types

• NULL: empty value.
• INTEGER: integer value (depending on the size, the value is stored in 1, 2, 3, 4, 6 or 8 bytes).
• REAL: floating point number, stored as 8-byte IEEE.
• TEXT: text string, stored in encrypted form (UTF-8, UTF-16BE or UTF-16LE).
• BLOB: binary data, stored in the form in which it was entered.

Benefits of SQLite

• Simple file-based design: the entire database consists of just one file, which increases its portability.
• Standards: Despite its simplicity, the SQLite system is based on SQL. Some functions are omitted (RIGHT OUTER JOIN or FOR EACH STATEMENT), but others are added instead.
• SQLite is great for development or testing. At these stages, a simple but scalable solution is almost always needed.

Disadvantages of SQLite

• No user management. More complex DBMSs support user management (their relationships, privileges, etc.). A simple SQLite DBMS does not provide such a function.
• Unable to improve performance. The SQLite library is easy to configure and use. However, it is designed in such a way that it does not allow additional performance to be obtained through fine tuning. That is, it is technically impossible to make SQLite more productive.

When is the best time to use SQLite?

• Simple built-in applications that require portability, such as single-user local applications, mobile applications, games.
• Disk replacement. Typically, applications that need to read or write files to disk can use SQLite to gain additional functionality.
• Testing.

When is it better not to use SQLite

• Multi-user applications. If an application is built in such a way that a large number of clients simultaneously use one database, then it is better to implement a full-featured relational DBMS (for example, MySQL) into such an application.
• Applications that record large amounts of data. write operation is one of the limitations of SQLite. This DBMS allows only one write operation at a time, hence it limits the throughput.

MySQL

MySQL is the most popular DBMS. It's multifunctional open application, supporting the operation of a huge number of sites. MySQL is quite easy to use and can store large amounts of data.

Note: Given the popularity of MySQL, a large number of third party applications, tools and libraries.

MySQL does not implement the full SQL standard. Despite this, MySQL offers many functionality for users: autonomous database server, interaction with applications and websites, etc.

MySQL Data Types

• TINYINT: an integer in the range -128 to 127 (1 byte).
• SMALLINT: integer from -32768 to 32767 (2 bytes).
• MEDIUMINT: number from -8388608 to 8388608 (3 bytes).
• INT or INTEGER: a number in the range -2147683648 to 2147683648 (4 bytes).
• BIGINT: number from -2 63 to 2 63 -1 (8 bytes).
• FLOAT: floating point number (4 bytes).
• DOUBLE, DOUBLE PRECISION, REAL: double precision floating point number.
• DECIMAL, NUMERIC: values ​​of increased accuracy.
• DATE: date.
• DATETIME: date and time.
• TIMESTAMP: timestamp.
• TIME: time in hh:mm:ss format.
• YEAR: year (stored as 4 digits by default, but 2 can be configured).
• CHAR: fixed length string.
• VARCHAR: variable strings.
• TINYBLOB, TINYTEXT: The TEXT type allows you to store text, and BLOB stores images, sound, electronic documents and so on. Maximum length is 225 characters.
• BLOB, TEXT: large amounts of text, maximum 65535 characters.
• MEDIUMBLOB, MEDIUMTEXT: similar to the previous one, but up to a maximum of 16777215 characters.
• LONGBLOB, LONGTEXT: similar to the previous one, but up to a maximum of 4294967295 characters.
• ENUM: accepts only one of the values ​​of the given set.
• SET: Accepts any or all elements from the values ​​of a given set.

MySQL Benefits

• Easy to use: MySQL is very easy to install and configure. Third-party tools, including visualizers (interfaces), greatly simplify working with data.
• Functionality: MySQL supports a huge number of SQL functions.
• Security: MySQL provides many built-in advanced features to protect your data.
• Scalability and performance: MySQL can handle large amounts of data.

Disadvantages of MySQL

• Limitations: MySQL's structure imposes some limitations that may prevent advanced applications from running.
• Vulnerabilities: The data processing method used in MySQL makes this DBMS slightly less reliable compared to other DBMSs.
• Slow development: Although MySQL is an open source product, it is developing very slowly. However, it should be noted that several full-fledged databases are based on MySQL (for example, MariaDB).

When to use MySQL

• Distributed Operations: Autonomous Database Server MySQL data supports many operations and provides several additional features.
• High data security: MySQL offers high data security.
• Websites and Web Applications: Despite its limitations, MySQL can power almost any website and web application. This flexible and scalable tool is easy to use.
• Custom solutions: MySQL can be tailored to meet the exact requirements of a site or application.

When is it better not to use MySQL?

• Conflicts with SQL: Since MySQL still does not fully implement SQL standards, it is not fully compatible with SQL. Therefore, MySQL cannot always be integrated with another DBMS.
• Weak concurrency support: Although MySQL performs read operations well, simultaneous read and write operations can cause problems.
• Lack of some functions (for example, full-text search).

PostgreSQL

PostgreSQL is an advanced open object-oriented DBMS. PostgreSQL implements ANSI/ISO SQL standards.

Unlike other DBMSs, PostgreSQL supports very important object-oriented and relational database features: reliable ACID (atomicity, consistency, isolation, durability) transactions, etc.

Based on reliable technology, the PostgreSQL DBMS can simultaneously process a large number of tasks. Consistency support is achieved without blocking reads thanks to MVCC.

Although the PostgreSQL DBMS is not as popular as MySQL, a large number of additional tools and libraries have also been developed for it, which simplify working with data and increase the performance of the DBMS.

PostgreSQL Data Types

• bigint: signed eight-byte integer.
• bigserial: An eight-byte auto-incrementing integer.
• bit [(n)]: fixed length bit string.
• bit varying [(n)]: bit string with variable length.
• boolean: boolean value (true/false).
• box: a quadrilateral on a plane.
• bytea: binary data.
• character varying [(n)]: A variable-length character string.
• character [(n)]: fixed-length character string
• cidr: IPv4 or IPv6 network address.
• circle: circle on a plane.
• date: date (year, month, day).
• double precision: double precision floating point number (8 bytes).
• inet: IPv4 or IPv6 host address.
• integer: signed four-byte integer.
• interval [(p)]: time interval.
• line: an infinite line on a plane.
• lseg: line segment on a plane.
• macaddr: MAC (Media Access Control) address.
• money: currency.
• numeric [(p, s)]: exact numeric value with the selected precision.
• path: geometric path on a plane.
• point: geometric point on a plane.
• polygon: closed geometric path on a plane (polygon)
• real: single precision floating point number (4 bytes).
• smallint: signed two-byte integer.
• serial: four-byte integer with auto-increment.
• text: a variable length character string.
• time [(p)] : time of day (no time zone).
• time [(p)] with time zone: time of day and time zone.
• timestamp [(p)] : timestamp (date and time) without time zone.
• timestamp [(p)] with time zone: timestamp with time zone.
• tsquery: Text search query.
• tsvector: Text search document.
• txid_snapshot: A snapshot of the user-level transaction ID.
• uuid: universally unique identifier.
• xml: XML data.

Benefits of PostgreSQL

• The PostgreSQL database management system is open, SQL-compatible, and free.
• The active PostgreSQL community will help you find a solution to any DBMS-related problem, at any time of the day.
• Support for third-party tools: In addition to built-in advanced features, PostgreSQL supports many open third-party tools for design, data management, and more.
• Scalability and extensibility.
• Object orientation.

Disadvantages of PostgreSQL

• Performance: In some situations, PostgreSQL performance is lower than MySQL.
• Low popularity.
• Due to the above disadvantages, not all hosting providers support PostgreSQL.

When to use PostgreSQL

• If the application requires data integrity.
• To perform complex user tasks.
• If in the future the application needs a more reliable paid database, it will be easier to switch from PostgreSQL.
• To support applications with complex structures, PostgreSQL offers a special set of functions.

When is it better not to use PostgreSQL

• If the application needs fast read operations.
• If your application doesn't need absolute data integrity, ACID, or complex structure, PostgreSQL may be too complex a solution.
• Data replication is more complicated than in MySQL, so it is better not to use PostgreSQL in clusters.