最新章節

• Leave a review - let other readers know what you think
• Other Books You May Enjoy
• Summary
• Using FDWs
• Using pg_chameleon
• Migrating data and schema

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

• coverpage
• Title Page
• Packt Upsell
• Why subscribe?
• PacktPub.com
• Contributors
• About the author
• About the reviewer
• Packt is searching for authors like you
• Preface
• Who this book is for
• What this book covers
• To get the most out of this book
• Download the color images
• Conventions used
• Get in touch
• Reviews
• PostgreSQL Overview
• What is new in PostgreSQL 10.0?
• Understanding new database administration functions
• Using additional information in pg_stat_activity
• Introducing SCRAM-SHA-256
• Improving support for replication
• Understanding logical replication
• Introducing quorum COMMIT
• Partitioning data
• Making use of CREATE STATISTICS
• Improving parallelism
• Introducing ICU encodings
• Summary
• Understanding Transactions and Locking
• Working with PostgreSQL transactions
• Handling errors inside a transaction
• Making use of SAVEPOINT
• 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
• 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
• Speeding 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
• 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
• The rank and dense_rank functions
• The ntile() function
• The lead() and lag() functions
• The first_value() nth_value() and last_value() functions
• The row_number() function
• Writing your own aggregates
• Creating simple aggregates
• Adding support for parallel queries
• Improving efficiency
• Writing hypothetical aggregates
• Summary
• 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 the postgresql.conf file
• Defining log destination and rotation
• Configuring syslog
• Logging slow queries
• Defining what and how to log
• Summary
• 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
• Understanding PostgreSQL 10.0 partitioning
• Adjusting parameters for good query performance
• Speeding up sorting
• Speeding up administrative tasks
• Summary
• 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
• 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
• 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 formats
• Replaying backups
• Handling global data
• Summary
• 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
• Configuring 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
• Making use of CREATE PUBLICATION and CREATE SUBSCRIPTION
• Summary
• 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 - considering 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
• 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
• Migrating to PostgreSQL
• Migrating SQL statements to PostgreSQL
• Using lateral joins
• Supporting lateral
• Using grouping sets
• Supporting grouping sets
• Using the WITH clause – common table expressions
• Supporting the WITH clause
• Using the WITH RECURSIVE clause
• Supporting the WITH RECURSIVE clause
• Using the FILTER clause
• Supporting the FILTER clause
• Using windowing functions
• Supporting windowing and analytics
• Using ordered sets – WITHIN GROUP clause
• Supporting the WITHIN GROUP clause
• Using the TABLESAMPLE clause
• Supporting TABLESAMPLE clause
• Using limit/offset
• Supporting the FETCH FIRST clause
• Using OFFSET
• Supporting the 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 FDWs
• Summary
• Other Books You May Enjoy
• Leave a review - let other readers know what you think 更新時間：2021-06-30 19:04:45