title

text

Artemy Ryabinkov
Artemy Ryabinkov Avito
17:00 06 February
22 мин

Best Practices and Practical Nuances when working with Postgres using Go

In my talk I'll tell you about practices of working with Postgres in the Go-services. I’ll describe general advantages and disadvantages of the basic tools that are commonly used when working with Postgres using Go. Of course, we will touch on the nuances that need to be taken into account when your services are running inside the Kubernetes. I will also talk about Avito’s experience in providing a database of product’s developers. This presentation will be of interest to developers who want to avoid problems when working with Postgres, and will be useful to DBA who want to know what difficulties customers face in their database.

Слайды

Видео

Другие доклады

  • Ivan Panchenko
    Ivan Panchenko Postgres Professional
    90 мин

    NoSQL/PL: Programming in non-SQL-like procedural languages

    Workshop on Server-Side development in procedural languages PL/Perl ,PL/Python, PL / v8 inside PostgreSQL and Postgres Pro. You will not only learn what they are for but also how to use them correctly and what results can be achieved using them.

  • Esteban Zimányi
    Esteban Zimányi ULB
    45 мин

    MobilityDB: A PostgreSQL extension for mobility data management

    We will be presenting MobilityDB, a PostgreSQL extension that extends the type system of PostgreSQL and PostGIS with abstract data types for representing moving object data. These types can represent the evolution on time of values of some element type, called the base type of the temporal type. For instance, temporal integers may be used to represent the evolution on time of the number of employees of a department. In this case, the data type is “temporal integer” and “integer” is the base type. Similarly, a temporal float may be used to represent the evolution on time of the temperature of a room. As another example, a temporal point may be used to represent the evolution on time of the location of a car, as reported by GPS devices. Temporal types are useful because representing values that evolve in time is essential in many applications, for example in mobility applications.

    The temporal types in MobilityDB are based on the bool, int, float, and text base types provided by PostgreSQL, and on the geometry and geography base types provided by PostGIS (restricted to 2D or 3D points). MobilityDB follows the ongoing OGC standards on Moving Features (http://www.opengeospatial.org/standards/movingfeatures), and in particular the OGC Moving Features Access, which specifies operations that can be applied to time-varying geometries.

    A rich set of functions and operators is available to perform various operations on temporal types. In general there are three classes:

    • Lifed functions and operators: the operators on the base types (such as arithmetic operators and for integers and floats, spatial relationships and distance for geometries) are intuitively generalized when the values evolve in time. Spatiotemporal functions in MobilityDB generalize spatial functions provided by PostGIS for both "geometry" and "geography" types, for instance the "ST_Intersection". Basically, MobilityDB takes care of the temporal aspects and delegates the spatial processing to PostGIS.
    • Temporal functions and operators: they process the temporal dimension of the value which can be an instant, a range, an array of instant, or an array of ranges. Examples are the atperiods function that restricts a temporal type to a given array of time ranges, and the duration function that extracts the definition time of a value.
    • Spatiotemporal functions and operators: all remaining functions fall into this category. Examples are speed(tgeompoint/tgeogpoint), azimuth(tgeompoint/tgeogpoint), maxValue(tfloat/tint), twAvg(tfloat) a time weighted average, etc.

    Both GiST and SP-GiST have been extended to support the temporal types. The GiST index implements an R-tree for temporal alphanumeric types and a TB-tree for temporal point types. The SP-GiST index implements a Quad-tree for temporal alphanumeric types and an Oct-tree for temporal point types. The approach used for developing SP-GIST indexes for MobilityDB allowed us to add SP-GIST indexes for 2-dimensional, 3-dimensional and n-dimensional geometries in PostGIS.

    Two types of numeric aggregate functions are available. In addition to the traditional functions min, max, count, sum, and avg, there are window (also known as cumulative) versions of them. Given a time interval w, the window aggregate functions compute the value of the function at an instant t by considering the values during the interval [t − w, t]. In contrast to standard aggregation, temporal aggregation may return a result which is of a bigger size than the input. For this reason, the temporal aggregate functions have been extremely optimized in order to perform efficiently.

    MobilityDB has a preliminary implementation of the statistic collector functions and the selectivity functions for the temporal types.

    In terms of size, the extension is made of 67k lines of C code, 19k lines of SQL code, 67k lines of SQL unit tests. It defines 40 types, 2300 functions, and 1350 operators.

    The talk will illustrate the spatiotemporal concepts and the data model of the temporal types. It will briefly describe the components of MobilityDB: indexing, aggregations, functions and operators, and the SQL interface. Query examples and uses cases will be illustrated allover the talk. The current status of MobilityDB and the planned development will also be presented.

    The talk shall be given by: - Esteban Zimányi: Professor and Director of the Department of Computer and Decision Engineering of the Universite Libre de Bruxelles. - Mahmoud SAKR: Postdoc researcher in the Universite Libre de Bruxelles.

  • Oleg Bartunov
    Oleg Bartunov Postgres Professional
    45 мин

    Professional Postgres

    The famous Russian PostgreSQL developer Oleg Bartunov will open the conference with his report on how and why PostgreSQL has turned from an open source university project into modern industrial grade database.

  • Alexander Fedorov
    Alexander Fedorov dbeaver.com
    Andrey Hitrin
    Andrey Hitrin RedSys
    22 мин

    The Ultimate Tool: Xobot IDE

    IIn the world of programming, the creation of source code for databases "procedural extensions" is something solitary. Most DBMS offers procedural languages and "stored procedures" to create procedural extensions. In Postgres the number of supported procedural languages has already exceeded a dozen.

    Traditionally, stored procedures have many applications: it is difficult to resist the attraction to perform a data operation directly in the storage, especially in the Enterprise development. This approach quickly leads to the spreading of the business logic and dramatically increases the cost of support and development of the system as a whole.

    The life cycle of stored procedures makes it difficult to use standard Change Management tools and practices. It is necessary to adapt the operations upon the stored procedures to the standards of Change Management, yet staying within the comfortable development practices.

    We'll look at the typical tasks of the procedural extensions development and discuss the solutions we are implementing in the Xobot IDE.