PgConf.Russia 2017

Full text search in PostgreSQL is probably the most advanced one among relational DBMS. This tutorial will explain how to setup full text search configurations and dictionaries and how to build a ful text search system using an example of a simple popular science web site, with demonstration of various ranking functions. Also I will tell about new RUM index, which allows to accelerate execution of some kinds of full text queries and implements a new improved ranking function.

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

1. 1C:Enterprise 8 and PostgreSQL 9 interoperability 1.1 Changes in new 1C platform versions 1.2 v81c_data and v81c_index schemas 1.3 Sending 1C queries to SQL 1.4 Using 1C technological log events for PostgreSQL diagnostics
2. Analyzing queries that affect PostgreSQL performance 2.1 A free tool for automating log parsing 2.2 Pareto principle in action 2.3 Installation and configuration of the tool 2.4 A case study of query optimization 2.4.1 An issue in a PostgreSQL query 2.4.2 Finding non-optimal operations in a query 2.4.3 Resolving inefficiencies
3. PostgreSQL statistics for performance diagnostics 3.1 Comparing Postgres with MS SQL Server 3.2 Troubleshooting locks 3.3 Operating load diagnostics 4 Case studies by the gilev.ru team

It's not a secret that PostgreSQL connections are expensive so you should save them. To solve this problem there are PgPool-II and PgBouncer for quite a long time. At Yandex tens of thousands of connections to a single database is not a surprise so we use pgbouncer since time immemorial. This talk gives an overview of problems we faced and ways to solve them.

VIDEO