Geekly Articles each Day
📜 ⬆️ ⬇️

PostgreSQL features not found in MySQL, and vice versa



Many are afraid to move from "muscle" to "posgres" because they only vaguely understand what it will give. Some are stopped by the idea that Postgres probably is too complicated a base and requires training. And also, that maybe something will have to be lost due to the transition. I'll try to clarify the situation a little.

Generally speaking, if someone is afraid of complexity, then for starters, you can do as everyone usually does: go to MySQL to PostgreSQL without using new features. SQL is SQL in Africa, it is not rocket science. With this transition, nothing complicated (with so-called programming) will not be for you. Well, the quotes are different, the syntax is a little stricter. Those. use pg as mysql with other quotes for a start, and then learn as you go.

Now, regarding what is in one, but not in the other and vice versa. To understand what kind of buns are there. Here is a list, of course not complete and it is clearly not sorted by importance, but nonetheless.
')
Let's start with the flaws in the face, which probably are not in the muscle.

  1. To work in production with postgresom, you need to set it up professionally. If you do not set up correctly shared_buffers, settings for auto-vacuum, etc., then on serious loads everything will slowly work. It is especially frustrating that for highload projects almost always pgbouncer is also needed (this is a third-party development), which accumulates connections in the queue and ensures that there are only a limited number of connections to the destination itself. Strange that this is not built into postgres itself
  2. Avtovakuumy. To put it simply, in order to achieve high rates of write / delete speed, the poster leaves heaps of garbage, which are then cleaned by specially trained demons. If it is wrong to set up avtovacums or switch off the fool altogether, especially on a very loaded base, then the space occupied by the tables will swell up, and sooner or later everything that can get clogged up, or even without swelling, the base can simply stand and say that ran out of transaction id. At each conference there are 3-4 reports about how someone heroically fought with a vacuum pump and won.
  3. Until recently, there was no INSERT IGNORE and INSERT ON DUPLICATE KEY UPDATE. Starting from 9.5 there appeared analogues. It is very strange that they took so long to implement such a necessary functional.
  4. In Mysql, you can use variables in the query directly.
    	 SELECT @x: = 0;
         SELECT @x: = @ x + 1 FROM table;

    After this, there is no such thing, at least I did not find (write if you made a mistake). Well, that is, you can certainly make a store where you can do anything at all, but just so that in the request it’s kind of not.
  5. There is no normal phpmyadmin equivalent. In fact, most of the cool posgresists I know work with SQL on the command line, which is difficult to get used to at first. No, there are all sorts of pgMyAdmin, etc., but each of them has some kind of disability. Perhaps there are good paid, I did not check.
  6. Mysql everyone knows, nobody knows postgresql. Therefore, new projects are often afraid to start postgresql, because it will have to be supported, and indeed the fear of the unknown. There is a whole class of php programmers for whom the word “database” and mysql are one and the same, synonymous words. Those. they somehow difficult to get out of the shell, or something.
  7. They say cheap web hosting is not very fond of postgresql, because it is more difficult to administer. For example, to create a user who can log in to postgres, you need to do it in two places: execute the sql query and set it in pg_hba.conf

Of the disadvantages compared with mysql, that's all. If you know something else specifically that is in mysql and which is not in postgresql - write in the comments. Now the buns that postgresql has:

  1. CTE (Common Table Expression)

    If to explain in a simple way, the subqueries can be written separately, giving them names, and all this within the framework of a single query to the database. for example
     WITH subquery1 AS (
       SELECT ...  
       JOIN ...
       JOIN ...
      GROUP BY ....

 ),
 subquery2 AS (
       SELECT ...
       WHERE ....
 )

 SELECT * 
 FROM subquery1
     JOIN subquery 2
            ON ...

    A very useful thing for highly complex queries, where without named subqueries you can break your entire brain by conjuring with join s and brackets of subqueries. There, of course, there are a lot of nuances in performance that you need to know, but still an incredibly useful thing. Which is not in MySQL. By the way, subqueries in the CTE can be used recursively, for example, to get the entire subtree in the table of the form “id, parent_id”.
  2. Work with ip-addresses. For example, you need to quickly determine the city / country by ip-address.

    Here I must say that in the past there are custom data types and even operators that work with these types. Some can be done by yourself, some can be obtained by putting the extension to the destination. For example, there is an ip4r extension that allows doing something like this:

     - 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 a given ip using the && operator:

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

    There are other operators up to the heap: one can fit the ranges into one another, etc. To make the search very fast, you can build a special GIST index:
     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 this in mysql can not imagine, maybe there is some way?
  3. A variety of CONSTRAINTS, i.e. integrity constraints of the database itself. MySQL also has UNIQUE, NOT NULL, FOREIGN KEY, and so on. But how about this:

    Modifying the table from the previous example:

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

    This entry ensures that the table only intersects the ip ranges. When trying to insert a range, ip of which is partially already in the table, it will be an abuse:

     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).

    Similarly, you can use, for example, the data type circle and check that non-intersecting circles are stored in the table. By the way, some geometric types and operations with them are built right into the standard delivery: circle, box, polygon, etc.

    Another useful constraint:

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

    And you will never accidentally put there a product with a zero price. Of course, the conditions inside the check can be any.
  4. The killer feature of the latest versions of the postgres is a jsonb type that allows you to quickly search by jason. I will not dwell in detail, because in every second article about it all the ears buzzed.
  5. The so-called "window functions". For example, it is necessary to issue for each employee his salary, and the average salary for the department in the same line, without using subqueries and group by.

     SELECT 
 depname, 
 empno, 
 salary, 
 avg (salary) OVER (PARTITION BY depname) 
 FROM empsalary;

   depname |  empno |  salary |  avg          
 ----------- + ------- + -------- + --------------------- -
  develop |  11 |  5200 |  5020.00000000000000
  develop |  7 |  4200 |  5020.00000000000000
  develop |  9 |  4500 |  5020.00000000000000
  develop |  8 |  6000 |  5020.00000000000000
  develop |  10 |  5200 |  5020.00000000000000
  personnel |  5 |  3500 |  3700.00000000000000
  personnel |  2 |  3900 |  3700.00000000000000
  sales |  3 |  4800 |  4866.6666666666666667
  sales |  1 |  5000 |  4866.6666666666666667
  sales |  4 |  4800 |  4866.6666666666666667
 (10 rows)

    Through window functions, you can simplify a whole class of tasks, for example, it is very useful for any analytics and billing.
  6. Stored procedures can be written in different languages: pure sql, pl / pgsql (it is a language convenient for working with a SQL database, but slow), in javascript (pl / v8), in pearl, and God knows what. You can even attach your favorite language to the poster, if you own C and are quite plodding. Details about this were told on pgday. In my opinion, in postgresql everything is not so smooth with languages ​​in storage, but anything is 100 times better than in mysql.
  7. You can create indexes not only by fields, but also by functions from them.
  8. Replication (Hot Standby) is done according to the mind. It works quickly and consistently.
  9. Speed. According to my subjective feelings, and I worked for many years with both databases, Postgresql as a whole is much faster than MySQL. Factor of. Both on an insert, and on reading. If properly configured, of course.
    This is especially apparent when executing complex queries that mysql simply cannot cope with, and temporary tables should be kept.
  10. rigor in everything. In mysql, it seems that only in 5.7 they made a strict default mode (I didn’t check, is this really so?). Before that, it was possible to insert in the decimal (5.2) type field a larger number, and as a result silently get 999.99. Silent cutting of lines, etc. Such fun there darkness. And this is the default behavior. Postgresql bones will lay down and will swear, but will not silently perform an ambiguous request.
  11. Transactional. CREATE TABLE, ALTER TABLE, etc., as well as simple queries, can be executed in one transaction or rolled back in the middle if something is wrong. Over time, you just don’t understand how it used to get out on mysql without it.
  12. Full text search out of the box. There, in my opinion, the syntax is a bit unusual for a normal person, but everything works and there is no need to connect third-party sphinx gadgets from the side.
  13. Sequences (sequences). In mysql, there is only AUTO_INCREMENT on the table field, which is ticking one by one. In postgresql, this mechanism lives separately from the table, which can be used for a variety of needs, and you can also create them looped
  14. DBA seems to be the main advantage of postgresql for its transactional machine. Transactions are built deep and well there, so everything works quickly and reliably, both on the insert and on reading. In mysql, the system is different, there is a base, and there are separate engines (such as: innodb, myisam, etc.), and not all transaction engines. Because of this separation with transactions, there are some problems. For example, myisam is not transactional at all, innodb is transactional, and both tables can be used in a single query. How does the base work, I do not presume to predict, probably difficult and crutch.
  15. Subjectively, postgresql has fewer bugs. I don’t know how they are achieving this, but for me this is a fact - a very stable and reliable system, even on large loads and data volumes.

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

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

Source: https://habr.com/ru/post/268631/

More articles:


All Articles