Book Image

PostgreSQL for Data Architects

By : Jayadevan M
Book Image

PostgreSQL for Data Architects

By: Jayadevan M

Overview of this book

Related Content you might be interested in

Current Title:

Small book image
PostgreSQL for Data Architects
Jayadevan M
Mar, 2015 | 272 pages
No titles found
Table of Contents (19 chapters)
PostgreSQL for Data Architects
PostgreSQL for Data Architects
Credits
Credits
About the Author
About the Author
About the Reviewers
About the Reviewers
www.PacktPub.com
www.PacktPub.com
Preface
Preface
Free Chapter
1
Installing PostgreSQL
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
2
Server Architecture
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
3
PostgreSQL – Object Hierarchy and Roles
PostgreSQL – Object Hierarchy and Roles
The PostgreSQL cluster
Understanding tablespaces
Databases, schemas, and search_path
Roles and privileges
Summary
4
Working with Transactions
Working with Transactions
Understanding transactions
PostgreSQL and MVCC
Summary
5
Data Modeling with SQL Power Architect
Data Modeling with SQL Power Architect
Tools for databases and their uses
Database design tools
Generating SQL
Summary
6
Client Tools
Client Tools
GUI tools and command-line tools
pgAdmin – downloading and installation
psql – working from the command line
Summary
7
SQL Tuning
SQL Tuning
Understanding basic facts about databases
Query execution components
Finding the execution plan
Optimization guidelines and catches
Summary
8
Server Tuning
Server Tuning
Server-wide memory settings
Managing writes, connections, and maintenance
Seek/scan cost and statistics parameters
Materialized views
Partitioned tables
Summary
9
Tools to Move Data in and out of PostgreSQL
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
10
Scaling, Replication, and Backup and Recovery
Scaling, Replication, and Backup and Recovery
Scalability
Point-in-time recovery
Summary
11
PostgreSQL – Troubleshooting
PostgreSQL – Troubleshooting
Connection issues
Authentication and permission issues
Parameter changes not effective
Query not responding
Summary
12
PostgreSQL – Extras
PostgreSQL – Extras
Interesting data types
Features over time
Summary
Index
Index

About the Author

Jayadevan Maymala is a database developer, designer, and architect. He started working with the Oracle database in 1999. Over the years, he has worked with DB2, Sybase, and SQL Server. Of late, he has been working with open source technologies. His database of choice is PostgreSQL. In his career, he has worked in different domains spanning supply chain management, finance, and travel. He has spent an equal amount of time working with databases supporting critical transaction processing systems as well as data warehouses supporting analytical systems.

When he is not working on open source technologies, he spends time reading and updating himself on economic and political issues.

I would like to thank my wife, Uma, for putting up with my marathon writing sessions over weekends. I am also deeply indebted to the PostgreSQL community, which has individuals who are always promptly replying to my basic and not-so-basic queries. It's an amazing team that has been working tirelessly to build such a great database and then give it away for free with such liberal licensing terms. Thank you.

Previous Section
Next Chapter