This is an object relational database management system that mainly emphasizes on extensibility and on standards-compliance. It being a database server its primary function is to store data securely supporting best practices, and to allow for retrieval at the request of other software applications. PostgreSQL boast of being able to handle workloads ranging from small single-machine application to large internet-facing applications with many concurrent users.
PostgreSQL implements majority of the SQL: 2011 standard that is its acid compliant and transactional hence avoiding locking of issues using multi-version concurrency control. It’s also able to provide immunity to dirty reads and full serializability. It handles complex SQL queries too using many indexing methods that are not available in other databases.
In addition to the possibility of working with the major proprietary and open sources databases, PostgreSQL supports migration from them using its extensive standard SQL support and available migration tools. Proprietary extensions in databases such as Oracle can be emulated by built-in and third-party open source compatibility extensions. The recent version also provides replication of the database itself for availability and scalability.
Control and Connectivity of PostgreSQL
Procedural languages allow developers to extend the database with custom functions (subroutines), often called stored procedures. These function can be used in the building of triggers (functions invoked upon modification of certain data) and custom aggregate functions. Procedural languages can also be invoked without defining a function, using the “DO” command at SQL level.
This languages are divided into two groups: “Safe” languages are sandboxed and can be safely used by any user. While procedures written in “unsafe” languages can only be created by super-users, mainly because they allow bypassing the database’s security restrictions, but can also access sources external to the database. Some languages such as Perl provide both safe and unsafe versions.
PostgreSQL has built-in support for three procedural languages:
- Plain SQL (safe). Simpler SQL functions can get expanded inline into the calling (SQL) query, which saves functions call overhead and allows the query optimizer to “see inside” the function.
- PL/pgSQL (safe), which resembles Oracle’s PL/SQL procedural language and SQL/PSM
- C (unsafe), which allows loading custom shared libraries into the database. Functions written in C offer the best performance, but bugs in code can crash and potentially corrupt the database. Most built-in functions are written in C
Foreign Data Wrappers
As for PostgreSQL version 9.1, it is able to link to other systems to retrieve data via foreign data wrappers (FDWs). These can take the form of any data source such as: a file system, another relational database management system (RDBMS), or a web service. This means regular database queries can use these data sources like regular tables and even join multiple data sources together.
PostgreSQL has several interface available and is also widely supported among programming language libraries. Built-in interfaces include LIBPQ (PostgreSQL’s official C application interface) and ECPG (an embedded C system). The external interfaces are such as LIBPQXX: C++ interface and PostgresDAC: PostgresDAC (for Embarcadero RadStudio/Delphi/CBuilder XE-XE3) among many others.
PostgreSQL is a cross-platform and runs on many operating systems including Linux, FreeBSD, Solaris and Microsoft Windows. Mac OS X, starting with OS X 10.7 Lion, has the server as its standard default database in the server edition and PostgreSQL client tools in the desktop edition. The vast majority of Linux distribution have it available in supplied packages.
- Dynamic Data Masking Management System (DBMS)
- Oracle SQL Injection Protection
- Database Design Software