5 amazing features of PostgreSQL
PostgreSQL has become the best alternative when it comes to open source relational databases. This is because it has a very comprehensive set of features that give you great flexibility and allow us to “tune” our model of data to our needs. In this article, I talk about some of the interesting features of PostgreSQL:
1. Hot Standby
This feature allows customers to connect to the server and run searches in the database while it is in recovery or standby mode.
Also allows you to switch from this mode to normal mode without stopping the flow of users searches and maintaining open connections. It is important to mention that this whole process occurs while the database is in read-only mode.
2. JSON (B)
The weakness of relational databases is that they are not efficient in the management of objects in JSON format. However, PostgreSQL has excellent features that can index elements and make advanced searches in this format.
It is best to use it to save the information to a single element and index any of its properties.
3. Real-time Notifications
PostgreSQL is not designed to be a database that works 100% real time. But it offers the functions LISTEN, UNLISTEN and NOTIFY that facilitates the sending of asynchronous notifications to all the processes connected to the database.
This is very useful when you have, for example, a server for clients connected by the web and one for customers connected by phone. In this way, it would be possible to send notifications when specific changes in the database. In addition, we would have the option to include the details of the change in the form of text (and customers can apply them to their local copy) or simply use it to inform clients that they must re-download some element of the database.
Thanks to these functions we can create a system of data that is kept synchronized on all our clients asynchronously and gives us greater control over the process.
4. Point In Time Recovery (PITR)
A very interesting feature of PostgreSQL is that it records every transaction in the write-ahead-log ( WAL ). This allows us to restore our database to any point in time, replicating the list of updates from the nearest checkpoint.
This is particularly useful when you are working with very large databases where it is not practical to do full backups frequently.
5. Streaming Replication
PostgreSQL offers several services that are useful to scale our database. For example, Streaming replication constantly sends the status of the WAL to our backup servers. These applied changes to maintain updated copies that are stored and are in charge of the Hot Standby functionality.
There are many more useful functions of PostgreSQL. I hope you guys use it in your database…