| Description |
1 online resource (1 volume) : illustrations |
| Contents |
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 featuresImproving 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 transactionsObserving 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 wayImproving 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 indexesAdding 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. |
| Summary |
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. E... |
| Subject |
PostgreSQL.
|
|
PostgreSQL |
|
SQL (Computer program language)
|
|
Database management.
|
|
Databases -- Design.
|
|
SQL (Langage de programmation) |
|
Bases de données -- Gestion. |
|
Database management |
|
Databases -- Design |
|
SQL (Computer program language) |
| Added Title |
Mastering Postgre Structure Query Language 9.6 |
| ISBN |
178355536X |
|
9781783555369 (electronic bk.) |
|
More Resources