I have used PostgreSQL sporadically over the years, but only for small projects with short lifespans. Another such project is being started and this time I thought I would gather some information together on PostgreSQL and post it on the blog for future reference.
General Info
PostgreSQL is an extremely popular open source object-relational database system and it claims to be “The world’s most advanced open source database.” It has been around for over three decades and is still in active development. In fact, with the emphasis on cloud migrations going on and the wide support of PostgreSQL on all the major cloud platforms, this might be the zenith of PostgreSQL usage. It is typically rated as being in the Top Five of most popular database management systems in use with the other four being Oracle, Microsoft SQL Server, and MySQL (SQLite is the most widely utilized embedded RDMS in the world.)
The latest version 13.1 was released on November 12, 2020. The database is available on multiple platforms, including Microsoft Windows (for Windows XP and later) with a separate FAQ available just for this platform.
How Do You Pronounce PostgreSQL?
I have learned today that I have been mis-pronouncing it for years. There is an audio file available that demonstrates the proper English pronunciation. (Postgres-Q-L)
Which Distribution To Use?
There are a few different variations available, some are open source, some are commercially licensed.
Download the latest version directly from the main open source project website (postgresql.org.) You can select from Linux, macOS, Windows, Solaris, BSD. For Microsoft Windows you can select from an interactive installer created by EDB, or simply download the zip archive of the binaries from the EDB website.
EDB offers a slightly customized installer for the open source version on their download page. They also offer an EDB Postgres Advanced Server, various support options, and a variety of tools for managing PostgreSQL.
2ndQuadrant offers a certified installer for the open source version, and they also offer commercially supported versions with advanced features. The latest digitally signed installer for the open source version is available here. This installer supports fully interactive, command-line, or unattended mode operations.
Crunchy Data offers various enhanced commercial versions and support.
Cybertec offers the typical support services (administration, consulting design, development, etc) as well as their own Enterprise Edition along with tools and add-ons for PostgreSQL.
Fujitsu Enterprise Postgres is another commercial distribution with advanced features supported by a large company. Note that as of August 2020, Amit Kapila (one of the major contributors to PostgreSQL) has joined Fujitsu, leading their PostgreSQL team as Senior Director. See this blog post for details.
HighGo offers their own enhanced PostgreSQL Server with Parallel Backup, Shard Management, and enhancements to Partitioning.
PostgresPro is another commercial distribution to possibly consider with Standard, Enterprise and Cloud based options all containing advanced features. This is a smaller Russian company with some PostgreSQL contributors that have developed custom extensions and features. Also see their GitHub page for more info.
Many other extensions are available, including those from PostGIS and Citus.
Open Source License
The open source version is licensed under a unique PostgreSQL License which is similar to the commonly utilized MIT license. Simply put: do what you want and realize that there is no warranty.
Commercial Support
If you are leveraging the open source version, you may still want to get support. Other than the various commercial options listed above, likely the most popular dedicated support option is from Command Prompt, Inc. They have been commercially supporting PostgreSQL longer than anyone, dating back to 1997.
Another popular support option is PGExperts, a North American company dedicated to PostgreSQL support.
A company dedicated to PostgreSQL support is Data Egret who would like to be your Remote PostgreSQL DBA Team. They offer 24/7 support, consulting, and training services.
PostgreSQL Connectivity Options For Delphi
There are a number of options to connect to PostgreSQL from Delphi.
FireDAC comes with the Enterprise Edition of Delphi but relies on external DLLs
Devart offers a PostgreSQL DAC with either direct mode support or can rely on external DLLs with a Standard license costing $200/year. They also offer UniDAC to connect to most popular databases with a Standard license at $300/year.
Microolap offers their PostgresDAC for connectivity. A personal license costs $135, Business license (internal use) cost $300 and a Commercial license (distribution rights) for $400 per developer. (Windows deployment includes: libpq.dll, libintl-8.dll, ssleay32.dll, libeay32.dll)
ZeosLib is a set of open source database components for Delphi to access various databases including PostgreSQL, MySQL, MariaDB, Interbase, Firebird, MS SQL Server and others. (Requires libpq.dll and libintl.dll)
The open source Synopse mORMot Framework offers various types of connectivity. (OleDB, ODBC, and ZeosLib)
You can also utilize ODBC connectivity via dbExpress and ADO/dbGO. (DevArt also offers dbExpress components.) Refer to the official psqlODBC driver.
More Information
A highly used blog aggregator is available on planet.postgresql.org with plenty of articles to read.
Fujitsu recently started an PostgreSQL Insider section dedicated to enthusiasts.
A recent blog post from November 23, 2020 from Amit Kapila covers some future versions of PostgreSQL 14 and beyond.
There are currently 9,600 users on the Postgres slack channel.
There are currently 10,400 members of the PostgreSQL Server group on Facebook.
There are currently 13,200 members of the PostgreSQL Professionals group on LinkedIn.
The official Twitter account, postgresql currently has 21,100 followers.
Blog entries from members of the PostgreSQL community are on the planetpostgres Twitter account currently with 23,200 followers.
Note on EDB and 2ndQuadrant
Two of the largest companies supporting PostgreSQL have recently merged with the purchase of 2ndQuadrant by EDB. See the acquisition announcement from September 30, 2020 on EDB’s website. Their announcement includes the typical positive flare such as “The acquisition will integrate two companies who are major contributors to PostgreSQL. The new unified roster of experts brings a long history of building and optimizing PostgreSQL for the most demanding global organizations.”
I may do follow-up posts on installing and utilizing PostgreSQL with Delphi on the Windows platform. If I missed something major in this post, please let me know!