Cargando…
PostgreSQL for data architects : discover how to design, develop, and maintain your database application effectively with PostgreSQL /
Annotation
| Clasificación: | Libro Electrónico |
|---|---|
| Autor principal: | |
| Formato: | Electrónico eBook |
| Idioma: | Inglés |
| Publicado: |
Birmingham, UK :
Packt Publishing,
2015.
|
| Colección: | Community experience distilled.
|
| Temas: | |
| Acceso en línea: | Texto completo (Requiere registro previo con correo institucional) |
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