PostgreSQL for Data Architects

Discover how to design, develop, and maintain your database application effectively with PostgreSQL

PostgreSQL for Data Architects

Progressing
Jayadevan Maymala

4 customer reviews
Discover how to design, develop, and maintain your database application effectively with PostgreSQL
$39.99
$49.99
RRP $39.99
RRP $49.99
eBook
Print + eBook

Get unlimited access to this and 3,500 other courses today!

With unlimited access to a constantly growing library of over 3,500 courses, a subscription to Mapt gives you everything you need to get that next promotion or to land that dream job. Cancel anytime.

+ Collection
Free Sample

Book Details

ISBN 139781783288601
Paperback272 pages

Book Description

PostgreSQL is an incredibly flexible and dependable open source relational database. Harnessing its power will make your applications more reliable and extensible without increasing costs. Using PostgreSQL's advanced features will save you work and increase performance, once you've discovered how to set it up.

PostgreSQL for Data Architects will teach you everything you need to learn in order to get a scalable and optimized PostgreSQL server up and running.

The book starts with basic concepts like installing PostgreSQL from source and covers theoretical aspects such as concurrency and transaction management. After this, you'll learn how to set up replication, use load balancing to scale horizontally, and troubleshoot errors.

Finally, you will get acquainted with useful tools available in the PostgreSQL ecosystem used for analyzing PostgreSQL logs, setting up load balancing, and recovery.

Table of Contents

Chapter 1: Installing PostgreSQL
Installation options
Dependencies to compile the source
Configuring and creating the makefile
Building and creating the executables
Installing and moving the files to where they belong
Initializing a cluster
Working with extensions
Summary
Chapter 2: Server Architecture
Starting with the daemon process
Understanding the shared buffer
Checkpoint
WAL and the WAL writer process
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
Summary
Chapter 3: PostgreSQL – Object Hierarchy and Roles
The PostgreSQL cluster
Understanding tablespaces
Databases, schemas, and search_path
Roles and privileges
Summary
Chapter 4: Working with Transactions
Understanding transactions
PostgreSQL and MVCC
Summary
Chapter 5: Data Modeling with SQL Power Architect
Tools for databases and their uses
Database design tools
Generating SQL
Summary
Chapter 6: Client Tools
GUI tools and command-line tools
pgAdmin – downloading and installation
psql – working from the command line
Summary
Chapter 7: SQL Tuning
Understanding basic facts about databases
Query execution components
Finding the execution plan
Optimization guidelines and catches
Summary
Chapter 8: Server Tuning
Server-wide memory settings
Managing writes, connections, and maintenance
Seek/scan cost and statistics parameters
Materialized views
Partitioned tables
Summary
Chapter 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
Summary
Chapter 10: Scaling, Replication, and Backup and Recovery
Scalability
Point-in-time recovery
Summary
Chapter 11: PostgreSQL – Troubleshooting
Connection issues
Authentication and permission issues
Parameter changes not effective
Query not responding
Summary
Chapter 12: PostgreSQL – Extras
Interesting data types
Features over time
Summary

What You Will Learn

  • Compile PostgreSQL from source and understand the PostgreSQL architecture
  • Configure parameters and change default settings for a PostgreSQL server
  • Leverage the logging mechanism to identify errors and suboptimal queries
  • Use replication to scale horizontally
  • Set up backup and recovery processes
  • Tweak parameters to optimize queries and processes
  • Troubleshoot connection errors
  • Use indexes and rewrite queries to improve performance
  • Install and use PostgreSQL extensions
  • Learn how to leverage the command-line client (psql)

Authors

Table of Contents

Chapter 1: Installing PostgreSQL
Installation options
Dependencies to compile the source
Configuring and creating the makefile
Building and creating the executables
Installing and moving the files to where they belong
Initializing a cluster
Working with extensions
Summary
Chapter 2: Server Architecture
Starting with the daemon process
Understanding the shared buffer
Checkpoint
WAL and the WAL writer process
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
Summary
Chapter 3: PostgreSQL – Object Hierarchy and Roles
The PostgreSQL cluster
Understanding tablespaces
Databases, schemas, and search_path
Roles and privileges
Summary
Chapter 4: Working with Transactions
Understanding transactions
PostgreSQL and MVCC
Summary
Chapter 5: Data Modeling with SQL Power Architect
Tools for databases and their uses
Database design tools
Generating SQL
Summary
Chapter 6: Client Tools
GUI tools and command-line tools
pgAdmin – downloading and installation
psql – working from the command line
Summary
Chapter 7: SQL Tuning
Understanding basic facts about databases
Query execution components
Finding the execution plan
Optimization guidelines and catches
Summary
Chapter 8: Server Tuning
Server-wide memory settings
Managing writes, connections, and maintenance
Seek/scan cost and statistics parameters
Materialized views
Partitioned tables
Summary
Chapter 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
Summary
Chapter 10: Scaling, Replication, and Backup and Recovery
Scalability
Point-in-time recovery
Summary
Chapter 11: PostgreSQL – Troubleshooting
Connection issues
Authentication and permission issues
Parameter changes not effective
Query not responding
Summary
Chapter 12: PostgreSQL – Extras
Interesting data types
Features over time
Summary

Book Details

ISBN 139781783288601
Paperback272 pages
Read More
From 4 reviews

Read More Reviews