Search icon CANCEL
Subscription
0
Cart icon
Your Cart (0 item)
Close icon
You have no products in your basket yet
Save more on your purchases! discount-offer-chevron-icon
Savings automatically calculated. No voucher code required.
Arrow left icon
Explore Products
Best Sellers
New Releases
Books
Videos
Audiobooks
Learning Hub
Newsletter Hub
Free Learning
Arrow right icon
timer SALE ENDS IN
0 Days
:
00 Hours
:
00 Minutes
:
00 Seconds
PostgreSQL 10 High Performance
PostgreSQL 10 High Performance

PostgreSQL 10 High Performance: Expert techniques for query optimization, high availability, and efficient database maintenance , Third Edition

eBook
$9.99 $39.99
Paperback
$48.99
Subscription
Free Trial
Renews at $19.99p/m

What do you get with eBook?

Product feature icon Instant access to your Digital eBook purchase
Product feature icon Download this book in EPUB and PDF formats
Product feature icon Access this title in our online reader with advanced features
Product feature icon DRM FREE - Read whenever, wherever and however you want
OR
Modal Close icon
Payment Processing...
tick Completed

Billing Address

Table of content icon View table of contents Preview book icon Preview Book

PostgreSQL 10 High Performance

Database Hardware

This chapter aims to help prioritize spending when planning out the purchase of a new server intended to run PostgreSQL. If you already have a running database server, then following the instructions in, Chapter 3, Database Hardware Benchmarking, and Chapter 8, Database Benchmarking, might be a more appropriate place to start. Ultimately, you may end up taking a few round trips alternating through that material and what's covered here. For example, if you benchmark your server and the disks seem slow, the background here might give you an idea of what hardware change you could make to improve that. Once that's done, it's back to benchmarking again; repeat until performance matches your expectations.

In this chapter, we will cover the following topics:

  • Balancing hardware spending
  • Reliable controller and disk setup
...

Balancing hardware spending

One of the reasons working with open source databases such as PostgreSQL can be so effective is that every dollar you save on software licensing can be put toward better hardware instead. The three main components you'll need to balance in your budget are CPUs, memory, and disks, with the disk controller as a related and critical part too.

CPUs

Currently, available processors are bundling at least 2, and possibly as many as 22, cores into each CPU, making the core count the figure of merit for most database applications and, with PostgreSQL 10.x, we can use multiple cores for every single query with the mechanism called parallel query.

There are two basic decisions you need to make while deciding...

Reliable controller and disk setup

PostgreSQL uses a write-ahead log (WAL) to write data in a way that survives a database or hardware crash. This is similar to the log buffer or redo log found in other databases. The database documentation covers the motivation and implementation of the WAL at https://www.postgresql.org/docs/current/static/wal-intro.html.

To quote from that introduction:

"WAL's central concept is that changes to data files (where tables and indexes reside) must be written only after those changes have been logged, that is, after log records describing the changes have been flushed to permanent storage."

This procedure ensures that if your application has received commit for a transaction that transaction is on permanent storage, and will not be lost even if there is a crash. This satisfies the durability portion of the atomicity, consistency, isolation...

Summary

Building a database server with good performance is hard work. There are many individual components that are available in multiple quality levels and corresponding costs. Also, there are plenty of small details you must get right or you'll risk data corruption. Luckily, you don't have to start from scratch. Stick to common, well understood components with known good performance, while keeping an eye on reliability too, and you can build a well-balanced database server for a reasonable budget. Always make sure to run your own benchmarks on the result though. It's very easy to sabotage even good equipment with the wrong configuration when running a database.

Allocating your hardware budget between CPUs, memory, and disks is very application-dependent.

Carefully selecting and configuring your controller and disk caches is critical for reliable database operation...

Left arrow icon Right arrow icon
Download code icon Download Code

Key benefits

  • Obtain optimal PostgreSQL 10 database performance, ranging from initial design to routine maintenance
  • Fine tune the performance of your queries and avoid the common pitfalls that can slow your system down
  • Contains tips and tricks on scaling successful database installations, and ensuring a highly available PostgreSQL solution

Description

PostgreSQL database servers have a common set of problems that they encounter as their usage gets heavier and requirements get more demanding. Peek into the future of your PostgreSQL 10 database's problems today. Know the warning signs to look for and how to avoid the most common issues before they even happen. Surprisingly, most PostgreSQL database applications evolve in the same way—choose the right hardware, tune the operating system and server memory use, optimize queries against the database and CPUs with the right indexes, and monitor every layer, from hardware to queries, using tools from inside and outside PostgreSQL. Also, using monitoring insight, PostgreSQL database applications continuously rework the design and configuration. On reaching the limits of a single server, they break things up; connection pooling, caching, partitioning, replication, and parallel queries can all help handle increasing database workloads. By the end of this book, you will have all the knowledge you need to design, run, and manage your PostgreSQL solution while ensuring high performance and high availability

Who is this book for?

This book is designed for database administrators and PostgreSQL architects who already use or plan to exploit the features of PostgreSQL 10 to design and maintain a high-performance PostgreSQL database. A working knowledge of SQL, and some experience with PostgreSQL will be helpful in getting the most out of this book.

What you will learn

  • Learn best practices for scaling PostgreSQL 10 installations
  • Discover the best hardware for developing high-performance PostgreSQL applications
  • Benchmark your whole system – from hardware to application
  • Learn by real examples how server parameters impact performance
  • Discover PostgreSQL 10 features for partitioning and parallel query
  • Monitor your server, both inside and outside the database
  • Design and implement a good replication system on PostgreSQL 10

Product Details

Country selected
Publication date, Length, Edition, Language, ISBN-13
Publication date : Apr 30, 2018
Length: 508 pages
Edition : 3rd
Language : English
ISBN-13 : 9781788472456
Vendor :
PostgreSQL Global Development Group
Category :
Languages :
Tools :

What do you get with eBook?

Product feature icon Instant access to your Digital eBook purchase
Product feature icon Download this book in EPUB and PDF formats
Product feature icon Access this title in our online reader with advanced features
Product feature icon DRM FREE - Read whenever, wherever and however you want
OR
Modal Close icon
Payment Processing...
tick Completed

Billing Address

Product Details

Publication date : Apr 30, 2018
Length: 508 pages
Edition : 3rd
Language : English
ISBN-13 : 9781788472456
Vendor :
PostgreSQL Global Development Group
Category :
Languages :
Tools :

Packt Subscriptions

See our plans and pricing
Modal Close icon
$19.99 billed monthly
Feature tick icon Unlimited access to Packt's library of 7,000+ practical books and videos
Feature tick icon Constantly refreshed with 50+ new titles a month
Feature tick icon Exclusive Early access to books as they're written
Feature tick icon Solve problems while you work with advanced search and reference features
Feature tick icon Offline reading on the mobile app
Feature tick icon Simple pricing, no contract
$199.99 billed annually
Feature tick icon Unlimited access to Packt's library of 7,000+ practical books and videos
Feature tick icon Constantly refreshed with 50+ new titles a month
Feature tick icon Exclusive Early access to books as they're written
Feature tick icon Solve problems while you work with advanced search and reference features
Feature tick icon Offline reading on the mobile app
Feature tick icon Choose a DRM-free eBook or Video every month to keep
Feature tick icon PLUS own as many other DRM-free eBooks or Videos as you like for just $5 each
Feature tick icon Exclusive print discounts
$279.99 billed in 18 months
Feature tick icon Unlimited access to Packt's library of 7,000+ practical books and videos
Feature tick icon Constantly refreshed with 50+ new titles a month
Feature tick icon Exclusive Early access to books as they're written
Feature tick icon Solve problems while you work with advanced search and reference features
Feature tick icon Offline reading on the mobile app
Feature tick icon Choose a DRM-free eBook or Video every month to keep
Feature tick icon PLUS own as many other DRM-free eBooks or Videos as you like for just $5 each
Feature tick icon Exclusive print discounts

Frequently bought together


Stars icon
Total $ 147.97
PostgreSQL 10 High Performance
$48.99
Mastering PostgreSQL 10
$43.99
PostgreSQL 10 Administration Cookbook
$54.99
Total $ 147.97 Stars icon
Banner background image

Table of Contents

17 Chapters
PostgreSQL Versions Chevron down icon Chevron up icon
Database Hardware Chevron down icon Chevron up icon
Database Hardware Benchmarking Chevron down icon Chevron up icon
Disk Setup Chevron down icon Chevron up icon
Memory for Database Caching Chevron down icon Chevron up icon
Server Configuration Tuning Chevron down icon Chevron up icon
Routine Maintenance Chevron down icon Chevron up icon
Database Benchmarking Chevron down icon Chevron up icon
Database Indexing Chevron down icon Chevron up icon
Query Optimization Chevron down icon Chevron up icon
Database Activity and Statistics Chevron down icon Chevron up icon
Monitoring and Trending Chevron down icon Chevron up icon
Pooling and Caching Chevron down icon Chevron up icon
Scaling with Replication Chevron down icon Chevron up icon
Partitioning Data Chevron down icon Chevron up icon
Avoiding Common Problems Chevron down icon Chevron up icon
Other Books You May Enjoy Chevron down icon Chevron up icon

Customer reviews

Rating distribution
Full star icon Full star icon Half star icon Empty star icon Empty star icon 2.5
(2 Ratings)
5 star 0%
4 star 0%
3 star 50%
2 star 50%
1 star 0%
Justin Pryzby Sep 26, 2018
Full star icon Full star icon Full star icon Empty star icon Empty star icon 3
The book is okay, and has a bunch of good information consolidated in one place.But it's also full of the same typos as the original book for 9.0, having apparently not been passed through basic nor technical editor, proofreader, nor review.And still has a bunch of outdated information from the postgres8.3 and 9.0 days when the book was first authored. For example, in the span of 5 paragraphs, we read, first: "...we can use multiple cores for every single query...", then, "...PG does not allow splitting a single query across more than one core...".What's the point of an update for v9.6 and then v10 if it's not done well and to completion ?
Amazon Verified review Amazon
Dustin J. Mitchell Jan 20, 2020
Full star icon Full star icon Empty star icon Empty star icon Empty star icon 2
First, despite the "10" in the title and the 2018 publication date, most of this book is based on Postgres 8.3 (2008) with mentions of 9.4 (2014) and occasional reference to 10 (2017) in a future-y wild-west kind of way. It also spends a good bit of time talking about hardware selection, including details of some now-rather-outdated processor architectures (Nehalem, 2008), good old 7200RPM spinning-rusty disks, and fancy "new" (2007) DDR3 RAM -- yet if you're deploying a DB today, it's probably hosted in a cloud, making all of those considerations are moot. So if you're looking to catch up on the latest developments in Postgres, keep looking.Second, this book is un-edited. Not poorly edited, no: from the authors keyboards to your eyes, with all of the omitted words, stray newlines, weird formatting, and nonsenical statements that an author trying to make a deadline will write. Sadly, I think this is the state of this genre of books these days: editors are expensive! So if you're planning to study the content carefully in hopes of understanding the nuances of MVCC or VACUUM FULL, you'll be scratching your head over nothing. I suggest you skip the book and go straight to the web.Having said all that, if you'd like an overview of the things that experienced Postgres DBAs think about, along with some high-level advice ("tweak this setting when..", "this sounds great but has the following downsides..") then this might be worth picking up.
Amazon Verified review Amazon
Get free access to Packt library with over 7500+ books and video courses for 7 days!
Start Free Trial

FAQs

How do I buy and download an eBook? Chevron down icon Chevron up icon

Where there is an eBook version of a title available, you can buy it from the book details for that title. Add either the standalone eBook or the eBook and print book bundle to your shopping cart. Your eBook will show in your cart as a product on its own. After completing checkout and payment in the normal way, you will receive your receipt on the screen containing a link to a personalised PDF download file. This link will remain active for 30 days. You can download backup copies of the file by logging in to your account at any time.

If you already have Adobe reader installed, then clicking on the link will download and open the PDF file directly. If you don't, then save the PDF file on your machine and download the Reader to view it.

Please Note: Packt eBooks are non-returnable and non-refundable.

Packt eBook and Licensing When you buy an eBook from Packt Publishing, completing your purchase means you accept the terms of our licence agreement. Please read the full text of the agreement. In it we have tried to balance the need for the ebook to be usable for you the reader with our needs to protect the rights of us as Publishers and of our authors. In summary, the agreement says:

  • You may make copies of your eBook for your own use onto any machine
  • You may not pass copies of the eBook on to anyone else
How can I make a purchase on your website? Chevron down icon Chevron up icon

If you want to purchase a video course, eBook or Bundle (Print+eBook) please follow below steps:

  1. Register on our website using your email address and the password.
  2. Search for the title by name or ISBN using the search option.
  3. Select the title you want to purchase.
  4. Choose the format you wish to purchase the title in; if you order the Print Book, you get a free eBook copy of the same title. 
  5. Proceed with the checkout process (payment to be made using Credit Card, Debit Cart, or PayPal)
Where can I access support around an eBook? Chevron down icon Chevron up icon
  • If you experience a problem with using or installing Adobe Reader, the contact Adobe directly.
  • To view the errata for the book, see www.packtpub.com/support and view the pages for the title you have.
  • To view your account details or to download a new copy of the book go to www.packtpub.com/account
  • To contact us directly if a problem is not resolved, use www.packtpub.com/contact-us
What eBook formats do Packt support? Chevron down icon Chevron up icon

Our eBooks are currently available in a variety of formats such as PDF and ePubs. In the future, this may well change with trends and development in technology, but please note that our PDFs are not Adobe eBook Reader format, which has greater restrictions on security.

You will need to use Adobe Reader v9 or later in order to read Packt's PDF eBooks.

What are the benefits of eBooks? Chevron down icon Chevron up icon
  • You can get the information you need immediately
  • You can easily take them with you on a laptop
  • You can download them an unlimited number of times
  • You can print them out
  • They are copy-paste enabled
  • They are searchable
  • There is no password protection
  • They are lower price than print
  • They save resources and space
What is an eBook? Chevron down icon Chevron up icon

Packt eBooks are a complete electronic version of the print edition, available in PDF and ePub formats. Every piece of content down to the page numbering is the same. Because we save the costs of printing and shipping the book to you, we are able to offer eBooks at a lower cost than print editions.

When you have purchased an eBook, simply login to your account and click on the link in Your Download Area. We recommend you saving the file to your hard drive before opening it.

For optimal viewing of our eBooks, we recommend you download and install the free Adobe Reader version 9.