October 31, 2016 at 16:41PostgreSQL Safety
It so happened that I started working with PostgreSQL three years ago and during this time I managed to methodically collect all the possible rakes that you can imagine. And to tell you the truth, if I had the opportunity to share my bitter experience with myself three years ago, my life would be much simpler and the nerve cells more whole. That is why I decided to write an absolutely subjective article with a set of rules that I adhere to when developing on PostgreSQL. Perhaps this article will help someone bypass the rake I have collected (and step on others, haha!).
Nearly every point below is a sad story full of suffering and exaltation. And the word "pain!" the items worked out by the stories are marked, with the recollection of which I still tremble at night.
The same list of rules
Nearly every point below is a sad story full of suffering and exaltation. And the word "pain!" the items worked out by the stories are marked, with the recollection of which I still tremble at night.
- Version the database
schema The database schema is the code you wrote. It should be in the version control system and versioned with the rest of the project. In the case of PostgreSQL, I liked Pyrseas the most for these purposes . It turns a schema with all PostgreSQL-specific objects into a yaml file that is versioned. It is convenient to work with such a file in branches and merge changes, unlike pure SQL. With the final step, the yaml file is compared with the database schema and a migration to SQL is automatically generated. - Pain! Never apply changes directly to the combat base.
Even if the change is simple, incredibly urgent and really want to. First you need to apply it on the basis of the developers, commit it to a branch, apply the changes on the basis of the trunk (identical to the combat base). And only then, when all is well in the trunk, apply on a combat base. It is long, paranoid, but saves from many problems. - Pain! Before writing delete or update, write where.
And even before you run the code, exhale, count to three and make sure that you are in the session of the right base. About truncate, I generally keep quiet, without three "Our Father" do not even think to run, amen!
UPD . koropovskiy : It’s more useful to set set autocommit off for the current session.
tgz : or before each update and delete write begin. - Test Driven Development
Always write tests first, and then create database objects. We are talking about any objects: schemes, tables, functions, types, extensions - no exceptions! At first it seems hard, but later you will thank yourself many times. Even with the initial creation of the circuit, it is easy to miss something. And when refactoring tables in six months, only the tests you write will save you from a sudden shot in the leg in any function. In the case of PostgreSQL, there is a wonderful pgTAP extension . I recommend for each circuit to create an additional scheme "schema_name_tap", in which to write functions for testing. And then just run the tests through pg_prove. - Don’t forget to configure PITR.
I’m afraid to play the role of Captain of Evidence, but any database should have a backup configured. Moreover, it is desirable to be able to restore the base at any time. This is necessary not only for disaster recovery, but also gives many interesting opportunities for developers to work in certain time slices of the database. PostgreSQL has barman for this . - Data Consistency
Inconsistent data in the database has never led to anything good. Even a small number of them can easily turn the entire base into garbage. Therefore, you should never neglect normalization and restrictions like foreign keys and checks. Use a denormalized form (for example, jsonb) only after making sure that it is not possible to implement the circuit in a normalized form with an acceptable level of complexity and performance - a denormalized form can potentially lead to inconsistent data. For all the arguments of the supporters of denormalization, answer that they didn’t just come up with normalization for nothing, and keep quiet with a meaningful look. - Create foreign keys deferrable initially deferred
In this case, you postpone checking the restriction at the end of the transaction, which allows you to get inconsistency with impunity during its execution (but in the end everything is consistent or will cause an error). Moreover, by changing the flag inside the transaction to immediate, you can force a check of the constraint at the right time of the transaction.
UPD . The comments indicate that deferrable is an ambiguous practice that simplifies a number of import tasks, but complicates the debugging process within a transaction and is bad practice for novice developers. Although I stubbornly tend to think that it is better to have deferrable keys than not to have them, consider an alternative view of the question. - Do not use the public schema.
This is a utility schema for functions from extensions. For your needs, create separate schemes. Treat them like modules and create a new schema for each logically separate set of entities. - Separate scheme for API
For functions that are called on the application side, you can create a separate scheme "api_v_version_number". This will allow you to clearly control where the functions that are the interfaces to your database lie. To name functions in this scheme, you can use the template "entity_get / post / patch / delete_arguments". - Triggers for auditing
Triggers are best suited for auditing actions. I also recommend creating a universal trigger function to record any actions of an arbitrary table. To do this, you need to extract data about the structure of the target table from information_schema and understand whether the old or new row will be inserted depending on the action taken. Due to this decision, the code becomes moreloving and seductive.
If you plan to use triggers to calculate the accumulation register, then be careful in the logic - one mistake and you can get inconsistent data. Rumor has it, this is a very dangerous kung fu. - Pain! Importing data into a new schema
The worst, but regularly occurring event in the life of a database developer. PostgreSQL helps FDW a lot , all the more so they were well uploaded in 9.6 (if their developers care, then FDW can build a plan on the remote side). By the way, there is such a convenient design as "import foreign schema", which saves you from writing wrappers over a bunch of tables. It is also good practice to have a set of functions that save a set of SQL commands for deleting and restoring existing foreign and primary keys in the database. I recommend importing, first writing a set of views with data identical in structure to the target tables. And from them make an insert using copy (not insert!). It is better to keep the entire sequence of SQL commands in a separate versioned file and run them through psql with the -1 key (in a single transaction). By the way, import is the only case when in PostgreSQL you can turn off fsync by first making a backup and crossing your fingers. - Don’t write in SQL: 1999
No, really, since then a lot of water has flowed: an entire generation has graduated from school, mobile phones from bricks have turned into supercomputers by the standards of 1999. In general, you should not write as our fathers wrote. Use "with", with it the code becomes cleaner and can be read from top to bottom, and not loop among join blocks. By the way, if join is done across fields with the same name, then it is more concise to use “using” rather than “on”. And of course, never use offset in your battle code. And there is such a wonderful thing “join lateral” that is often forgotten about - and at this moment the kitten is sad in the world.
UPD. Using "with", do not forget that the result of its execution creates a CTE, which eats up memory and does not support indexes when querying it. So used too often and to the point of "with" can adversely affect the performance of the request. Therefore, do not forget to analyze the request through the scheduler. “With” is especially good when you need to get a table that will be used differently in several parts of the query below. And remember, “with” drastically improves the readability of the query, and in each new version of PostgreSQL it works more efficiently. Other things being equal - prefer this particular design. - Temporary tables
If you can write a query without temporary tables - do not hesitate and write! Typically, a CTE created by a "with" construct is an acceptable alternative. The fact is that PostgreSQL creates a temporary file for each temporary table ... and yes, another sad kitten on the planet. - Pain! The scariest antipattern in SQL
Never use view constructsselect myfunc() from table;
The execution time of such a request increases linearly with the number of rows. Such a query can always be rewritten into something without a function applied to each row, and win a couple of orders of magnitude in execution speed. - The main secret of requests
If your request runs slowly on a test computer, then it will not work faster in production. Here is the best analogy about roads with cars. A test computer is a one-way road. The production server is a ten-row road. In ten rows at rush hour there will be much more cars without traffic jams than in one lane. But if your car is an old bucket, then she won’t go as a Ferrari, how many empty lanes do not give her. - Use indexes, Luke!
It depends on how correctly you create them and use them, the request will be executed in tenths of a second or minute. I recommend that you browse the Marcus Vinand b-tree index site - the best publicly available explanation on balanced trees I've seen on the Internet. And his book is also cool, yes. - group by or window function?
No, of course, the window function can do more. But sometimes aggregation can be counted and so and so. In such cases, I follow the rule: if aggregation is calculated according to covering indices, only group by. If there are no covering indexes, then you can try the window function. - set_config
set_config can be used not only to set the settings for postgresql.conf within a transaction, but also to pass a custom variable to the transaction (if it is defined in advance in postgresql.conf). Using such variables in a transaction, you can very interestingly influence the behavior of called functions. - FTS and trigrams
They are wonderful! They give us full-text and fuzzy searches while retaining the full power of SQL. Just remember to use them. - Calling your own exceptions
Often, in a large project, you have to raise many exceptions with your own codes and messages. In order not to get confused in them, there is an option to create for exceptions a separate type of type “code - exception text”, as well as functions for calling them (wrapper over “raise”), adding and removing. And if you covered all your database objects with tests, then you cannot accidentally delete the exception code that is already in use somewhere. - A lot of paranoia is never enough. It’s
good practice to remember to configure ACLs on tables and start functions with a “security definer”. When functions are read-only, feng shui requires that they have the “stable” flag set. - Pain! Cherry on the cake
UPD . You can never redirect the application user through the server to the database, one-to-one broadcasting the application user to the database user. Even if it seems to you that at the same time it is possible to configure security in the database for users and their groups using standard PostreSQL tools, never do this, this is a trap! With this scheme, connection pools cannot be used, and each connected application user will eat a resource-intensive connection to the database. Databases hold hundreds of connections, and servers hold thousands, and for this reason, applications use load balancers and connection pools. And when translating one to one of each user into the database, when the load increases, you will have to break the scheme and rewrite everything.
