PostgreSQL is not Rocket Science. How much eggs?

Keep seeing statements from the "PostgreSQL database is too complicated for my small project, so I will continue to work with MySQL".
In this article I would like to show that to the person who knows MySQL, will not be absolutely no difficulty to begin to develop a PostgreSQL


Start from very far

The classic economic law of supply and demand suggests that the decline in the commodity prices automatically increase demand. However, in practice it is not so, because people do not have full information.

Let's say you wanted to make eggs for Breakfast. And you need the eggs.

To get the OPTIMAL result you need to travel around ALL the shops to compare prices, quality, hear customer testimonials, and finally choose an acceptable product for you at the lowest price. Of course, nobody does, because there is a high probability of dying from hunger before all this is done.

Or, for example, you made the coolest startup a website that should change the world. But if no one knows it, it's like it at all. Need advertising, marketing, etc.

In other words, not only is the quality and price of the product, but also the cost of making the decision on some organizational issues. And it is very, very important. In this article I would like to reduce costs by switching to postgresql. Ie suggest “the price of eggs”.

On last PG day Oleg Bunin made a speech, in which it was said a lot of things right about what PostgreSQL is, despite its coolness, undeservedly little-used in projects. Cited different reasons, most of which I completely agree.

But! At the same time Oleg gave an example from his practice when he reluctantly advised his client to use for a new project of MySQL, because it is impossible so to take and the whole team (which already knows how, muscul) from scratch to relearn to write to PostgreSQL. Little books, courses, etc. And eventually the business will suffer.

I believe that this is not quite true.

Let's take a typical project for MySQL. What there used for? In 99% of cases this is the simplest queries, because in this database so far, no recursive CTE or windowing functions (though plan to in the future versions), no custom data types or advanced work with arrays or indexes on expressions, etc, etc.

Let's do a comparison of the syntax of a simple query on different databases.

MySQL:

the

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

Postgres:

the

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

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

Okay, okay, there are some nuances.

MySQL complex identifiers can be escaped using `, and posgres to do this, use " (so string constants must use single quotes)

In MySQL INSERT IGNORE...
in posgres INSERT ... ON CONFLICT...

In posgres no function INET_NTOA, but there are plenty of ways to do the same in other ways.

In MySQL the term called DATABASE in postgres is also called the SCHEMA.

In MySQL you need to write BIGINT AUTO_INCREMENT, bigserial posgres

There are other differences, such as in General “scale”.

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

It seems to me that the person who had, so to speak, the brain on your shoulders, don't need books and courses to comprehend the incredible depth of these differences. Enough of the manual or even stupid StackOverflow.

And it is perfectly confirmed by the practice: a number of projects and have moved on postgres: first, all wrote as I used 1:1 as to mysql, and only then, as you explore different features, started to implement new functionality.
Another argument I often hear: “Oh, this is some kind of spaceship, which I will not do. All of these CTE and Partial indexes too lazy to learn it. My project is MySQL's capabilities ”.

But damn, for a simple project will not be generally no difference between bases. Therefore, from the perspective of the developer any additional costs will not be from postgres. But if it will grow (your project), then you may need the advanced features of the database:

— to simplify complex queries using CTE
— to do many things at once with one query using window functions.
— Ability to make multiple CREATE TABLE, ALTER TABLE, etc. as part of a transaction (and rollback, if something went wrong)
— CREATE INDEX CONCURRENTLY (create index on large tables, not base raping)
— Materialized View
Index on expression (instead of generate and support individual speakers in this case)
— Partial Index (to speed up certain queries or, for example, to ensure uniqueness condition)
— and a bunch of different indexes (gist, gin, and brin, etc).
— work with arrays, such as using unnest and array functions
Replication hot standby
— Inheritance of tables
— custom data types, and rules for conversion (create type, create cast). A lot of extensions made on this account, such as ip4r allows you to easily and quickly work with ip ranges (ip search ranges can be indexed gist-index)
— custom operators (CREATE OPERATOR). I haven't tried, but look this way
— stored procedures in different languages, for example javascript (plv8) or python
— time Timoney (timestamp with time zone)
— deleting and adding columns in tables
— indexed json (jsonb type)
— check the validity of data using check (it is possible, for example to check the presence/absence of essential elements in json)
— sequence instead of auto_increment (you can do looped common to multiple tables, etc)
— Foreign Data Wrappers — you can tagounit in a single query a table from Postgres, mysql and csv file
and 100500 more other features, extensions, and even forks. I work with pogresan for a long time, but always open to new opportunities.

In fairness it should be noted that mysql seems to have started to thrive and get rid of legacy. Ie what was in 5.5, and what we have now is already a substantial difference (e.g., strict mode by default). They say the next version will refuse to use the myisam system tables, add a CTE and window functions is a big step forward. But subjectively, yet it's still very, very far behind posgres. Which is, in General, is not in place ( wiki.postgresql.org/wiki/Todo )

All that is written above, is true from the developer's point of view. From the point of view of admins and DBA databases differ, there's nothing to say. However, for a simple project again, enough of Google and stackoverflow.

To summarize: to a muscle team to start the project in posgres, we need only the person who put setunit database for a specific load profile. (this, incidentally, is true for any DBMS). You can hire for that matter any DBA. And here is the code you can write without any problems.

This is not just a theoretical argument, I myself worked(W) in the composition of projects that smirilis on postgresql without any books, etc. And subsequently the new guys in General without problems join in the development. Because when the code finally show advanced features of Postgres, there are already people who can ask what and how.

PS If I read DBA, please write on Habr clear article for newbies "How to put and configure PostgreSQL. Basis.". IMHO this article is very necessary.

Article based on information from habrahabr.ru