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

Query execution components

PostgreSQL query execution involves the following four key components:

  • Parser: This performs syntax and semantic checking of the SQL string

  • Rewriter: This changes the query is some cases; for example, if the query is against a view, the rewriter will modify the query so that it goes against the base tables instead of the view

  • Planner: This key component comes up with a plan of execution

  • Executor: This executes the plan generated by the planner

Planner

The planner's job is to come up with a plan of execution. A plan is a tree consisting of one or more nodes. Each node, on execution, returns zero or more tuples. These tuples are used by the parent nodes in the tree until we reach the root node, which returns the results to the client. The planner has to make a few decisions, which we will cover now.

Access methods

Consider a simple query:

SELECT FIRST_NAME, LAST_NAME FROM EMP WHERE DEPT_NAME = 'HR'

A crucial decision the optimizer has to take is how to get the data that the...

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