PgConf.Russia 2017

• why we build PostgreSQL from sources?
• choice build options
• dependencies
• creation system environment
• how to customise Linux for work with PostgreSQL
• extra soft for PostgreSQL DBA

VIDEO

< Query failed: ERROR: deadlock detected
< DETAIL: Process 17371 waits for ShareLock on transaction 102733872; Blocked by process.
< Process 10414 waits for ShareLock on transaction 102733874; Blocked by process 17371.

Such "unpleasant" messages from the server can seriously puzzle the developer. When working with locks, in particular, with transactions in general, it is necessary to take into account the features of the implementation of client libraries, which can cause the above exception.

In the short talk, the mechanics of the interaction of locks will be explained, main attention being paid to causes of deadlocks. References to the relevant documentation pages will be given. A technique of "bypassing" this problem of concurrent data access will be described and illustrated with some generalized examples from practice are shown. The talk will be interesting to database developers and administrators as well as the client-side application developers.

Most of the main Skype services use a database platform based on PostgreSQL and other open-source technologies, such as Skytools, plProxy, pgBouncer, etc. This platform consists of several hundreds of servers with thousands of databases, which process hundreds of thousands of transactions per second. At the same time, the platform architecture allows its users (applications and their developers) to work with "logical" databases, without any worries about their real “physical” structure.

Our Skype Database Platform team is responsible for the database platform infrastructure. We develop automation systems for various processes that help us ensure service reliability and facilitate development, testing, and deployment of code. In this presentation, I will outline the database platform architecture, review its main components, and tell you about the methods we use in our every-day work to ensure high availability, scalability, replication, fault tolerance, and more.

Nowadays one can make a decent urban research based simply on public datasets, making interesting and unexpected insights. In the presentation, I'll show examples of these calculations in PostGIS, the industry standard de-facto.

But just PostGIS is not enough. You need tools to import, verify and visualize the data. It's critically important to visualize the data live, to debug your calculations and shorten iterations. I'll describe all these steps:

1. Collecting the data: public API, OpenStreetMap; direct user input.
2. 3rd party APIs for calculations.
3. Visualization of GIS and other sorts of data: QGIS, Matplotlib, Zeppelin integrated with PostGIS.
4. Debugging the calculations: live visualization (Arc, QGIS, NextGIS Web)
5. Scripting and minimizing the chores: Makefile, Gulp