book

PostgreSQL High Performance Cookbook

by Dinesh Kumar, Chitij Chauhan

March 2017

Intermediate to advanced

360 pages

7h 29m

English

Packt Publishing

Read now

Unlock full access

Why subscribe?
What this book covers

Getting readyHow to do it...How it works...There's more...See also
Downloading the example codeErrataPiracyQuestions
Introduction
Getting readyHow to do it...PhoronixsysbenchHow it works...Phoronixsysbench
Getting readyHow to do it...PhoronixtmpfsWrite testRead testHow it works...
Getting readyHow to do it...Phoronixbonnie++How it works...bonnie++
Getting readyHow to do it...How it works...
Getting readyHow to do it...How it works...
Getting readyHow to do it...Sequential mixed read and writeRandom mixed read and writeHow it works...
Getting readyHow to do it...How it works...
Getting readyHow to do it...How it works...RAID 0RAID 1RAID 5RAID 6RAID 10
Getting readyHow to do it...How it works...
Getting readyHow to do it...Read-onlyWrite-onlyHow it works...
Introduction
Getting readyHow to do it...How it works...
Getting readyHow to do it...How it works...
How to do it...How it works...
Getting readyHow to do it...How it works...
How to do it...How it works...
How to do it...How it works...
How to do it...How it works...
How to do it...How it works...
Introduction
Getting readyHow to do it...shared_bufferstemp_bufferswork_memmaintenance_work_memwal_buffersmax_stack_deptheffective_cache_sizeHow it works...
Getting readyHow to do it...kernel.shmmaxkernel.shmallkernel.shmmnivm.swappinessvm.overcommit_memoryvm.overcommit_ratiovm.dirty_background_ratiovm.dirty_ratioHow it works...
Getting readyHow to do it...kernel.sched_autogroup_enabledkernel.sched_min_granularity_nskernel.sched_latency_nskernel.sched_wakeup_granularity_nskernel.sched_migration_cost_nsHow it works...
Getting readyHow to do it...CFQnoopDeadlineHow it works...
Getting readyHow to do it...How it works...
Getting readyHow to do it...How it works...
Introduction
Getting readyHow to do it...How it works...
Getting readyHow to do it...How it works...
Getting readyHow to do it...How it works...
How to do it...How it works...
How to do it...How it works...
How to do it...How it works...
How to do it...How it works...
Getting readyHow to do it...How it works...There's more...
Getting readyHow to do it...How it works...
How to do it...How it works...
Getting readyHow to do it...How it works...
Introduction
Getting ReadyHow to do it...How it works...
Getting readyHow to do it...How it works...There's more...
Getting readyHow to do it...How it works...
Getting readyHow to do it...How it works...There's more...
Getting readyHow to do it...How it works...
Getting readyHow to do it...How it works...
Getting readyHow to do it...How it works...
How to do it...How it works...
Getting readyHow to do it...How it works...
Introduction
Getting readyHow to do it...How it works...
Getting readyHow to do it...How it works...
Getting readyHow to do it...How it works...
Getting readyHow to do it...How it works...
Getting readyHow to do it...How it works...
Getting readyHow to do it...
Introduction
Getting readyHow to do it...How it works...
Getting readyHow to do it...How it works...
Getting readyHow to do it...How it works...
Getting readyHow to do it...How it works...
How to do it...How it works...
How to do it...How it works...
How to do it...How it works...
How to do it...How it works...
Introduction
Getting readyHow to do it...How it works...There's more...
Getting readyHow to do it...How it works...
Getting readyHow to do it...How it works...There's more...Transactional locksTable level locksPrepared transaction locksUsage of SKIP LOCKED
Getting readyHow to do it...Using FOR UPDATEAdvisory locks
Getting readyHow to do it...pg_stat_user_tablespg_statio_user_tablesHow it works...
Getting readyHow to do it...How it works...
Getting readyHow to do it...Column sizeRelation sizeDatabase sizeTablespace sizeHow it works...
Getting readyHow to do it...How it works...There's more...
Getting readyHow to do it...How it works...There's more...
Getting readyHow to do it...How it works...null_fracavg_widthn_distinctmost_common_valsmost_common_freqshistogram_boundscorrelationmost_common_elemsmost_common_elem_freqs
Getting readyHow to do it...How it works...checkpoints_timedcheckpoints_reqcheckpoint_write_timecheckpoint_sync_timebuffers_checkpointbuffers_cleanmax_written_cleanbuffers_backendbuffers_backend_fsyncbuffers_allocstats_resetThere's more...
Introduction
Getting readyHow to do it...How it works...
Getting readyHow to do it...How it works...xminxmaxAutovacuum
Getting readyHow to do it...How it works...Fixing transaction ID wraparoundPreventing transaction ID wraparound
Getting readyHow to do it...How it works...
Getting readyHow to do it...How it works...
Introduction
Getting readyHow to do it...How it works...
Getting readyHow to do it...How it works...
Getting readyHow to do it...How it works...There's more...
Introduction
Getting readyHow to do it…How it works…
Getting readyHow to do it…How it works…
Getting readyHow to do it…Cold cacheHot cacheHow it works…
Getting readyHow to do it…How it works…There's more…
Getting readyHow to do it…How it works…
Getting readyHow to do it…How it works…There's more…
Getting readyHow to do it…How it works…Sequential scanIndex scanPhase IPhase II
Getting readyHow to do it…How it works…
Getting readyHow to do it…How it works…
Getting readyHow to do it…AggregateHash aggregateHow it works…AggregateState transition functionsHash aggregates
Getting readyHow to do it…How it works…CTE scanRecursive union
Getting readyHow to do it…How it works…Phase IPhase II
Getting readyHow to do it…Hash joinMerge joinHow it works…Hash joinPhase IPhase IIMerge joinPhase IPhase II
Getting readyHow to do it…How it works…
Getting readyHow to do it…How it works…INTERSECTEXCEPTUNION
Getting readyHow to do it…How it works…
Introduction
Getting readyHow to do it...How it works...idx_scanidx_tup_readidx_tup_fetchidx_blks_readidx_blks_hit
Getting readyHow to do it...How it works...Index scanBitmap heap scanIndex only scan
Getting readyHow to do it...How it works...Sequential scanIndex scanIndex only scanBitmap heap scanThere's more...
Getting readyHow to do it...How it works...There's more...
Getting readyHow to do it...How it works...
Getting readyHow to do it...How it works...
Getting readyHow to do it...How it works...
Getting readyHow to do it...How it works...
Getting readyHow to do it...How it works...
Getting readyHow to do it...How it works...

Overview

This book is your definitive guide to understanding and improving PostgreSQL database performance. You'll learn about query optimization, database monitoring, and advanced memory and configuration techniques. With examples and clear explanations, you'll gain the skills to identify performance bottlenecks and make your database system highly efficient.

What this Book will help me do

Effectively optimize PostgreSQL queries to enhance response times.
Utilize robust server monitoring techniques to identify and address inefficiencies.
Implement memory optimization strategies to maximize server performance.
Master replication and failover methods for high availability.
Build strategies for secure and efficient database migrations.

Author(s)

Dinesh Kumar and None Chauhan are experienced database professionals with years of expertise in working with PostgreSQL. They have been involved in database design, optimization, and innovations in open-source database technologies. Their teaching approach is clear and actionable, making the topics accessible to both beginners and seasoned professionals.

Who is it for?

This book is ideally suited for developers and database administrators with a basic understanding of PostgreSQL. If you're seeking practical guidance to enhance your PostgreSQL performance tuning and maintenance skills, this book is designed for you. It covers concepts for professionals looking to advance their database expertise. Beginners in database management who are motivated to learn advanced techniques will also find it approachable.