Mastering PostgreSQL 9.6

Name: Mastering PostgreSQL 9.6 | A comprehensive guide for PostgreSQL 9.6 developers and administrators
Brand: Packt Publishing
Price: 44.39 EUR
Availability: OnlineOnly

A comprehensive guide for PostgreSQL 9.6 developers and administrators

Hans-Jurgen Schonig(Autor*in)

Packt Publishing

1. Auflage

Erschienen am 30. Mai 2017

416 Seiten

E-Book

ePUB mit Adobe-DRM

Systemvoraussetzungen

978-1-78355-536-9 (ISBN)

44,39 €inkl. 7% MwSt.

Systemvoraussetzungen

für ePUB mit Adobe-DRM

E-Book Einzellizenz

Als Download verfügbar

Beschreibung

Weitere Details

Inhalt

Cover
Copyright
Credits
About the Author
About the Reviewer
www.PacktPub.com
Customer Feedback
Table of Contents
Preface
Chapter 1: PostgreSQL Overview
What is new in PostgreSQL 9.6?
Understanding new database administration functions
Killing idle sessions
Finding more detailed information in pg_stat_activity
Tracking vaccum progress
Improving vacuum speed
Digging into new SQL and developer-related functions
Using new backup and replication functionality
Streamlining wal_level and monitoring
Using multiple synchronous standby servers
Understanding performance-related features
Improving relation extensions
Checkpoint sorting and kernel interaction
Using more advanced foreign data wrappers
Introducing parallel queries
Adding snapshot too old
Summary
Chapter 2: Understanding Transactions and Locking
Working with PostgreSQL transactions
Handling errors inside a transaction
Making use of savepoints
Transactional DDLs
Understanding basic locking
Avoiding typical mistakes and explicit locking
Considering alternative solutions
Making use of FOR SHARE and FOR UPDATE
Understanding transaction isolation levels
Considering SSI transactions
Observing deadlocks and similar issues
Utilizing advisory locks
Optimizing storage and managing cleanup
Configuring VACUUM and autovacuum
Digging into transaction wraparound-related issues
A word on VACUUM FULL
Watching VACUUM at work
Making use of snapshot too old
Summary
Chapter 3: Making Use of Indexes
Understanding simple queries and the cost model
Making use of EXPLAIN
Digging into the PostgreSQL cost model
Deploying simple indexes
Making use of sorted output
Using more than one index at a time
Using bitmap scans effectively
Using indexes in an intelligent way
Improving speed using clustered tables
Clustering tables
Making use of index only scans
Understanding additional B-tree features
Combined indexes
Adding functional indexes
Reducing space consumption
Adding data while indexing
Introducing operator classes
Hacking up an operator class for a B-tree
Creating new operators
Creating operator classes
Testing custom operator classes
Understanding PostgreSQL index types
Hash indexes
GiST indexes
Understanding how GiST works
Extending GiST
GIN indexes
Extending GIN
SP-GiST indexes
BRIN indexes
Extending BRIN indexes
Adding additional indexes
Achieving better answers with fuzzy searching
Taking advantage of pg_trgm
Speed up LIKE queries
Handling regular expressions
Understanding full-text search - FTS
Comparing strings
Defining GIN indexes
Debugging your search
Gathering word statistics
Taking advantage of exclusion operators
Summary
Chapter 4: Handling Advanced SQL
Introducing grouping sets
Loading some sample data
Applying grouping sets
Investigating performance
Combining grouping sets with the FILTER clause
Making use of ordered sets
Understanding hypothetical aggregates
Utilizing windowing functions and analytics
Partitioning data
Ordering data inside a window
Using sliding windows
Abstracting window clauses
Making use of onboard windowing functions
rank and dense_rank functions
ntile() function
lead() and lag() functions
first_value(), nth_value(), and last_value() functions
row_number() function
Writing your own aggregates
Creating simple aggregates
Adding support for parallel queries
Improving efficiency
Writing hypothetical aggregates
Summary
Chapter 5: Log Files and System Statistics
Gathering runtime statistics
Working with PostgreSQL system views
Checking live traffic
Inspecting databases
Inspecting tables
Making sense of pg_stat_user_tables
Digging into indexes
Tracking the background worker
Tracking, archiving, and streaming
Checking SSL connections
Inspecting transactions in real time
Tracking vacuum progress
Using pg_stat_statements
Creating log files
Configuring postgresql.conf file
Defining log destination and rotation
Configuring syslog
Logging slow queries
Defining what and how to log
Summary
Chapter 6: Optimizing Queries for Good Performance
Learning what the optimizer does
Optimizations by example
Evaluating join options
Nested loops
Hash joins
Merge joins
Applying transformations
Inlining the view
Flattening subselects
Applying equality constraints
Exhaustive searching
Trying it all out
Making the process fail
Constant folding
Understanding function inlining
Join pruning
Speedup set operations
Understanding execution plans
Approaching plans systematically
Making EXPLAIN more verbose
Spotting problems
Spotting changes in runtime
Inspecting estimates
Inspecting buffer usage
Fixing high buffer usage
Understanding and fixing joins
Getting joins right
Processing outer joins
Understanding the join_collapse_limit variable
Enabling and disabling optimizer settings
Understanding genetic query optimization
Partitioning data
Creating partitions
Applying table constraints
Modifying inherited structures
Moving tables in and out of partitioned structures
Cleaning up data
Adjusting parameters for good query performance
Speeding up sorting
Speedup administrative tasks
Summary
Chapter 7: Writing Stored Procedures
Understanding stored procedure languages
The anatomy of a stored procedure
Introducing dollar quoting
Making use of anonymous code blocks
Using functions and transactions
Understanding various stored procedure languages
Introducing PL/pgSQL
Handling quoting
Managing scopes
Understanding advanced error handling
Making use of GET DIAGNOSTICS
Using cursors to fetch data in chunks
Utilizing composite types
Writing triggers in PL/pgSQL
Introducing PL/Perl
Using PL/Perl for datatype abstraction
Deciding between PL/Perl and PL/PerlU
Making use of the SPI interface
Using SPI for set returning functions
Escaping in PL/Perl and support functions
Sharing data across function calls
Writing triggers in Perl
Introducing PL/Python
Writing simple PL/Python code
Using the SPI interface
Handling errors
Improving stored procedure performance
Reducing the number of function calls
Using cached plans
Assigning costs to functions
Using stored procedures
Summary
Chapter 8: Managing PostgreSQL Security
Managing network security
Understanding bind addresses and connections
Inspecting connections and performance
Living in a world without TCP
Managing pg_hba.conf
Handling SSL
Handling instance-level security
Creating and modifying users
Defining database-level security
Adjusting schema-level permissions
Working with tables
Handling column-level security
Configuring default privileges
Digging into row-level security - RLS
Inspecting permissions
Reassigning objects and dropping users
Summary
Chapter 9: Handling Backup and Recovery
Performing simple dumps
Running pg_dump
Passing passwords and connection information
Using environment variables
Making use of .pgpass
Using service files
Extracting subsets of data
Handling various data formats
Replaying backups
Handling global data
Summary
Chapter 10: Making Sense of Backups and Replication
Understanding the transaction log
Looking at the transaction log
Understanding checkpoints
Optimizing the transaction log
Transaction log archiving and recovery
Configuring for archiving
Confguring the pg_hba.conf file
Creating base backups
Reducing the bandwidth of a backup
Mapping tablespaces
Using different formats
Testing transaction log archiving
Replaying the transaction log
Finding the right timestamp
Cleaning up the transaction log archive
Setting up asynchronous replication
Performing a basic setup
Improving security
Halting and resuming replication
Checking replication to ensure availability
Performing failovers and understanding timelines
Managing conflicts
Making replication more reliable
Upgrading to synchronous replication
Adjusting durability
Making use of replication slots
Handling physical replication slots
Handling logical replication slots
Use cases of logical slots
Summary
Chapter 11: Deciding on Useful Extensions
Understanding how extensions work
Checking for available extensions
Making use of contrib modules
Using the adminpack
Applying bloom filters
Deploying btree_gist and btree_gin
Dblink - consider phasing out
Fetching files with file_fdw
Inspecting storage using pageinspect
Investigating caching with pg_buffercache
Encrypting data with pgcrypto
Prewarming caches with pg_prewarm
Inspecting performance with pg_stat_statements
Inspecting storage with pgstattuple
Fuzzy searches with pg_trgm
Connecting to remote servers using postgres_fdw
Handling mistakes and typos
Other useful extensions
Summary
Chapter 12: Troubleshooting PostgreSQL
Approaching an unknown database
Inspecting pg_stat_activity
Querying pg_stat_activity
Treating Hibernate statements
Figuring out where queries come from
Checking for slow queries
Inspecting individual queries
Digging deeper with perf
Inspecting the log
Checking for missing indexes
Checking for memory and I/O
Understanding noteworthy error scenarios
Facing clog corruption
Understanding checkpoint messages
Managing corrupted data pages
Careless connection management
Fighting table bloat
Summary
Chapter 13: Migrating to PostgreSQL
Migrating SQL statements to PostgreSQL
Using lateral joins
Supporting lateral
Using grouping sets
Supporting grouping sets
Using WITH clause - common table expressions
Supporting WITH clause
Using WITH RECURSIVE clause
Supporting WITH RECURSIVE clause
Using FILTER clause
Supporting FILTER clause
Using windowing functions
Supporting windowing and analytics
Using ordered sets - WITHIN GROUP clause
Supporting WITHIN GROUP clause
Using TABLESAMPLE clause
Supporting TABLESAMPLE clause
Using limit/offset
Supporting FETCH FIRST clause
Using OFFSET
Supporting OFFSET clause
Using temporal tables
Supporting temporal tables
Matching patterns in time series
Moving from Oracle to PostgreSQL
Using the oracle_fdw extension to move data
Using ora2pg to migrate from Oracle
Common pitfalls
Moving from MySQL or MariaDB to PostgreSQL
Handling data in MySQL and MariaDB
Changing column definitions
Handling null values
Expecting problems
Migrating data and schema
Using pg_chameleon
Using foreign data wrappers
Summary
Index

Systemvoraussetzungen

Als PDF speichern Als Link merken