Resources

PgDCP Engineering Resources

Platform and site reliability engineers should review:

• psql command line tutorial and cheat sheet
• Postgres features showcase (commented SQL samples)
• postgres_dba set of useful tools for Postgres DBAs and all engineers
• Set of Practices for common PG engineering suggestions
• pgcenter CLI tool for observing and troubleshooting Postgres
• PGXN client CLI tool to interact with the PostgreSQL Extension Network
• Useful views and functions for postgreSQL DBA’s
• Postgres extension to introspect postgres log files from within the database
• Postgres clone schema utility without need of going outside of database. Makes developers life easy by running single function to clone schema with all objects. It is very handy on Postgres RDS.
• An unassuming proposal for PLSQL Continuous Integration using revision control, Jenkins and Maven.
• Securing Your PostgreSQL Database
• Advanced multi-threaded PostgreSQL connection pooler and request router
• Postgres Container Apps: Easy Deploy Postgres Apps

Engineers writing stored routines (functions, SPs) should review:

• Boost your User-Defined Functions in PostgreSQL describes some useful techniques for improving UDFs.
• Building a recommendation engine inside Postgres with Python and Pandas
• postgresql-plpython-webservice shows how to caching web service requests via PL/Python
• Metagration, a PostgreSQL migration tool written in PostgreSQL
• BedquiltDB is a JSON document-store built on PostgreSQL using PL/Python

Engineers writing applications should consider these PostgreSQL-native libraries:

• makeExtendSchemaPlugin merges additional GraphQL types and resolvers into a Postgraphile PostgreSQL schema using a similar syntax to graphql-tools.
• uuid-ossp for UUIDs as primary keys
• Universally Unique Lexicographically Sortable Identifier (ULID)
• ltree for representing labels of data stored in a hierarchical tree-like structure
• pg_trgm module provides functions and operators for determining the similarity of alphanumeric text based on trigram matching
• simplified auditing based on SQL logging and FDWs to import logs instead of writing triggers
  • Audit Trigger 91plus generic trigger function used for recording changes to tables into an audit log table
  • pgMemento provides an audit trail for your data inside a PostgreSQL database using triggers and server-side functions written in PL/pgSQL
  • Temporal Tables PostgreSQL Extension
• pg_cron to run periodic jobs in PostgreSQL
• shortkey for YouTube-like Short IDs as Postgres Primary Keys
• dexter automatic indexer
• message-db message and event store
• RecDB Recommendation Engine
• pg_similarity extension to support similarity queries on PostgreSQL
• dox Document Database API extension when needing simple JSON store
• colpivot.sql dynamic row to column pivotation/transpose
• Guidance to implement NIST level 2 RBAC Hierarchical RBAC in PostgreSQL
• ldap2pg to synchronize Postgres roles and privileges from YAML or LDAP
• SeeQR database analytic tool to compare the efficiency of different schemas and queries on a granular level
• postgres-basename-dirname contains functions which provide equivalents to the POSIX functions basename and dirname
• postgresql-similarity extension package which provides functions that calculate similarity between two strings
• pg_median_utils functions like median_filter which behaves the same as SciPy’s medfilt
• orafce ORACLE compatibility functions
• postgres-typescript generates typescript functions from SQL files and lets you call these functions from your app
• SPARQL compiler functions for PostgreSQL
• PL/pgSQL implementation of hashids library, another alternative: plpg_hashids
• Helpers for PL/pgSQL applications
• session_variable Postgres database extension provides a way to create and maintain session scoped variables and constants, more or less like Oracle’s global variables
• Git Based Application Configuration, a Postgres extension that allows you to store and manipulate data in Git repositories stored in tables within the database. This is designed to be used for storage of configuration data for applications and services.
• PostgreSQL extension with support for version string comparison

Engineers writing SQL-first code should use the following tools:

• sqlcheck and plpgsql_check for linting SQL source code
• pgTAP - Database testing framework for Postgres
• pgcmp for comparing Postgres database schemas
• Web-based Explain Visualizer (pev) and CLI query visualizer (gocmdpev) for performance optimization
• JSON Schema validation for PostgreSQL when using JSON and JSONB columns
• Use readable database errors as a guide for creating errors in the database which can be used in the front-end
• postgresqltuner script to analyse PostgreSQL database configuration, and give tuning advice
• Use HyperLogLog data structures and TopN PostgreSQL extension for higher performing value counting when data amounts get large
• See GraphQL for Postgres which teaches techniques for how to parse GraphQL queries and transform them into SQL, all inside PostgreSQL (this is not production-level code but is good for education)
• Plugin for prettier to support formatting of PostgreSQL-flavour SQL, including function bodies in SQL, pgSQL, PLV8, plpython, etc.

Engineers needing to instrument PostgreSQL:

• Deno Postgres SQL parser
• pg_query 2.0: The easiest way to parse Postgres queries

Machine Learning without leaving PostgreSQL:

• Apache MADlib
• mindsdb.com for machine Learning without leaving the database

Content engineers who need datasets:

• pgloader loads data from various sources into PostgreSQL
• ISO-3166 - All countries and subcountries in the world

Precision Knowledge:

• Lesser Known PostgreSQL Features
• Evolutionary Database Design
• Intuit’s Data Mesh Strategy
• Web development platform built entirely in PostgreSQL
• Postgres Analytics - Tips, best practices & extensions
• OWASP API Security Project
• Beyond REST is Netflix’s approach to Rapid Development with GraphQL Microservices
• 6 Common Mistakes for SQL Queries that “Should be Working”
• Postgres Notify for Real Time Dashboards
• Postgres regex search over 10,000 GitHub repositories (using only a Macbook)
• How to Modernize Your Data & Analytics Platform parts one, two, three, and four.