Browse Library
Sign In Start Free Trial
Close icon Search icon
Account
Browse Library Sign In Start Free Trial

Add to playlist

Create a Playlist

Modal Close icon
You need to login to use this feature.
  • Book Overview & Buying PostgreSQL for Data Architects
  • Table Of Contents Toc
PostgreSQL for Data Architects

PostgreSQL for Data Architects

By : Jayadevan M
5 (2)
Buy this Book
close
close
PostgreSQL for Data Architects

PostgreSQL for Data Architects

5 (2)
By: Jayadevan M
Buy this Book

Overview of this book

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.
Table of Contents (14 chapters)
close
close
close
Preface
Icon
Preface
Icon
What this book covers
Icon
What you need for this book
Icon
Who this book is for
Icon
Conventions
Icon
Reader feedback
Icon
Customer support
Lock Free Chapter
1
1. Installing PostgreSQL
Icon
1. Installing PostgreSQL
Icon
Installation options
Icon
Dependencies to compile the source
Icon
Configuring and creating the makefile
Icon
Building and creating the executables
Icon
Installing and moving the files to where they belong
Icon
Initializing a cluster
Icon
Working with extensions
Icon
Summary
2
2. Server Architecture
Icon
2. Server Architecture
Icon
Starting with the daemon process
Icon
Understanding the shared buffer
Icon
Checkpoint
Icon
WAL and the WAL writer process
Icon
The background writer
Icon
The autovacuum launcher process
Icon
The logging process
Icon
The stats collector process
Icon
The WAL sender and WAL receiver
Icon
Sorting in memory with work_mem
Icon
Maintenance with maintenance_work_mem
Icon
Summary
3
3. PostgreSQL – Object Hierarchy and Roles
Icon
3. PostgreSQL – Object Hierarchy and Roles
Icon
The PostgreSQL cluster
Icon
Understanding tablespaces
Icon
Databases, schemas, and search_path
Icon
Roles and privileges
Icon
Summary
4
4. Working with Transactions
Icon
4. Working with Transactions
Icon
Understanding transactions
Icon
PostgreSQL and MVCC
Icon
Summary
5
5. Data Modeling with SQL Power Architect
Icon
5. Data Modeling with SQL Power Architect
Icon
Tools for databases and their uses
Icon
Database design tools
Icon
Generating SQL
Icon
Summary
6
6. Client Tools
Icon
6. Client Tools
Icon
GUI tools and command-line tools
Icon
pgAdmin – downloading and installation
Icon
psql – working from the command line
Icon
Summary
7
7. SQL Tuning
chevron up
Icon
7. SQL Tuning
Icon
Understanding basic facts about databases
Icon
Query execution components
Icon
Finding the execution plan
Icon
Optimization guidelines and catches
Icon
Summary
8
8. Server Tuning
Icon
8. Server Tuning
Icon
Server-wide memory settings
Icon
Managing writes, connections, and maintenance
Icon
Seek/scan cost and statistics parameters
Icon
Materialized views
Icon
Partitioned tables
Icon
Summary
9
9. Tools to Move Data in and out of PostgreSQL
Icon
9. Tools to Move Data in and out of PostgreSQL
Icon
Setting up the production database – considerations
Icon
COPY
Icon
Fast loading with pg_bulkload
Icon
pg_dump
Icon
Filtering options
Icon
Summary
10
10. Scaling, Replication, and Backup and Recovery
Icon
10. Scaling, Replication, and Backup and Recovery
Icon
Scalability
Icon
Point-in-time recovery
Icon
Summary
11
11. PostgreSQL – Troubleshooting
Icon
11. PostgreSQL – Troubleshooting
Icon
Connection issues
Icon
Authentication and permission issues
Icon
Parameter changes not effective
Icon
Query not responding
Icon
Summary
12
12. PostgreSQL – Extras
Icon
12. PostgreSQL – Extras
Icon
Interesting data types
Icon
Features over time
Icon
Summary
13
Index
Icon
Index

Chapter 7. SQL Tuning

Having covered how to access PostgreSQL using client tools, it's time to focus on queries and query optimization. First, we will cover a few facts before looking at the decisions the optimizer has to take. Finally, we will cover a few optimization strategies and a few rules of thumb to be followed.

We need to realize that query optimization does not always mean rewriting queries. It might involve changing table definitions, creating indexes, and sometimes even dropping indexes. It's also necessary to understand what information the optimizer needs to arrive at optimal plans. Let's cover a few facts about how databases are used and how do they work.

CONTINUE READING
83
Tech Concepts
36
Programming languages
73
Tech Tools
Icon Unlimited access to the largest independent learning library in tech of over 8,000 expert-authored tech books and videos.
Icon Innovative learning tools, including AI book assistants, code context explainers, and text-to-speech.
Icon 50+ new titles added per month and exclusive early access to books as they are being written.
PostgreSQL for Data Architects
End of Section 7
Next Section
notes
bookmark Notes and Bookmarks search Search in title playlist Add to playlist font-size Font size

Change the font size

margin-width Margin width

Change margin width

day-mode Day/Sepia/Night Modes

Change background colour

Close icon Search
Country selected
Close icon Your notes and bookmarks

Confirmation

Modal Close icon
claim successful
Order Page Read Now

Buy this book with your credits?

Modal Close icon
Are you sure you want to buy this book with one of your credits?
Close
YES, BUY

Submit Your Feedback

Modal Close icon
Modal Close icon
Modal Close icon