Upgrade to Pro — share decks privately, control downloads, hide ads and more …

The 11th Annual PostgreSQL Conference Europe

The 11th Annual PostgreSQL Conference Europe

The 11th Annual PostgreSQL Conference Europe(from developer’s perspective)
Speaker: Darko Zivanovic @darkoziv

Aa335d9f4a299100625e6f11efd33516?s=128

AWS User Group Belgrade

November 05, 2019
Tweet

Transcript

  1. The 11th Annual PostgreSQL Conference Europe (from developer’s perspective) Darko

    Živanović (@darkoziv) darko@deversity.net
  2. None
  3. About PGConf.EU 2019 - PostgreSQL Conference Europe is by far

    the largest PostgreSQL conference in Europe. - This year PgConf.EU was held in Milan, Italy on October 15–18 and had more than 500 attendees. - In general, the conference is suitable for many different audiences: - DBAs already using PostgreSQL, or considering doing so - Developers of any kind of application, from hobbyists to large web and enterprise applications - Decision-makers interested in evaluating the world's most advanced open source database as an alternative to traditional proprietary products - PostgreSQL contributors — code, documentation, support — whatever you help the project with 3
  4. References (1) - More Than a Query Language: SQL in

    the 21st Century - Markus Winand (@MarkusWinand, @ModernSQL) - Wonderful SQL features your ORMs can use (or not) - Louise Grandjonc (@louisemeta) - Deep Dive Into PostgreSQL Indexes - Ibrar Ahmed - 25 Interesting features of PostgreSQL 12 - Jobin Augustine (@jobinau) 4
  5. References (2) - Foreign Data Wrappers and their utilization in

    real world scenarios - Boriss Mejias (@tchorix) - Performance analysis at full power - Julien Rouhaud (@rjuju123) - Jsonpath in examples and roadmap - Nikita Glukhov, Oleg Bartunov 5
  6. What is new in PostgreSQL 12 (released on 3rd October

    2019) 6
  7. What is new in PostgreSQL 12 - SQL/JSON features -

    COPY FROM with WHERE - Generated columns - Partitioning improvements - Index improvements - Security improvements 7
  8. JSON path (1) - SQL 2016 standards among other things

    contains set of SQL/JSON features for JSON processing inside of relational database. - The core of SQL/JSON is JSON path language, allowing access parts of JSON documents and make computations over them. - The following set of plain functions allows to execute "jsonpath" over "jsonb" values: - * jsonb_path_exists(jsonb, jsonpath[, jsonb, bool]), - * jsonb_path_match(jsonb, jsonpath[, jsonb, bool]), - * jsonb_path_query(jsonb, jsonpath[, jsonb, bool]), - * jsonb_path_query_array(jsonb, jsonpath[, jsonb, bool]). - * jsonb_path_query_first(jsonb, jsonpath[, jsonb, bool]). 8
  9. JSON path (2) 9 CREATE TABLE characters (data jsonb);

  10. JSON path (3) 10

  11. NoSQL users attracted by the NoSQL Postgres features 11

  12. COPY FROM with WHERE - Copying data between a file

    and a table supports filtering of records. 12
  13. Generated columns - A generated column is a special column

    that is always computed from other columns. - Generated columns reduce the need for unnecessary triggers. 13
  14. Partitioning - Synthetic benchmark claims upto 76 times improvements for

    SELECT and 420 times for UPDATEs on partitioned tables. - Users now have the ability to alter partitioned tables without blocking queries and use foreign keys to reference partitioned tables. 14
  15. REINDEX CONCURRENTLY - REINDEX CONCURRENTLY allows for read/write operations to

    still happen on the parent table while a reindexing operation is happening. - This option will basically create a new index and then replaces the existing index at the very end of the operation. REINDEX INDEX CONCURRENTLY index_1; 15
  16. Multi-factor authentication - Additionally, PostgreSQL 12 now supports a form

    of multi-factor authentication. - A PostgreSQL server can now require an authenticating client to provide a valid SSL certificate with their username using the clientcert=verify-full option and combine this with the requirement of a separate authentication method (e.g. scram-sha-256). 16
  17. Other interesting PostgreSQL features 17

  18. 18

  19. 19

  20. 20

  21. 21

  22. Window functions - Similar to an aggregate function, a window

    function operates on a set of rows. - However, it does not reduce the number of rows returned by the query. - The term window describes the set of rows on which the window function operates. - A window function returns values from the rows in a window. 22
  23. 23

  24. Grouping Sets - A grouping set is a set of

    columns by which you group. - Typically, a single aggregate query defines a single grouping set. - The GROUPING SETS allows you to define multiple grouping sets in the same query. 24
  25. 25

  26. 26

  27. 27

  28. 28

  29. 29

  30. 30

  31. Amazing SQL your ORM can (or can’t) do 31

  32. Performance Analysis with pg_stat_statements (1) - One of the most

    useful Postgres extension for performance analysis. - It is found in the contrib directory of a PostgreSQL distribution. - This means it already ships with Postgres and you don’t have to go and build it from source or install packages. You may have to enable it for your database if it is not already enabled. - It can give us info about: - Most frequent queries - Slowest queries - Queries generating most amount of temporary files - Per-query hit-ratio - Queries requiring more work_mem - ... 32
  33. Performance Analysis with pg_stat_statements (2) 33

  34. Foreign Data Wrappers (1) 34

  35. Foreign Data Wrappers (2) 35

  36. Foreign Data Wrappers (3) 36

  37. Foreign Data Wrappers (4) 37

  38. Foreign Data Wrappers (5) 38

  39. Foreign Data Wrappers (6) 39

  40. Foreign Data Wrappers (7) 40

  41. Foreign Data Wrappers (8) 41

  42. Foreign Data Wrappers (9) 42

  43. PostgreSQL on Amazon RDS - PostgreSQL Version 12 Beta 3

    is the latest version of PostgreSQL available on Amazon RDS in the Database Preview Environment. - PostgreSQL Version 11.5 is the latest stable version of PostgreSQL available on Amazon RDS. 43