PostgreSQL for data architects
Subtitle: discover how to design, develop, and maintain your database application effectively with PostgreSQL
By: Jayadevan Maymala
Series: Community experience disitlled; Community experience distilled
Publisher: Packt Publishing
Published: 2015
Place of publication: Birmingham, UK
Language: eng
ISBN-13: 9781783288618; 9781783288601
Subjects: Database management; Database design; Bases de données; Gestion; Conception; COMPUTERS / Database Management / General; PostgreSQL
Other titles: Discover how to design, develop, and maintain your database application effectively with PostgreSQL; Postgre Structured Query Language for data architects
Description: Annotation
Notes: Includes index.
Table of contents: title: Cover; Copyright; Credits; About the Author; About the Reviewers; www.PacktPub.com; Table of Contents; Preface; Chapter 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; Chapter 2: Server Architecture; title: Starting with the daemon processUnderstanding 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; Chapter 3: PostgreSQL; title: Object Hierarchy and Roles; The PostgreSQL cluster; Understanding tablespaces; title: Managing temporary objects with temporary tablespacesViews; Databases, schemas, and search_path; Schemas; title: use cases; Roles and privileges; Summary; Chapter 4: Working with Transactions; Understanding transactions; ACID properties of transactions; A for atomicity; C for consistency; I for isolation; D for durability; PostgreSQL and MVCC; Summary; Chapter 5: Data Modeling with SQL Power Architect; Tools for databases and their uses; Database design tools; SQL Power Architect; title: downloading and installing; Creating tables; Generating SQL; Reverse engineering and making changes; title: Exporting the data modelProfiling; Summary; Chapter 6: Client Tools; GUI tools and command-line tools; pgAdmin; title: downloading and installation; Adding a server; The pgAdmin main window; The Query tool; psql; title: working from the command line; psql; title: connection options; The power of \d; More meta-commands; Setting up the environment; History of commands; Summary; Chapter 7: SQL Tuning; Understanding basic facts about databases; Fact 1; title: databases are more frequently read from than written to; Fact 2; title: data is always read in blocks or pages, not as individual records or columns; title: Approaches to reducing the number of blocks read/writtenQuery 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; Chapter 8: Server Tuning; Server-wide memory settings; shared_buffers; effective_cache_size