PostgreSQL, which are not in MySQL, and Vice versa

Many people are afraid to go with the "muscule to posgres" due to the fact that only vaguely understand what it will give. Some stops thought that maybe Postgres is too complex and requires a base of study. And that maybe something will lose in the transition. I'll try to clarify a bit.

Generally speaking, if someone is afraid of difficulty, it is possible to start to do as normally do: "vtupuyu" to change from MySQL to PostgreSQL without using the new features. SQL it and SQL in Africa, it's not rocket science. In such a transition, nothing complicated (with TZ. programming) is not for you. Well, other quotes, the syntax is a little stricter. I.e. use pg as mysql with other quotes to begin with, and then learn in the course of the play.

Now, about the fact that there is one, but not in another and Vice versa. To understand some of the bun there. Here is a list of course not complete and it is clearly not sorted by importance, but nonetheless.

Let's start with the disadvantages of Postgres, which is probably not in the muscle.

the
1. to work in production with postgresol, it needs a good professional set up. If you don't expose correctly shared_buffers settings autovacuum, etc., for serious loads, it's gonna be slow work. Especially hate that for highload projects almost always need pgbouncer (it's third-party development), which saves connections in the queue and follows to posgres was a limited number of connections. It is strange that it is not built into postgres itself
the
2. Autovacuum. Speaking of simple, that to achieve a high speed write/delete, posgres leaves a pile of debris, which are then cleaned by specially trained demons. If you incorrectly configure autovacuum or a fool to turn off, especially on a very loaded database, the space occupied by the tables will swell, and sooner or later, or score all that may be clogged, or even without swelling, the base can simply get up a stake and say that ended the id of the transaction. At every conference there are 3-4 reports of how someone heroically fought and won if so.
the
3. Until recently there was a INSERT IGNORE and INSERT ON DUPLICATE KEY UPDATE. Since 9.5 came counterparts. It is very strange that so long delayed the realization of this is necessary for all functionality.
the
4. In Mysql the query to operate on variables
   the

   SELECT @x:=0;
   SELECT @x:=@x+1 FROM table;
   

   
   In posgres this is not, at least I have not found (please comment if wrong). I mean, you can certainly make hranicu where you can do anything at all, but directly in the query, like as not.
the
5. there are No normal analog phpmyadmin. In fact, most known to me a tough progresista work with SQL at the command prompt, what difficult to get used to at first. No, there are all sorts of pgMyAdmin, etc., but each of them has some kind of handicap. Perhaps pay is good, I haven't checked.
the
6. everyone knows Mysql, postgresql, nobody knows. Therefore, new projects are often afraid to start to postgresql, because it will be necessary to support, and indeed the fear of the unknown. There is a whole class of php programmers, for whom the word “database” and mysql is the same, synonymous. Ie they find it difficult to get out from the shell.
the
7. they Say that cheap hosting is not very fond of postgresql, because it is more difficult to administer. For example, to create a user who can login to postgres to do it in two places: to execute sql query and set in pg_hba.conf

Of drawbacks compared with mysql for now. If there's anything else specific you know what is in mysql and what is not in postgresql, please write in comments. Now the buns, which have in postgresql:

the
1. CTE ( Common Table Expression)
   
   If to explain in simple words, the subqueries can be recorded separately, giving them names, and all this in one DB query. For example,
   the

   WITH subquery1 AS (
   SELECT ... 
   JOIN...
   JOIN...
   
   
   ),
   subquery2 AS (
   SELECT ...
   WHERE ....
   )
   
   SELECT * 
   FROM subquery1
   JOIN subquery 2
   ON ...
   
   

   
   Very useful for complicated queries, where named subqueries can break your whole brain, conjuring with join-s, and parentheses subqueries. There is certainly a lot of nuances there performance that need to know, but still incredibly useful. Which is not in MySQL. By the way, the subquery in CTE can be used recursively, for example, to retrieve all subtree in the table of the “id, parent_id”.
the
2. Work with ip addresses. For example, it is necessary to quickly determine the city/country by ip address.
   
   Here I must say that posgres there are custom data types and even the operators that these types of work. Some you can do yourself, some you can get by putting an extension to Postgres. For example, there is the ip4r extension, which allows to do something like this:
   
   the

   -- create a table with ip ranges
   create table ip_ranges (
   ip_range ip4r
   );
   insert into ip_ranges
   values 
   ('2.2.3.4-2.2.3.10'),
   ('1.2.0.0/16');
   
   

   
   Now we can get a list of ranges that intersect with the specified ip using operator &&
   
   the

   test= > select * from ip_ranges where ip_range && '1.2.1.1';
   ip_range 
   ------------
   1.2.0.0/16
   (1 row)
   

   
   To a heap there are other operators: viagenie ranges from one to another etc. So the search was very fast, you can build a GIST index is
   the

   CREATE INDEX ip_ranges_idx ON ip_ranges USING GIST (ip_range);
   

   
   And everything will just “fly” even on huge amounts of data. How to do that in mysql no idea, maybe there's some method?
the
3. Various CONSTRAINTS, i.e., constraints of the database, ensuring integrity. In MySQL there are also constraints UNIQUE, NOT NULL, FOREIGN KEY and etc. But how about this:
   
   Modify the table from the previous example:
   
   the

   ALTER TABLE ip_ranges
   ADD CONSTRAINT ip_ranges_exclude
   EXCLUDE USING GIST(ip_range WITH &&);
   

   
   This entry ensures that the table only disjoint with each other ip ranges. When you try to insert the range of ip which already partially exists in the table, will abuse:
   
   the

   test= > insert into ip_ranges values ('1.2.3.4/32');
   ERROR: conflicting key value violates exclusion constraint "ip_ranges_exclude"
   DETAIL: Key (ip_range)=(1.2.3.4) conflicts with existing key (ip_range)=(1.2.0.0/16).
   

   
   It is also possible to use, for example, the data type circle and check if the table were stored non-intersecting circles. By the way, some geometric types and operations with them built right in the standard delivery: circle, box, polygon, etc.
   
   Another useful constraint:
   
   the

   create table goods (
   id bigint,
   price decimal(11,2),
   ...
   
   check (price >= 0.01)
   )
   
   

   
   And you'll never accidentally put a product with zero price. Of course, the conditions in check can be any.

the Killer feature of the latest versions of Postgres — type jsonb, which allows very fast search by jasonm. I will not dwell, because in every other article about it all ears buzz. the

4. So-called “window functions”. For example, it is necessary to issue each employee his salary, and average salary by Department in the same line, without the use of subqueries and group by.
   
   the

   SELECT 
   depname, 
   empno, 
   salary 
   avg(salary) OVER (PARTITION BY depname) 
   FROM empsalary;
   
   depname | empno | salary | avg 
   -----------+-------+--------+-----------------------
   develop| 11 | 5200 | 5020.0000000000000000
   develop| 7 | 4200 | 5020.0000000000000000
   develop| 9 | 4500 | 5020.0000000000000000
   develop| 8 | 6000 | 5020.0000000000000000
   develop| 10 | 5200 | 5020.0000000000000000
   personnel| 5 | 3500 | 3700.0000000000000000
   personnel| 2 | 3900 | 3700.0000000000000000
   sales| 3 | 4800 | 4866.6666666666666667
   sales| 1 | 5000 | 4866.6666666666666667
   sales| 4 | 4800 | 4866.6666666666666667
   (10 rows)
   
   

   
   Using window functions, you can simplify a whole class of problems, for example very useful for any Analytics and billing.
the
5. Stored procedures can be written in different languages: net, sql, pl/pgsql (this is the language comfortable to work with SQL database, but medlennovat), javascript (pl/v8), Perl and God knows what. You can even attach posgres your favorite language if you have a si and rather plodding. More about this is described on PG day. In my opinion, in postgresql, everything is not so smooth with the language in hranimyh, but certainly 100 times better than mysql.
the
6. you Can make the indices not only in the fields, but funcial from them.
the
7. Speed. According to my feeling, and I worked for many years with both databases, Postgresql in General much faster than MySQL. At times. How to insert and read. If configured correctly, of course.
   This is particularly evident when performing complex queries, which mysql just can't cope, and to build temporary tables.
the
8. rigor throughout. In mysql it seems to be only 5.7 made strict mode the default (I have not checked, but is it really?). Before that can be inserted in a field of type decimal(5,2) number more polozenko, and as a result silently to 999.99. Silent truncation of lines, etc. These jokes there is darkness. And this is the default behavior. Postgresql bones lie and will swear, but not silently carry the ambiguous query.
the
9. Transactioncost. CREATE TABLE, ALTER TABLE, etc., and how simple queries can be performed in a single transaction or rollback transaction in the middle, if something is wrong. Over time, just don't understand how I ever got by without it in mysql.
the
10. full-text search out of the box. There is in my opinion a bit unusual for normal human syntax, but it works and no need to connect third-party gadgets to the side of the sphinx type.
the
11. Sequences (sequences). In mysql only has AUTO_INCREMENT on a field in the table which ticks one by one. In postgresql this mechanism exists separately from the table that you can use for various needs, in addition, you can create looped
the
12. it Seems that the DBA is considered the main advantage of postgresql is its transaction machine. Transaction there are embedded deeply and well, so everything runs quickly and reliably, how to insert and read. Mysql is another system that has a base there, and there is a separate engine (such as innodb, myisam, etc.), and the engines are not all transactional. Because of this separation transaction, there are some problems. For example, myisam is not transactional at all, innodb is transactional, and both tables can be used in a single request. As this is base I do not undertake to predict, probably difficult and spike.
the
13. in postgresql Subjectively less bugs. I don't know how they do it, but for me it is a fact — a very stable and reliable system, even at high loads and data volumes.

This is my first post on Habr (sandbox), so please criticize strongly, but constructively.

What are the specific advantages and disadvantages of these bases? Write in the comments.

Article based on information from habrahabr.ru