Book description
Master the capabilities of PostgreSQL 9.6 to efficiently manage and maintain your database
About This Book
Your one-stop guide to mastering the advanced concepts in PostgreSQL with ease
Master query optimization, replication, and high availability with PostgreSQL
Extend the functionalities of PostgreSQL to suit your organizational needs with minimum effort
Who This Book Is For
If you are a PostgreSQL data architect or an administrator who wants to understand how to implement advanced functionalities and master complex administrative tasks with PostgreSQL, then this book is perfect for you. Prior experience of administrating a PostgreSQL database and a working knowledge of SQL is required to make the best use of this book.
What You Will Learn
Get to grips with the advanced features of PostgreSQL 9.6 and handle advanced SQL
Make use of the indexing features in PostgreSQL and fine-tune the performance of your queries
Work with the stored procedures and manage backup and recovery
Master the replication and failover techniques
Troubleshoot your PostgreSQL instance for solutions to the common and not-so-common problems
Learn how to migrate your database from MySQL and Oracle to PostgreSQL without any hassle
In Detail
PostgreSQL is an open source database used for handling large datasets (Big Data) and as a JSON document database. It also has applications in the software and web domains. This book will enable you to build better PostgreSQL applications and administer databases more efficiently.
We begin by explaining the advanced database design concepts in PostgreSQL 9.6, along with indexing and query optimization. You will also see how to work with event triggers and perform concurrent transactions and table partitioning, along with exploring SQL and server tuning. We will walk you through implementing advanced administrative tasks such as server maintenance and monitoring, replication, recovery and high availability, and much more. You will understand the common and not-so-common troubleshooting problems and how you can overcome them.
By the end of this book, you will have an expert-level command of the advanced database functionalities and will be able to implement advanced administrative tasks with PostgreSQL.
Style and Approach
This book is a comprehensive guide covering all the concepts you need to master PostgreSQL. Packed with hands-on examples, tips and tricks, even the most advanced concepts are explained in a very easy-to-follow manner. Every chapter in the book does not only focus on how each task is performed, but also why.
Table of contents
- Preface
- PostgreSQL Overview
- Understanding Transactions and Locking
- Making Use of Indexes
- Handling Advanced SQL
- Log Files and System Statistics
-
Optimizing Queries for Good Performance
- Learning what the optimizer does
- Understanding execution plans
- Understanding and fixing joins
- Enabling and disabling optimizer settings
- Partitioning data
- Adjusting parameters for good query performance
- Summary
- Writing Stored Procedures
-
Managing PostgreSQL Security
- Managing network security
- Digging into row-level security - RLS
- Inspecting permissions
- Reassigning objects and dropping users
- Summary
- Handling Backup and Recovery
- Making Sense of Backups and Replication
-
Deciding on Useful Extensions
- Understanding how extensions work
-
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
- Other useful extensions
- Summary
- Troubleshooting PostgreSQL
-
Migrating to PostgreSQL
-
Migrating SQL statements to PostgreSQL
- Using lateral joins
- Using grouping sets
- Using WITH clause - common table expressions
- Using WITH RECURSIVE clause
- Using FILTER clause
- Using windowing functions
- Using ordered sets - WITHIN GROUP clause
- Using TABLESAMPLE clause
- Using limit/offset
- Using OFFSET
- Using temporal tables
- Matching patterns in time series
- Moving from Oracle to PostgreSQL
- Moving from MySQL or MariaDB to PostgreSQL
- Summary
-
Migrating SQL statements to PostgreSQL
Product information
- Title: Mastering PostgreSQL 9.6
- Author(s):
- Release date: May 2017
- Publisher(s): Packt Publishing
- ISBN: 9781783555352
You might also like
book
Oracle SOA Suite 11g Administrator's Handbook
This book will quickly become your constant companion in achieving the reliability and security you want …
book
PostgreSQL 9.0 High Performance
If you’re an intermediate to advanced database administrator, this book is the shortcut to optimizing and …
book
Pro T-SQL 2012 Programmer's Guide, Third Edition
Pro T-SQL 2012 Programmer's Guide is every developer's key to making full use of SQL Server …
video
SSCP Video Course Domain 6 - Networking and Telecom
Networking and Telecom Shon Harris The fast, powerful way to prepare for your SSCP exam! Get …