As a developer, Database Administrator or Systems Engineer, having a background in databases is paramount in your career development. In that regard, PostgreSQL is one fantastic database that every aspiring person in the mentioned fields should consider taking a deep dive in. Let us clear the thicket about PostgreSQL before we take a yacht into its deeper waters.
PostgreSQL is a powerful, open source object-relational database system that uses and extends the SQL language combined with many features that safely store and scale the most complicated data workloads. Its origins date back to 1986 as part of the POSTGRES project at the University of California at Berkeley and has more than 30 years of active development on the core platform. Source: PostgreSQL Page
Features of PostgreSQL
These are some of the features that PostgreSQL users enjoy:
- Support for many Data Types such as
Primitives: Integer, Numeric, String, Boolean
Structured: Date/Time, Array, Range, UUID
Document: JSON/JSONB, XML, Key-value (Hstore)
Geometry: Point, Line, Circle, Polygon
Customizations: Composite, Custom Types - On matters Concurrency and Performance
Indexing: B-tree, Multicolumn, Expressions, Partial
Advanced Indexing: GiST, SP-Gist, KNN Gist, GIN, BRIN, Covering indexes, Bloom filters
Sophisticated query planner / optimizer, index-only scans, multicolumn statistics
Transactions, Nested Transactions (via savepoints)
Multi-Version concurrency Control (MVCC)
Parallelization of read queries and building B-tree indexes
Table partitioning
All transaction isolation levels defined in the SQL standard, including Serializable
Just-in-time (JIT) compilation of expressions - Reliability, Disaster Recovery
Write-ahead Logging (WAL)
Replication: Asynchronous, Synchronous, Logical
Point-in-time-recovery (PITR), active standbys
Tablespaces - Security
Authentication: GSSAPI, SSPI, LDAP, SCRAM-SHA-256, Certificate, and more
Robust access-control system
Column and row-level security
Multi-factor authentication with certificates and an additional method - Extensibility
Stored functions and procedures
Procedural Languages: PL/PGSQL, Perl, Python (and many more)
SQL/JSON path expressions
Foreign data wrappers: connect to other databases or streams with a standard SQL interface
Customizable storage interface for tables
Many extensions that provide additional functionality, including PostGIS - And much much more
With that not so sufficient acquaintance, we can now comfortably move to the main course meal where we will be serving up the hottest books that will satiate your PostgreSQL database learning appetite.
1. PostgreSQL: Up and Running
Crated by a team of experienced authors, this book holds a lot of information that will help you migrate to PostgreSQL as fast as possible. One of the authors, Regina Obe is a co-principal of Paragon Corporation, a database consulting company based in Boston. She has over 15 years of professional experience in various programming languages and database systems, with special focus on spatial databases. Her co-author, Leo Hsu, is a co-principal of Paragon Corporation, a database consulting company based in Boston. He has over 15 years of professional experience developing and thinking about databases for organizations large and small.
With examples throughout, this book shows you how to achieve tasks that are difficult or impossible in other databases. This third edition covers new features, such as ANSI-SQL constructs found only in proprietary databases until now: foreign data wrapper (FDW) enhancements; new full text functions and operator syntax introduced in version 9.6; XML constructs new in version 10; query parallelization features introduced in 9.6 and enhanced in 10; built-in logical replication introduced in Version 10.e.
If you are a current PostgreSQL user, you will pick up gems you may have missed before. You will get to:
- Learn basic administration tasks such as role management, database creation, backup, and restore
- Apply the psql command-line utility and the pgAdmin graphical administration tool
- Explore PostgreSQL tables, constraints, and indexes
- Learn powerful SQL constructs not generally found in other databases
- Use several different languages to write database functions
- Tune your queries to run as fast as your hardware will allow
- Query external and variegated data sources with foreign data wrappers
- Learn how to use built-in replication to replicate data
For the readers who have prior experience with some other database, they can jump right to the key points of PostgreSQL and get to see the benefits they will accrue. Click on the following link to get a copy of this amazing resource.
2. Mastering PostgreSQL 15
Author Hans-Jürgen Schönig has been in the industry for 18 years. This wealth of experience and time spent with PostgreSQL databases is enough to tell you that what is inside this book is a gem.
Starting with an introduction to the new features in PostgreSQL 15, this book will guide you in building efficient and fault-tolerant PostgreSQL apps. You’ll explore advanced PostgreSQL features, such as logical replication, database clusters, performance tuning, advanced indexing, monitoring, and user management, to manage and maintain your database. You’ll then work with the PostgreSQL optimizer, configure PostgreSQL for high speed, and move from Oracle to PostgreSQL. The book also covers transactions, locking, and indexes, and shows you how to improve performance with query optimization. You’ll also focus on how to manage network security and work with backups and replication while exploring useful PostgreSQL extensions that optimize the performance of large databases.
By the end of this PostgreSQL book, you will be able to get the most out of your database by executing advanced administrative tasks.
What you will learn
- Get well versed with advanced SQL functions in PostgreSQL 15
- Get to grips with administrative tasks such as log file management and monitoring
- Work with stored procedures and manage backup and recovery
- Employ replication and failover techniques to reduce data loss
- Perform database migration from Oracle to PostgreSQL with ease
- Replicate PostgreSQL database systems to create backups and scale your database
- Manage and improve server security to protect your data
- Troubleshoot your PostgreSQL instance to find solutions to common and not-so-common problems
- Work with stored procedures and manage backup and recovery
If you are a PostgreSQL developer, database administrator or professional who want to implement advanced functionalities and master complex administrative tasks with PostgreSQL 15, then it is high time you pick up this text and consume it. Any prior experience with database administration will really help in accelerating your learning. With 18 years experience with PostgreSQL, the author will take you on a solid learning journey and you will come out as a master in PostgreSQL. Get your copy delivered from Amazon via the following link.
3. Essential Postgres
Targeting those who are new and would wish to learn PostgreSQL, author Rick Silva, a software developer with decades of database experience wrote this book to be their teacher and companion. This book gets right to the point, and gives you exposure to the most used – and useful – parts of database development using Postgres version 13. Writing SQL statements, creating tables, functions, triggers, views, as well as data integrity are explained.
Spread over 195 pages, this resource is simple and straight to the point. And guess what, if you want to learn the basics of Postgres development, if you are a Junior or Intermediate Developer, Essential Postgres by Rick Silva is your teacher and friend.
This book will help you to:
- Learn to write SQL queries
- Learn to create, drop, and change database tables
- Explore Postgres data types for text, integers, dates, and booleans
- Join database tables using inner joins, outer joins, cross joins, and self-joins
- Explore ways to compare values in your SQL queries
- Find out how to order your query results and to group like data
- Call Postgres built-in functions and write your own
- Create views, materialized views, sequences, procedures, and triggers
The link below will lead you to Amazon where you will be able to get your copy delivered:
4. Learning SQL
Whether you will be using a relational database or not, if you are working in data science, business intelligence, software development or some other facet of data analysis, you will likely need to know SQL. PostgreSQL uses SQL and having a strong background in SQL will boost your PostgreSQL administration remarkably.
Author Alan Beaulieu helps developers get up to speed with SQL fundamentals for writing database applications, performing administrative tasks, and generating reports. You will find new chapters on SQL and big data, analytic functions, and working with very large databases.
Each chapter presents a self-contained lesson on a key SQL concept or technique using numerous illustrations and annotated examples. Exercises let you practice the skills you learn. Knowledge of SQL is a must for interacting with data. With Learning SQL, you’ll quickly discover how to put the power and flexibility of this language to work.
What you will encounter inside:
- Move quickly through SQL basics and several advanced features
- Use SQL data statements to generate, manipulate, and retrieve data
- Create database objects, such as tables, indexes, and constraints with SQL schema statements
- Learn how datasets interact with queries; understand the importance of subqueries
- Convert and manipulate data with SQL’s built-in functions and use conditional logic in data statements
For those who would wish to start with SQL before jumping into PostgreSQL, this is the kind of resource that will serve you well. The author has been designing, building, and implementing custom database applications for over 25 years and is the best teacher to get your SQL fixed. Pick on his brain by clicking on the link provided where you will be able to order your copy from Amazon.
5. PostgreSQL 12 High Availability Cookbook
Shaun Thomas, the author understands the importance of taking precautions before disasters happen especially whn it has to do with your precious data. He has been experimenting with PostgreSQL since late 2000 and serves as a database consultant, teacher, blogger, and support engineer with 2ndQuadrant.
In this updated edition, Shaun Thomas ensures that you will learn the important concepts related to node architecture design, as well as techniques such as using repmgr for failover automation. From cluster layout and hardware selection to software stacks and horizontal scalability, this PostgreSQL cookbook will help you build a PostgreSQL cluster that will survive crashes, resist data corruption, and grow smoothly with customer demand.
You will start by understanding how to plan a PostgreSQL database architecture that is resistant to outages and scalable, as it is the scaffolding on which everything rests. With the bedrock established, you will cover the topics that PostgreSQL database administrators need to know to manage a highly available cluster. This includes configuration, troubleshooting, monitoring and alerting, backups through proxies, failover automation, and other considerations that are essential for a healthy PostgreSQL cluster. Later, you’ll learn to use multi-master replication to maximize server availability. Later chapters will guide you through managing major version upgrades without downtime.
What you will learn
- Understand how to protect data with PostgreSQL replication tools
- Focus on hardware planning to ensure that your database runs efficiently
- Reduce database resource contention with connection pooling
- Monitor and visualize cluster activity with Nagios and the TIG (Telegraf, InfluxDB, Grafana) stack
- Construct a robust software stack that can detect and avert outages
- Use multi-master to achieve an enduring PostgreSQL cluster
By the end of this book, you will have learned how to build an efficient and adaptive PostgreSQL 12 database cluster which is tolerant and Highly Available. If you are a Postgres administrator and developers who is looking to build and maintain a highly reliable PostgreSQL cluster, there is no better place to look than all knowledge shared in this resource. You know what to do, head over to Amazon and order this copy now. Click below and you will be guided there.
6. PostgreSQL Query Optimization
Henrietta Dombrovskaya is a database researcher and developer with over 35 years of academic and industrial experience. She holds a PhD in computer science from the University of Saint Petersburg, Russia. At present, she is Associate Director of Databases at Braviant Holdings, Chicago, Illinois. She is an active member of the PostgreSQL community, a frequent speaker at the PostgreSQL conference, and a local organizer of the Chicago PostgreSQL User Group.
This book helps you write queries that perform fast and deliver results on time. You will learn that query optimization is not a dark art practiced by a small, secretive cabal of sorcerers. The book also covers advanced topics such as the use of functions and procedures, dynamic SQL, and generated queries. All of these techniques are then used together to produce performant applications, avoiding the pitfalls of object-relational mappers.
You will:
- Identify optimization goals in OLTP and OLAP systems
- Read and understand PostgreSQL execution plans
- Distinguish between short queries and long queries
- Choose the right optimization technique for each query type
- Identify indexes that will improve query performance
- Optimize full table scans
- Avoid the pitfalls of object-relational mapping systems
- Optimize the entire application rather than just database queries
7. PostgreSQL 14 Administration Cookbook
Starting with a summary of the authors, Simon Riggs is the CTO of 2ndQuadrant, having contributed to PostgreSQL as a major developer and committer for 14 years. Co-author Gianni Ciolli is the head of professional services at 2ndQuadrant and has been a PostgreSQL consultant, trainer, and speaker at many PostgreSQL conferences in Europe and abroad over the last 10 years. Co-author Sudheer Kumar Meesala is a lead architect at Endurance International Group and has spent the last few years designing and building scalable and secure web applications within finance and internet industries.
With many features in its arsenal, PostgreSQL 14 allows you to scale up your PostgreSQL infrastructure. This book takes a step-by-step, recipe-based approach to effective PostgreSQL administration.
Firstly, you will understand how to control crash recovery and plan backups by working through new features such as logical replication, native table partitioning, and additional query parallelism. You will also learn how to tackle a variety of problems and pain points that database administrators often face including creating tables, managing views, improving performance, and securing your database. As you make steady progress, the book will draw attention to important topics such as monitoring roles, backup, and recovery of your PostgreSQL 14 database. This will help you to gain insights into roles and produce a summary of log files, which will in turn ensure high availability, concurrency, and replication.
What you will learn
- Plan, manage, and maintain PostgreSQL databases in production
- Work with the newly introduced features of PostgreSQL 14
- Use pgAdmin or OmniDB to perform database administrator (DBA) tasks
- Use psql to write accurate and repeatable scripts
- Understand how to tackle real-world data issues with the help of examples
- Select and implement robust backup and recovery techniques in PostgreSQL 14
- Deploy best practices for planning and designing live databases
By the end of this book, you will have the skills you need to manage your PostgreSQL 14 database efficiently. It is an intermediate-level book ideal for database administrators, data architects, database developers, or anyone with an interest in planning and running live production databases using PostgreSQL 14. Spread over twelve chapters, there is a lot to learn and the concepts can be applied to other Postgres versions as well. Click below to get your copy delivered from Amazon.
8. Practical SQL
SQL (Structured Query Language) is the standard programming language for defining, organizing, and exploring data in relational databases. Author Anthony DeBarros is an award-winning journalist who’s combined avid interests in data analysis, coding, software product development, and storytelling for much of his career. This book by Anthony focuses on using SQL to find the story your data tells, with the popular open-source database PostgreSQL and the pgAdmin interface as its primary tools.
You will first cover the fundamentals of databases and the SQL language, then build skills by analyzing data from the U.S. Census and other federal and state government agencies. With exercises and real-world examples in each chapter, this book will teach even those who have never programmed before all the tools necessary to build powerful databases and access information quickly and efficiently.
You will learn how to:
- Create databases and related tables using your own data
- Define the right data types for your information
- Aggregate, sort, and filter data to find patterns
- Use basic math and advanced statistical functions
- Identify errors in data and clean them up
- Import and export data using delimited text files
- Write queries for geographic information systems (GIS)
- Create advanced queries and automate tasks
The readers will find that Practical SQL delivers clear examples with an easy-to-follow approach to teach you the tools you need to build and manage your own databases. Whether you have programmed before or not, this resource will expose you to SQL and Postgres in the process. Feel free to get your SQL fixed by getting this resource from Amazon below.
9. Learning SQL (3rd Edition): Generate, Manipulate, and Retrieve Data
With the latest edition of this introductory guide, author Alan Beaulieu helps developers get up to speed with SQL fundamentals for writing database applications, performing administrative tasks, and generating reports. You’ll find new chapters on SQL and big data, analytic functions, and working with very large databases.
Each chapter presents a self-contained lesson on a key SQL concept or technique using numerous examples. Exercises let you practice the skills you learn. Knowledge of SQL is a must for interacting with data. With Learning SQL, you’ll quickly discover how to put the power and flexibility of this language to work.
- Move quickly through SQL basics and several advanced features
- Use SQL data statements to generate, manipulate, and retrieve data
- Create database objects, such as tables, indexes, and constraints with SQL schema statements
- Learn how datasets interact with queries; understand the importance of subqueries
- Convert and manipulate data with SQL’s built-in functions and use conditional logic in data statements
Concluding Remarks
Postgres is Open Source, easy to install and amazing to learn. Thanks to its high performance, reliability, and robustness, PostgreSQL has become the most advanced database in tech. It has the capability of helping you build dynamic database solutions for enterprise applications that are highly available, robust and reliable. As a developer, database administrator or data analyst, having the skills needed to administer, configure and use PostgreSQL is a huge bonus. Begin today.
Other interesting books can be found under the following articles:
