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

Understanding basic facts about databases

Let's understand a few facts about databases.

Fact 1 – databases are more frequently read from than written to

In almost all applications, except in the rarest of rare cases, a piece of data is read many times and written to or changed a few times. It's important to keep this fact in mind when we try to optimize queries. Let's look at a few examples.

First, let's consider a site http://www.expedia.com. The flight fares for a specific route get updated a few times at most for a specific class of seats and a specific date range. However, these sets of data will be queried many, many times by users around the world before a booking (a set of writes) eventually happens. This is often referred to as look to book ratio. It can range from 10:1 to a really high ratio like 50:1 or more in some cases.

Let's consider banking applications. We very often check our account balances. Even when we use an online platform with the sole purpose of transferring money to...

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