PostgreSQL is not Rocket Science. How much is the eggs now?

I constantly come across statements from the PostgreSQL series is too complicated a base for my small project, so I will continue to work with MySQL.
In this article, I would like to show that a person who knows MySQL will have absolutely no difficulty in starting development with PostgreSQL.


I'll start very far away
')
The classical economic law of supply and demand assumes that as the price of a product decreases, demand automatically increases. However, in practice, this is not entirely true, because people do not have complete information.

Suppose you want to make scrambled eggs for breakfast. And you need eggs.

To get the OPTIMAL result, you need to travel ALL the stores, compare prices, quality, listen to customer reviews, and finally choose a good product for you at the lowest price. Naturally, no one does this, because there is a high probability of dying of hunger while all this is being done.

Or, for example, you made the coolest startup, a certain site that should turn the world around. But if nobody knows about him, it's all the same that he doesn't exist at all. Need advertising, marketing, etc.

In other words, not only the quality and price of the product, but also the costs of making a decision, for some organizational issues are important. And all this is very, very significant. In this article, I would like to slightly reduce the costs of transition to postgresql DBMS. Those. prompt “egg prices”.

On the past pgday, Oleg Bunin made a speech in which many correct things were said that PostgreSQL, despite all its coolness, was undeservedly used a little in projects. Various reasons were cited, with most of which I fully agree.

But! At the same time, Oleg gave an example from his practice, when he reluctantly advised his client to use MySQL for the new project, because you can't take the whole team (which already knows how to muskul) to retrain from scratch to write to PostgreSQL. Few books, courses, etc. And in the end, the business will suffer from this.

I believe that this is not entirely true.

Take a typical MySQL project. What is used there? In 99% of cases, these are the simplest queries, because in this database there are still no recursive CTEs, no window functions (although they are planning in future versions), no custom data types, no advanced work with arrays, no indexes on expressions, and so on. d etc.

Let's compare the syntax of simple queries on different databases.

MySQL:

SELECT name FROM users WHERE id = 5; UPDATE users SET name = '' WHERE id = 5; INSERT INTO users (name) VALUES (''); 

Postgres:

 --  SELECT name FROM users WHERE id = 5; UPDATE users SET name = '' WHERE id = 5; INSERT INTO users (name) VALUES (''); 

This functionality is already enough to write a lot of things.

Well, okay, okay, there are some nuances.

In MySQL, complex identifiers can be escaped with ` , and in the preceding statement it is used for this " (therefore, you must use single quotes in string constants)

MySQL uses INSERT IGNORE ...,
in posgres INSERT ... ON CONFLICT ...

In the postpoint there is no INET_NTOA function, but there are a lot of ways to do the same thing in other ways.

In MySQL, the term is called DATABASE, in postgres the same is called SCHEMA.

In MySQL, you need to write BIGINT AUTO_INCREMENT, in the bigserial list

There are other differences, the same in general “large-scale”.

But tell me, is it so inconceivably difficult to understand quotes and other minor nuances?

It seems to me that a person who has, so to speak, a brain on his shoulders, does not need books and courses to comprehend the incredible depth of these differences. Manual enough or even stupid StackOverflow.

And this is well confirmed by the practice: many projects moved to postgres: at first everything was written as used 1: 1 as on mysql, and only then, as we studied various features, we began to introduce a new functionality for ourselves.

I often hear another argument: “Oh, well, this is some kind of spaceship that I cannot master. All these CTE and Partial indices, too lazy to study all this. My project has enough MySQL features. ”

But damn, for a simple project there will be no difference at all between the bases. Therefore, from the developer’s point of view, there will be no additional costs for postgres. And if it grows up (your project), then you may later need advanced features of the database:

- simplify complex queries using CTE
- do many things at once with one request using window functions.
- Ability to make multiple CREATE TABLE, ALTER TABLE, etc. within a transaction (and rolled back in the middle if something went wrong)
- CREATE INDEX CONCURRENTLY (create an index on large tables without forcing the base)
- Materialized View
- Index by expression (instead of generating and maintaining a separate column for this case)
- Partial Index (to speed up some queries or, for example, to ensure uniqueness by condition)
- and a bunch of different indexes (gist, gin, brin, etc.).
- work with arrays, for example, using unnest and array functions
- Replication hot standby
- Inheritance tables
- custom data types and rules for conversion (create type, create cast). A lot of extensions are made on this account, for example, the type ip4r allows you to easily and quickly work with ip address ranges (ip search in ranges can be indexed with a gist index)
- custom operators (CREATE OPERATOR). I haven't tried it myself yet, but I look this way.
- stored procedures in different languages, for example javascript (plv8) or python
- time with timezone (timestamp with time zone)
- quick deletion and addition of columns in tables
- indexed json (jsonb type)
- checking the validity of the data through the check (in it, for example, you can check the presence / absence of the necessary elements in json)
- sequences instead of auto_increment (you can do looped, common to several tables, etc.)
- Foreign Data Wrappers - you can zagzhoyunit in a single query table from the post, from mysql and csv-file
- and another 100,500 other features, extensions, and even forks. I have been working with the company for a long time, but I constantly open new opportunities.

For the sake of justice, it should be noted that mysql seems to have begun to develop well and get rid of Legacy. That is, what was in 5.5 and what is now is already a significant difference (for example, strict mode by default). They say that in the next version they will refuse to use myisam in system tables, add CTE and window functions - this is a big step forward. But subjectively for now it is still very, very far behind the edge. Which, too, in general, does not stand still ( wiki.postgresql.org/wiki/Todo )

Everything that is written above is fair from the point of view of the developer. From the point of view of admins and database db differ, there's nothing to say. However, for a simple project, again, enough Google and stackoverflow.

To summarize, in order for a muscular team to start a project on a post, you need only a person who will supply and charge the base for a specific load profile. (this, however, is true for any DBMS). You can hire some dBA for this business. But the code itself can be written without any problems.

This is not just a theoretical reasoning, I myself worked (s) as part of projects that migrated to postgresql without any books, etc. And later, newcomers, too, in general, no problems pouring into the development. Because when the code finally comes up with advanced features from the program, there are already people who can be asked what and how.

PS If DBA read me, please write a distinct article for beginners on Habr “How to install and configure PostgreSQL. Basics. " IMHO this article is very necessary.