What's New in PostgreSQL 17 for Developer.

Slide 1

Slide 1 text

What's New for Developer's in PostgreSQL 17 Deepak Mahto DataCloudGaze Consulting

Slide 2

Slide 2 text

© 2024 All Rights Reserved About Me I am Deepak Mahto, and I like to call myself your Database Guy. ● Founder at DataCloudGaze Consulting. ● Conduct training on PostgreSQL and Migration at DatabaseGyaan. ● Loves to blog on Databases Learnings. ● I live in Mumbai and have a 3-year-old child. ● Loves to explore street food.

Slide 3

Slide 3 text

© 2024 All Rights Reserved Agenda ● Key upcoming features in PostgreSQL 17 for Developers. ○ Optimizer ○ Utility Command ○ Partitions ○ Merge ○ PL/pgSQL ○ Partitions ○ JSON ○ Trigger ○ Privilege ○ And more..

Slide 4

Slide 4 text

PostgreSQL 17 - Optimizer Sections

Slide 5

Slide 5 text

Slide 6

Slide 6 text

Slide 7

Slide 7 text

Slide 8

Slide 8 text

Slide 9

Slide 9 text

Slide 10

Slide 10 text

Slide 11

Slide 11 text

Slide 12

Slide 12 text

Slide 13

Slide 13 text

PostgreSQL 17 - PL/pgSQL

Slide 14

Slide 14 text

Slide 15

Slide 15 text

PostgreSQL 17 - Utility Command

Slide 16

Slide 16 text

Slide 17

Slide 17 text

Slide 18

Slide 18 text

Slide 19

Slide 19 text

© 2024 All Rights Reserved Utility Command - Copy ● Handling data type incompatibilities with ON_ERROR options ● Improved logging with LOG_VERBOSITY ● Observability via pg_stat_progress_copy with tuples_skipped

Slide 20

Slide 20 text

Slide 21

Slide 21 text

Slide 22

Slide 22 text

PostgreSQL 17 - New JSON Functions.

Slide 23

Slide 23 text

Slide 24

Slide 24 text

Slide 25

Slide 25 text

Slide 26

Slide 26 text

PostgreSQL 17 - Partitions.

Slide 27

Slide 27 text

Slide 28

Slide 28 text

Slide 29

Slide 29 text

Slide 30

Slide 30 text

PostgreSQL 17 - Merge

Slide 31

Slide 31 text

Slide 32

Slide 32 text

Slide 33

Slide 33 text

Slide 34

Slide 34 text

PostgreSQL 17 - psql command line

Slide 35

Slide 35 text

Slide 36

Slide 36 text

PostgreSQL 17 - Server Conﬁguration

Slide 37

Slide 37 text

Slide 38

Slide 38 text

Slide 39

Slide 39 text

© 2024 All Rights Reserved Configurable SLRU cache ● Postgres 17 adds configurable SLRU cache sizes to boost performance in large workloads. ● Splitting SLRU caches into multiple banks with locks enhances scalability. ● Configuration changes require a restart, key for workloads facing contention

Slide 40

Slide 40 text

PostgreSQL 17 - Privileges

Slide 41

Slide 41 text

Slide 42

Slide 42 text

PostgreSQL 17 - Builtin Collation

Slide 43

Slide 43 text

© 2024 All Rights Reserved New Builtin Collation ● Built-in collation provider with UTF-8 encoding ● Provides sorting semantics similar to 'C' collation ● Guaranteed immutable for consistent sort results across systems

Slide 44

Slide 44 text

PostgreSQL 17 - Event Trigger on Logon

Slide 45

Slide 45 text

Slide 46

Slide 46 text

Slide 47

Slide 47 text

© 2024 All Rights Reserved Getting Started and Test your self. docker run -d --name=pg17b3 -e POSTGRES_PASSWORD=************ postgres:17beta3 -p 5432:5435 docker exec -it pg17b3 psql -h localhost -U postgres -d postgres Running PostgreSQL 17 Beta within Docker.

Slide 48

Slide 48 text

© 2024 All Rights Reserved Thank you! https://www.datacloudgaze.com/ https://www.linkedin.com/in/mahtodeepak/ https://x.com/mahtodeepak05 https://databaserookies.wordpress.com/ Code : PGHYD30 , 30% off https://www.databasegyaan.com