from databases from scratch for self-taught to a guide about databases in the clouds
In general, this object-relational DBMS does not need additional representation. Developed over 20 years ago, it is designed to create and maintain databases for server applications, including resource-intensive analytical databases. One of the features of PostgreSQL is that it is open source. We love it for its developed community and the ability to deploy postgru independently and for free.
We have prepared a selection of useful books for those who have just started or are going to work with PostgreSQL. It includes up-to-date manuals in Russian and English. If you know another one or two great books, feel free to recommend in the comments.
“PostgreSQL query optimization” — G. Dombrovskaya, B. Novikov, A. Beylikova
Level: continuing
What’s cool about it: The book will teach database administrators who do not have sufficient experience with PostgreSQL how to solve common problems using this DBMS. For example, the publication details what to do when the application page does not open or the system crashes right before performing key actions. All scenarios presented in the book have been tested by authors who have worked with industrial applications for a long time..
Using the example of working with a large database of a virtual airline, you will learn how to:
- perform targeted optimization in OLTP and OLAP systems,
- better understand execution plans in PostgreSQL system,
- choose indexes that improve query performance,
- optimize the full table scan process,
- choose the best optimization technique for certain queries,
- painlessly solve problems when working with ORM frameworks.
The book can be easily downloaded on the Internet – for example, in a group
.
“Learning PostgreSQL 10” – S. Juba, A. Volkov
Level: beginners
What’s cool about it: The authors are practitioners with extensive experience not only with PostgreSQL. So, Juba Salakhaldin is a certified software developer, a specialist in PostgreSQL, Greenplum and SQL Server DBMS, ETL data processing processes, development of OLAP and OLTP applications. As the authors note, the information contained in the guide will be enough to answer all the questions of a novice developer or database administrator.
Topics covered in the book include:
- relational and object-relational databases with the necessary algebraic operations and data modeling,
- advantages, architectural features and capabilities of PostgreSQL,
- the main blocks and functions of PostgreSQL, including coding, object hierarchy and database components (the database of a car site is considered as an example),
- additional blocks and advanced Postgres features: views, selects, retrievals, set returns, subqueries, grouping and aggregation capabilities
- transactions, locks, security fundamentals, data types and structures, directories,
- performance optimization and testing,
- compatibility with server applications written in Python, scalability issues.
Book files can be downloaded from
. There is also information on its acquisition. But we are sure that the book can be found in the public domain.
“Fundamentals of database technologies: a tutorial” – B. Novikov, E. Gorshkova, N. Grafeeva
Level: mixed
What’s cool about it: The textbook is divided into two parts. The first one is for beginners in database science. In it, beginners will learn about the basics of application design, and PostgreSQL is used as the basic DBMS for learning.
The second part will be of interest to those who already have experience with DBMS. Developers and database administrators on Postgres will get acquainted with the possibilities of the SQL language, which go beyond the basic courses, learn how to create parallel and distributed database systems based on PostgreSQL. After each chapter, exercises are given to reinforce the material.
This voluminous manual was written for students of technical universities. But it will also be useful to every practicing PostgreSQL developer and database administrator who wants to improve their professional level or solve a specific problem.
Topics that will be of interest to professional developers include:
- adaptive query optimization methods: parametric, semantic and multi-criteria query optimization,
- database reliability issues, recovery mechanisms, description of media destruction, additional features, functions and procedures of PL/pgSQL and dynamic SQL,
- PostgreSQL extension mechanisms, including special modules, wrappers and third-party procedural languages, models, tools, and providing support for full-text and fuzzy search,
- data security, rights and access control, privileges, administration, database replication, architecture of parallel and distributed databases, issues of consistency and query execution.
The textbook is free – available at
.
“PostgreSQL from the inside” – E. Rogov
Level: continuing
What’s cool about it: The author strives to avoid ready-made recipes (after all, you can’t get enough of them for every case), but tries to give the reader an understanding of the PostgreSQL mechanics. Having comprehended them, experts will be able to independently find a solution in each case.
This approach may slow down the solution of a particular task, but will bring much more benefits in the future. Sooner or later, the programmer will face problems, the solution of which can no longer be “googled”. The author has been working at Postgres Professional (a DBMS development company) since 2015 and conducts training courses there for students of various levels.
The five parts of the book cover the following topics in detail:
- isolation, versioning, cleaning inside pages and automatic cleaning, restructuring,
- PostgreSQL buffer cache, journal and its modes,
- various types of locks (or locks) to solve the problem of simultaneous access to data files,
- queries, statistics, various access methods, scanning, hashing, sorting, nested loops,
- hash indexes for finding row version identifiers and types of hash indexes (GiST, SP-GiST, GIN, BRIN, and others).
Each chapter is richly illustrated with code examples, diagrams and tables, so that even a reader who is not familiar with this DBMS, but has already worked with others, should not have any problems mastering the material.
You can download the book directly from pages on the official website the company where the author works.
“Postgres: first acquaintance” – P. Luzanov, E. Rogov, I. Levshin
Level: beginners
What’s cool about it: The tutorial is always up-to-date, as it is constantly updated (with the release of each new version of PostgreSQL). By the way, one of the authors – Igor Levshin – has blogin which he publishes monthly reports detailing what’s happening in the Postgres world, from development nuances to events around the world.
The second author can also be trusted – he is the director of educational programs at Postgres Professional. The authors provide detailed information about Postgres courses, how to get certified in this DBMS and how to keep up with the rest in the study of the subject..
In addition to the general description of the DBMS, which is presented in the first chapter, the reader will also learn about the following:
- new features and “chips” of PostgreSQL 15, the latest version of the program at the moment,
- features of DBMS installation in Windows and Linux OS,
- connecting to the server, writing SQL queries, using transactions,
- demobase as an opportunity to deepen your knowledge of SQL,
- using PostgreSQL as a database for analytical and web applications,
- features of server settings when connecting 1C and other systems.
You can download the book for free
. And you can get it in paper form if you take part in one of the events organized by DBMS developers.
PostgreSQL. Fundamentals of the SQL language” — E. Morgunov
Level: beginners
What’s cool about it: This manual can be used for self-study. Suitable for absolutely “zero” specialists. In the first three chapters, the author talks about databases in an accessible form, gives an understanding of the relational model. This is followed by a description of the SQL language and preparation for work, after which a detailed description of the possible operations with tables is given. And only after that we move specifically to PostgreSQL.
There are 10 chapters in the book in total. You’ll start by learning about PostgreSQL data types and end up with performance improvements that are always relevant when working with large databases. You’ll learn how to manage the scheduler, optimize queries, learn how to form joins, and view tables.
Each chapter contains many useful examples, and at the end there is a list of control questions and tasks. It is recommended to perform them to consolidate practical skills, after which you can proceed to experiments with test databases.
Another free and at the same time high-quality work, which you can read or download all in the same place – on the site PostgreSQL.
“Mastering PostgreSQL 13” / “PostgreSQL 13. Mastery of Development” – Hans-Jürgen Schoenig
Level: continuing
What’s cool about it: The last chapter of the book talks about how to painlessly migrate from other databases to PostgreSQL.. Popular databases covered here include MySQL, Oracle, MariaDB, SQLite, Db2 LUW, and Microsoft SQL Server. The purpose of the author is to deepen the knowledge of developers and database administrators, presenting important things in an easy to understand way.
The book has 13 chapters. Let’s take a look at a few of them. For example, Chapter 7 introduces additional topics related to server-side code. Popular server programming languages are considered and tips for compatibility with applications in these languages are given. And Chapter 10, “Understanding Backup and Replication,” deals with the issue of redundancy. You will learn asynchronous and synchronous replication of the PostgreSQL database system. All modern functions are explained as clearly as possible.
You can buy a book in any form (paper, electronic) Here. At the moment, it is problematic to order the original paper version from abroad.
Beginning PostgreSQL on the Cloud
Level: continuing
What’s cool about it: The authors decided it was time to write a book that would help PostgreSQL professionals understand the advantages and limitations of known cloud services. The authors elaborate on the challenges you may face when deploying databases in the cloud.
Discusses the specific steps and procedures involved in migrating from on-premises to the cloud. One of the chapters also details the architecture of PostgreSQL, which should help you understand most of the options to better customize your PostgreSQL environment.
The book contains detailed information about the main companies providing services for deploying a PostgreSQL database in the cloud.
It is dedicated to foreign providers, but this knowledge will also be useful in working with ready-made database clusters in Russia.
The book begins with an introduction to DBaaS and IaaS and a brief description of the factors to consider when deploying databases. The focus is on helping novice PostgreSQL users deploy an enterprise database. The book covers the main aspects of this process, such as security and vulnerabilities, encryption, replication, monitoring, and several others. All these topics are discussed taking into account the characteristics of each cloud server service provider.
You can download the electronic version of the publication offered at some sites.
You may also be interested in these texts:
→ Spain’s largest museum of game consoles, video games and PC games: the perfect Saturday for a geek
→ Even more submarine cables, good and different: Internet backbones in Russia and the world
→ “Steep dive” for hard drives: sales have fallen by almost half, but sales of tape drives are growing. What’s happening?
