PostgreSQL for Data Architects

Jayadevan Maymala

• Publisher: Packt Publishing
• Year: 2015
• ISBN:
  9781783288601
• Topic: Computer Science
• Subtopic: Data Modelling & Design
• Index: Computer Science

PostgreSQL for Data Architects

---

Table of Contents

PostgreSQL for Data Architects

Credits

About the Author

About the Reviewers

www.PacktPub.com

Support files, eBooks, discount offers, and more

Why subscribe?

Free access for Packt account holders

Preface

What this book covers

What you need for this book

Who this book is for

Conventions

Reader feedback

Customer support

Downloading the example code

Errata

Piracy

Questions

1. Installing PostgreSQL

Installation options

Downloading and extracting the source

Inspecting the contents

Dependencies to compile the source

Configuring and creating the makefile

Building and creating the executables

Installing and moving the files to where they belong

Inspecting the changes

Initializing a cluster

A quick walk through the directories

Processes created

Important files created

Working with extensions

Summary

2. Server Architecture

Starting with the daemon process

Understanding the shared buffer

Inspecting the buffer cache

Checkpoint

WAL and the WAL writer process

Recovery

Incremental backup and point-in-time recovery

Replication

The background writer

The autovacuum launcher process

The logging process

The stats collector process

The WAL sender and WAL receiver

Sorting in memory with work_mem

Maintenance with maintenance_work_mem

Understanding effective_cache_size

Summary

3. PostgreSQL – Object Hierarchy and Roles

The PostgreSQL cluster

Understanding tablespaces

Managing temporary objects with temporary tablespaces

Views

Databases, schemas, and search_path

Schemas – use cases

Roles and privileges

Summary

4. Working with Transactions

Understanding transactions

ACID properties of transactions

A for atomicity

C for consistency

I for isolation

Read uncommitted

Read committed

Repeatable reads

Serializable

Read committed

Repeatable read

Serializable level

D for durability

PostgreSQL and MVCC

Summary

5. Data Modeling with SQL Power Architect

Tools for databases and their uses

Database design tools

SQL Power Architect – downloading and installing

Creating tables

Generating SQL

Reverse engineering and making changes

Exporting the data model

Profiling

Summary

6. Client Tools

GUI tools and command-line tools

pgAdmin – downloading and installation

Adding a server

The pgAdmin main window

The Query tool

psql – working from the command line

psql – connection options

The power of \d

More meta-commands

Setting up the environment

History of commands

Summary

7. SQL Tuning

Understanding basic facts about databases

Fact 1 – databases are more frequently read from than written to

Fact 2 – data is always read in blocks or pages, not as individual records or columns

Approaches to reducing the number of blocks read/written

Query execution components

Planner

Access methods

Join strategies

Finding the execution plan

Optimization guidelines and catches

Indexing foreign keys

Using SELECT *

Using ORDER BY

Using DISTINCT

Using UNION ALL instead of UNION

Using functions in the FILTER clause

Reducing the number of SQL statements

Reducing function executions

Not using indexes

Partial indexes

Optimizing functions

Summary

8. Server Tuning

Server-wide memory settings

shared_buffers

effective_cache_size

Managing writes, connections, and maintenance

Seek/scan cost and statistics parameters

CPU costs

Materialized views

Partitioned tables

Summary

9. Tools to Move Data in and out of PostgreSQL

Setting up the production database – considerations

COPY

Fast loading with pg_bulkload

pg_dump

Filtering options

pg_dumpall

pg_restore

Summary

10. Scaling, Replication, and Backup and Recovery

Scalability

Vertical scaling

Horizontal scaling

Master-slave(s) with read/write separation

Streaming replication

Configuring primary

Configuring secondary

Making the standby in synch with primary

Connection pooling, load balancing, and failover with pgpool-II

Configuring pgpool-II

Test read/write separation

Test failover

Sharding

Multi-master full replication

Point-in-time recovery

Summary

11. PostgreSQL – Troubleshooting

Connection issues

Authentication and permission issues

Parameter changes not effective

Query not responding

Summary

12. PostgreSQL – Extras

Interesting data types

RANGE

Using network address types

hstore for key-value pairs

json/jsonb

XML

Inserting and verifying XML data

Generating XML files for table definitions and data

Geometry and geography

Foreign Data Wrappers

FDW for files

PostgreSQL FDW

Data wrappers – other aspects

pgbadger

Features over time

Interesting features in 9.4

Keeping the buffer ready

Better recoverability

Easy-to-change parameters

Logical decoding and consumption of changes

Summary

Index

PostgreSQL for Data Architects

Copyright © 2015 Packt Publishing

All rights reserved. No part of this book may be reproduced, stored in a retrieval system, or transmitted in any form or by any means, without the prior written permission of the publisher, except in the case of brief quotations embedded in critical articles or reviews.

Every effort has been made in the preparation of this book to ensure the accuracy of the information presented. However, the information contained in this book is sold without warranty, either express or implied. Neither the author, nor Packt Publishing, and its dealers and distributors will be held liable for any damages caused or alleged to be caused directly or indirectly by this book.

Packt Publishing has endeavored to provide trademark information about all of the companies and products mentioned in this book by the appropriate use of capitals. However, Packt Publishing cannot guarantee the accuracy of this information.

First published: March 2015

Production reference: 1240315

Published by Packt Publishing Ltd.

Livery Place

35 Livery Street

Birmingham B3 2PB, UK.

ISBN 978-1-78328-860-1

www.packtpub.com

Cover image by Kai Stachowiak (<[email protected]>)

Credits

Author

Jayadevan Maymala

Reviewers

Pascal Charest

Bahman Movaqar

Ângelo Marcos Rigo

Hans-Jürgen Schönig

Stéphane Wirtel

Commissioning Edi...