Developing Modern Database Applications with PostgreSQL

Dr. Quan Ha Le, Marcelo Diaz

• Publisher: Packt Publishing
• Year: 2021
• ISBN:
  9781838641061
• Edition: 1
• Topic: Computer Science
• Subtopic: Databases
• Index: Computer Science

Get up to speed with core PostgreSQL tasks such as database administration, application development, database performance monitoring, and database testingKey Features• Build real-world enterprise database management systems using Postgres 12 features• Explore the development, administrative and security aspects of PostgreSQL 12• Implement best practices from industry experts to build powerful database applicationsBook DescriptionPostgreSQL is an open-source object-relational database management system (DBMS) that provides enterprise-level services, including high performance and scalability. This book is a collection of unique projects providing you with a wealth of information relating to administering, monitoring, and testing PostgreSQL. The focus of each project is on both the development and the administrative aspects of PostgreSQL.Starting by exploring development aspects such as database design and its implementation, you'll then cover PostgreSQL administration by understanding PostgreSQL architecture, PostgreSQL performance, and high-availability clusters. Various PostgreSQL projects are explained through current technologies such as DevOps and cloud platforms using programming languages like Python and Node.js. Later, you'll get to grips with the well-known database API tool, PostgREST, before learning how to use popular PostgreSQL database testing frameworks. The book is also packed with essential tips and tricks and common patterns for working seamlessly in a production environment. All the chapters will be explained with the help of a real-world case study on a small banking application for managing ATM locations in a city.By the end of this DBMS book, you'll be proficient in building reliable database solutions as per your organization's needs.What you will learn• Set up high availability PostgreSQL database clusters in the same containment, a cross-containment, and on the cloud• Monitor the performance of a PostgreSQL database• Create automated unit tests and implement test-driven development for a PostgreSQL database• Develop PostgreSQL apps on cloud platforms using DevOps with Python and Node.js• Write robust APIs for PostgreSQL databases using Python programming, Node.js, and PostgREST• Create a geospatial database using PostGIS and PostgreSQL• Implement automatic configuration by Ansible and Terraform for PostgresWho this book is forThis PostgreSQL book is for database developers, database administrators, data architects, or anyone who wants to build end-to-end database projects using Postgres. This book will also appeal to software engineers, IT technicians, computer science researchers, and university students who are interested in database development and administration. Some familiarity with PostgreSQL and Linux is required to grasp the concepts covered in the book effectively.

Section 1 - Introducing PostgreSQL Development and Administration

This section provides a brief introduction to the topics covered in the book, namely development and administration in PostgreSQL. This section contains the following chapter:

• Chapter 1, Introduction to PostgreSQL Development and Administration

Introduction to PostgreSQL Development and Administration

PostgreSQL is an object-relational database management system (ORDBMS) based on the INGRES (INteractive Graphics REtrieval System) package, which was developed at the University of California, Berkeley. The POSTGRES (Post Ingres) project started in 1985, and version 1 was released to a small number of external users in June of 1989. Now, with more than 20 years of development, PostgreSQL has become the most advanced open source database, available all over the world.

This chapter introduces the development of PostgreSQL and how PostgreSQL has become a popular Database as a Service (DBaaS) among the current clouds. We will present an overview of the numerous features of PostgreSQL in various development environments, such as NodeJS and Django. Moving forward, we will introduce you to the PostGIS extension, which is a PostgreSQL facility for a geospatial PostgreSQL database. We will also present the PostgREST standalone web server, which aims to do one thing well: add an HTTP interface to any PostgreSQL databases or RESTful APIs.

Then, in the second part of the chapter, we will learn about the administration of PostgreSQL. We will utilize DevOps through the setting up of PostgreSQL high availability (HA) clusters. We will also set up New Relic to monitor a PostgreSQL database, carry out performance tests on a PostgreSQL database with PGBench and JMeter, and use PostgreSQL testing frameworks.

In this chapter, we will cover the following main topics:

• An overview of PostgreSQL development
• An overview of PostgreSQL administration

An overview of PostgreSQL development

In 1994, Postgres95 was released to the world by Andrew Yu and Jolly Chen as an open source descendant of the original POSTGRES Berkeley code; they added a SQL language interpreter to POSTGRES. By 1996, the name "Postgres95" was changed to a new name – PostgreSQL Version 6.0 – combining the original POSTGRES version and recent versions with SQL capability. Recently, DB-Engines and the SD Times 2018 100 have featured PostgreSQL as the "DBMS of the Year 2017."

In October 2019, the first PostgreSQL 12.0 version was released by the PostgreSQL Global Development Group. Since then, PostgreSQL has been the most advanced open source database all over the world. Version 12 provides many important improvements, including the following:

• The increased performance of standard B-tree indexes and also the reduction of the index size for B-tree indexes.
• The ability to rebuild indexes concurrently so that a REINDEX operation will not block any index writes; the parallel indexes introduced from PostgreSQL 10 now get more benefits with the new REINDEX CONCURRENTLY statement.
• The efficient capabilities regarding partitioning performance so that developers can now process (that is, query or alter) thousands of partitions simultaneously without blocking, and they can use foreign keys to reference partitioned tables.
• The most common value statistics for the CREATE STATISTICS command, leading to improved query plans.
• The common table expressions using WITH queries can now be inlined for quicker queries.
• The INCLUDE clause for generalized search tree (GiST) indexes is an extensible data structure that allows you to develop indices over any kind of data.
• The reduction of write-ahead log (WAL) overheads generated from a GiST, GIN, or SP-GiST index.
• The checksum control ability via the pg checksums statement (which used to be pg_verify_checksums) so that developers can enable or disable an offline cluster without dumping and reloading data (note that online checksum enablement is still in progress and not yet available in PostgreSQL 12).

Since October 2019, the latest PostgreSQL version 12 has been released with more improvements to the performance of the INSERT and COPY statements for partitioned tables and the attachment of a new table partition without blocking queries. You can read more about the fixes to previous releases at https://www.postgresql.org/docs/12/release-12.html.

Finally, PostgreSQL 12 benefits all users with notable improvements to query performance over larger datasets and space utilization; PostgreSQL 12 has quickly received good reviews and evaluations across the database industry. Version 12 of PostgreSQL is obviously the preferred open source database for all developers.

PostgreSQL 12 is now developed on clouds or so-called cloud databases and DBaaS.

What is DBaaS?

DBaaS, which is sometimes referred to as a cloud database, provides many kinds of databases as a managed service. DBaaS works in the same way as Infrastructure as a Service (IaaS) or Platform as a Service (PaaS). IaaS provides infrastructure components and PaaS provides development platforms as managed services in the cloud. In fact, the offerings of IaaS and PaaS often include databases.

When customers demand a DBaaS offering in the cloud, they only pay for what they use on a monthly or annual basis. They do not have to pay for what they do not use. The cloud providers are responsible for managing database services such as maintenance, upgrades, or administration for their customers. At present, the DBaaS offerings include both relational database management systems (RDBMS) and NoSQL databases.

The primary advantage of the cloud service model is that customers do not have to install or maintain their software in the data center; it is well understood by every developer. However, there are also some disadvantages, such as a lack of control over network performance issues or the inability to compress data or other database maintenance tasks.

PostgreSQL can be delivered as DBaaS on many clouds, such as Amazon Web Services (AWS), Google Cloud SQL, Microsoft Azure, Heroku, and EnterpriseDB Cloud.

The development of PostgreSQL by various environments

Most applications, at some point, need to persist data. This can be through files, local storage, cloud services, or often databases. Relational database systems are usually a good default choice, particularly PostgreSQL, which is a very powerful open source SQL server.

Some companies have resources to organize their own dedicated database team. If you are lucky enough to work in such a company, they will probably craft all of their stored procedures inside their databases. So, you will only have to use the language of your choice – NodeJS or Python – to call these stored procedures. In practice, this might not occur very often, and the truth is that many developers, or even so-called full stack developers, know very little about SQL and will seek out various abstractions in their favorite package repository.

Features of PostgreSQL with NodeJS

As a newfangled piece of technology, NodeJS is a cutting-edge web server that generates vast attention, not only from start-ups but also from giant enterprises. NodeJS is considered an exceptional framework for the IT market because of the following factors:

• NodeJS is an open source cross-platform that is built on the V8 JavaScript runtime of Chrome.
• NodeJS is ideal for data-intensive, real-time applications, and it enables event-driven programming.
• NodeJS is also a super-fast JavaScript interpreter because it works on a non-blocking I/O model.
• Outperforming conventional web servers, NodeJS has been a viable option for Yahoo, eBay, and Walmart.
• NodeJS employs push technology.
• It is also a lightweight web server in terms of memory usage.
• NodeJS can be a good solution for data-dense, real-time web applications across multiple devices.

The following features of NodeJS are usually emphasized by developers:

• The super-fast web server: NodeJS operates at a very high speed by using a single-threaded event loop model to process all of its asynchronous I/O operations. Therefore, any major actions can be performed quickly with NodeJS, such as network connections, filesystems, and reading/writing to databases. NodeJS supports developers by allowing them to create quick and robust network applications and offers parallel connections to increase throughput.

• The JavaScript web server: The NodeJS suite is a JavaScript runtime environment, and developers can now write JavaScript not only in the browser but also on the server. When the browser code and the server code are created in a similar manner, it is convenient to transport data between the server and the client. NodeJS fills the gap between the frontend and backend skills, and full stack developers can use JavaScript for both. The fact that all NodeJS programs are made using JavaScript increases the effectiveness of the web development process.
• Real-time data streaming: NodeJS considers both HTTP requests and responses as data streams. Hence, when data comes in the form of streams, the overall processing time can be reduced because NodeJS can process files and upload files at the same time. Developers can choose NodeJS for real-time video or audio recording.
• Real-time web applications: Because NodeJS is very fast, it is obviously a winner for games and chat apps as well as any other multi-user real-time web apps. The synchronization process is fast and orderly due to the event-driven architecture of NodeJS, and the event loop of NodeJS through the web socket protocol handles the multi-user function.
• Node Package Manager (NPM): NodeJS is an open source suite with more than 60,000 modules in the NPM.
• A good solution for synchronization: NodeJS is very efficient in its ability to solve common network development problems because it manages asynchronous I/O very well with its single-threaded event loop. In order to handle many clients, all I/O tasks are...

Table of contents

• Title Page
• Copyright
• Contributors
• About Packt
• Preface
• Section 1 - Introducing PostgreSQL Development and Administration
• Introduction to PostgreSQL Development and Administration
• Section 2 - Development in PostgreSQL
• Setting Up a PostgreSQL RDS for ATMs
• Using PostgreSQL and Node.js for Banking Transactions
• Managing Bank ATM Locations Using PostgreSQL and Django
• Creating a Geospatial Database Using PostGIS and PostgreSQL
• Managing Banking Transactions using PostgREST
• Section 3 - Administration in PostgreSQL
• PostgreSQL with DevOps for Continuous Delivery
• PostgreSQL High Availability Clusters
• High-Performance Team Dashboards Using PostgreSQL and New Relic
• Testing the Performance of Our Banking App with PGBench and JMeter
• Test Frameworks for PostgreSQL
• APPENDIX - PostgreSQL among the Other Current Clouds
• Other Books You May Enjoy