Foreign Data Wrapper implementation enables sub-querying remote data sources within the current query. Combined with sharding (using pg_pathman, in particular) this engine allows a distributed store of large tables mostly for hard-case analytic queries.
As part of the master class, this report will demonstrate such a way of a horizontal partition of data in a database, and compare it with the sharding solutions available in PostgreSQL.
1. Traffic commands instead of data traffic.
2. Sharding.
3. Scalability.
4. Interactivity.
5. Development facilitation and acceleration.
Partitioning in PostgreSQL is a long-awaited functional. Although Postgres can implement partitioning through inheritance, this approach has a number of drawbacks, such as the need to manually create sections and support triggers, significant overhead costs for scheduling, and lack of runtime optimizations. In the report, we will talk about the extension of pg_pathman, which we are working at.
Pg_pathman supports HASH and RANGE partitioning and performs optimization in the planning and execution stages, supports quick insertion by eliminating triggers in favor of Custom Node, contains functions for managing sections (add, split, merge, etc.), FDW-based sharding, non-blocking data migration and other possibilities.
We will also tell about integrating the pg_pathman into PostgresPro Enterprise Edition and supporting Oracle-like syntax for partitioning. In conclusion, we will tell about the new partitioning capabilities in PostgreSQL 10, which is already implemented and the ways of further development.
The report provides an overview of various cluster solutions that are available in PostgreSQL, and is focused on Professional—Multimaster developed by Postgres that is based on statement-based replication and integral distributed transaction manager, as well as Gorynych, a proven high-availability cluster, that is based on stream replication, and Corosync/Pacemaker.
The report will be supported by a live demonstration.
By implementing support for data stores with indexed search and aggregates in Postgres 17 years ago, and then hstore, a type for storing “key-value” data, we opened a way to a new world—the world of semistructured data. Nowadays, talking about such data in PostgreSQL, we basically mean functionality related with JSON/JSONB. Notably, in this regard, Postgres is often far more effective than specialized NoSQL bases, yet retaining user-friendliness, reliability and full functionality of a proven PostgreSQL relational database management system. A new SQL standard has been recently released which specifies structures for working with JSON. We have already implemented basic support for this standard—it will appear in Postgres Pro 10 version and in PostgreSQL 11, in an extended form.
The report will deal with resilience of DBMS as such, and any server solutions based on it, as
well as possible ways to minimize your server’s downtime in case of a trouble. We will talk
about back-ups, data redundancy, logic and physical copying, incremental multi-level backup,
and also about different approaches to data replication. We will look at various types of standby
configurations and try to figure out which of the mentioned provisions can provide high data
availability.
31 января 2017 года у GitLab случилась авария связанная с эксплуатацией СУБД PostgreSQL. Результатом аварии стало частичное удаление данных и остановка проекта на время восстановления. К счастью команда повела себя очень открыто и предоставила исчерпывающие материалы по этой аварии. В предоставленной информации рассказано что произошло, какие предпринимались меры для восстановления и какие меры будут предприняты для предотвращения подобных аварий. Основной целью этого доклада является детальный разбор постмортема - который был представлен GitLab'ом, выделение ключевых моментов, попытка проанализировать их и предложить меры как следовало бы действовать. Также я рассмотрю меры которые команда GitLab планирует сделать для предотвращения таких инцидентов в будущем. Доклад будет полезен администраторам баз данным и системным администраторам которые потенциально могут столкнуться с аварийными ситуациями в PostgreSQL.
OpenStreetMap is a huge database of objects all around the world. You can find there both countries and cities, and the nearest lampposts and benches. Then you can make an application or use the data for analytics. But "you can" does not mean "you can easily": to get something out of OpenStreetMap, you need to know the materiel, you need to know about the services, you need to be able to make geographical requests. From this report you will learn how to make requests in Nominatim and Overpass API, how to download OSM data and what to do with these files, and what PostGIS has to do with it. Finally, we will deal with a one-hundred-page list of object types: do we need to learn it by heart, or is there an easier way to find the notation for the type you are looking for?