Learning PostgreSQL

Learning PostgreSQL

Overview of this book

PostgreSQL is one of the most powerful and easy to use database management systems. It supports the most advanced features included in SQL standards. The book starts with the introduction of relational databases with PostegreSQL. It then moves on to covering data definition language (DDL) with emphasis on PostgreSQL and common DDL commands supported by ANSI SQL. You will then learn the data manipulation language (DML), and advanced topics like locking and multi version concurrency control (MVCC). This will give you a very robust background to tune and troubleshoot your application. The book then covers the implementation of data models in the database such as creating tables, setting up integrity constraints, building indexes, defining views and other schema objects. Next, it will give you an overview about the NoSQL capabilities of PostgreSQL along with Hstore, XML, Json and arrays. Finally by the end of the book, you'll learn to use the JDBC driver and manipulate data objects in the Hibernate framework.

Learning PostgreSQL

Credits

About the Authors

About the Reviewers

www.PacktPub.com

Preface

Free Chapter

Relational Databases

Database management systems

Relational algebra

Data modeling

Summary

PostgreSQL in Action

An overview of PostgreSQL

PostgreSQL capabilities

Installing PostgreSQL

Summary

PostgreSQL Basic Building Blocks

Database coding

PostgreSQL objects hierarchy

PostgreSQL high-level object interaction

PostgreSQL database components

Summary

PostgreSQL Advanced Building Blocks

Views

Indexes

Functions

PostgreSQL user-defined data types

Triggers and rule systems

Summary

SQL Language

SQL fundamentals

Querying the data with the SELECT statement

Changing the data in the database

Summary

Advanced Query Writing

Common table expressions

Window functions

Advanced SQL

Transaction isolation and multiversion concurrency control

Summary

Server-Side Programming with PL/pgSQL

Introduction

PostgreSQL function parameters

The PostgreSQL PL/pgSQL control statements

Exception handling

Dynamic SQL

Summary

PostgreSQL Security

Authentication in PostgreSQL

PostgreSQL default access privileges

PostgreSQL security levels

Encrypting data

Summary

The PostgreSQL System Catalog and System Administration Functions

The system catalog

Getting the database cluster and client tools version

Terminating and canceling user sessions

Setting and getting database cluster settings

Getting the database and database object size

Cleaning up the database

Cleaning up data in the database

Managing database locks

Adding missing indexes on foreign keys and altering the default statistic

Getting the views dependency tree

Summary

Optimizing Database Performance

PostgreSQL configuration tuning

Tuning PostgreSQL queries

Cross column correlation

Table partitioning

Summary

Beyond Conventional Data types

PostgreSQL arrays

Hash store

The PostgreSQL JSON data type

A PostgreSQL full text search

Summary

Testing

Unit testing

Schema difference

The interfaces test

PostgreSQL benchmarks

Summary

PostgreSQL JDBC

Introduction to JDBC

Connecting to a PostgreSQL database

Issuing a query and processing the results

Function handling

Design considerations

Summary

PostgreSQL and Hibernate

Introduction to ORM and Hibernate

Installation and configuration

Working with entities

Summary

Index

Customer Reviews

5 star

4 star

3 star

2 star

1 star

PostgreSQL benchmarks

An important question regarding a database system is: how fast is it? How many transactions can it handle per second, or how much time a particular query takes to execute? The topic on the performance of a database has been covered in Chapter 10, Optimizing Database Performance. Here, we will only discuss the task of measuring it.

The Psql meta-command \timing is used to measure the time of execution of a particular SQL command. Once timing is enabled, psql shows the time of execution for each command:

car_portal-# \timing
Timing is on.
car_portal=# SELECT count(*) FROM car_portal_app.car;
 count
-------
   229
(1 row)
Time: 19.492 ms

Usually, that is enough to understand which query is faster. However, one cannot rely on that timing when it comes to estimating the number requests the server can handle per second. This is because the time for a single query depends on many random factors: the current load of the server, the state of the cache, and so on.

PostgreSQL provides...

Learning PostgreSQL

Learning PostgreSQL

Overview of this book

Related Content you might be interested in

Current Title:

Learning PostgreSQL

PostgreSQL benchmarks