PostgreSQL

PostgreSQL is an object-relational database. Additionally, it is distributed using the PostgreSQL license, which is very similar to MIT or BSD. It extends SQL syntax with additional functionality and is very often used in production solutions where a MySQL-type database can't handle it. It has many data types built in, such as JSONB for example, when we need to store larger unstructured data formations in it without the need for a NoSQL solution. The most common reason for choosing just PostgreSQL is precisely the support for multiple data types and full support for ACID transactions. Compared to MySQL, it handles complex queries very well, while it has similar performance for simple queries. PostgreSQL has a built-in replication mechanism that can be used right away. Besides, it gives the possibility to build indexes based on B-tree structure or hash table.

PostgreSQL has a broad community to support it, and it has frequent updates. It is the most widely used database for web servers, financial applications requiring full ACID support, and all solutions where long uninterrupted performance and reliability are required.

PostgreSQL database security

Data security depends on how we configure our server on which it will be stored. It is worth keeping in mind that a firewall should be set up on the server, and communication with the database should take place via TLS. In our solutions based on AWS architecture, we make sure that the PostgreSQL server is in a separate private network without access to it directly from the Internet, but only from a private network with Internet access on which the backend is set up. Also, we make sure that backups are automated and performed once a day. Taking care of data security, it's worth tempting to go for a master-slave solution, where the second server would either operate in read-only mode, or be on standby to run if the master version lost availability. It's worth remembering not to store keys such as passwords in the Github code repository, but only in environment variables stored in encrypted form. It's worth remembering to hash key information stored in the database, such as passwords, so that even in the event of a data leak they would not be possible to guess.

Are you looking for a contractor working with PostgreSQL ?

Check case studies