Book Image

Mastering PostgreSQL 9.6

By : Hans-Jürgen Schönig
Book Image

Mastering PostgreSQL 9.6

By: Hans-Jürgen Schönig

Overview of this book

PostgreSQL is an open source database used for handling large datasets (Big Data) and as a JSON document database. It also has applications in the software and web domains. This book will enable you to build better PostgreSQL applications and administer databases more efficiently. We begin by explaining the advanced database design concepts in PostgreSQL 9.6, along with indexing and query optimization. You will also see how to work with event triggers and perform concurrent transactions and table partitioning, along with exploring SQL and server tuning. We will walk you through implementing advanced administrative tasks such as server maintenance and monitoring, replication, recovery and high availability, and much more. You will understand the common and not-so-common troubleshooting problems and how you can overcome them. By the end of this book, you will have an expert-level command of the advanced database functionalities and will be able to implement advanced administrative tasks with PostgreSQL.
Table of Contents (14 chapters)
Free Chapter
PostgreSQL Overview

Partitioning data

Given default 8,000 blocks, PostgreSQL can store up to 32 TB of data inside a single table. If you compile PostgreSQL with 32,000 blocks, you can even put up to 128 TB into a single table. However, large tables like that are not necessarily too convenient anymore and it can make sense to partition tables to make processing easier and in some cases a bit faster.

Starting with PostgreSQL 10.0, we will most likely have improved partitioning, which will offer end users significantly easier handling of data partitioning.

At the time this chapter was written, PostgreSQL 10.0 had not been released yet and therefore the old means of partitioning are covered.

Creating partitions

Before digging deeper into the advantages of partitioning, I want to show how...