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

Add to playlist

Create a Playlist

Modal Close icon
You need to login to use this feature.
  • Learning PostgreSQL 11
  • Toc
  • feedback
Learning PostgreSQL 11

Learning PostgreSQL 11

By : Christopher Travers, Volkov
2.7 (6)
close
Learning PostgreSQL 11

Learning PostgreSQL 11

2.7 (6)
By: Christopher Travers, Volkov

Overview of this book

PostgreSQL is one of the most popular open source database management systems in the world, and it supports advanced features included in SQL standards. This book will familiarize you with the latest features in PostgreSQL 11, and get you up and running with building efficient PostgreSQL database solutions from scratch. Learning PostgreSQL, 11 begins by covering the concepts of relational databases and their core principles. You’ll explore the Data Definition Language (DDL) and commonly used DDL commands supported by ANSI SQL. You’ll also learn how to create tables, define integrity constraints, build indexes, and set up views and other schema objects. As you advance, you’ll come to understand Data Manipulation Language (DML) and server-side programming capabilities using PL/pgSQL, giving you a robust background to develop, tune, test, and troubleshoot your database application. The book will guide you in exploring NoSQL capabilities and connecting to your database to manipulate data objects. You’ll get to grips with using data warehousing in analytical solutions and reports, and scaling the database for high availability and performance. By the end of this book, you’ll have gained a thorough understanding of PostgreSQL 11 and developed the necessary skills to build efficient database solutions.
Table of Contents (18 chapters)
close
close
Free Chapter
1
Relational Databases
Relational Databases
Database management systems
Relational and object-relational databases
Relational algebra
Data modeling
Summary
Questions
2
PostgreSQL in Action
PostgreSQL in Action
An overview of PostgreSQL
PostgreSQL capabilities
Installing PostgreSQL
Summary
Questions
3
PostgreSQL Basic Building Blocks
PostgreSQL Basic Building Blocks
Database coding
PostgreSQL object hierarchy
PostgreSQL database components
PostgreSQL native data types
The car web portal database
Summary
Questions
4
PostgreSQL Advanced Building Blocks
PostgreSQL Advanced Building Blocks
Views
Indexes
Functions
User-defined data types
Trigger and rule systems
Summary
Questions
5
SQL Language
SQL Language
SQL fundamentals
Querying data with SELECT statements
Changing the data in a database
Summary
Questions
6
Advanced Query Writing
Advanced Query Writing
Common table expressions
Window functions
Advanced SQL techniques
Summary
Questions
7
Server-Side Programming with PL/pgSQL
Server-Side Programming with PL/pgSQL
The SQL language and PL/pgSQL – a comparison
Functions and procedures – a comparison
PostgreSQL function parameters
The PostgreSQL PL/pgSQL control statements
Function predefined variables
Exception handling
Dynamic SQL
Summary
Questions
8
OLAP and Data Warehousing
OLAP and Data Warehousing
OLTP and OLAP
Online analytical processing
Partitioning
Parallel query
Index-only scans
Summary
Questions
9
Beyond Conventional Data Types
Beyond Conventional Data Types
Arrays
The hash store data structure
The JSON data structure
A PostgreSQL full-text search
Summary
Questions
10
Transactions and Concurrency Control
Transactions and Concurrency Control
Transactions
Explicit locking
Transaction in functions and procedures
Summary
Questions
11
PostgreSQL Security
PostgreSQL Security
Authentication in PostgreSQL
Authentication best practices
PostgreSQL default access privileges
Role system and proxy authentication
PostgreSQL security levels
Encrypting data
Summary
Questions
12
The PostgreSQL Catalog
The PostgreSQL Catalog
The system catalog
System catalog for administrators
Cleaning up the database
Tuning for performance
Selective dump
Summary
Questions
13
Optimizing Database Performance
Optimizing Database Performance
PostgreSQL configuration tuning
Tuning performance for write
Tuning performance for read
Detecting problems in query plans
Common mistakes in writing queries
Table partitioning
Query rewriting
Summary
Questions
14
Testing
Testing
Technical requirements
Unit testing
Database-abstraction interfaces
Summary
Questions
15
Using PostgreSQL in Python Applications
Using PostgreSQL in Python Applications
Technical requirements
Python DB-API 2.0
Low-level database access with Psycopg 2
Alternative drivers for PostgreSQL
Object-relational mapping with SQLAlchemy
Summary
Questions
16
Scalability
Scalability
Technical requirements
The problem of scalability and the CAP theorem
Data replication in PostgreSQL
Building a scalable PostgreSQL solution
Summary
Questions
Further reading
17
What's Next?
What's Next?
Fundamentals
Working with PostgreSQL software
Working with data in PostgreSQL databases
Summary
18
Other Books You May Enjoy
chevron up
Other Books You May Enjoy
Leave a review - let other readers know what you think

Other Books You May Enjoy

If you enjoyed this book, you may be interested in these other books by Packt:

Mastering PostgreSQL 11, Second Edition
Hans-Jürgen Schönig

ISBN: 978-1-78953-781-9

  • Get to grips with advanced PostgreSQL 11 features and SQL functions
  • Make use of the indexing features in PostgreSQL and fine-tune the performance of your queries
  • Work with stored procedures and manage backup and recovery
  • Master replication and failover techniques
  • Troubleshoot your PostgreSQL instance for solutions to common and not-so-common problems
  • Perform database migration from MySQL and Oracle to PostgreSQL with ease

PostgreSQL 10 Administration Cookbook
Simon Riggs, Bob Gianni Ciolli

ISBN: 978-1-78847-492-4

  • Get to grips with the newly released PostgreSQL 10 features to improve database performance and reliability
  • Manage open source PostgreSQL versions 10 on various platforms...
End of Section 1
Next Section
bookmark search playlist font-size

Change the font size

margin-width

Change margin width

day-mode

Change background colour

Close icon Search
Country selected
Close icon Your notes and bookmarks

Delete Bookmark

Modal Close icon
Are you sure you want to delete it?
Cancel
Yes, Delete