PostgreSQL has come to the lead of the relational database market over the last ten years. Features such as the geospatial capabilities and the JSON support has made the Database Management System very popular between developers and database administrators. In this article, we will cover five cool Postgres features that often get overlooked.
What Is Postgres?
PostgreSQL is a free, open-source object-oriented relational database management system. The database supports both SQL for relational and JSON for non-relational queries. The system emphasizes extensibility and compliance with SQL standards, while designed to handle a wide range of workloads. It is used in single machines as well as in data warehouses or Web services.
Postgres key features include:
- Compatible with major platforms—such as Windows, Linux, Mac OS, Solaris.
- Supports most common programming languages—such as C/C++, Phyton, Go and Java
- Supports Multiversion Concurrency Control—which allows several users to access and work in the same database at the same time.
- Supports JSON— which allows linking with other NoSQL data stores.
- Object oriented—ACID and SQL standard compatible
- A large community of users—develop and release extensions regularly.
PostgreSQL ranks as the best Database Management System for the last two years according to DB-Engines. It is very popular with developers and database administrators, mainly because of the following three reasons:
- Free license—Postgres is free to use, forever, you can store unlimited data, without limits in functionality you can even distribute it.
- No vendor lock-in—since their license is so liberal, there are many companies providing services for Postgres. That eliminates the risk of vendor lock-in.
- It is extendable—extensions are a popular way to add features to Postgres. Users can write extensions to add features they need in the database.
This last feature is the reason Postgres is not simply a relational database anymore, but a data platform. Postgres extensions work like lower level APIs allowing to change or extending the database functionality. Thus, users can adapt Postgres for new use cases without requiring changing the core database This drives the following benefits:
- Allows the Postgres core to evolve at a stable pace, ensuring a solid foundation.
- On the other hand, you can use the extensions to apply the database to new areas.
What You Didn’t Know
Some developers call Postgres the coolest database management system, and with reason. It offers developers the flexibility to tweak and work with the system to adapt it to their needs. PostgreSQL aims to help developers build applications, and administrators to protect data integrity managing it efficiently no matter the size of the dataset. Some often overlooked, but very useful PostgreSQL features you may not know include:
#1. Create your own data types
Postgres feature GiST, Generalized Search Tree indexing. This feature allows you to create custom data types deciding what to store and how to store it. One of the most popular projects made based on this feature is PostGIS, adding spatial support by providing data types used by Geographic Information Systems (GIS).
#2. You can win disaster recovery with streaming replication
Postgres includes a solid replication system built in. The system can be set up in many configurations to help with Disaster Recovery. This streaming replication can continually update standby servers, meaning they are ready for recovery when needed. This feature aims to provide a backup site that can be promoted to master if the main database is down due to a disaster.
Thanks to the flexibility of Postgres license, there are several solutions that help users to manage Postgres backup and disaster recovery.
#3. You can query XML data
Many companies work with Extensible Markup Language (XML) data nowadays, as it allows to electronically share structured data via the Internet. You can use Postgres to query XML data stored in the database directly. For example, you can store in PostgreSQL an XML file created in other application, then you can query just the element you need from the file.
#4. You don’t need to worry about the type of authentication
PostgreSQL supports twelve authentication systems, which is useful whether you are using PostgreSQL in a Windows environment with Active Directory, for example. In addition, Postgres supports, GSSAPI, password-based authentication, RADIUS, and even trust-based authentication.
$5. You can manage large datasets with sharding
Postgres horizontal partitioning feature, allows you to break a single database into smaller, manageable chunks. You can then distribute those chunks across multiple servers, to spread the load and keep a high throughput.
There are two types of partitioning, range partitioning, and list partitioning. In the first case, you can partition a table into ranges defined by a key column such as date or another identifier. In the second case, you can list explicitly which key values you want to appear in each partition. One of the added benefits of sharding is that it minimizes response times for database queries by distributing the data in smaller, manageable portions.
Supported by its large and involved community, Postgres continues to get better with each new release. The consistently improved performance has resulted in more than a data management system, but a data platform.
These tools and tips we covered represent just the tip of the iceberg of what you can do with PostgreSQL features and extensions, you can create your own data types, manage your data size, and if you need a new feature, write an extension. With PostgreSQL, the sky is the limit.