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

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 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 official documentation and conflict_action.

VladimirKrstulja/PortsTipsTricksAndGotchas/databases/postgresql (last edited 2017-01-05T13:14:34+0000 by VladimirKrstulja)