
Transcription
Mastering PostgreSQL 13Fourth EditionBuild, administer, and maintain database applications efficiently withPostgreSQL 13Hans-Jürgen Schönig
BIRMINGHAM - MUMBAI
Mastering PostgreSQL 13Fourth EditionCopyright 2020 Packt PublishingAll rights reserved. No part of this book may be reproduced, stored in a retrieval system, or transmitted in any form or by anymeans, without the prior written permission of the publisher, except in the case of brief quotations embedded in critical articles orreviews.Every effort has been made in the preparation of this book to ensure the accuracy of the information presented. However, theinformation contained in this book is sold without warranty, either express or implied. Neither the author nor Packt Publishing orits dealers and distributors will be held liable for any damages caused or alleged to have been caused directly or indirectly by thisbook.Packt Publishing has endeavored to provide trademark information about all of the companies and products mentioned in thisbook by the appropriate use of capitals. However, Packt Publishing cannot guarantee the accuracy of this information.Commissioning Editor: Sunith ShettyAcquisition Editor: Devika BattikeContent Development Editor: Sean LoboSenior Editor: Mohammed Yusuf ImaratwaleTechnical Editor: Manikandan KurupCopy Editor: Safis EditingProject Coordinator: Aishwarya MohanProofreader: Safis EditingIndexer: Rekha NairProduction Designer: Nilesh MohiteFirst published: Jan 2018Second edition: October 2018Third edition: November 2019Fourth edition: November 2020Production reference: 1121120Published by Packt Publishing Ltd.Livery Place35 Livery StreetBirminghamB3 2PB, UK.ISBN 978-1-80056-749-8www.packt.com
Packt.comSubscribe to our online digital library for full access to over 7,000 booksand videos, as well as industry leading tools to help you plan your personaldevelopment and advance your career. For more information, please visitour website.Why subscribe?Spend less time learning and more time coding with practical eBooksand Videos from over 4,000 industry professionalsImprove your learning with Skill Plans built especially for youGet a free eBook or video every monthFully searchable for easy access to vital informationCopy and paste, print, and bookmark contentDid you know that Packt offers eBook versions of every book published,with PDF and ePub files available? You can upgrade to the eBook versionat www.packt.com and as a print book customer, you are entitled to a discounton the eBook copy. Get in touch with us at [email protected] for moredetails.At www.packt.com, you can also read a collection of free technical articles, signup for a range of free newsletters, and receive exclusive discounts andoffers on Packt books and eBooks.
ContributorsAbout the authorHans-Jürgen Schönig has 18 years' experience with PostgreSQL. He is theCEO of a PostgreSQL consulting and support company called CybertecPostgreSQL International GmbH. It has successfully served countlesscustomers around the globe. Before founding Cybertec PostgreSQLInternational GmbH in 2000, he worked as a database developer at a privateresearch company that focused on the Austrian labor market, where heprimarily worked on data mining and forecast models. He has also writtenseveral books about PostgreSQL.About the reviewerMarcelo Diaz is a software engineer with more than 15 years ofexperience, and with a special focus on PostgreSQL. He is passionate aboutopen source software and has promoted its application in critical and highdemand environments where he has worked as a software developer andconsultant for both private and public companies. He currently works veryhappily at Cybertec and as a technical reviewer for Packt Publishing. Heenjoys spending his leisure time with his daughter, Malvina, and his wife,Romina. He also likes playing football.
Packt is searching for authors likeyouIf you're interested in becoming an author for Packt, please visit authors.packtpub.com and apply today. We have worked with thousands of developers andtech professionals, just like you, to help them share their insight with theglobal tech community. You can make a general application, apply for aspecific hot topic that we are recruiting an author for, or submit your ownidea.
Table of ContentsTitle PageCopyright and CreditsMastering PostgreSQL 13 Fourth EditionAbout PacktWhy subscribe?ContributorsAbout the authorAbout the reviewerPackt is searching for authors like youPrefaceWho this book is forWhat this book coversTo get the most out of this bookDownload the example code filesConventions usedGet in touchReviews1. PostgreSQL13 OverviewWhat's new in PostgreSQL 13?Digging into SQL and developer-related topicsImproving psql command-line handlingImproving pgbenchGenerating random UUIDs more easilyDropping databases fasterAdding ALTER TABLE . DROP EXPRESSION .Making use of performance improvementsDeduplication of B-tree indexesAdding incremental sortingAdding -j 8 to reindexdbAllowing hash aggregates to spill to diskSpeeding up PL/pgSQLParallelizing VACUUM operationsAllowing skipping of WAL for full table writes
Additional performance improvementsMaking monitoring more powerfulAdditional system viewsSummary2. UnderstandingTransactions and LockingWorking with PostgreSQL transactionsHandling errors inside a transactionMaking use of SAVEPOINTTransactional DDLsUnderstanding basic lockingAvoiding typical mistakes and explicit lockingConsidering alternative solutionsMaking use of FOR SHARE and FOR UPDATEUnderstanding transaction isolation levelsConsidering Serializable Snapshot Isolation transactionsObserving deadlocks and similar issuesUtilizing advisory locksOptimizing storage and managing cleanupConfiguring VACUUM and autovacuumDigging into transaction wraparound-related issuesA word on VACUUM FULLWatching VACUUM at workLimiting transactions by making use of snapshot too oldMaking use of more VACUUM featuresSummaryQuestions3. MakingUse of IndexesUnderstanding simple queries and the cost modelMaking use of EXPLAINDigging into the PostgreSQL cost modelDeploying simple indexesMaking use of sorted outputUsing more than one index at a timeUsing bitmap scans effectivelyUsing indexes in an intelligent wayUnderstanding index de-duplicationImproving speed using clustered tables
Clustering tablesMaking use of index-only scansUnderstanding additional B-tree featuresCombined indexesAdding functional indexesReducing space consumptionAdding data while indexingIntroducing operator classesCreating an operator class for a B-treeCreating new operatorsCreating operator classesTesting custom operator classesUnderstanding PostgreSQL index typesHash indexesGiST indexesUnderstanding how GiST worksExtending GiSTGIN indexesExtending GINSP-GiST indexesBRIN indexesExtending BRIN indexesAdding additional indexesAchieving better answers with fuzzy searchingTaking advantage of pg trgmSpeeding up LIKE queriesHandling regular expressionsUnderstanding full-text searchComparing stringsDefining GIN indexesDebugging your searchGathering word statisticsTaking advantage of exclusion operatorsSummaryQuestions4. HandlingAdvanced SQLIntroducing grouping sets
Loading some sample dataApplying grouping setsInvestigating performanceCombining grouping sets with the FILTER clauseMaking use of ordered setsUnderstanding hypothetical aggregatesUtilizing windowing functions and analyticsPartitioning dataOrdering data inside a windowUsing sliding windowsUnderstanding the subtle difference between ROWS and RANGERemoving duplicates using EXCLUDE TIES and EXCLUDE GROUPAbstracting window clausesUsing on-board windowing functionsThe rank and dense rank functionsThe ntile() functionThe lead() and lag() functionsThe first value(), nth value(), and last value() functionsThe row number() functionWriting your own aggregatesCreating simple aggregatesAdding support for parallel queriesImproving efficiencyWriting hypothetical aggregatesSummary5. LogFiles and System StatisticsGathering runtime statisticsWorking with PostgreSQL system viewsChecking live trafficInspecting databasesInspecting tablesMaking sense of pg stat user tablesDigging into indexesTracking the background workerTracking, archiving, and streamingChecking SSL connectionsInspecting transactions in real time
Tracking VACUUM and CREATE INDEX progressUsing pg stat statementsCreating log filesConfiguring the postgresql.conf fileDefining log destination and rotationConfiguring syslogLogging slow queriesDefining what and how to logSummaryQuestions6. OptimizingQueries for Good PerformanceLearning what the optimizer doesA practical example – How the query optimizer handles a sample queryEvaluating join optionsNested loopsHash joinsMerge joinsApplying transformationsApplying equality constraintsExhaustive searchingChecking out execution plansMaking the process failConstant foldingUnderstanding function inliningIntroducing join pruningSpeedup set operationsUnderstanding execution plansApproaching plans systematicallyMaking EXPLAIN more verboseSpotting problemsSpotting changes in runtimeInspecting estimatesInspecting buffer usageFixing high buffer usageUnderstanding and fixing joinsGetting joins rightProcessing outer joins
Understanding the join collapse limit variableEnabling and disabling optimizer settingsUnderstanding genetic query optimizationPartitioning dataCreating inherited tablesApplying table constraintsModifying inherited structuresMoving tables in and out of partitioned structuresCleaning up dataUnderstanding PostgreSQL 13.x partitioningAdjusting parameters for good query performanceSpeeding up sortingSpeeding up administrative tasksMaking use of parallel queriesWhat is PostgreSQL able to do in parallel?Parallelism in practiceIntroducing JIT compilationConfiguring JITRunning queriesSummary7. WritingStored ProceduresUnderstanding stored procedure languagesUnderstanding the fundamentals of stored procedures versus functionsThe anatomy of a functionIntroducing dollar quotingMaking use of anonymous code blocksUsing functions and transactionsExploring various stored procedure languagesIntroducing PL/pgSQLHandling quoting and the string formatManaging scopesUnderstanding advanced error handlingMaking use of GET DIAGNOSTICSUsing cursors to fetch data in chunksUtilizing composite typesWriting triggers in PL/pgSQLWriting stored procedures in PL/pgSQL
Introducing PL/PerlUsing PL/Perl for data type abstractionDeciding between PL/Perl and PL/PerlUMaking use of the SPI interfaceUsing the SPI for set returning functionsEscaping in PL/Perl and support functionsSharing data across function callsWriting triggers in PerlIntroducing PL/PythonWriting simple PL/Python codeUsing the SPI interfaceHandling errorsImproving functionsReducing the number of function callsUsing cached plansAssigning costs to functionsUsing functions for various purposesSummaryQuestions8. ManagingPostgreSQL SecurityManaging network securityUnderstanding bind addresses and connectionsInspecting connections and performanceLiving in a world without TCPManaging pg hba.confHandling SSLHandling instance-level securityCreating and modifying usersDefining database-level securityAdjusting schema-level permissionsWorking with tablesHandling column-level securityConfiguring default privilegesDigging into RLSInspecting permissionsReassigning objects and dropping usersSummary
Questions9. HandlingBackup and RecoveryPerforming simple dumpsRunning pg dumpPassing passwords and connection informationUsing environment variablesMaking use of .pgpassUsing service filesExtracting subsets of dataHandling various formatsReplaying backupsHandling global dataSummaryQuestions10. MakingSense of Backups and ReplicationUnderstanding the transaction logLooking at the transaction logUnderstanding checkpointsOptimizing the transaction logTransaction log archiving and recoveryConfiguring for archivingConfiguring the pg hba.conf fileCreating base backupsReducing the bandwidth of backupsMapping tablespacesUsing different formatsTesting transaction log archivingReplaying the transaction logFinding the right timestampCleaning up the transaction log archiveSetting up asynchronous replicationPerforming a basic setupImproving securityHalting and resuming replicationChecking replication to ensure availabilityPerforming failovers and understanding timelinesManaging conflicts
Making replication more reliableUpgrading to synchronous replicationAdjusting durabilityMaking use of replication slotsHandling physical replication slotsHandling logical replication slotsUse cases for logical replication slotsMaking use of the CREATE PUBLICATION and CREATE SUBSCRIPTION commandsSummaryQuestions11. Decidingon Useful ExtensionsUnderstanding how extensions workChecking for available extensionsMaking use of contrib modulesUsing the adminpack moduleApplying bloom filtersDeploying btree gist and btree gindblink – considering phasing outFetching files with file fdwInspecting storage using pageinspectInvestigating caching with pg buffercacheEncrypting data with pgcryptoPrewarming caches with pg prewarmInspecting performance with pg stat statementsInspecting storage with pgstattupleFuzzy searching with pg trgmConnecting to remote servers using postgres fdwHandling mistakes and typosOther useful extensionsSummary12. TroubleshootingPostgreSQLApproaching an unknown databaseInspecting pg stat activityQuerying pg stat activityTreating Hibernate statementsFiguring out where queries come fromChecking for slow queries
Inspecting individual queriesDigging deeper with perfInspecting the logChecking for missing indexesChecking for memory and I/OUnderstanding noteworthy error scenariosFacing clog corruptionUnderstanding checkpoint messagesManaging corrupted data pagesCareless connection managementFighting table bloatSummaryQuestions13. Migratingto PostgreSQLMigrating SQL statements to PostgreSQLUsing lateral joinsSupporting lateral joinsUsing grouping setsSupporting grouping setsUsing the WITH clause – common table expressionsSupporting the WITH clauseUsing the WITH RECURSIVE clauseSupporting the WITH RECURSIVE clauseUsing the FILTER clauseSupporting the FILTER clauseUsing windowing functionsSupporting windowing and analyticsUsing ordered sets – the WITHIN GROUP clauseSupporting the WITHIN GROUP clauseUsing the TABLESAMPLE clauseSupporting the TABLESAMPLE clauseUsing limit/offsetSupporting the FETCH FIRST clauseUsing the OFFSET clauseSupporting the OFFSET clauseUsing temporal tablesSupporting temporal tables
Matching patterns in time seriesMoving from Oracle to PostgreSQLUsing the oracle fdw extension to move dataUsing ora migrator for fast migrationCYBERTEC Migrator – migration for the "big boys"Using Ora2Pg to migrate from OracleCommon pitfallsHandling data in MySQL and MariaDBChanging column definitionsHandling null valuesExpecting problemsMigrating data and schemasUsing pg chameleonUsing FDWsSummaryOther Books You May EnjoyLeave a review - let other readers know what you think
PrefaceThis updated fourth edition of Mastering PostgreSQL 13 will help youunderstand PostgreSQL administration and build dynamic databasesolutions for enterprise apps with the latest release of PostgreSQL, teachingyou how to design both physical and technical aspects of systemarchitecture with ease.Starting with an introduction to the newly released features in PostgreSQL13, this book will help you build efficient and fault-tolerant PostgreSQLapps. You'll also delve into advanced PostgreSQL features, includinglogical replication, database clusters, performance tuning, advancedindexing, monitoring, and user management, to manage and maintain yourdatabase. You will then work with the PostgreSQL optimizer, configurePostgreSQL for high speed, and move from Oracle to PostgreSQL. Afterthat, you'll cover transactions, locking, and indexes, and learn how toperform query optimization for improved performance. Later, you'll focuson how to manage network security, and delve into backup and replicationwhile exploring useful PostgreSQL extensions to help you optimize theperformance of large databases.By the end of this book, you'll be able to get the most out of your databaseby performing advanced administrative tasks.
Who this book is forThis database administrator book is for PostgreSQL developers andadministrators and database professionals who want to implement advancedfunctionalities and master complex administrative tasks with PostgreSQL13. Prior experience in PostgreSQL and familiarity with the basics ofdatabase administration are expected.
What this book covers, PostgreSQL 13 Overview, provides an overview of PostgreSQLand its features. You will learn about the new functionalities available inPostgreSQL 13.Chapter 1, Understanding Transactions and Locking, covers one of the mostimportant aspects of any database system: proper database work is usuallynot possible without the existence of transactions. Understandingtransactions and locking is vital to performance, as well as professionalwork.Chapter 2, Making Use of Indexes, covers everything you need to know aboutindexes. Indexes are key to performance and are therefore an importantcornerstone if you want a good user experience and high throughput. All theimportant aspects of indexing will be covered.Chapter 3, Handling Advanced SQL, introduces you to some of the mostimportant concepts of modern SQL. You will learn about windowingfunctions as well as other important current elements of SQL.Chapter 4, Log Files and System Statistics, guides you through administrativetasks such as log file management and monitoring. You will learn how toinspect your servers and extract runtime information from PostgreSQL.Chapter 5, Optimizing Queries for Good Performance, tells you everythingyou need to know about good PostgreSQL performance. The chapter coversSQL tuning as well as information about memory management.Chapter 6, Writing Stored Procedures, teaches you some of the advancedtopics related to server-side code. The most important server-sideprogramming languages are covered and important aspects are pointed out.Chapter 7
, Managing PostgreSQL Security, is designed to help you improvethe security of your server. The chapter features everything from usermanagement to Row-Level Security (RLS). Information about encryptionis also included.Chapter 8, Handling Backup and Recovery, is all about backups and datarecovery. You will learn how to back up your data, which will enable you torestore things in the event of a disaster.Chapter 9, Making Sense of Backups and Replication, is all aboutredundancy. You will learn how to asynchronously and synchronouslyreplicate PostgreSQL database systems. All modern features are covered asextensively as possible.Chapter 10, Deciding on Useful Extensions, describes widely used modulesthat add more functionality to PostgreSQL. You will learn about the mostcommon extensions.Chapter 11, Troubleshooting PostgreSQL, offers a systematic approach tofixing problems in PostgreSQL. It will enable you to spot commonproblems and approach them in an organized way.Chapter 12, Migrating to PostgreSQL, is the final chapter of this book andshows you how to migrate from commercial databases to PostgreSQL. Thechapter covers the most important databases migrated these days.Chapter 13To get the most out of this bookThis book has been written for a broad audience. In order to follow theexamples presented in this book, it makes sense to have at least someexperience with SQL and, perhaps, even PostgreSQL in general (althoughthis is not mandatory). In general, it is a good idea to have some familiaritywith the Unix command-line interface as well.
Software/hardware covered in the bookOS requirementsPostgreSQL 13Windows/Linux/macOSIf you are using the digital version of this book, we advise you to typethe code yourself or access the code via the GitHub repository (linkavailable in the next section). Doing so will help you avoid any potentialerrors related to the copying and pasting of code.At the end of each chapter, you will find questions for self-assessment. Theanswers to those questions can be found in the GitHub repository (link inthe following section).Download the example code filesYou can download the example code files for this book from GitHub at reSQL-13-Fourth-Edition. In casethere's an update to the code, it will be updated on the existing GitHubrepository.We also have other code bundles from our rich catalog of books and videosavailable at https://github.com/PacktPublishing/. Check them out!Conventions usedThere are a number of text conventions used throughout this book.: Indicates code words in text, database table names, folder names,filenames, file extensions, pathnames, dummy URLs, user input, andTwitter handles. Here is an example: "You can just use COMMIT, COMMIT WORK, orCOMMIT TRANSACTION."CodeInText
A block of code is set as follows:test # \h COMMITCommand: COMMITDescription: commit the current transactionSyntax:COMMIT [ WORK TRANSACTION ] [ AND [ NO ] CHAIN ]URL: Bold: Indicates a new term, an important word, or words that you seeonscreen. For example, words in menus or dialog boxes appear in the textlike this. Here is an example: "For normal Online Transaction Processing(OLTP), read committed has various advantages, because changes can beseen much earlier and the odds of unexpected errors are usually lower."Warnings or important notes appear like this.Tips and tricks appear like this.Get in touchFeedback from our readers is always welcome.General feedback: If you have questions about any aspect of thisbook, mention the book title in the subject of your message and email usat [email protected]: Although we have taken every care to ensure the accuracy of ourcontent, mistakes do happen. If you have found a mistake in this book, wewould be grateful if you would report this to us. Please visit www.packtpub.com/support/errata, selecting your book, clicking on the Errata Submission Formlink, and entering the details.Piracy: If you come across any illegal copies of our works in any form onthe Internet, we would be grateful if you would provide us with the locationaddress or website name. Please contact us at [email protected] with a linkto the material.
If you are interested in becoming an author: If there is a topic that youhave expertise in and you are interested in either writing or contributing to abook, please visit authors.packtpub.com.ReviewsPlease leave a review. Once you have read and used this book, why notleave a review on the site that you purchased it from? Potential readers canthen see and use your unbiased opinion to make purchase decisions, we atPackt can understand what you think about our products, and our authorscan see your feedback on their book. Thank you!For more information about Packt, please visit packt.com.
PostgreSQL 13 OverviewPostgreSQL has come a long way. Over 30 years of constant developmenthave provided us with one of the most exceptional open source productsaround. Over the years, PostgreSQL has become more and more popular.PostgreSQL 13 offers many more features and will give solutions an evenbigger boost than ever before. Many of the new features open the door tofurther development in the future and will enable developers to implementcutting-edge technologies for decades to come. In this chapter, you will beintroduced to these new features and will get an overview of what has beenimproved, added, and even changed.The following topics will be covered:What's new in PostgreSQL 13?SQL and developer-related featuresBackup, recovery, and replicationPerformance-related topicsStorage-related topicsAll relevant features will be covered. Of course, there is always more, andthousands of other tiny changes have made it into PostgreSQL 13. Whatyou will see in this chapter are the highlights of the new release.What's new in PostgreSQL 13?PostgreSQL 13 is a major milestone and many big and small features havemade it into the database core this time. In this chapter, you will beintroduced to the most important developments in the PostgreSQL world.Let's get started and see what the developers have come up with.Digging into SQL and developer-related topics
PostgreSQL 13 provides some new features that are especially important todevelopers.Improving psql command-line handlingIn PostgreSQL 13 a small extension has been committed that basicallyhelps to track the state of a session in psql better. You can now see whether atransaction is running successfully or not. Let's take a look at a simplelisting:test # BEGIN;BEGINtest *# SELECT 1;?column?---------1(1 row)test *# SELECT 1 / 0;ERROR: division by zerotest !# SELECT 1 / 0;ERROR: current transaction is aborted, commands ignored until end of transactionblocktest !# COMMIT;ROLLBACKWhat is so special about this code block? The thing to notice is that theprompt has changed. The reason is that %x has been added to PROMPT1 andPROMPT2. We can now see instantly if a transaction is active, in dire straits, orif no transaction is active. This makes command-line handling a bit easierand the expectation is that fewer errors will happen.Improving pgbenchis one of the most commonly used tools to benchmark a PostgreSQLdatabase. In older versions, pgbench created some standard tables. With theintroduction of PostgreSQL 13, the default dataset can now be partitioned(out of the box).pgbenchHere is what happens:% pgbench -i -s 100 --partitions 10.done in 19.70 s (drop tables 0.00 s, create tables 0.03 s, generate 7.34 s, vacuum10.24 s, primary keys 2.08 s).
test # \d List OF relationsSchema Name TYPE -------- --------------------- ------------------- public pgbench accounts partitioned TABLE public pgbench accounts 1 TABLE public pgbench accounts 10 TABLE public pgbench accounts 2 TABLE public pgbench accounts 3 TABLE .The -- partitions tell pgbench how many partitions to create. It is now possibleto benchmark a partitioned table in relation to a non-partitioned defaultdataset more easily.Generating random UUIDs more easilyIn older versions, we had to load an extension to handle UUIDs. Manyusers did not know that those extensions actually existed or did not want toenable them for whatever reason. In PostgreSQL 13, it is possible togenerate random UUIDs easily without any extension. Here is how itworks:test # SELECT gen random uuid();gen random b5d5-442b-a40b-9c6a9d51a9a1(1 row)Dropping databases fasterThe ability to drop databases faster is an important one and definitely one ofmy favorite new features. What was the problem?postgres # DROP DATABASE test;ERROR: database "test" is being accessed by other usersDETAIL: There is 1 other session using the database.A database could only be dropped if nobody else was connected to thedatabase. For many users, this was hard to achieve. Often, new connectionscome in all the time, and running ALTER DATABASE to block new connectionsand drop existing ones just to kill the database was simply too cumbersome.The WITH (force) option dramatically simplifies things from the end user'sperspective:
postgres # DROP DATABASE test WITH (force);DROP DATABASEThe database will be dropped regardless of what is going on in the system,which makes the process much more reliable.Adding ALTER TABLE . DROP EXPRESSION .PostgreSQL has the ability to materialize the output of an expression. Thefollowing listing shows how a generated column can be used:test # CREATE TABLE t test (aint,bint,c int GENERATED ALWAYS AS (a * b) STORED);CREATE TABLEtest # INSERT INTO t test (a, b) VALUES (10, 20);INSERT 0 1As you can see, one row has been added to the table:test # SELECT * FROM t test;a b c---- ---- ----10 20 200(1 row)The question now is: How can we get rid of generated expressions again?PostgreSQL 13 has the answer:test # ALTER TABLE t test ALTER COLUMN c DROP EXPRESSION ;ALTER TABLEcis now an ordinary column just like all the others:test # \d t test;Table "public.t test"Column Type Collation Nullable Default-------- --------- ----------- ---------- --------a integer b integer c integer You can now insert into c directly without getting an error.Making use of performance improvements
PostgreSQL 13 adds a couple of performance improvements. In thissection, you will learn about some of the more relevant improvements thatcan make a difference in real life. Of course, there are hundreds of smallchanges that have been made here, but the aim of this chapter is really tofocus on the most important aspects that make a real difference.Deduplication of B-tree indexesB-trees are by far the most important index structure in the PostgreSQLworld. Most indexes out there are B-trees. In PostgreSQL 13, B-trees havebeen improved substantially. In older versions, identical entries were storedindividually in the index. If you had 1 million table entries for hans, theindex would actually have 1 million copies of "hans" in the data structure aswell. In PostgreSQL 12, the typical index entry used to be "(value, ctid)".This has been changed. PostgreSQL will now handle duplicates much moreefficiently.Let's create an example and see how this works:test #CREATEtest #INSERTCREATE TABLE tab (a int, b int);TABLEINSERT INTO tab SELECT id, 1 FROM generate series(1, 5000000) AS id;0 5000000In this case, we have created 5 million rows. The a column has 5 milliondifferent values while the b column contains 5 million identical entries. Let'screate two indexes and see what happens:test #CREATEtest #CREATECREATE INDEX idx a ON tab (a);INDEXCREATE INDEX idx b ON tab (b);INDEXThe size of the table itself has not changed in PostgreSQL 13. We will seethe same size as before:test # \d List o
several books about PostgreSQL. About the reviewer Marcelo Diaz is a software engineer with more than 15 years of experience, and with a special focus on PostgreSQL. He is passionate about open source software and has promoted its application in critical and high-demand environments where he has worked as a software developer andFile Size: 3MB