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

Table partitioning

Table partitioning is used to increase performance by physically arranging data in the hard disk based on a certain grouping criteria. There are two techniques for table partitioning:

Vertical table partitioning: The table is divided into several tables in order to decrease the row size. This allows a faster sequential scan on divided tables as a relation page holds more rows. To explain, let's assume that we want to store pictures for each seller in the database to be used as their respective logos. One could model this by adding a column of the bytea or blob type. The other approach is to have another table reference the sellers table, as follows:
```
CREATE TABLE car_portal_app.seller_logo (
  seller_id INT PRIMARY KEY REFERENCES car_portal_app.seller_account (seller_account_id),
  logo bytea NOT NULL
);
```
Horizontal table partitioning: This is used to decrease the whole table size by splitting the rows over multiple tables; it is supported by table inheritance and constraint...

Learning PostgreSQL

Learning PostgreSQL

Overview of this book

Related Content you might be interested in

Current Title:

Learning PostgreSQL

Table partitioning