PostgreSQL for Data Architects by Maymala, Jayadevan -- Read -- Imperial Library of Trantor

Index

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

← Prev
Back
Next →

← Prev
Back
Next →