Home Page Icon
Home Page
Table of Contents for
Learning PostgreSQL 10, Second Edtion
Close
Learning PostgreSQL 10, Second Edtion
by Andrey Volkov, Salahaldin Juba
Learning PostgreSQL 10 - Second Edition
Preface
What this book covers
What you need for this book
Who this book is for
Conventions
Reader feedback
Customer support
Downloading the example code
Downloading the color images of this book
Errata
Piracy
Questions
Relational Databases
Database management systems
A brief history
Database categories
The NoSQL databases
The CAP theorem
NoSQL motivation
Key-value databases
Columnar databases
Document databases
Graph databases
Relational and object relational databases
ACID properties
The SQL language
Relational model concepts
Relation
Tuple
NULL value
Attribute
Constraint
Domain integrity constraint
Entity integrity constraint
Referential integrity constraints
Semantic constraints
Relational algebra
The select and project operations
The rename operation
The set theory operations
The cartesian product operation
Data modeling
Data model perspectives
The entity-relation model
Sample application
Entities, attributes, and keys
Mapping ER to relations
UML class diagrams
Summary
PostgreSQL in Action
An overview of PostgreSQL
PostgreSQL history
The advantages of PostgreSQL
Business advantages of PostgreSQL
PostgreSQL user advantages
PostgreSQL applications
Success stories
Forks
PostgreSQL architecture
The PostgreSQL community
PostgreSQL capabilities
Replication
Security
Extension
NoSQL capabilities
Foreign data wrappers
Performance
Installing PostgreSQL
Installing PostgreSQL using Advanced Package Tool
Client installation
Server installation
Basic server configuration
Installing PostgreSQL on Windows
The PostgreSQL clients
The psql client
psql advanced settings
PostgreSQL utility tools
Summary
PostgreSQL Basic Building Blocks
Database coding
Database naming conventions
PostgreSQL identifiers
Documentation
Version control system
Database migration tool
PostgreSQL objects hierarchy
Template databases
User databases
Roles
Tablespace
Template procedural languages
Settings
PostgreSQL high-level object interaction
PostgreSQL database components
Schema
Schema usages
Table
PostgreSQL native data types
Numeric types
Character types
Date and time types
The car web portal database
Summary
PostgreSQL Advanced Building Blocks
Views
View synopsis
View categories
Materialized views
Updatable views
Indexes
Index synopsis
Index selectivity
Index types
Index categories
Best practices on indexes
Functions
PostgreSQL native programming language
Creating a function in the C language
Function usages
Function dependency
PostgreSQL function categories
PostgreSQL anonymous functions
User-defined data types
Triggers and rule systems
Rule system
Trigger system
Triggers with arguments
Triggers and updatable views
Summary
SQL Language
SQL fundamentals
SQL lexical structure
Querying data with SELECT statements
Structure of a SELECT query
Select-list
SQL expressions
DISTINCT
FROM clause
Selecting from multiple tables
Self-joins
The WHERE clause
Comparison operators
Pattern matching
Row and array comparison constructs
Grouping and aggregation
The GROUP BY clause
The HAVING clause
Ordering and limiting the results
Subqueries
Set operations – UNION, EXCEPT, and INTERSECT
Dealing with NULLs
Changing the data in the database
INSERT statement
The UPDATE statement
UPDATE using sub-select
UPDATE using additional tables
The DELETE statement
The TRUNCATE statement
Summary
Advanced Query Writing
Common table expressions
Reusing SQL code with CTE
Recursive and hierarchical queries
Changing data in multiple tables at a time
Window functions
Window definition
The WINDOW clause
Using window functions
Window functions with grouping and aggregation
Advanced SQL techniques
Selecting the first records
Selecting a data sample
Set returning functions
Lateral subqueries
Advanced grouping
Advanced aggregation
Summary
Server-Side Programming with PL/pgSQL
SQL language and PL/pgSQL – a comparison
PostgreSQL function parameters
Function authorization-related parameters
Function planner-related parameters
Function configuration-related parameters
The PostgreSQL PL/pgSQL control statements
Declaration statements
Assignment statements
Conditional statements
Iteration
Loop statement
While loop statement
For loop statement
Returning from the function
Returning void
Returning a single row
Returning multiple rows
Function predefined variables
Exception handling
Dynamic SQL
Executing DDL statements in dynamic SQL
Executing DML statements in dynamic SQL
Dynamic SQL and the caching effect
Recommended practices for dynamic SQL usage
Summary
OLAP and Data Warehousing
Online analytical processing
Extract, transform, and load
Data modeling for OLAP
Aggregation
Partitioning
Parallel query
Index-only scans
Summary
Beyond Conventional Data Types
Arrays
Common functions of arrays and their operators
Modifying and accessing arrays
Indexing arrays
The hash store data structure
Indexing an hstore
The JSON data structure
JSON and XML
JSON data types for PostgreSQL
Modifying and accessing JSON types
Indexing a JSON data type
Providing a RESTful API interface for PostgreSQL
A PostgreSQL full text search
The tsquery and tsvector data types
The tsvector data type
The tsquery data type
Pattern matching
Full text search indexing
Summary
Transactions and Concurrency Control
Transactions
Transaction and ACID properties
Transaction and concurrency
MVCC in PostgreSQL
Transaction isolation levels
Explicit locking
Table-level locks
Table locking modes
Row-level locks
Row-level lock modes
Deadlocks
Advisory locks
Summary
PostgreSQL Security
Authentication in PostgreSQL
PostgreSQL pg_hba.conf
Listen addresses
Authentication best practices
PostgreSQL default access privileges
Role system and proxy authentication
PostgreSQL security levels
Database security level
Schema security level
Table-level security
Column-level security
Row-level security
Encrypting data
PostgreSQL role password encryption
pgcrypto
One-way encryption
Two-way encryption
Summary
The PostgreSQL Catalog
The system catalog
System catalog for administrators
Getting the database cluster and client tools version
Terminating and canceling user sessions
Defining and getting database cluster settings
Getting the database and database object size
Cleaning up the database
Cleaning up the database data
Tuning for performance
Selective dump
Summary
Optimizing Database Performance
PostgreSQL configuration tuning
Maximum number of connections
Memory settings
Hard disk settings
Planner-related settings
Bench marking is your friend
Tuning performance for write
Tuning performance for read
Explain command and execution plan
Detecting problems in query plans
Common mistakes in writing queries
Unnecessary operations
Misplaced or missing indexes
Using CTE when not mandatory
Using the PL/pgSQL procedural language consideration
Cross column correlation
Table partitioning
Constraint exclusion limitations
Query rewriting
Summary
Testing
Unit testing
Specificity of unit testing in databases
Unit test frameworks
Schema difference
Database abstraction interfaces
Data difference
Performance testing
Summary
Using PostgreSQL in Python Applications
Python DB API 2.0
Low-level database access with psycopg2
Connecting to a database
Connection pooling
Executing SQL commands
Reading data from a database
The COPY command
Asynchronous access
Alternative drivers for PostgreSQL
pg8000
asyncpg
Object relational mapping with SQLAlchemy
Main components of SQLAlchemy
Connecting to a database and retrieving data with SQL Expression Language
ORM
Summary
Scalability
The problem of scalability and the CAP theorem
Data replication in PostgreSQL
Transaction log
Physical replication
Log shipping replication
Streaming replication
Synchronous replication
Logical replication
Using replication to scale PostgreSQL
Scaling for heavy querying
Data sharding
Scaling for big number of connections
Summary
Search in book...
Toggle Font Controls
Playlists
Add To
Create new playlist
Name your new playlist
Playlist description (optional)
Cancel
Create playlist
Sign In
Email address
Password
Forgot Password?
Create account
Login
or
Continue with Facebook
Continue with Google
Sign Up
Full Name
Email address
Confirm Email Address
Password
Login
Create account
or
Continue with Facebook
Continue with Google
Next
Next Chapter
Title Page
Add Highlight
No Comment
..................Content has been hidden....................
You can't read the all page of ebook, please click
here
login for view all page.
Day Mode
Cloud Mode
Night Mode
Reset