PostgreSQL

Articles

• How to use Analyse
• UUID or SERIAL for primary keys
  • Sequences are highly-optimised for concurrent access
  • If using UUID, there is an extension with a data type
• Zero downtime PostgreSQL migration done right
• Do you need redis?
• Direct write requests to single instance using HTTP requests. Globally distributed PostgreSQL
• database.dev - code snippets for use with supabase
• Lesser known PostgreSQL features

Tools

• Supabase’s PostgreSQL docker image with bundled extensions. Built using Ansible.
• sqlfluff - an SQL Linter which includes a postgres dialect
  • sqlfluff-github-actions has examples of how to use with GitHub Actions

Extensions

• pg_safeupdate prevents UPDATE or DELETE commands that do not specify conditions
• Plprofiler
• plpgsql_check a linter for plgpsql functions
• pgsql-http - a HTTP client for PostgreSQL
• pgcron - run periodic jobs in PostgreSQL
• pljava - stored proceedures, triggers and functions for PostgreSQL
  • pljava Hello World
  • Introduction to pljava - dated 2012, so I’m unsure how up-to-date it is
  • XSLT 2.0 - Only XPath 1.0 is supported by Postgres’ XML functions, a common use-case for pljava is to use Java XML tools
    • Saxon XSLT example - pljava example on how to use Saxon
    • XSLT 2.0 Processing in Java - example on how to re-implement the xslt function

Code snippets

• List the size of PostgreSQL databases

    select schemaname as table_schema,
           relname as table_name,
           pg_size_pretty(pg_relation_size(relid)) as data_size
    from pg_catalog.pg_statio_user_tables
    order by pg_relation_size(relid) desc;
  

• From StackOverflow - Postgres Manually Modify Sequence

  Re-build a SEQUENCE

  SELECT setval(pg_get_serial_sequence('table_name', 'id'), coalesce(max(id), 0)+1 , false) FROM table_name;
  

• Set the schema for the psql command using PGOPTIONS:

  PGOPTIONS="--search_path=${schema_name}" psql -U postgres -h localhost "$db_name" -f "$sql_file"
  

• pg-shortkey - a trigger that allows you to use YouTube-like short keys as a primary key