Search icon CANCEL
Arrow left icon
Explore Products
Best Sellers
New Releases
Books
Videos
Audiobooks
Learning Hub
Conferences
Free Learning
Arrow right icon
Learn PostgreSQL
Learn PostgreSQL

Learn PostgreSQL: Build and manage high-performance database solutions using PostgreSQL 12 and 13

Arrow left icon
Profile Icon Ferrari Profile Icon Enrico Pirozzi
Arrow right icon
€18.99 per month
Full star icon Full star icon Full star icon Half star icon Empty star icon 3.4 (5 Ratings)
Paperback Oct 2020 650 pages 1st Edition
eBook
€20.98 €29.99
Paperback
€36.99
Subscription
Free Trial
Renews at €18.99p/m
Arrow left icon
Profile Icon Ferrari Profile Icon Enrico Pirozzi
Arrow right icon
€18.99 per month
Full star icon Full star icon Full star icon Half star icon Empty star icon 3.4 (5 Ratings)
Paperback Oct 2020 650 pages 1st Edition
eBook
€20.98 €29.99
Paperback
€36.99
Subscription
Free Trial
Renews at €18.99p/m
eBook
€20.98 €29.99
Paperback
€36.99
Subscription
Free Trial
Renews at €18.99p/m

What do you get with a Packt Subscription?

Free for first 7 days. $19.99 p/m after that. Cancel any time!
Product feature icon Unlimited ad-free access to the largest independent learning library in tech. Access this title and thousands more!
Product feature icon 50+ new titles added per month, including many first-to-market concepts and exclusive early access to books as they are being written.
Product feature icon Innovative learning tools, including AI book assistants, code context explainers, and text-to-speech.
Product feature icon Thousands of reference materials covering every tech concept you need to stay up to date.
Subscribe now
View plans & pricing
Table of content icon View table of contents Preview book icon Preview Book

Learn PostgreSQL

Introduction to PostgreSQL

PostgreSQL is a well-known open-source relational database, and its motto states what the project intends to be: the most advanced open-source database in the world.

The main qualities that attract masses of new users every year and keep current users enthusiastic about their projects are its rock-solid stability, scalability, and safeness, as well as the features that an enterprise-level database management system provides.

But PostgreSQL is not just a database; it has grown to be a whole ecosystem of extensions, tools, and languages tied together by communities spread around the world.

PostgreSQL is an open-source project and is fully developed in the open-source world. That means that there is no single entity in charge of the project and the result is that PostgreSQL is not a commercial product. In other words, PostgreSQL belongs to everyone, and...

Technical requirements

PostgreSQL at a glance

As a relational database, PostgreSQL provides a lot of features, and it is quite difficult to "scare" a PostgreSQL instance. In fact, a single instance can contain more than 4 billion individual databases, each with unlimited total size and capacity for more than 1 billion tables, each containing 32 TB of data. Moreover, if there's any concern that those upper limits won't suffice, please consider that a single table can have 1,600 columns, each 1 GB in size, with an unlimited number of multi-column (up to 32 columns) indexes. In short, PostgreSQL can store much more data than you can possibly think of!

Therefore, there is no amount of data that PostgreSQL cannot handle, but of course, in order to perform well with certain big databases, you need to understand PostgreSQL and its features.

PostgreSQL is fully ACID-compliant and has a very strong foundation in data integrity and concurrency. It ships with a procedural language, named PL/pgSQL...

Exploring PostgreSQL terminology

A PostgreSQL instance is called a cluster because a single instance can serve and handle multiple databases. Every database is an isolated space where users and applications can store data.

A database is accessed by allowed users, but users connected to a database cannot cross the database boundaries and interact with data contained in another database, unless they explicitly connect to the latter database too.

A database can be organized into namespaces, called schemas. A schema is a mnemonic name that the user can assign to organize database objects, such as tables, into a more structured collection. Schemas cannot be nested, so they represent a flat namespace.

Database objects are represented by everything the user can create and manage within the database—for instance, tables, functions, triggers, and data types. Every object belongs to one and only one schema that, if not specified, is the default public schema.

Users are defined at a cluster...

Installing PostgreSQL 12 or higher

PostgreSQL can run on several Unix and Unix-like operating systems, such as Linux, as well as on Microsoft Windows. So far, the most supported platform remains Linux because most PostgreSQL developers work on this platform, and so it is the one with the most tested use cases. However, deploying on other platforms should not present any problems and, most importantly, is not going to put your data at any risk.

This section will focus on installing PostgreSQL 12, since it is the latest stable version available worldwide. You will learn, however, how to build your own version of PostgreSQL, and this may also be the way to install PostgreSQL 13 on your system.

Before installing PostgreSQL 12, you need to choose, or at least evaluate, how to install it. There are two main ways to get PostgreSQL 12 up and running, as follows:

  • Compiling from sources
  • Using a binary package

Binary packages are provided by the PostgreSQL community or the operating system, and...

Summary

This chapter has introduced you to PostgreSQL, its history, and its main features. You have learned about PostgreSQL terminology, as well as how to install a cluster on Unix-like operating systems, such as GNU/Linux Debian, Fedora, and FreeBSD, as well as installing the tool from various sources.

In the following chapters, you will start using this great database engine and learn details about every main single feature it provides.

References

Left arrow icon Right arrow icon
Download code icon Download Code

Key benefits

  • Set up your database cluster and monitor, secure, and fine-tune it for optimal performance
  • Learn the fundamentals of database management and implement client- and server-side programming using SQL and PL/pgSQL
  • Explore useful tips to develop efficient PostgreSQL database solutions from scratch

Description

PostgreSQL is one of the fastest-growing open source object-relational database management systems (DBMS) in the world. As well as being easy to use, it’s scalable and highly efficient. In this book, you’ll explore PostgreSQL 12 and 13 and learn how to build database solutions using it. Complete with hands-on tutorials, this guide will teach you how to achieve the right database design required for a reliable environment. You'll learn how to install and configure a PostgreSQL server and even manage users and connections. The book then progresses to key concepts of relational databases, before taking you through the Data Definition Language (DDL) and commonly used DDL commands. To build on your skills, you’ll understand how to interact with the live cluster, create database objects, and use tools to connect to the live cluster. You’ll then get to grips with creating tables, building indexes, and designing your database schema. Later, you'll explore the Data Manipulation Language (DML) and server-side programming capabilities of PostgreSQL using PL/pgSQL, before learning how to monitor, test, and troubleshoot your database application to ensure high-performance and reliability. By the end of this book, you'll be well-versed with the Postgres database and be able to set up your own PostgreSQL instance and use it to build robust solutions.

Who is this book for?

This Postgres book is for anyone interested in learning about the PostgreSQL database from scratch. Anyone looking to build robust data warehousing applications and scale the database for high-availability and performance using the latest features of PostgreSQL will also find this book useful. Although prior knowledge of PostgreSQL is not required, familiarity with databases is expected.

What you will learn

  • Understand how users and connections are managed by running a PostgreSQL instance
  • Interact with transaction boundaries using server-side programming
  • Identify bottlenecks to maintain your database efficiently
  • Create and manage extensions to add new functionalities to your cluster
  • Choose the best index type for each situation
  • Use online tools to set up a memory configuration that will suit most databases
  • Explore how Postgres can be used in multi-instance environments to provide high-availability, redundancy, and scalability

Product Details

Country selected
Publication date, Length, Edition, Language, ISBN-13
Publication date : Oct 09, 2020
Length: 650 pages
Edition : 1st
Language : English
ISBN-13 : 9781838985288
Vendor :
PostgreSQL Global Development Group
Category :
Languages :
Concepts :
Tools :

What do you get with a Packt Subscription?

Free for first 7 days. $19.99 p/m after that. Cancel any time!
Product feature icon Unlimited ad-free access to the largest independent learning library in tech. Access this title and thousands more!
Product feature icon 50+ new titles added per month, including many first-to-market concepts and exclusive early access to books as they are being written.
Product feature icon Innovative learning tools, including AI book assistants, code context explainers, and text-to-speech.
Product feature icon Thousands of reference materials covering every tech concept you need to stay up to date.
Subscribe now
View plans & pricing

Product Details

Publication date : Oct 09, 2020
Length: 650 pages
Edition : 1st
Language : English
ISBN-13 : 9781838985288
Vendor :
PostgreSQL Global Development Group
Category :
Languages :
Concepts :
Tools :

Packt Subscriptions

See our plans and pricing
Modal Close icon
€18.99 billed monthly
Feature tick icon Unlimited access to Packt's library of 7,000+ practical books and videos
Feature tick icon Constantly refreshed with 50+ new titles a month
Feature tick icon Exclusive Early access to books as they're written
Feature tick icon Solve problems while you work with advanced search and reference features
Feature tick icon Offline reading on the mobile app
Feature tick icon Simple pricing, no contract
€189.99 billed annually
Feature tick icon Unlimited access to Packt's library of 7,000+ practical books and videos
Feature tick icon Constantly refreshed with 50+ new titles a month
Feature tick icon Exclusive Early access to books as they're written
Feature tick icon Solve problems while you work with advanced search and reference features
Feature tick icon Offline reading on the mobile app
Feature tick icon Choose a DRM-free eBook or Video every month to keep
Feature tick icon PLUS own as many other DRM-free eBooks or Videos as you like for just €5 each
Feature tick icon Exclusive print discounts
€264.99 billed in 18 months
Feature tick icon Unlimited access to Packt's library of 7,000+ practical books and videos
Feature tick icon Constantly refreshed with 50+ new titles a month
Feature tick icon Exclusive Early access to books as they're written
Feature tick icon Solve problems while you work with advanced search and reference features
Feature tick icon Offline reading on the mobile app
Feature tick icon Choose a DRM-free eBook or Video every month to keep
Feature tick icon PLUS own as many other DRM-free eBooks or Videos as you like for just €5 each
Feature tick icon Exclusive print discounts

Frequently bought together


Stars icon
Total 106.97
Mastering PostgreSQL 13
€32.99
Learn PostgreSQL
€36.99
PostgreSQL 12 High Availability Cookbook
€36.99
Total 106.97 Stars icon

Table of Contents

26 Chapters
Section 1: Getting Started Chevron down icon Chevron up icon
Introduction to PostgreSQL Chevron down icon Chevron up icon
Getting to Know Your Cluster Chevron down icon Chevron up icon
Managing Users and Connections Chevron down icon Chevron up icon
Section 2: Interacting with the Database Chevron down icon Chevron up icon
Basic Statements Chevron down icon Chevron up icon
Advanced Statements Chevron down icon Chevron up icon
Window Functions Chevron down icon Chevron up icon
Server-Side Programming Chevron down icon Chevron up icon
Triggers and Rules Chevron down icon Chevron up icon
Partitioning Chevron down icon Chevron up icon
Section 3: Administering the Cluster Chevron down icon Chevron up icon
Users, Roles, and Database Security Chevron down icon Chevron up icon
Transactions, MVCC, WALs, and Checkpoints Chevron down icon Chevron up icon
Extending the Database - the Extension Ecosystem Chevron down icon Chevron up icon
Indexes and Performance Optimization Chevron down icon Chevron up icon
Logging and Auditing Chevron down icon Chevron up icon
Backup and Restore Chevron down icon Chevron up icon
Configuration and Monitoring Chevron down icon Chevron up icon
Section 4: Replication Chevron down icon Chevron up icon
Physical Replication Chevron down icon Chevron up icon
Logical Replication Chevron down icon Chevron up icon
Section 5: The PostegreSQL Ecosystem Chevron down icon Chevron up icon
Useful Tools and Extensions Chevron down icon Chevron up icon
Toward PostgreSQL 13 Chevron down icon Chevron up icon
Other Books You May Enjoy Chevron down icon Chevron up icon

Customer reviews

Rating distribution
Full star icon Full star icon Full star icon Half star icon Empty star icon 3.4
(5 Ratings)
5 star 40%
4 star 20%
3 star 0%
2 star 20%
1 star 20%
Kindleのお客様 Mar 15, 2023
Full star icon Full star icon Empty star icon Empty star icon Empty star icon 2
Not too happy with this one. The writing is poor and the examples are confusing. Sometimes the examples don't even use the feature they are supposed to be showing. The whole thing feels unorganized and just thrown together in random order without much thought. It also lacks the depth that I was hoping for.
Amazon Verified review Amazon
Chris Feb 18, 2023
Full star icon Full star icon Full star icon Full star icon Full star icon 5
I have been looking for a resource to streamline my learning of postgresql and also learn SQL at the same time on Ubuntu 20.04 which ships with Postgresql 12. I have been scanning through this book and it seems to have everything I wanted. Info on psql and intro SQL material. I do not see the major issues that the other reviewer saw. I think it is perfectly readable and wish I had not been discuoraged by those comments initially. It is also evident in the sample of the book that formatting is fine. Just take a look.
Amazon Verified review Amazon
fkasulke Mar 24, 2021
Full star icon Full star icon Full star icon Full star icon Full star icon 5
Ich liebe Fach-Bücher. Da kann man eine Randnotiz hinzufügen und auch ein paar Blätter einlegen, wenn im Inhalt mal was fehlt. Ich geniesse es, bequem im Sessel zu sitzen, anstatt verkrampft auf den Bildschirm zu starren.Nach 2 Tagen habe ich die Replikation und die Partitionierung mit Bedingungen eingerichtet.Inhalt ist gut strukturiert und ausreichend ausführlich, was bei "modernen Fach-Comics" anderer Verlage nicht immer der Fall ist.
Amazon Verified review Amazon
George Jempty Jan 16, 2021
Full star icon Empty star icon Empty star icon Empty star icon Empty star icon 1
Not the author's fault but anybody intending to buy the Kindle version needs to know this. When you get to the part on SQL commands like insert and select, the number of characters per line decrease and decrease until there's only one per line. Simply unacceptable.
Amazon Verified review Amazon
JOSE R. Dec 07, 2020
Full star icon Full star icon Full star icon Full star icon Empty star icon 4
El libro no entra en todas y cada una de las posibilidades de cada concepto y comando, enseña lo necesario para empezar de forma que no abrume, pero si cita donde se pueden ampliar los conocimientos.Me leí la documentación oficial, y para empezar y aprender es mejor un libro de estas características, luego cada uno es libre de profundizar mas en cada tema. Lo bueno es que como digo no abruma y da buenas explicaciones. Una vez acabe el libro, se consigue un nivel básico que ayuda a saber donde seguir para profundizar mas, de forma amena, pero sin escatimar en las explicaciones cuando hay que darlas.
Amazon Verified review Amazon
Get free access to Packt library with over 7500+ books and video courses for 7 days!
Start Free Trial

FAQs

What is included in a Packt subscription? Chevron down icon Chevron up icon

A subscription provides you with full access to view all Packt and licnesed content online, this includes exclusive access to Early Access titles. Depending on the tier chosen you can also earn credits and discounts to use for owning content

How can I cancel my subscription? Chevron down icon Chevron up icon

To cancel your subscription with us simply go to the account page - found in the top right of the page or at https://subscription.packtpub.com/my-account/subscription - From here you will see the ‘cancel subscription’ button in the grey box with your subscription information in.

What are credits? Chevron down icon Chevron up icon

Credits can be earned from reading 40 section of any title within the payment cycle - a month starting from the day of subscription payment. You also earn a Credit every month if you subscribe to our annual or 18 month plans. Credits can be used to buy books DRM free, the same way that you would pay for a book. Your credits can be found in the subscription homepage - subscription.packtpub.com - clicking on ‘the my’ library dropdown and selecting ‘credits’.

What happens if an Early Access Course is cancelled? Chevron down icon Chevron up icon

Projects are rarely cancelled, but sometimes it's unavoidable. If an Early Access course is cancelled or excessively delayed, you can exchange your purchase for another course. For further details, please contact us here.

Where can I send feedback about an Early Access title? Chevron down icon Chevron up icon

If you have any feedback about the product you're reading, or Early Access in general, then please fill out a contact form here and we'll make sure the feedback gets to the right team. 

Can I download the code files for Early Access titles? Chevron down icon Chevron up icon

We try to ensure that all books in Early Access have code available to use, download, and fork on GitHub. This helps us be more agile in the development of the book, and helps keep the often changing code base of new versions and new technologies as up to date as possible. Unfortunately, however, there will be rare cases when it is not possible for us to have downloadable code samples available until publication.

When we publish the book, the code files will also be available to download from the Packt website.

How accurate is the publication date? Chevron down icon Chevron up icon

The publication date is as accurate as we can be at any point in the project. Unfortunately, delays can happen. Often those delays are out of our control, such as changes to the technology code base or delays in the tech release. We do our best to give you an accurate estimate of the publication date at any given time, and as more chapters are delivered, the more accurate the delivery date will become.

How will I know when new chapters are ready? Chevron down icon Chevron up icon

We'll let you know every time there has been an update to a course that you've bought in Early Access. You'll get an email to let you know there has been a new chapter, or a change to a previous chapter. The new chapters are automatically added to your account, so you can also check back there any time you're ready and download or read them online.

I am a Packt subscriber, do I get Early Access? Chevron down icon Chevron up icon

Yes, all Early Access content is fully available through your subscription. You will need to have a paid for or active trial subscription in order to access all titles.

How is Early Access delivered? Chevron down icon Chevron up icon

Early Access is currently only available as a PDF or through our online reader. As we make changes or add new chapters, the files in your Packt account will be updated so you can download them again or view them online immediately.

How do I buy Early Access content? Chevron down icon Chevron up icon

Early Access is a way of us getting our content to you quicker, but the method of buying the Early Access course is still the same. Just find the course you want to buy, go through the check-out steps, and you’ll get a confirmation email from us with information and a link to the relevant Early Access courses.

What is Early Access? Chevron down icon Chevron up icon

Keeping up to date with the latest technology is difficult; new versions, new frameworks, new techniques. This feature gives you a head-start to our content, as it's being created. With Early Access you'll receive each chapter as it's written, and get regular updates throughout the product's development, as well as the final course as soon as it's ready.We created Early Access as a means of giving you the information you need, as soon as it's available. As we go through the process of developing a course, 99% of it can be ready but we can't publish until that last 1% falls in to place. Early Access helps to unlock the potential of our content early, to help you start your learning when you need it most. You not only get access to every chapter as it's delivered, edited, and updated, but you'll also get the finalized, DRM-free product to download in any format you want when it's published. As a member of Packt, you'll also be eligible for our exclusive offers, including a free course every day, and discounts on new and popular titles.