title

text

Ivan Frolkov
Ivan Frolkov Postgres Professional
13:20 03 March
22 мин

Trying to gain peace of mind by using constraints

There's a common delusion that constraints should never be used as they affect performance in a negative way, interfere with regular work, and are, all in all, useless. The database is commonly perceived as just a storage without any logic. I'll explain why it isn't so and what this careless approach may lead to.

Слайды

Видео

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

  • Julien Rouhaud
    Julien Rouhaud
    Tatsuro Yamada
    Tatsuro Yamada NTT Comware
    22 мин

    Building automatic adviser and performance tuning tools in PostgreSQL

    PostgreSQL is a mature and robust RDBMS since it has 30 years of history. Over the year, its query optimizer has been enhanced and usually produces good query plans.

    However, can it always come up with good query plans? The optimization process has to use some assumptions to produce plans fast enough. Some of those assumptions are relatively easy to check (e.g. statistics are up-to-date), some harder (e.g. correct indexes are created), and some nearly impossible (e.g. making sure that the statistic samples are representative enough even for skewed data repartition). For now, given those various caveats, DBA sometimes can't always realize easily that they miss a chance to get a meaningful performance improvement.

    To help DBA to get a truly good query plan, we'll present below some tools that can help to fix some of those problems by providing a missing index adviser, looking for extended statistics to create, and row estimation error correction information to get appropriate join orders with join methods automatically.

    • pg_qualstats: provides a new index and extended statistics suggestions to gather many predicate statistics on the production workload.
    • pg_plan_advsr: provides alternative good query plans automatically to analyze iterative query executions information to fix estimation rows error.

    In this talk, we will explain how those tools work under the hood and see what can be done, how they can work together. Also, we will mention what other tools also exist for related problems. Therefore, it will be useful for DBA who are interested in improving query performance or want to check whether current settings of indexes and statistics are adequate.

  • Yana Krasteva
    Yana Krasteva Swarm64
    22 мин

    Modern DWH with open-source PostgreSQL

    PostgreSQL has a long history in DWH. Netezza, Redshift, and Greenplum have turned specific PostgreSQL releases into DWH solutions. Nowadays, with the trends in PostgreSQL towards performance improvements (better partitioning, better statistics, JIT Compilation, etc.) and advanced PostgreSQL extensions, like the Swarm64 Data Accelerator, you can create a forward-looking, no lock-in, versatile, and reliable DWH. This talk will cover the PostgreSQL and DWH trends and touch on key arguments for choosing open source PostgreSQL for DWH.

  • Daniele Varrazzo
    Daniele Varrazzo Codice Lieve
    90 мин

    Python for PostgreSQL: how to use it, how to be good at it

    Let's see, with practical examples, how to make Python and PostgreSQL talk to each other seamlessly: how to connect to a server, how to exchange data, manage transactions, passing parameters in a safe and expressive way, how to manage notifications.

    We will cover psycopg2, the most used PostgreSQL adapter for Python, but also look and the up and coming psycopg3: what will remain the same, what will change, how to better organise a Python program to make the most of PostgreSQL.

  • Mahmoud SAKR
    Mahmoud SAKR université libre de bruxelles
    Esteban Zimányi
    Esteban Zimányi ULB
    90 мин

    Managing moving objects data with MobilityDB

    MobilityDB is a moving object database extension to PostgreSQL and PostGIS. It has types and functions for storing an querying geospatial trajectories, as first class citizens. The main type is called tgeompoint (temporal geometry point). It represents a complete movement track of a geometry point, such as a car, a bird, or a person. The function speed(tgeompoint) computes the time varying speed of the object, as a tfloat (temporal float). Similar to these examples, MobilityDB has 6 temporal types, and over 300 functions. As such, it is a function-rich platform for Mobility Data Management.

    In this tutorial you will:

    • learn about moving object databases
    • write MobilityDB SQL queries and explore a database of geospatial trajectories
    • walk through the different type, indexes, and functions of MobilityDB.