title

text

Arthur Zakirov
Arthur Zakirov Postgres Professional
18:00 05 February
22 мин

Использование pg_variables в качестве временных таблиц

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.

Слайды

Видео

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

  • Pavel Molyavin
    Pavel Molyavin 2ГИС
    45 мин

    Готовим PostgreSQL в эпоху DevOps. Опыт 2ГИС

    The dark age for PostgreSQL started at 2GIS after transitioning to the microservice architecture. Every team tried to cook database on their own — by installing instances, juggling versions, trying to code deployments with numerous tools or using manual operations. It was the right time to develop a “silver bullet” — a common set of tools to solve all the problems at once. We created our own cluster solution based on well-known PostgreSQL, repmgr, pgbouncer and Barman. Despite of the complexity of our final solution, we developed a repeatable flexible deployment to accelerate postgresql cluster deployment and management. Also we deployed the our own cluster to consolidate all databases. It helped to eliminate team efforts for database management and focus on their main goals. Failover works, we tried it :-)

  • Pavel Luzanov
    Pavel Luzanov Postgres Professional
    22 мин

    Обучение и сертификация: что нового и ближайшие планы

    The first training course "DBA1. PostgreSQL Administration. Basic Course" was introduced by Postgres Professional in September 2015. The course was based on the then up-to-date version of PostgreSQL 9.4. A set of new courses has been introduced since then. In turn, the PostgreSQL database didn't stand still either.

    The report will present the current line of courses for administrators and application developers. We will also show the roadmap for the near future, which includes specialist certification.

  • Artemy Ryabinkov
    Artemy Ryabinkov Avito
    22 мин

    Практики, особенности и нюансы при работе с Postgres в 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.

  • Nikolay Samokhvalov
    Nikolay Samokhvalov Nombox LLC
    45 мин

    Промышленный подход к тюнингу PostgreSQL: эксперименты над базами данных

    Shared_buffers = 25% – is it too much or not enough? Or it's the right value?

    How can we ensure that this – pretty much outdated – recommendation suit well our needs?

    It is time to start apply enterprise-level approach to tuning postgresql.conf. Not using various blind auto-tuners or advices from old articles and blog posts, but based on the following two aspects:

    1. comprehensive database experiments, conducted in automated fashion, repeated multiple times in conditions as close to production as possible, and
    2. deep understanding of DBMS and OS internals.

    Using Nancy CLI (https://gitlab.com/postgres.ai/nancy) we will consider a concrete example: infamous shared_buffers, under various circumstances, in various projects. We will try to figure out, how to optimize this settings for given infrastructure, database, and workload.