ORM: how to write queries and not to drive database crazy

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.

Manage and Protect your PostgreSQL data with Commvault

Reliable backup and recovery, at enterprise level for the PostgreSQL environment. No more traditional backups. CBT (Change Block Tracking) technology is the next generation incremental backup. Faster than snapshots, CBT back up blocks that change, not all of your data, reducing server and network traffic and eliminating the need for traditional backups. Benefits: • Data protection mode close to Real-Time • Update with ease

LTREE: extending the syntax

At the end of 2018, I've got a request to extend the syntax of the ltree contrib. I'm finalizing the patch and going to speak about:

• the current state of the extension,
• the extended syntax, and
• the process of development and testing the extension.

Using pg_variables as temporary tables

PostgreSQL provides possibility to create temporary tables. Though a temporary table is accessible only to a single session and is removed at the end of the session, all information about it is stored in the system catalogs of PostgreSQL. This is related to several issues, which make it difficult or impossible to use temporary tables in some cases. There are attempts to solve this feature, including in our company. But they have not yet succeeded, mainly because of the PostgreSQL engine. In the talk I want to tell about simple and small pg_variables extension. It allows you to create table variables along with scalar ones. I will tell how it can replace temporary tables, what advantages and disadvantages it has.