PostgreSQL is better than other SQL databases open source. Part 1
Today let's talk about the benefits of Postgres over other open source systems. This topic we will reveal in more detail on PG Day'16 Russia, which remained for only two months.
You may be asking yourself: "Why PostgreSQL?" After all, there are other options relational databases and open source (in this article, we have considered MySQL, MariaDB, Firebird), so what's Postgres can offer that I don't have them? In the PostgreSQL slogan declares that it is "the Most advanced database of open source in the world." We give a few reasons why Postgres makes such statements.
In the first part of this series we will talk about storing data models, structure, types and size limits. And in second part more focus on sample and data manipulations.
the
PostgreSQL is not just relational, and object-relational DBMS. This gives it some advantages over other SQL databases, open source such as MySQL, MariaDB and Firebird.
A fundamental characteristic of object-relational databases is the support of custom objects and their behavior, including data types, functions, operations, domains, and indexes. It makes Postgres incredibly flexible and reliable. Among other things, he is able to create, store and retrieve complex data structures. Some of the examples below you will see nested and compound structures, which are not supported by a standard RDBMS.
the
There is an extensive list of data types supported by Postgres. In addition to the numeric, float, text, Boolean and other expected data types (and many variations), PostgreSQL boasts support for uuid, money transferred, geometric, binary types, network addresses, bit strings, text search, xml, json, arrays, composite types and ranges, as well as some internal types for object identification and location logs. It is fair to say that MySQL, MariaDB and Firebird also have some of these data types, but only Postgres supports them all.
Let's check some of them:
PostgreSQL provides storage for various types of network addresses. The data type of CIDR (classless Internet routing domain, Classless Internet Domain Routing) should agreement for network addresses IPv4 and IPv6. Here are some examples:
the
For storing network addresses available the INET data type, used for both IPv4 and IPv6 hosts where the subnet are optional. The data type MACADDR may be used to store MAC addresses to identify equipment such as 08-00-2b-01-02-03.
The MySQL and MariaDB also have INET function to convert network addresses, but they don't provide data types for internal storage of network addresses. At Firebird, too, there are types to store network addresses.
Since Postgres is an object — relational database, arrays of values can be stored, most of the existing data types. You can do this by adding square brackets to the specification of the data type of the column or by using the expression ARRAY. The array size can be specified, but this is optional. Let's look at a menu for the gala picnic to demonstrate the use of arrays:
the
MySQL, MariaDB, Firebird and do not know how. To store such arrays of values in traditional relational databases, you will have to use a workaround and create a separate table with a row for each value of the array.
GEODATA are rapidly becoming a basic requirement for many applications. PostgreSQL has long supported many geometric data types such as points, lines, circles, and polygons. One of these types – PATH, it consists of many consecutive points and can be open (the start and end points not connected) or closed (start and end points are connected). Let us consider as an example the tourist trail. In this case, the trail is a loop, so the start and end points are connected, and so my path is closed. Parentheses around a set of coordinates point to a closed path, and the square — at the open.
the
The PostGIS extension for PostgreSQL complements the existing properties of a geometric data auxiliary spatial types, functions, operators and indexes. It provides support for the location and supports both raster and vector data. It also provides compatibility with many third-party geospatial tools (copyrighted and open source) for displaying, rendering and manipulating data.
Note that 5.7.8 in MySQL and MariaDB since version 5.3.3, was added extending the data types to support the standard geographic information OpenGIS. This MySQL version and subsequent versions of MariaDB offer storage of data types, similar to regular geo-data of Postgres. However, in MySQL and MariaDB data values must first be converted into a geometric format with a simple command before being inserted into the table. Firebird currently doesn't support geometric data types.
JSON support in PostgreSQL allows you to jump to the storage of schema-less data in a SQL database. This can be useful when the data structure requires some flexibility: for example, if in the process of developing the structure still changes, or even know what fields will contain the data object.
A JSON data type provides validation of JSON, which allows you to use a specialized JSON operators and functions built into Postgres to query and manipulate data. Also available type JSONB — binary version of JSON, in which spaces are removed, sorting of the objects is not retained, instead they are stored in the best way, and saved only the last value for a key with duplicates. JSONB usually is the preferred format because it requires less space for objects that may be indexed and processed faster as it does not require re-parsing.
5.7.8 in MySQL and MariaDB 10.0.1 was added, support of embedded JSON objects. But, although there are many functions and operators for JSON, which are now available in these databases, they are not indexed as JSONB in PostgreSQL. Firebird has not yet joined the trend and supports JSON objects only as text.
If suddenly so happens that the extensive list of data types of Postgres isn't sufficient, you can use CREATE TYPE to create new data types such as integral, enumerable, range, and base. Consider the example of creating and sending requests of a new composite type:
the
Because they are not object-relational, MySQL, MariaDB, and Firebird do not provide this powerful functionality.
the
PostgreSQL can handle a lot of data. Current published limits are listed below:
the
To Compose [approx. lane: the organization, which employs the author of the original article] we automatically scale your installation, so you don't have to worry about the growing number of data. But as you know any database administrator, should be treated with caution too large and unlimited possibilities. We suggest to use common sense when creating tables and adding indexes.
For comparison, MySQL and MariaDB are notorious for limiting the row size to 65535 bytes. Firebird also provides only 64K as the maximum size of the string. Usually the amount of data is limited by the maximum file size your operating system. Because PostgreSQL is able to store tabular data in many smaller files, it can circumvent this limitation. But it is worth noting that a very large number of files can adversely affect performance. MySQL and MariaDB support a larger number of columns in the table (up to 4,096 depending on the data type) and large individual dimensions of a table than PostgreSQL, but the need to exceed the current limitations of Postgres occurs only in extremely rare cases.
the
Postgres tends to conform to the ANSI-SQL:2008 meets the requirements of ACID (atomicity, consistency, isolation and durability) and is known for its referential and transactional integrity. Primary keys, bounding and cascading foreign keys, unique constraints, NOT NULL constraints, check constraints, and other functions ensure data integrity ensures that only correct data will be saved.
MySQL and MariaDB are working longer to meet the standard SQL engines tables InnoDB/XtraDB. Now they offer the option STRICT using SQL, which establishes the validation data used. Despite this, depending on which mode you use, unreliable and even trimmed without your knowledge data can be inserted or created during the upgrade. None of these databases does not currently support CHECK constraints. In addition, they have many features in relation to the referential integrity constraints on foreign keys. In addition to the above, data integrity can be greatly compromised depending on the selected backend storage. MySQL (and MariaDB fork), make no secret of the fact that exchanged the integrity and standards of speed and efficiency.
the
Postgres many opportunities. Created using the object-relational model, it supports complex structures and a wide range of built-in and user-defined data types. It provides the extended capacity of the data and has earned the trust of respect for the integrity of the data. You may not need all those advanced features data storage, which we explored in this article, but since needs can quickly increase, there is a definite advantage to have it all at hand.
If you think that PostgreSQL doesn't meet your needs, or you prefer to “shoot from the hip”, then you should pay attention to the NoSQL databases that we offer to Compose, or to think of other SQL databases that we mentioned. Each of them has its advantages. Compose firmly believe that it is very important to choose the right database for a particular problem... sometimes it means that you need to choose multiple databases!
Want more Postgres? second part of this series we will look at data manipulation and search in PostgreSQL, including virtual tables, querying, indexing, and language extensions.
Article based on information from habrahabr.ru
You may be asking yourself: "Why PostgreSQL?" After all, there are other options relational databases and open source (in this article, we have considered MySQL, MariaDB, Firebird), so what's Postgres can offer that I don't have them? In the PostgreSQL slogan declares that it is "the Most advanced database of open source in the world." We give a few reasons why Postgres makes such statements.
In the first part of this series we will talk about storing data models, structure, types and size limits. And in second part more focus on sample and data manipulations.
the
data Model
PostgreSQL is not just relational, and object-relational DBMS. This gives it some advantages over other SQL databases, open source such as MySQL, MariaDB and Firebird.
A fundamental characteristic of object-relational databases is the support of custom objects and their behavior, including data types, functions, operations, domains, and indexes. It makes Postgres incredibly flexible and reliable. Among other things, he is able to create, store and retrieve complex data structures. Some of the examples below you will see nested and compound structures, which are not supported by a standard RDBMS.
the
Structure and data types
There is an extensive list of data types supported by Postgres. In addition to the numeric, float, text, Boolean and other expected data types (and many variations), PostgreSQL boasts support for uuid, money transferred, geometric, binary types, network addresses, bit strings, text search, xml, json, arrays, composite types and ranges, as well as some internal types for object identification and location logs. It is fair to say that MySQL, MariaDB and Firebird also have some of these data types, but only Postgres supports them all.
Let's check some of them:
Network address
PostgreSQL provides storage for various types of network addresses. The data type of CIDR (classless Internet routing domain, Classless Internet Domain Routing) should agreement for network addresses IPv4 and IPv6. Here are some examples:
the
-
the
- 192.168.100.128/25 the
- 10.1.2.3/32 the
- 2001:4f8:3:ba:2e0:81ff:fe22:d1f1/128 the
- ::ffff:1.2.3.0/128
For storing network addresses available the INET data type, used for both IPv4 and IPv6 hosts where the subnet are optional. The data type MACADDR may be used to store MAC addresses to identify equipment such as 08-00-2b-01-02-03.
The MySQL and MariaDB also have INET function to convert network addresses, but they don't provide data types for internal storage of network addresses. At Firebird, too, there are types to store network addresses.
Multidimensional arrays
Since Postgres is an object — relational database, arrays of values can be stored, most of the existing data types. You can do this by adding square brackets to the specification of the data type of the column or by using the expression ARRAY. The array size can be specified, but this is optional. Let's look at a menu for the gala picnic to demonstrate the use of arrays:
the
-- create a table whose values are arrays
CREATE TABLE holiday_picnic (
holiday varchar(50) -- the string value
sandwich text[], -- array of
side text[] [], -- multi-dimensional array
dessert text ARRAY -- array
beverage text ARRAY[4] -- array of 4 elements
);
-- insert the values of the arrays in the table
('Labor Day',
'{"roast beef","veggie","turkey"}',
'{
{"potato salad","green salad","salad macaroni"},
{"chips","crackers"}
}',
'{"fruit cocktail","berry pie","ice cream"}',
'{"soda","juice","beer","water"}'
);
MySQL, MariaDB, Firebird and do not know how. To store such arrays of values in traditional relational databases, you will have to use a workaround and create a separate table with a row for each value of the array.
Geometric data
GEODATA are rapidly becoming a basic requirement for many applications. PostgreSQL has long supported many geometric data types such as points, lines, circles, and polygons. One of these types – PATH, it consists of many consecutive points and can be open (the start and end points not connected) or closed (start and end points are connected). Let us consider as an example the tourist trail. In this case, the trail is a loop, so the start and end points are connected, and so my path is closed. Parentheses around a set of coordinates point to a closed path, and the square — at the open.
the
-- create a table to store paths
CREATE TABLE trails (
trail_name varchar(250),
trail_path path
);
-- insert the path to the table
-- for which the route is determined by the coordinates in the format latitude-longitude
INSERT INTO calls 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.22675),
(37.180783333333,-122.22466666667),
(37.176116666667,-122.2222),
(37.1753,-122.22293333333),
(37.173116666667,-122.22281666667)));
The PostGIS extension for PostgreSQL complements the existing properties of a geometric data auxiliary spatial types, functions, operators and indexes. It provides support for the location and supports both raster and vector data. It also provides compatibility with many third-party geospatial tools (copyrighted and open source) for displaying, rendering and manipulating data.
Note that 5.7.8 in MySQL and MariaDB since version 5.3.3, was added extending the data types to support the standard geographic information OpenGIS. This MySQL version and subsequent versions of MariaDB offer storage of data types, similar to regular geo-data of Postgres. However, in MySQL and MariaDB data values must first be converted into a geometric format with a simple command before being inserted into the table. Firebird currently doesn't support geometric data types.
JSON Support
JSON support in PostgreSQL allows you to jump to the storage of schema-less data in a SQL database. This can be useful when the data structure requires some flexibility: for example, if in the process of developing the structure still changes, or even know what fields will contain the data object.
A JSON data type provides validation of JSON, which allows you to use a specialized JSON operators and functions built into Postgres to query and manipulate data. Also available type JSONB — binary version of JSON, in which spaces are removed, sorting of the objects is not retained, instead they are stored in the best way, and saved only the last value for a key with duplicates. JSONB usually is the preferred format because it requires less space for objects that may be indexed and processed faster as it does not require re-parsing.
5.7.8 in MySQL and MariaDB 10.0.1 was added, support of embedded JSON objects. But, although there are many functions and operators for JSON, which are now available in these databases, they are not indexed as JSONB in PostgreSQL. Firebird has not yet joined the trend and supports JSON objects only as text.
Create new type
If suddenly so happens that the extensive list of data types of Postgres isn't sufficient, you can use CREATE TYPE to create new data types such as integral, enumerable, range, and base. Consider the example of creating and sending requests of a new composite type:
the
-- 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 a composite type "wine"
CREATE TABLE pairings (
menu_entree varchar(50),
wine_pairing wine
);
-- insert data into the table using the formula ROW
INSERT INTO pairings of VALUES
(The'Lobster Tail',ROW('Stag"s Leap','Chardonnay', 2012)),
('Elk Medallions',ROW('Rombauer','Cabernet Sauvignon',2012));
/*
the sample from the table using the name column
(use parentheses separated by the point of the field name
in the 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 this powerful functionality.
the
Size of data
PostgreSQL can handle a lot of data. Current published limits are listed below:
| the Maximum database size | Unlimited |
| the Maximum table size | 32 TB |
| Maximum row size | 1.6 TB |
| Maximum field size | 1 GB |
| Maximum number of rows in the table | Unlimited |
| Maximum number of columns in a table | 250-1600 depending on column type |
| Maximum number of indexes per table | Unlimited |
To Compose [approx. lane: the organization, which employs the author of the original article] we automatically scale your installation, so you don't have to worry about the growing number of data. But as you know any database administrator, should be treated with caution too large and unlimited possibilities. We suggest to use common sense when creating tables and adding indexes.
For comparison, MySQL and MariaDB are notorious for limiting the row size to 65535 bytes. Firebird also provides only 64K as the maximum size of the string. Usually the amount of data is limited by the maximum file size your operating system. Because PostgreSQL is able to store tabular data in many smaller files, it can circumvent this limitation. But it is worth noting that a very large number of files can adversely affect performance. MySQL and MariaDB support a larger number of columns in the table (up to 4,096 depending on the data type) and large individual dimensions of a table than PostgreSQL, but the need to exceed the current limitations of Postgres occurs only in extremely rare cases.
the
data Integrity
Postgres tends to conform to the ANSI-SQL:2008 meets the requirements of ACID (atomicity, consistency, isolation and durability) and is known for its referential and transactional integrity. Primary keys, bounding and cascading foreign keys, unique constraints, NOT NULL constraints, check constraints, and other functions ensure data integrity ensures that only correct data will be saved.
MySQL and MariaDB are working longer to meet the standard SQL engines tables InnoDB/XtraDB. Now they offer the option STRICT using SQL, which establishes the validation data used. Despite this, depending on which mode you use, unreliable and even trimmed without your knowledge data can be inserted or created during the upgrade. None of these databases does not currently support CHECK constraints. In addition, they have many features in relation to the referential integrity constraints on foreign keys. In addition to the above, data integrity can be greatly compromised depending on the selected backend storage. MySQL (and MariaDB fork), make no secret of the fact that exchanged the integrity and standards of speed and efficiency.
the
Summing up
Postgres many opportunities. Created using the object-relational model, it supports complex structures and a wide range of built-in and user-defined data types. It provides the extended capacity of the data and has earned the trust of respect for the integrity of the data. You may not need all those advanced features data storage, which we explored in this article, but since needs can quickly increase, there is a definite advantage to have it all at hand.
If you think that PostgreSQL doesn't meet your needs, or you prefer to “shoot from the hip”, then you should pay attention to the NoSQL databases that we offer to Compose, or to think of other SQL databases that we mentioned. Each of them has its advantages. Compose firmly believe that it is very important to choose the right database for a particular problem... sometimes it means that you need to choose multiple databases!
Want more Postgres? second part of this series we will look at data manipulation and search in PostgreSQL, including virtual tables, querying, indexing, and language extensions.
