The 11th Annual PostgreSQL Conference Europe

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

Transcript

1. The 11th Annual PostgreSQL Conference Europe (from developer’s perspective) Darko Živanović (@darkoziv) darko@deversity.net
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
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
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
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