PostgreSQL for data architects : discover how to design, develop, and maintain your database application effectively with PostgreSQL /
Annotation
Saved in:
| Main Author: | |
|---|---|
| Format: | Electronic eBook |
| Language: | English |
| Published: |
Birmingham, UK :
Packt Publishing,
2015.
|
| Series: | Community experience distilled.
|
| Subjects: | |
| Online Access: | CONNECT CONNECT |
Table of Contents:
- 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
- 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
- Object Hierarchy and Roles; The PostgreSQL cluster; Understanding tablespaces
- Managing temporary objects with temporary tablespacesViews; Databases, schemas, and search_path; Schemas
- 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
- downloading and installing; Creating tables; Generating SQL; Reverse engineering and making changes
- Exporting the data modelProfiling; Summary; Chapter 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; Chapter 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/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
