====== PostgreSQL ======

%%PostgreSQL%% is a sophisticated Object-Relational DBMS, supporting almost all SQL constructs, including subselects, transactions, and user-defined types and functions.

  * [[http://www.freshports.org/databases/postgresql95-server|PostgreSQL 9.5 Server]] at Freshports
  * [[http://www.freshports.org/databases/postgresql96-server|PostgreSQL 9.6 Server]] at Freshports

===== Important Information Before running initdb =====

If you're not using a **checksumming** filesystem like ZFS, or the database files will not reside on a **geli**'d filesystem with enabled checksums, you might wish to **enable** data checksumming. It can only be enabled during the **initdb** phase, by adding "-k" flag to the ''%%postgres_initdb_flags%%'' rcvar. Check the [[https://www.freebsd.org/cgi/man.cgi?query=initdb&manpath=FreeBSD+11.0-RELEASE+and+Ports|initdb(1)]] manpage for more info and make sure you understand the performance implications.

The advice to run with ECC memory with ZFS equally applies here, if you run with %%PostgreSQL%%'s own data checksumming. Namely, relying on the checksum to detect silent data corruption is inadequate without ECC RAM, because already corrupt data might be stored and checksummed by the db, and thus considered valid.

===== Run-time Tips and Tricks =====

%%PostgreSQL%% since version 9.5 supports a concept of "upsert", that is "update or insert" where a row is attempted to be updated by some key and in case of uniqe / constraint violation, it will be inserted instead. Please consult the [[https://www.postgresql.org/docs/9.5/static/sql-insert.html|official documentation]] and **conflict_action**.