Due to the rapid devaluation of the ruble, buying a Microsoft SQL DBMS has become very expensive, and for some companies the cost of these licenses has become completely unaffordable. Currently, to deploy a Microsoft SQL server for 20 users, you need to purchase the following licenses:

1 operating system license (WinSvrStd 2012R2)

20 licenses for connection to the server (WinSvrCAL 2012)

1 license for DBMS server (SQLSvrStd 2014)

20 licenses for connecting to the DBMS (SQLCAL 2014)

Estimated cost of such a package 275,000 rub., which is quite expensive for a company with only 20 people. These costs can be avoided if you create a DBMS server using free software. Install a Linux operating system and a free version of the DBMS - PostgreSQL. On such a server, you can easily deploy a 1C enterprise server, as well as other roles that can potentially be combined with the role of databases, for example WebServer or file storage.

Since using free software is very attractive from a financial point of view, it was decided to see how good it is in terms of performance.

1C performance testing:

To perform the test, we used the equipment and software listed in Table 1. The physical server for both stands was the same, only the software was changed. The settings of both DBMSs were used by default and we do not describe them in detail in the article. The PostGreSQL distribution kit with the corresponding patches was taken from the 1C company website, the version is the latest available on this site.

Table 1. Test benches

	Characteristics	Stand No. 1	Stand No. 2
	operating system	CentOS 6	Windows Server 2012R2
		PostgreSQL 9.3.3	Microsoft SQL Server 2012R2
	CPU	Intel Core i 5 3330 (3.0 Ghz)
	RAM	24 GB DDD 3 1333 Ghz
	HDD	SSD 240 GB Intel

To begin with, the “Gilev test” was performed, which showed a slight advantage of stand number 2 against a stand with free software.

See the results below, the difference in values ​​is only 3%.

For information:“Gilev test” is a popular synthetic 1C test that performs a number of standard operations - the faster the test is performed, the higher the score. The assessment is performed in conventional units. The resulting score can be compared with the scale attached to the test, which will show how high the performance of the current system is.

Figure 1. Gilev test result. Stand No. 2 DBMSMS SQL

Figure 2. Gilev test result. Stand No. 1 DBMSPostgreSQL

Next, it was decided to perform testing using the APDEX method. The essence of the method is to measure the time it takes to perform basic operations in 1C; measurements are taken several times over a certain period of time. Next, the obtained result is compared with the acceptable time to complete a particular operation.

To do this, we took a real working base of one of the heaviest 1C configurations; the characteristics of the base are indicated in table No. 2.

Table 2. Test base characteristics

The time it took to perform 7 standard operations with objects in the database was measured. Each test was performed 10 times and the average was obtained. Measurements were taken using a thick client via local network. The client was installed on workstation running Windows 7. We also tried to run tests from a client installed on Ubuntu Linux, but it did not work stably and it was decided to run all tests only from a client on Windows.

Table 3. ResultsAPDEX

Key operation		Execution time in seconds			Deviation
		Stand No. 2 (MSSQL )	Stand No. 1 (Free Software)
Opening a document	Customer order
Carrying out documents	Customer order
Posting a new document	Document object: Customer order
Report generated	Income Expense Analysis
Report generated	List of batches of goods
Report generated	List of goods in warehouses
Report generated	Settlements with clients

On average, our real database when using MSSQL worked 45% faster than on a bench with free software. On some tests the gap was very significant, but on others, for example, conducting a new document it was only 11%.

Conclusion:

1C on the MSSQL DBMS it works approximately 1.5 times faster than on PostgreSQL. Accordingly, if it is possible to buy or rent MSSQL licenses, it is better to use it for higher performance. For small and light databases, you can try using the MSSQL Express version. We have not conducted tests with it, so it can show itself in terms of performance either better or worse than PostgreSQL. This edition is limited to using 1 processor and 1 GB of RAM, and also does not work with databases larger than 10 GB. If the database grows to this size, then it will stop working completely, but as practice shows, if there are 15-20 users in the database, then you can work comfortably with a database size of 4-5GB, then the database begins to slow down greatly.

Evaluation by the Gilev test shows an extremely slight superiority of MSSQL, which allows us to make the assumption that other 1C databases can work on PostgreSQL just as well as on MSSQL, and possibly faster. Before choosing a DBMS, we recommend running tests on your specific database and comparing the results.

Usage PostgreSQL DBMS for deployment on it, 1C is an acceptable solution in conditions of a limited budget. The database will not work as fast as on MSSQL, but you do not need to pay for licenses.

At the end of 2017, we conducted new tests and published them in another article.

Another way to save money when using 1C is to rent a 1C server.

System integration. Consulting

Relational databases have been used for a long time. They have become popular thanks to control systems that implement relational model so good that she is in the best possible way working with data, especially for mission-critical applications and services.

MySQL has been around for a long time and has proven itself to be an excellent solution, Postgresql came to the market around the same time, but provides quite a lot interesting features and opportunities, thanks to which it is rapidly gaining popularity. In this article we will try to compare MySQL vs Postgresql, compare the main differences between these systems, find out how they work and try to understand which system will be better for your project.

Database management systems

Databases are designed for structured storage and quick access to various data. Each database, in addition to the data itself, must have a specific operating model according to which data processing will be performed. To manage databases, DBMS or database management systems are used, such programs include MySQL and Postgresql.

Relational database management systems allow data to be placed in tables by linking rows from different tables and thus linking different, logically combined data. Before you can save data, you need to create tables certain size and specify the data type for each column. Columns represent data fields, and the data itself is placed in rows. Both database management systems, MySQL vs Postgresql, are relational. Next we will look in more detail at how both programs differ. Now let's move on to a more detailed consideration.

Short story

MySQL

MySQL development began back in the 90s. The first internal release of the database took place in 1995. During this time, several companies were developing the program. Development was started by the Swedish company MySQL AB, which was acquired by Sun Microsystems, which itself became the property of Oracle. At the moment, since 2010, Oracle has been developing it.

Postgresql

Development of Postrgresql began back in 1986 at the University of California, Berkeley. Development lasted almost eight years, then the project was divided into two parts - the commercial IIlustra database and the entire free project Postrgesql, which is developed by enthusiasts.

Data storage

MySQL

MySQL is a relational database; various engines are used to store data in tables, but working with the engines is hidden in the system itself. The engine does not affect the syntax of requests and their execution. The main engines supported are MyISAM, InnoDB, MEMORY, Berkeley DB. They differ in the way data is written to the disk, as well as in the methods of reading it.

Postgresql

Postgresql is an object-relational database that runs on only one engine - the storage engine. All tables are represented as objects, they can be inherited, and all actions with tables are performed using objective-oriented functions. As in MySQL, all data is stored on disk, in specially sorted files, but the structure of these files and the records in them is very different.

SQL standard

Regardless of the database management system used, SQL is a standardized query language. And it is supported by all solutions, even MySQL or Postgresql. The SQL standard was developed in 1986 and during this time several versions have already been released.

MySQL

MySQL does not support all the new features of the SQL standard. The developers chose this development path to keep MySQL easy to use. The company tries to meet standards, but not at the expense of simplicity. If some feature can improve usability, then developers can implement it as their own extension without paying attention to the standard.

Postgresql

Postgresql is an open source project source code, it is developed by a team of enthusiasts and the developers try to comply with the SQL standard as much as possible and implement all the latest standards. But all this comes at the expense of simplicity. Postgresql is very complex and because of this it is not as popular as MySQL.

Processing capabilities

Other differences between postgresql and mysql emerge from the previous paragraph: data processing capabilities and limitations. Naturally, compliance with newer standards brings newer capabilities.

MySQL

When executing a request, MySQL loads the entire server response into the client's memory; for large amounts of data this may not be very convenient. Basically, Postgresql is superior to Mysql in terms of functions; we’ll look at which ones next.

Postgresql

Postgresql supports the use of cursors to navigate through received data. You only get a pointer; the entire response is stored in the memory of the database server. This pointer can be saved between sessions. It supports building indexes for several table columns at once. In addition, indexes can be various types, in addition to hash and b-tree, GiST and SP-GiST are available for working with cities, GIN for text search, BRIN and Bloom.

Postgresql supports regular expressions in queries, recursive queries and table inheritance. But there are several restrictions, for example, you can only add a new field at the end of the table.

Performance

Databases must be optimized for the environment in which you will work. Historically, MySQL focused on maximum performance, and Postgresql was developed as a database with a large number of settings and as much as possible compliant with the standard. But over time, Postgresql has received many improvements and optimizations.

MySQL

In most cases, an InnoDB table is used to organize work with a database in MySQL; this table is a B-tree with indexes. Indexes allow you to retrieve data from disk very quickly and require fewer disk operations. But scanning a tree requires finding two indexes, and this is already slow. All this means that MySQL will be faster than Postgresql only when using a primary key.

Postgresql

All Postgresql table header information is located in random access memory. You can't create a table that is out of memory. Table entries are sorted by index, so you can retrieve them very quickly. For greater convenience, you can apply multiple indexes to one table.

In general, PostgreSQL is faster, with the exception of using primary keys. Let's look at some tests with different operations:

Data types

One of the highlights of both databases is the supported data types that you can use. Since both solutions try to match SQL syntax, they have similar sets, but still differ in some ways.

MySQL

MySQL supports the following data types:

• TINYINT: very small integer.;
• SMALLINT: small whole;
• MEDIUMINT: medium sized whole;
• INT: 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: date and time combination;
• TIMESTAMP: timestamp;
• TIME: time;
  YEAR: year in YY or YYYY format;
• CHAR: fixed-size string, right-padded with spaces to maximum length;
• VARCHAR: variable length string;
• TINYBLOB, TINYTEXT: binary or text data with a maximum length of 255 characters;
• BLOB, TEXT: binary or text data with a maximum length of 65535 characters;
• MEDIUMBLOB, MEDIUMTEXT: text or binary data;
• LONGBLOB, LONGTEXT: text or binary data maximum 4294967295 characters long;
• ENUM: enumeration;
• SET: multitudes.

Postgresql

The supported field types in Postgresql are quite different, but they allow you to record exactly the same data:

• bigint: signed 8-byte integer;
• bigserial: auto-incremented 8-byte integer;
• bit: fixed length binary string;
• bit varying: variable length binary string;
• boolean: flag;
• box: rectangle on a plane;
• byte: binary data;
• character varying: fixed length character string;
• character:
• cidr: IPv4 or IPv6 network address;
• circle: circle on a plane;
• date: calendar date;
• double precision: double precision floating point number;
• inet: Internet address IPv4 or IPv6;
• integer: signed 4-byte integer;
• interval: period;
• line: an infinite straight line on a plane;
• lseg: 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: two-byte integer;
• serial: automatically incremented four-bit integer;
• text: variable length character string;
• time: Times of Day;
• timestamp: date and time;
• tsquery: text search query;
• tsvector: text search document;
• uuid: unique identificator;
• xml: XML data.

As you can see, there are more data types in Postgresql and they are more diverse; there are field types for certain types of data that MySQL does not have. The difference between MySQL and Postgresql is obvious.

Development

Both projects are open source, but are developed differently. Not everyone likes the development of MySQL. And this is where a comparison of mysql and postgresql gives many differences.

MySQL

Base MySQL data is being developed by Oracle and there are rumors that the company intends to slow down the development of the engine. A lot of forks of the project were created, including a fork of MariaDB from the developer of the original MySQL. But still development remains slow.

Postgresql

As mentioned at the beginning of the article, development began at the University of Berkeley. Then she moved to a commercial company. The program is currently being developed by an independent group of programmers and the board of several companies. New versions are released quite actively and receive more and more new features.

Blog of the Mail.ru Group company

In anticipation of my talk at PGCONF.RUSSIA 2015, I will share some observations about the important differences between MySQL DBMS and PostgreSQL. This material will be useful to all those who are no longer satisfied with the possibilities and MySQL features, as well as those who are taking their first steps in Postgres. Of course, this post should not be considered as an exhaustive list of differences, but it will be quite sufficient to make a decision in favor of one or another DBMS.

Replication

The topic of my report is “Asynchronous replication without censorship, or why PostgreSQL will conquer the world,” and replication is one of the most painful topics for busy projects using MySQL. There are many problems - correct operation, stability, performance - and at first glance they look unrelated. If we look at the historical context, we get an interesting conclusion: MySQL replication has so many problems because it was not thought out, and the point of no return was support for the storage engine (plug-in engines) without answers to the questions “what to do with the log?” and “how different storage engines can participate in replication.” In 2004, in the PostgreSQL mailing list, a user tried to “find” the storage engine in the PostgreSQL source code and was very surprised that they were not there. During the discussion, someone suggested adding this feature to PostgreSQL, and one of the developers replied, “Guys, if we do this, we will have problems with replication and transactions between engines.”

The problem is that many storage management systems... often do their own WAL and PITR. Some do their own buffer management, locking and replication/load management too. So, as you say, its hard to say where an interface should be
abstracted.

link to this letter in postgresql mailing list

More than 10 years have passed, and what do we see? MySQL has annoying problems with transactions between tables in different storage engines and MySQL has problems with replication. Over these ten years, PostgreSQL has added plug-in data types and indexes, and also has replication - that is, the advantage of MySQL has been leveled, while the architectural problems of MySQL remain and interfere with life. MySQL 5.7 tried to solve the replication performance problem by parallelizing it. Since the project at work is very sensitive to replication performance due to its scale, I tried to test if it got any better. I found that parallel replication in 5.7 is slower than single-threaded replication in 5.5, and only in some cases - about the same. If you are currently using MySQL 5.5 and want to upgrade to a more recent version, please note that migration is not possible for highly loaded projects, since replication will simply no longer keep up.

After the highload talk, Oracle took note of the test I developed and said they would try to fix the problem; recently they even wrote to me that they were able to see parallelism in their tests, and sent me the settings. If I'm not mistaken, with 16 threads there was a slight acceleration compared to the single-threaded version. Unfortunately, I have not yet repeated my tests on the provided settings - in particular because with such results our problems still remain relevant.

The exact reasons for this performance regression are unknown. There were several assumptions - for example, Christian Nelsen, one of the MariaDB developers, wrote on his blog that there may be problems with the performance scheme and thread synchronization. Because of this, there is a regression of 40%, which is visible in regular tests. Oracle developers refute this, and they even convinced me that it doesn’t exist; apparently, I see some other problem (and how many of them are there?).

In MySQL replication, problems with the storage engine are aggravated by the selected replication level - they are logical, while in PostgreSQL they are physical. In principle, logical replication has its advantages; it allows you to do more interesting things, I will also mention this in the report. But PostgreSQL, even within the framework of its physical replication, already reduces all these advantages to nothing. In other words, almost everything that is in MySQL can already be done in PostgreSQL (or will be possible in the near future).

You can't hope to implement low-level physical replication in MySQL. The problem is that instead of one log (as in PostgreSQL), there are two or four of them, depending on how you count them. PostgreSQL simply commits queries, they go to a log, and this log is used in replication. PostgreSQL replication is super stable because it uses the same log as failover operations. This mechanism has been written for a long time, well tested and optimized.

In MySQL the situation is different. We have a separate InnoDB log and a replication log, and we need to commit both. And this is a two-phase commit between journals, which by definition is slow. That is, we cannot simply say that we are repeating a transaction from the InnoDB log - we have to figure out what kind of request it is and run it again. Even if this is logical replication, at the line level, then these lines need to be looked for in the index. And not only do you have to do a lot of work to execute the query, but it will again be written to its InnoDB log on the replica, which is clearly not good for performance.

In PostgreSQL, in this sense, the architecture is much more thoughtful and better implemented. It recently announced a feature called Logical Decoding - which allows you to do all sorts of interesting things, which are very difficult to do within a physical journal. In PostgreSQL, this is an add-on on top, logical decoding allows you to work with a physical log as if it were a logical one. It is this functionality that will soon remove all the advantages of MySQL replication, except perhaps the log size - statement-based MySQL replication will win - but statement-based MySQL replication has completely wild problems in the most unexpected places, and should not be considered good decision(I will also talk about all this in the report).

In addition, there is trigger replication for PostgreSQL - this is Tungsten, which allows you to do the same thing. Trigger replication works as follows: triggers are set, they fill tables or write files, the result is sent to the replica and applied there. It is through Tungsten, as far as I know, that they migrate from MySQL to PostgreSQL and vice versa. In MySQL, logical replication works directly at the engine level, and it is no longer possible to do it any other way.

Documentation

PostgreSQL has much better documentation. MySQL even formally seems to have it, but the meaning of individual options can be difficult to understand. It seems to be written what they do, but to understand how to configure them correctly, you need to use unofficial documentation and look for articles on this topic. Often you need to understand the MySQL architecture; without this understanding, the settings look like some kind of magic.

For example, this is how the Percona company “shot”: they ran MySQL Performance Blog, and there were many articles on this blog that covered specific aspects of using MySQL. This brought wild popularity, brought clients into consulting, and allowed us to attract resources to launch the development of our own Percona-Server fork. The existence and popularity of the MySQL Performance Blog proves that official documentation is simply not enough.

PostgreSQL actually has all the answers in its documentation. On the other hand, I have heard a lot of criticism when comparing the PostgreSQL documentation with the “grown-up” Oracle. But this is actually a very important indicator. No one is trying to compare MySQL with the adult Oracle at all - that would be funny and absurd - but PostgreSQL is already starting to be compared quite seriously, the PostgreSQL community hears this criticism and is working to improve the product. This suggests that in its capabilities and performance it is beginning to compete with such a powerful system as Oracle, which is used by mobile operators and banks, while MySQL remains in the niche of websites. And giant projects that have grown to a large amount of data and users slurp up MySQL with a big spoon, constantly running into its limitations and architectural problems that cannot be corrected by spending a reasonable amount of effort and time.

An example of such large projects on PostgreSQL is 1C: PostgreSQL comes as an option instead of Microsoft SQL, and Microsoft SQL is truly a fantastic DBMS, one of the most powerful. PostgreSQL can replace MS SQL, and trying to replace it with MySQL... let's lower the curtain of pity over this scene, as Mark Twain wrote.

Standards

PostgreSQL complies with SQL-92, SQL-98, SQL-2003 (all reasonable parts of it are implemented) and is already working on SQL-2011. This is very cool. In comparison, MySQL doesn't even support SQL-92. Some will say that in MySQL such a goal was simply not set by the developers. But you need to understand that the difference between versions of the standard is not minor changes - these are new functionality. That is, at the moment when MySQL said: “We will not follow the standard,” they were not only introducing some minor differences that made MySQL difficult to support, they were also closing the door to the implementation of many necessary and important features. There is still no proper optimizer. What is called optimization there is called “parser” plus normalization in PostgreSQL. In MySQL, this is just a query execution plan, without separation. And MySQL will not come to support standards very soon, since there is a burden pressing on them backward compatibility. Yes, they want it, but in five years, maybe they will have something. PostgreSQL already has everything now.

Performance and administrative complexity

From point of view you just administration comparison is not in favor of PostgreSQL. MySQL is much easier to administer. And not because in this sense it is better thought out, but simply knows how to do much less. Accordingly, it is easier to configure it.

MySQL has a problem with complex queries. For example, MySQL does not know how to lower a group into separate parts of union all. The difference between the two queries - in our example, grouping by individual tables and union all on top worked 15 times faster than union all and then grouping, although the optimizer must bring both queries into the same, efficient query execution plan. We will have to generate such requests manually - that is, waste developers' time on what the database should do.

The “simplicity” of MySQL results, as can be seen above, from extremely poor capabilities - MySQL simply works worse and requires more time and effort during development. In contrast, PostrgreSQL has histograms and a normal optimizer, and will execute such queries efficiently. But if there are histograms, then there are their settings - at least bucket size. You need to know about the settings and change them in some cases - therefore, you need to understand what this setting is, what it is responsible for, be able to recognize such situations, and see how to choose the optimal parameters.

Occasionally it happens that PostrgreSQL's skill can hinder rather than help. 95% of the time everything works fine - better than MySQL - but one stupid query runs much slower. Or everything works well, and then suddenly (from the user’s point of view) as the project grows, some queries began to work poorly (there was more data, a different query execution plan began to be selected). Most likely, to fix it, just run analyze or tweak the settings a little. But you need to know what to do and how to do it. At a minimum, you need to read the PostgreSQL documentation on this topic, but for some reason they don’t like to read documentation. Maybe because it is of little help in MySQL? :)

I would like to emphasize that PostgreSQL is no worse in this sense, it just allows you to postpone problems, while MySQL immediately throws them out and you have to spend time and money solving them. In this sense, MySQL always works consistently poorly, and even at the development stage people take these features into account: they do everything as much as possible in a simple way. This applies only to productivity, more precisely, to the methods of achieving it and to its predictability. In terms of correctness and convenience, PostgreSQL is head and shoulders above MySQL.

So what should you choose?

To decide between MySQL and PostgreSQL for a specific project, you first need to answer other questions.

First, what experience does the team have? If the entire team has 10 years of experience working with MySQL and needs to get up and running as quickly as possible, then it’s not a fact that it’s worth changing a familiar tool to an unfamiliar one. But if deadlines are not critical, then PostgreSQL is worth trying.

Secondly, we must not forget about operational problems. If you do not have a highly loaded project, then from a performance point of view there is no difference between these two DBMSs. But PostgreSQL has another important advantage: it is more strict, does more checks for you, gives you less opportunity to make mistakes, and this is a huge advantage in the long term. For example, in MySQL you have to write your own tools to verify the regular referential integrity of the database. And even with this there can be problems. In this sense, PostgreSQL is a more powerful, more flexible tool, and it’s more pleasant to develop on it. But this largely depends on the experience of the developer.

To summarize: if you have a simple online store, no money for an admin, no serious ambitions to grow into big project and have experience with MySQL, then take MySQL. If you assume that the project will be popular, if it is large, it will be difficult to rewrite it, if it complex logic and relationships between tables - take PostgreSQL. Even out of the box it will work for you, will help you in development, will save time, and will make it easier for you to grow.

• Translation

Today let's talk about the advantages of Postgres over other open source systems. We will definitely cover this topic in more detail at PG Day"16 Russia, which is only two months away.

You may be asking yourself, “Why PostgreSQL?” After all, there are other open source relational database options (for the purposes of this article we looked at MySQL, MariaDB and Firebird), so what can Postgres offer that they don’t? PostgreSQL's tagline states that it is "The World's Most Advanced Open Source Database." We will give several reasons why Postgres makes such statements.

In the first part of this series, we will talk about data storage - model, structure, types and size limitations. And in the second part we will focus more on sampling and data manipulation.

Data model

PostgreSQL is not just a relational, but an object-relational DBMS. This gives it some advantages over other open source SQL databases such as MySQL, MariaDB and Firebird.

The fundamental characteristic of an object-relational database is its support custom objects and their behaviors, including data types, functions, operations, domains, and indexes. This makes Postgres incredibly flexible and reliable. Among other things, it can create, store and retrieve complex data structures. In some of the examples below, you will see nested and compound constructs that are not supported by standard RDBMSs.

Structures and data types

There is an extensive list of data types that Postgres supports. In addition to numeric, float, text, boolean and other expected data types (and many variations thereof), PostgreSQL boasts support for uuid, monetary, enumerated, geometric, binary, network addresses, bit strings, text search, xml, json, arrays, composite types and ranges, as well as some internal types for identifying objects and logging locations. To be fair, MySQL, MariaDB and Firebird also have some of these data types, but only Postgres supports them all.

Let's take a closer look at some of them:

Network addresses

PostgreSQL provides storage different types network addresses. The CIDR (Classless Internet Domain Routing) data type follows the convention for IPv4 and IPv6 network addresses. Here are some examples:

• 192.168.100.128/25
• 10.1.2.3/32
• 2001:4f8:3:ba:2e0:81ff:fe22:d1f1/128
• ::ffff:1.2.3.0/128

Also available for storing network addresses is the INET data type, used for IPv4 and IPv6 hosts, where subnets are optional. The MACADDR data type can be used to store MAC addresses for hardware identification, such as 08-00-2b-01-02-03.

MySQL and MariaDB also have INET functions for converting network addresses, but they do not provide data types for storing network addresses internally. Firebird also does not have types for storing network addresses.

Multidimensional arrays

Since Postgres is an object-relational database, arrays of values ​​can be stored for most existing types data. This can be done by adding square brackets to the data type specification for the column or by using the ARRAY expression. The size of the array can be specified, but is not required. Let's look at a holiday picnic menu to demonstrate the use of arrays:

We create a table whose values ​​are arrays CREATE TABLE holiday_picnic (holiday varchar(50) -- string value sandwich text, -- array side text , -- multidimensional array dessert text ARRAY, -- array beverage text ARRAY -- array of 4- x elements); -- insert array values ​​into the table INSERT INTO holiday_picnic VALUES ("Labor Day", "("roast beef","veggie","turkey")", "( ("potato salad","green salad","macaroni salad "), ("chips","crackers") )", "("fruit cocktail","berry pie","ice cream")", "("soda","juice","beer","water ")");
MySQL, MariaDB, and Firebird can't do this. To store such arrays of values ​​in traditional relational databases, you would have to use a workaround and create a separate table with rows for each of the array values.

Geometric data

Location data is quickly becoming a core requirement for many applications. PostgreSQL has long supported many geometric data types such as points, lines, circles, and polygons. One of these types is PATH, which consists of many sequential points and can be open (start and end points are not connected) or closed (start and end points are connected). Let's take a hiking trail as an example. IN in this case The hiking trail is a loop, so the start and end points are connected, which means my path is closed. Round brackets around a set of coordinates indicate a closed path, while square brackets indicate an open path.

Create a table to store trails CREATE TABLE trails (trail_name varchar(250), trail_path path); -- insert the trail into the table, -- for which the route is determined by coordinates in latitude-longitude format INSERT INTO trails VALUES ("Dool Trail - Creeping Forest Trail Loop", ((37.172,-122.22261666667), (37.171616666667,-122.22385), ( 37.1735,-122.2236), (37.175416666667,-122.223), (37.1758,-122.22378333333), (37.179466666667,-122.22866666667), (37.18395,-122.2267 5), (37.180783333333,-122.22466666667), (37.176116666667,-122.2222), (37.1753 ,-122.22293333333), (37.173116666667,-122.22281666667)));
The PostGIS extension for PostgreSQL extends existing geometric data properties with auxiliary spatial types, functions, operators, and indexes. It provides location support and supports both raster and vector data. It also provides compatibility with a variety of third-party geospatial tools (copyrighted and open source) for displaying, rendering, and working with data.

Note that MySQL 5.7.8 and MariaDB since version 5.3.3 have added data type extensions to support the OpenGIS geographic information standard. This MySQL version and subsequent versions of MariaDB offer data type storage similar to native Postgres geodata. However, in MySQL and MariaDB, data values ​​must first be converted to geometric format simple commands before being inserted into the table. Firebird does not currently support geometric data types.

JSON support

JSON support in PostgreSQL allows you to move to storing schema-less data in an SQL database. This can be useful when the data structure requires some flexibility: for example, if the structure is still changing during development or it is not known what fields the data object will contain.

Type JSON data provides JSON validation that allows you to use specialized JSON operators and functions built into Postgres to perform queries and manipulate data. Also available is the JSONB type, a binary variant of the JSON format that removes spaces, does not preserve the sorting of objects, instead stores them in the most optimal way, and only stores the last value for duplicate keys. JSONB is usually the preferred format because it requires less object space, can be indexed, and is faster to process because it doesn't require repeated parsing.

MySQL 5.7.8 and MariaDB 10.0.1 added support for native JSON objects. But while there are many functions and operators for JSON that are now available in these databases, they are not indexed in the same way as JSONB in ​​PostgreSQL. Firebird has not yet joined the trend and supports JSON objects only in text form.

Creating a new type

If you happen to find Postgres' extensive list of data types not enough, you can use the CREATE TYPE command to create new data types such as composite, enumerated, range, and basic. Let's look at an example of creating and sending requests of a new composite type:

Create a new composite type "wine" CREATE TYPE wine AS (wine_vineyard varchar(50), wine_type varchar(50), wine_year int); -- create a table that uses the composite type "wine" CREATE TABLE pairings (menu_entree varchar(50), wine_pairing wine); -- insert data into the table using the expression ROW INSERT INTO pairings VALUES ("Lobster Tail",ROW("Stag""s Leap","Chardonnay", 2012)), ("Elk Medallions",ROW("Rombauer", "Cabernet Sauvignon", 2012)); /* select from a table using the column name (use parentheses separated by a dot from the field name in a composite type) */ SELECT (wine_pairing).wine_vineyard, (wine_pairing).wine_type FROM pairings WHERE menu_entree = "Elk Medallions";
Because they are not object-relational, MySQL, MariaDB and Firebird do not provide such powerful functionality.

Data Dimensions

PostgreSQL can handle a lot of data. Current published restrictions are listed below:

In Compose [approx. per.: the organization in which the author of the original article works] we automatically scale your installation so that you do not have to worry about the growth of the amount of data. But as any database administrator knows, you should be wary of too many or too many options. We recommend using common sense when creating tables and adding indexes.

In comparison, MySQL and MariaDB are notorious for their row size limit of 65,535 bytes. Firebird also offers only 64Kb as the maximum line size. Usually the amount of data is limited maximum size files operating system. Because PostgreSQL can store tabular data in many smaller files, it can work around this limitation. But it's worth noting that too many files can negatively impact performance. MySQL and MariaDB support more columns per table (up to 4,096 depending on the data type) and larger individual table sizes than PostgreSQL, but the need to exceed the existing Postgres limits arises only in extremely rare cases.

Data integrity

Postgres strives to be ANSI-SQL:2008 compliant, ACID (Atomicity, Consistency, Isolation and Durability) compliant, and is known for its referential and transactional integrity. Primary keys, constraint and cascading foreign keys, unique constraints, NOT NULL constraints, check constraints, and other data integrity features ensure that only valid data is retained.

MySQL and MariaDB are working more towards conforming to the SQL standard with InnoDB/XtraDB table engines. They now offer a STRICT option using SQL modes that sets up validity checks on the data being used. However, depending on which mode you use, invalid and even truncated data without your knowledge may be inserted or created during the update. None of these databases currently support CHECK restrictions. In addition, they have many features regarding referential integrity constraints. foreign keys. In addition to the above, data integrity can be significantly compromised depending on the storage engine chosen. MySQL (and the MariaDB fork) have made no secret of the fact that they trade integrity and standards compliance for speed and efficiency.

Summing up

Postgres has many features. Built using the object-relational model, it supports complex structures and a wide range of built-in and user-defined data types. It provides expanded data capacity and is trusted caring attitude to data integrity. You may not need all of the advanced storage features we've explored in this article, but since your needs can quickly add up, there's a definite benefit to having it all at your fingertips.

If you feel that PostgreSQL does not suit your needs, or you prefer to “shoot from the hip”, then you may want to take a look at the NoSQL databases we offer at Compose, or consider others SQL databases the data we mentioned. Each of them has its own advantages. Compose firmly believes that it is very important to choose the right database for a given task... sometimes that means choosing multiple databases!

Want more Postgres?