Download as PDF, PPTX
Uploaded byMydbops
PostgreSQL 15 and its Major Features -(Aakash M - Mydbops) - Mydbops Opensource Database Meetup -13.
The document discusses the features and improvements of PostgreSQL 15, highlighting performance enhancements in system administration, logical replication, SQL, and security. Key improvements include optimized sorting algorithms, support for parallelism, and new security features such as revoking public create privileges and implementing security invoker views. It also outlines methods for upgrading to PostgreSQL 15, including logical dump/restore and binary in-place upgrade.
PostgreSQL 15 and its Major Features -(Aakash M - Mydbops) - Mydbops Opensource Database Meetup -13.
- 1. PostgreSQL 15 -Features & Improvements November 12th, 2022 Mydbops Database Meetup -13 Aakash M Database Reliabiltiy Engineer, Mydbops
- 2. Database Reliabiltiy Engineer Interestedin MySQL and PostgreSQL MySQL & AWS Certified Professional Tech Speaker/Blogger Aakash M About Me
- 3. • Services ontop open source databases • Founded in 2016 • 80 Member team • Assisted over 500+ Customers • AWS Partner , ISO certified and a PCI Certified Organisation About Mydbops
- 4. Mydbops Services Consulting Services Managed Services Focuses onMySQL, MongoDB, PostgreSQL Targeted Engagement 24 x 7 DBA Team
- 5.
- 6. PostgreSQL 10 Native Partitioning LogicalReplication Parallelism in Query Execution
- 7. PostgreSQL 11 Foreign Keysupport Covering Indexes Instant Column Addition Index Creation in parallel
- 8. PostgreSQL 12 Generated ColumnSupport Removed Recovery.conf Reindex Concurrent
- 9. PostgreSQL 13 • Deduplication ParallelVacuum Dynamic Replication Configuration
- 10. PostgreSQL 14 Reduced B-TreeBloat Replicating ongoing transactions Idle_session_timeout
- 11. What is newin PostgreSQL 15 ?
- 12. PostgreSQL 15 Performance Improvements 01 SystemAdministration 02 Logical Replication 03 Enhancements in SQL 04 Security 05
- 13.
- 14. Improved Sorting Algorithms •Why does Sort Performance matter ? • Not only in Order By • Group by, Union, Window Function • improvements make sort operation faster
- 15. Improvement in SingleColumn Sort • Single Column Sort • Select column1 from table order by column1; • Not this • Select column1, column2 from table order by column1;
- 16. Improvement in SingleColumn Sort • Entire tuple during sort operation • Only particular column 's Datum • No need to copy entire tuple
- 17. Reduce Memory Consumptionin Sorting • Two Memory Allocators • aset • Generation • Sorting used "aset" allocator • Allocates memory in the power of two (2^n) • Unnecessary memory allocation
- 18. Reduce Memory Consumptionin Sorting • Changed to Generation Allocator • Never round up allocation size
- 19. Support for Parallelism •Since 9.6 • Select Distinct • Past only Single CPU/Process • Multiple numbers of workers in Parallel
- 20.
- 21.
- 22. Stat Collector Gone •Track all activities • pg_stat_* views • IO Usage on pg_stat_tmp
- 23. Stat Collector Gone •Staticstics in Shared Memory • stats_temp_directory parameter gone • Checkpoint process
- 24. Alter System • Introducedin PostgreSQL 9.4 • Only superuser can do it • GRANT ALTER SYSTEM on PARAMETER <parametername> to <role>;
- 25. Allow Archiving viamodules • Running a shell command - archive_command • Performance Overhead • New parameter archive_library added • basic_archive contrib module
- 26. Backup and LoggingFormat Pg_basebackup: • supports server-side , client-side compression • Reduced network bandwidth
- 27. Backup and LoggingFormat Logging_format: • stderr, csvlog, syslog • New value - jsonlog added
- 28.
- 29. Filters in LogicalReplication • Can filter rows and columns
- 30. Improved Error Handling •Replication break if any error occurs • Can skip the transaction • Alter SUBSCRIPTION .. SKIP • pg_replication_origin_advance()
- 31.
- 32. Revoke Public Createfrom Public Schema • Allowed to create objects in Public schema • Need to provide it explicitly
- 33. Revoke Public Createfrom Public Schema V14: V15:
- 34. Security Invoker Views •Security Definer - Executed with the privileges of the user that owns it • Security Invoker - Checks the privileges of the user of the view rather than owner
- 35.
- 36.
- 37.
- 38. Unique Null • NULL!=NULL •Unique Indexes and Constraints
- 39.
- 40. Unique Null • UniqueNULLS NOT DISTINCT • Unique NULLS DISTINCT
- 41. Merge • New SQLCommand • Single SQL statement • When Matched - Action 1 in target table • When Not Matched - Action 2 in target table
- 42.
- 43. How to Upgrade •Logical Dump/Restore • Binary In-Place Upgrade • Logical Replication
- 44. Logical Dump/Restore • Simple •Semi-online mode • Flexible • Extremely slow
- 45. Binary In-Place Upgrade •Very fast • Requires testing • No semi-online • Lose replicas
- 46. Logical Replication • MinimalDowntime • Complex method • A lot of requirements
- 47. References • https://www.postgresql.org/about/news/postgresql-15-released-2526/ • depesz.com/tag/pg15/ •https://www.cybertec-postgresql.com/en/a-primer-on-postgresql-upgrade- methods/
- 48. Reach Us :Info@mydbops.com Thank You