PostgreSQL for data architects discover how to design, develop, and maintain your database application effectively with postgreSQL
This book is for developers and data architects who have some exposure to databases. It is assumed that you understand the basic concepts of tables and common database objects, including privileges and security.
| Otros Autores: | , , , , |
|---|---|
| Formato: | Libro electrónico |
| Idioma: | Inglés |
| Publicado: |
Birmingham, England ; Mumbai, [India] :
Packt Publishing
2015.
|
| Edición: | 1st edition |
| Colección: | Community experience distilled.
|
| Materias: | |
| Ver en Biblioteca Universitat Ramon Llull: | https://discovery.url.edu/permalink/34CSUC_URL/1im36ta/alma991009628957106719 |
Tabla de Contenidos:
- 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
- Managing writes, connections, and maintenance
