Using and extending the SQL language, PostgreSQL is an open-source database system that is powerful, using an object-relational model. It can deal with even the most demanding and complex workloads, safely storing data while offering excellent scalability.
Developed in 1986 as part of the University of California at Berkeley’s POSTGRES project, PostgreSQL has been seeing ongoing development over the decades – with more than 30 years of non-stop growth and development as the core is continuously expanded.
Thanks to a highly dedicated open source community PostgreSQL has an enviable position as one of the most proven and most reliable database systems. It has a reputation for maintaining data integrity and developers like the fact that it is extensible – while offering robust functionality out of the box.
You can run PostgreSQL on most of the world’s important operating systems, and PostgreSQL also meet ACID requirements – it’s done so for nearly twenty years. There are countless useful addons available for PostgreSQL – including POSTGIS; an extension that enables you to add geospatial data to your PostgreSQL database.
It’s no wonder therefore that it is now one of the most popular relational databases in the open source community – the first choice for countless organisations, businesses and individuals. It’s also easy to get up and running with; just choose the app you want to make and trust PostgreSQL to keep your data safe in a robust open source database.
Why PostgreSQL is a top choice
If you’re looking for a feature rich open source database PostgreSQL should be at the top of your list. It offers an array of features for developers who build cutting-edge apps, but it is also easy to administer: PostgreSQL ensures your data integrity never suffers thanks to the fact that it is highly resilient against faults. It doesn’t matter if you are working with a big or small project, PostgreSQL will help you keep your data in great shape.
It is open source, so it’s free to use – and you always benefit from the fact that it is extremely extensible. You can create your own data types or even incorporate code from a range of languages – and you don’t need to recompile the entire database when you do that.
While PostgreSQL extends SQL as everyone know it, it does conform to SQL wherever it can, only making exceptions where sticking to the SQL standard would imply poor choice from an architecture perspective. The SQL standard requires a range of specific features and it provides most of these, though sometimes the syntax can be slightly different. However, the team tries to move PostgreSQL closer and closer to SQL conformance over time.
Indeed, version 12 of PostgreSQL which was released at the end of 2019 is now so close to SQL Core that it only differs in 19 of the 179 features mandated by the SQL standard – it conforms with 160. Note that PostgreSQL is not unique in this respect – no other relational database fully conforms with the entire SQL:2016 Core standard.
Key PostgreSQL features
PostgreSQL is fully featured – even though it’s free and open source, developers can rely on it for even the most complex of apps. Let’s take a look.
Data types
Users can take advantage of all the essential data types – including primitives and structured types including not only arrays but also UUID. It also caters for document types, think XML as well as JSON and Hstore (key-value). Developers can also use geometry types, and if that’s not enough it offers developers the opportunity to define their own custom types – or to create a composite data type.
Data integrity features
Developers can take advantage of both primary keys and secondary keys. The platform also imposes specific constraints, including “UNIQUE, NOT NULL” as well as exclusion constraints – all in order to maintain data integrity. It also offers the use of both explicit and advisory locks.
Performance and concurrency
PostgreSQL takes a number of steps to ensure high performance – including through concurrency. Developers can use essential indexing features including B-tree and Expressions, while advanced indexing include everything from GIST through to covering indexes and BRIN.
The query planner is highly sophisticated and you can also optimise queries easily. You get multicolumn statistics, and it’s possible to perform index-only scanning. Both transactions and nested transactions are supported, the latter only if you make use of savepoints. MVCC – multi version concurrency control – is supported.
Developers can harness parallelized read queries, table partitioning as well as B-tree indexes. As we said earlier, PostgreSQL focuses on fully supporting SQL standards, so it supports all the SQL-provisioned transaction isolation levels, including the popular serializable isolation. Performance is also aided by JIT compilation of SQL expressions.
Disaster recovery and reliability
Thanks to a range of features developers can rest assured that databases are reliable, and that data is recoverable if disaster strikes. PostgreSQL supports WAL (write-ahead logging) and a full range of replication options, including logical and both synchronous and asynchronous replication. Active standby as well as point in time recovery (PITR) is also supported. The ability to use tablespaces also ensure that PostgreSQL is highly reliable.
PostgreSQL security features
Security is front and centre for most developers and PostgreSQL does not disappoint. That’s why the database supports the gamut of authentication options – ranging from SCRAM-SHA-256 through to GSSAPI and of course LDAP, amongst others. It offers a very reliable access control regime alongside security that goes down to the column and row levels. MFA is also supported alongside an extra method and support for the use of certificates.
Comprehensively extensible
We mentioned earlier that PostgreSQL is extensible. There are almost countless options for developers – ranging from the simple (stored procedures and functions) to the ability to fully integrate code from procedural languages such as Python or Per. PGSQL is also supported, alongside path expressions courtesy of JSON and SQL.
Developers can use data wrappers that are foreign, using standard SQL to connect to another database or a stream. The table storage interface is fully customizable. Developers can also make use of extension packages that add a huge swathe of new functionality, PostGIS is a prime example.
Localization
PostgreSQL supports international character sets (including through ICU) while collations are available, both accent and case sensitive. Database content can also be search via a full-text search.
PostgreSQL is extensible and scalable
It’s worth looking through the full documentation for PostgreSQL to get a grip on all the features that this powerful database platform offers. Don’t forget, you can easily extend your database – there are even defined APIs which can help developers add to the database system in order to meet even the most difficult of challenges.
Scalability is another big benefit of PostgreSQL, not only because it can manage large amounts of data but also because it offers stellar concurrency – the ability to handle masses of user requests simultaneously. In fact, some live clusters are handing terabytes of data in a production environment, with some highly tuned environments handling petabytes of data.
with