book

PostgreSQL: Up and Running, 3rd Edition

by Regina O. Obe, Leo S. Hsu

October 2017

Intermediate to advanced

314 pages

8h 13m

English

O'Reilly Media, Inc.

Book available

Read now

Unlock full access

AudienceFor More Information on PostgreSQLCode and Output FormattingConventions Used in This BookUsing Code ExamplesO’Reilly SafariHow to Contact Us
Why PostgreSQL?Why Not PostgreSQL?Where to Get PostgreSQLAdministration ToolspsqlpgAdminphpPgAdminAdminerPostgreSQL Database ObjectsWhat’s New in Latest Versions of PostgreSQL?Why Upgrade?Features Introduced in PostgreSQL 10Features Introduced in PostgreSQL 9.6Features Introduced in PostgreSQL 9.5Features Introduced in PostgreSQL 9.4Database DriversWhere to Get HelpNotable PostgreSQL Forks
Configuration FilesMaking Configurations Take EffectThe postgresql.conf FileThe pg_hba.conf FileManaging ConnectionsCheck for Queries Being BlockedRolesCreating Login RolesCreating Group RolesDatabase CreationTemplate DatabasesUsing SchemasPrivilegesTypes of PrivilegesGetting StartedGRANTDefault PrivilegesPrivilege IdiosyncrasiesExtensionsInstalling ExtensionsCommon ExtensionsBackup and RestoreSelective Backup Using pg_dumpSystemwide Backup Using pg_dumpallRestoring DataManaging Disk Storage with TablespacesCreating TablespacesMoving Objects Among TablespacesVerboten PracticesDon’t Delete PostgreSQL Core System Files and BinariesDon’t Grant Full OS Administrative Privileges to the Postgres System Account (postgres)Don’t Set shared_buffers Too HighDon’t Try to Start PostgreSQL on a Port Already in Use
Environment VariablesInteractive versus Noninteractive psqlpsql CustomizationsCustom PromptsTiming ExecutionsAutocommit CommandsShortcutsRetrieving Prior Commandspsql GemsExecuting Shell CommandsWatching StatementsRetrieving Details of Database ObjectsCrosstabsDynamic SQL ExecutionImporting and Exporting Datapsql Importpsql ExportCopying from or to ProgramBasic Reporting
Getting StartedOverview of FeaturesConnecting to a PostgreSQL ServerNavigating pgAdminpgAdmin FeaturesAutogenerating Queries from Table DefinitionsAccessing psql from pgAdmin3Editing postgresql.conf and pg_hba.conf from pgAdmin3Creating Database Assets and Setting PrivilegesImport and ExportBackup and RestorepgScriptGraphical ExplainJob Scheduling with pgAgentInstalling pgAgentScheduling JobsHelpful pgAgent Queries
NumericsSerialsGenerate Series FunctionTextualsString FunctionsSplitting Strings into Arrays, Tables, or SubstringsRegular Expressions and Pattern MatchingTemporalsTime Zones: What They Are and Are NotDatetime Operators and FunctionsArraysArray ConstructorsUnnesting Arrays to RowsArray Slicing and SplicingReferencing Elements in an ArrayArray Containment ChecksRange TypesDiscrete Versus Continuous RangesBuilt-in Range TypesDefining RangesDefining Tables with RangesRange OperatorsJSONInserting JSON DataQuerying JSONOutputting JSONBinary JSON: jsonbEditing JSONB dataXMLInserting XML DataQuerying XML DataFull Text SearchFTS ConfigurationsTSVectorsTSQueriesUsing Full Text SearchRanking ResultsFull Text StrippingFull Text Support for JSON and JSONBCustom and Composite Data TypesAll Tables Are Custom Data TypesBuilding Custom Data TypesComposites and NULLsBuilding Operators and Functions for Custom Types
TablesBasic Table CreationInherited TablesPartitioned TablesUnlogged TablesTYPE OFConstraintsForeign Key ConstraintsUnique ConstraintsCheck ConstraintsExclusion ConstraintsIndexesPostgreSQL Stock IndexesOperator ClassesFunctional IndexesPartial IndexesMulticolumn Indexes
ViewsSingle Table ViewsUsing Triggers to Update ViewsMaterialized ViewsHandy ConstructionsDISTINCT ONLIMIT and OFFSETShorthand CastingMultirow InsertILIKE for Case-Insensitive SearchANY Array SearchSet-Returning Functions in SELECTRestricting DELETE, UPDATE, and SELECT from Inherited TablesDELETE USINGReturning Affected Records to the UserUPSERTs: INSERT ON CONFLICT UPDATEComposite Types in QueriesDollar QuotingDOFILTER Clause for AggregatesPercentiles and ModeWindow FunctionsPARTITION BYORDER BYCommon Table ExpressionsBasic CTEsWritable CTEsRecursive CTELateral JoinsWITH ORDINALITYGROUPING SETS, CUBE, ROLLUP
Anatomy of PostgreSQL FunctionsFunction BasicsTriggers and Trigger FunctionsAggregatesTrusted and Untrusted LanguagesWriting Functions with SQLBasic SQL FunctionWriting SQL Aggregate FunctionsWriting PL/pgSQL FunctionsBasic PL/pgSQL FunctionWriting Trigger Functions in PL/pgSQLWriting PL/Python FunctionsBasic Python FunctionWriting PL/V8, PL/CoffeeScript, and PL/LiveScript FunctionsBasic FunctionsWriting Aggregate Functions with PL/V8Writing Window Functions in PL/V8
EXPLAINEXPLAIN OptionsSample Runs and OutputGraphical OutputsGathering Statistics on StatementsWriting Better QueriesOverusing Subqueries in SELECTAvoid SELECT *Make Good Use of CASEUsing FILTER Instead of CASEParallelized QueriesWhat Does a Parallel Query Plan Look Like?Parallel ScansParallel JoinsGuiding the Query PlannerStrategy SettingsHow Useful Is Your Index?Table StatisticsRandom Page Cost and Quality of DrivesCaching

Replication OverviewReplication JargonEvolution of PostgreSQL ReplicationThird-Party Replication OptionsSetting Up Full Server ReplicationConfiguring the MasterConfiguring the Slaves for Full Server Cluster ReplicationInitiating the Streaming Replication ProcessReplicating Only Some Tables or Databases with Logical ReplicationForeign Data WrappersQuerying Flat FilesQuerying Flat Files as Jagged ArraysQuerying Other PostgreSQL ServersQuerying Other Tabular Formats with ogr_fdwQuerying Nonconventional Data Sources
Windows and Desktop LinuxCentOS, Fedora, Red Hat, Scientific LinuxDebian, UbuntuFreeBSDmacOS
Database Backup Using pg_dumpServer Backup: pg_dumpallDatabase Restore: pg_restorepsql Interactive Commandspsql Noninteractive Commands

Content preview from PostgreSQL: Up and Running, 3rd Edition

Chapter 9. Query Performance Tuning

Sooner or later, we’ll all face a query that takes just a bit longer to execute than we have patience for. The best and easiest fix is to perfect the underlying SQL, followed by adding indexes and updating planner statistics. To guide you in these pursuits, PostgreSQL comes with a built-in explainer that tells you how the query planner is going to execute your SQL. Armed with your knack for writing flawless SQL, your instinct to sniff out useful indexes, and the insight of the explainer, you should have no trouble getting your queries to run as fast as your hardware budget will allow.

EXPLAIN

The easiest tools for targeting query performance problems are the EXPLAIN and EXPLAIN (ANALYZE) commands. EXPLAIN has been around since the early years of PostgreSQL. Over time the command has matured into a full-blown tool capable of reporting highly detailed information about the query execution. Along the way, it added more output formats. EXPLAIN can even dump the output to XML, JSON, or YAML.

Perhaps the most exciting enhancement for the casual user came several years back when pgAdmin introduced graphical explain. With a hard and long stare, you can identify where the bottlenecks are in your query, which tables are missing indexes, and whether the path of execution took an unexpected turn.