PgConf.Russia 2019

Declarative partitioning was a long-awaited feature and has been enhanced since its introduction in PostgreSQL 10. However, for many users, finding optimal partitioning schemes to have the best benefits from partitioning is not an easy task. Therefore, we added in HypoPG a new hypothetical partitioning feature which helps users to design partitioning. In this presentation, I will provide a brief introduction of HypoPG and explain declarative partitioning, and then I'll show the usage of hypothetical partitioning feature and explain how the extension is working.

In this session we will deep dive into the exciting features of Amazon RDS for PostgreSQL, including new versions of PostgreSQL releases, new extensions, larger instances. We will also show benchmarks of new RDS instance types, and their value proposition. We will also look at how high availability and read scaling works on RDS PostgreSQL. We will also explore lessons we have learned managing a large fleet of PostgreSQL instances, including important tunables and possible gotchas around pg_upgrade.

Many DBMS specialists do not like these three letters - ORM because they have repeatedly seen the enormous queries ORM-generated for simplest operations. However practice shows that the origin of the problem is not ORM itself but rather those developers who are not able to use ORM properly. In this report I will tell you the basic principles of how to write code for ORM which generates "good" queries and also show you "bad" code samples and what you get out of them. The main idea is we have to think in SQL-style when writing the code, and so to learn to foresee what kind of query will be generated. But even having mastered that you must always check the output SQL for complex queries. I will show an example when a slight change in ORM-logic increases the volume of output SQL by dozens of times(!). I will tell you about additional tools and tricks. Namely - disabling tracking, INCLUDE construction, alternative syntax for JOIN, how to get more data using a smaller number of queries, how to effectively write queries with grouping, and what do we need mappings for. I will not bypass the cases when it is not possible to effectively solve the problem by means of ORM (for example, queries with recursion). In addition to SELECT requests, there are some Batch-Update/Delete tools that allow you to update and delete data using ORM tools without downloading data to the client side. We'll also talk on how to force the ORM to insert large volumes of data quickly via Multi-Insert and COPY. I will also discuss how ORM supports PostgreSQL-specific data types i.g. arrays, hstore and jsonb. But does it make sense to use ORM at all, since there is so much to learn? Sure it does. There are advantages of using ORM, and we will discuss them as well. All examples are based on Entity Framework technology for .Net Core and .Net Framework in C#. There are some subtle differences in ORM usage in Hibernate/NHibernate, but the basic principles remain the same, so the report will be useful for developers using various technologies.

JSONB in PostgreSQL has interesting properties for design and development of business systems with heavy domain, helping to fight against complexity and variability. We will discuss the trade-offs of the JSONB approach. Using an open source project for Health IT - fhirbase we will:

• load synthetic medical data into PostgreSQL
• learn how to search and index this data (gin, jsquery, json-knife)
• use JSON aggregation to build complex queries (GraphQL)
• see how this data can be modified and validated
• discuss architecture consequences of JSONB usage

Technical requirements:

• docker
• docker-compose

Detailed prerequisites