PostgreSQL for Data Architects

PostgreSQL for Data Architects

by Jayadevan Maymala
Released March 2015
Publisher(s): Packt Publishing
ISBN: 9781783288601

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

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

In Detail

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.

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)

Table of contents

  1. PostgreSQL for Data Architects
    1. Table of Contents
    2. PostgreSQL for Data Architects
    3. Credits
    4. About the Author
    5. About the Reviewers
    6. www.PacktPub.com
      1. Support files, eBooks, discount offers, and more
        1. Why subscribe?
        2. Free access for Packt account holders
    7. Preface
      1. What this book covers
      2. What you need for this book
      3. Who this book is for
      4. Conventions
      5. Reader feedback
      6. Customer support
        1. Downloading the example code
        2. Errata
        3. Piracy
        4. Questions
    8. 1. Installing PostgreSQL
      1. Installation options
        1. Downloading and extracting the source
        2. Inspecting the contents
      2. Dependencies to compile the source
      3. Configuring and creating the makefile
      4. Building and creating the executables
      5. Installing and moving the files to where they belong
        1. Inspecting the changes
      6. Initializing a cluster
        1. A quick walk through the directories
        2. Processes created
        3. Important files created
      7. Working with extensions
      8. Summary
    9. 2. Server Architecture
      1. Starting with the daemon process
      2. Understanding the shared buffer
        1. Inspecting the buffer cache
      3. Checkpoint
      4. WAL and the WAL writer process
        1. Recovery
        2. Incremental backup and point-in-time recovery
        3. Replication
      5. The background writer
      6. The autovacuum launcher process
      7. The logging process
      8. The stats collector process
      9. The WAL sender and WAL receiver
      10. Sorting in memory with work_mem
      11. Maintenance with maintenance_work_mem
        1. Understanding effective_cache_size
      12. Summary
    10. 3. PostgreSQL – Object Hierarchy and Roles
      1. The PostgreSQL cluster
      2. Understanding tablespaces
        1. Managing temporary objects with temporary tablespaces
        2. Views
      3. Databases, schemas, and search_path
        1. Schemas – use cases
      4. Roles and privileges
      5. Summary
    11. 4. Working with Transactions
      1. Understanding transactions
        1. ACID properties of transactions
          1. A for atomicity
          2. C for consistency
          3. I for isolation
            1. Read uncommitted
            2. Read committed
            3. Repeatable reads
            4. Serializable
              1. Read committed
              2. Repeatable read
              3. Serializable level
          4. D for durability
      2. PostgreSQL and MVCC
      3. Summary
    12. 5. Data Modeling with SQL Power Architect
      1. Tools for databases and their uses
      2. Database design tools
        1. SQL Power Architect – downloading and installing
        2. Creating tables
      3. Generating SQL
        1. Reverse engineering and making changes
        2. Exporting the data model
        3. Profiling
      4. Summary
    13. 6. Client Tools
      1. GUI tools and command-line tools
      2. pgAdmin – downloading and installation
        1. Adding a server
        2. The pgAdmin main window
        3. The Query tool
      3. psql – working from the command line
        1. psql – connection options
        2. The power of \d
        3. More meta-commands
        4. Setting up the environment
        5. History of commands
      4. Summary
    14. 7. SQL Tuning
      1. Understanding basic facts about databases
        1. Fact 1 – databases are more frequently read from than written to
        2. Fact 2 – data is always read in blocks or pages, not as individual records or columns
        3. Approaches to reducing the number of blocks read/written
      2. Query execution components
        1. Planner
        2. Access methods
        3. Join strategies
      3. Finding the execution plan
      4. Optimization guidelines and catches
        1. Indexing foreign keys
        2. Using SELECT *
        3. Using ORDER BY
        4. Using DISTINCT
        5. Using UNION ALL instead of UNION
        6. Using functions in the FILTER clause
        7. Reducing the number of SQL statements
        8. Reducing function executions
        9. Not using indexes
        10. Partial indexes
        11. Optimizing functions
      5. Summary
    15. 8. Server Tuning
      1. Server-wide memory settings
        1. shared_buffers
        2. effective_cache_size
      2. Managing writes, connections, and maintenance
      3. Seek/scan cost and statistics parameters
        1. CPU costs
      4. Materialized views
      5. Partitioned tables
      6. Summary
    16. 9. Tools to Move Data in and out of PostgreSQL
      1. Setting up the production database – considerations
      2. COPY
      3. Fast loading with pg_bulkload
      4. pg_dump
      5. Filtering options
        1. pg_dumpall
        2. pg_restore
      6. Summary
    17. 10. Scaling, Replication, and Backup and Recovery
      1. Scalability
        1. Vertical scaling
        2. Horizontal scaling
          1. Master-slave(s) with read/write separation
          2. Streaming replication
            1. Configuring primary
            2. Configuring secondary
            3. Making the standby in synch with primary
          3. Connection pooling, load balancing, and failover with pgpool-II
            1. Configuring pgpool-II
            2. Test read/write separation
            3. Test failover
          4. Sharding
          5. Multi-master full replication
      2. Point-in-time recovery
      3. Summary
    18. 11. PostgreSQL – Troubleshooting
      1. Connection issues
      2. Authentication and permission issues
      3. Parameter changes not effective
      4. Query not responding
      5. Summary
    19. 12. PostgreSQL – Extras
      1. Interesting data types
        1. RANGE
          1. Using network address types
          2. hstore for key-value pairs
          3. json/jsonb
        2. XML
          1. Inserting and verifying XML data
          2. Generating XML files for table definitions and data
        3. Geometry and geography
        4. Foreign Data Wrappers
          1. FDW for files
          2. PostgreSQL FDW
          3. Data wrappers – other aspects
        5. pgbadger
      2. Features over time
        1. Interesting features in 9.4
          1. Keeping the buffer ready
          2. Better recoverability
          3. Easy-to-change parameters
          4. Logical decoding and consumption of changes
      3. Summary
    20. Index

Product information

  • Title: PostgreSQL for Data Architects
  • Author(s): Jayadevan Maymala
  • Release date: March 2015
  • Publisher(s): Packt Publishing
  • ISBN: 9781783288601