postgresql-patterns-library is a Russian language cookbook of PostgreSQL SQL recipes

This is a cookbook, not an extension you install

The repository describes itself in one line: a collection of ready made SQL queries for PostgreSQL covering frequently recurring tasks, namely fetching and modifying data, speeding up queries, and maintaining a database. The primary language is listed as PLpgSQL.

That framing sets expectations. There is nothing to compile, no extension to CREATE, and no service to start. What you find are SQL queries and functions, grouped into sections, that you read and adapt to your own schema. The value is in the pattern rather than the code, since the table names and column names in every recipe belong to the author's environment and to yours only after you rename them.

The audience is a working PostgreSQL engineer with a concrete problem in front of them: how do I validate an email column across a large table, how do I load a CSV, how do I find cyclic references in a graph table. The recipes are indexed by those questions rather than by SQL feature, and the table of contents in the README is built as a nested list of question headings.

The interesting constraint is language. All of the prose, the section titles and the question headings are in Russian, so the collection is effectively a Russian language cookbook even though the SQL itself is portable.

The validation section is built around Russian identifiers and documents

The first and longest group in the table of contents is validation and domains, and it is where the collection's regional character is clearest. The entries cover checking an email address for validity, finding all invalid emails in a table, deleting all invalid emails from a large table, and checking a CSS colour for validity.

Then come the Russian registration identifiers. There are separate entries for validating company details (ИНН, КПП, ОГРН, ОГРНИП), for validating bank details (БИК, the settlement account and the correspondent account), and for checking a СНИЛС number. There is also an entry for validating a phone number and one for validating a field value only when it is explicitly present in an UPDATE statement.

Those last two are different in kind from the rest. A regex check against a column is a one-liner, while the UPDATE-specific recipe describes a constraint that only fires when the column appears in the statement, which is the kind of detail that usually takes an hour to work out.

The domain-specific checks are the part that cannot be reused directly outside Russia, and they are also the part with the least room for error, since ИНН and ОГРН have checksum rules. A developer outside Russia should treat that group as background rather than as reusable code, while the email, CSS colour and phone recipes are portable as written.

Three recipes exist purely to smooth a MySQL or MariaDB migration

A dedicated group is titled migration from MySQL/MariaDB to PostgreSQL, and it contains three entries that are essentially compatibility shims: how to make an analogue of the MySQL group_concat() function, how to make an analogue of the MySQL set type, and how to make an analogue of the MySQL make_set() function.

These are recognisable problems from any MySQL to PostgreSQL port. group_concat has no direct equivalent in PostgreSQL, so people reach for string_agg with a different argument order and different null handling. The set type has no counterpart, which usually ends as an array or a lookup table. make_set turns a bitmask into a list of names, and the natural PostgreSQL answer involves unnest over a bitwise test.

Having the answers written down as ready made queries saves the debugging cycle of rediscovering them, and the group_concat entry in particular tends to be the first thing a porting team needs. The caveat is the same as for the rest of the collection: the code assumes a particular PostgreSQL version's function set, and the array and bitwise syntax involved has changed in behaviour across major versions.

CSV, JSON and array recipes cover the data plumbing most teams rewrite

The files group covers exporting the result of a SELECT into a CSV file, loading data from a CSV file into a table, and a cheat sheet for CSV export and import in PostgreSQL. The strings group adds parsing a CSV line into a table, transliterating Russian letters to Latin, escaping a string for use in a regular expression, and escaping a string for use with the LIKE operator.

The JSON group has two entries: selecting records that satisfy conditions taken from a JSON array, and comparing two JSON objects to obtain their differences. Both are the kind of question that arrives when a relational table starts carrying semi structured attributes.

The arrays group is the largest technical block, with an aggregate function for concatenating arrays, an intersection of arrays, getting unique elements or sorting them, finding elements that differ between two arrays, and building a foreign key on array elements. The last of these is the one with consequences: a foreign key pointing at array members is a denormalised relationship that PostgreSQL will not enforce the way it enforces a real foreign key, so it is a technique for a reporting table rather than for a schema you depend on.

The tree and graph group goes further. It lists retrieving ancestor and descendant names for each node of a tree, with regions as the worked example, finding cyclic relationships in a graph, and protecting against cyclic relationships. That last entry is the practically important one, since an unguarded recursive traversal can loop forever on data that contains a cycle.

Query tuning recipes target EXPLAIN, large IN lists and COUNT(*)

The optimisation group lists viewing an execution plan in a readable graphical form, speeding up SELECT queries with thousands of values in an IN(...) list, reusing the output of an EXPLAIN query inside another query, speeding up a SELECT after moving from PostgreSQL v10 to v12, speeding up a SELECT COUNT(*) query, and running a function N thousand times while measuring how long it takes.

Several of these are version sensitive in a way that matters. The IN(...) recipe addresses a well known planner behaviour where a large literal list is planned inefficiently, and the standard remedies differ between versions, so a snippet written against one release can be wrong on another. The v10 to v12 entry is explicitly about a version change and is best read as historical.

Elsewhere in the contents there are entries for finding duplicate records by field values, returning the execution duration of a query inside its own result, splitting a large table into chunks of N thousand rows, running a follow-up SQL statement only when the previous one returned no rows, pivoting a record into a set of columns, returning a total and a percentage per row in one query, computing age from a birth date, formatting a date or timestamp in ISO-8601, and calculating the surface distance between two points on Earth in kilometres.

The contents also cover finding nearby populated places relative to given coordinates, and a search section with entries for matching rows against a list of patterns, deriving entity names from a search phrase with word-start awareness for search suggestions, and suggesting corrections for a misspelled word.

The repository is a working environment, not just a set of files

The top level entries give a picture of the collection's working habits. Alongside README.md and LINKS.md there are directories for DBA work, functions, procedures, views, types and domains, plus experiment, test, dev and old folders, and a star_track.md file.

The dotfiles are the most telling. A .pgpass entry, a psqlrc directory, an inputrc entry and a bashrc directory sit at the root, which means the author kept the client side configuration for connecting, editing and shell habits next to the queries rather than in a separate dotfiles repository. The .gitignore is there as well, which raises an obvious question about the .pgpass file: it is a password file format, and while a .gitignore may well exclude it, its presence in the tree means you should check what is actually tracked before copying anything.

Some directories are named after specific operational tasks. db_copy, pg_copy, pg_log_csv_to_table and pg_receivewal suggest attention to data movement and to turning PostgreSQL's own logs into table rows, while pg_archive_log, pg_backup and pg_terminate_backend_log_to_table point at log archiving, backup and session termination monitoring. DDL_log_to_table suggests the same trick applied to DDL events.

None of these directories are described in the README excerpt, so what each contains has to be discovered by opening it.

MIT licensed, last pushed 2026-10-15, and documented only in Russian

The licence is MIT, which means the SQL in the repository can be copied into a commercial codebase without attribution conditions beyond keeping the licence notice, a practical advantage for a recipe collection that people are expected to paste directly into production schemas.

On upkeep, the last push was on 2026-10-15 and the repository is not archived, so the collection is still being added to. The repository has no GitHub releases, so there is no version to pin and no changelog to diff against. For a snippet collection that is a mild concern rather than a serious one, since a query that changes behaviour between commits is invisible to anyone who copied it earlier.

The documentation language is the real adoption barrier. Every heading and question in the table of contents is in Russian, and there is no English translation. A developer who does not read Russian gets the SQL structure, which is portable, but not the explanation of when a recipe applies or which edge cases it handles. That is a real loss, because the interesting content of a recipe is usually the caveat rather than the statement.

The contents also show that some recipes age: the migration from PostgreSQL v10 to v12 sits in the optimisation group alongside current advice, and the old/ directory suggests that superseded material is kept rather than removed. Selecting from this repository is closer to picking a tool out of a drawer than installing a package.

Editorial conclusion

postgresql-patterns-library suits a PostgreSQL developer who needs one specific query answered quickly, especially one working with Russian domain data such as ИНН, КПП, ОГРН, БИК or СНИЛС, and who is comfortable reading the Russian descriptions. It does not suit a team that needs a packaged extension with a versioned API, since the repository publishes no releases and installs nothing, and a non Russian speaking developer gets no help from the prose. Verify three things before relying on it: that the recipe you need is present rather than merely adjacent to it in the table of contents, that the SQL matches your PostgreSQL version, since the contents include a recipe about moving from PostgreSQL v10 to v12, and that the file you copy from is not sitting in the old/ directory, which is where retired content appears to be kept.

Frequently asked questions

Official sources

1. Issues
2. License: MIT
3. README
4. rin-nas/postgresql-patterns-library on GitHub