Minimum viable product content plan

Querying:

• CTEs

  Path: /building/sql/cte-common-table-expressions-in-postgres.mdx

  Audience: new to SQL, new to Postgres

  Problem to be solved: queries are becoming unweildy

• Window functions

  Path: /building/sql/window-functions.mdx

  Audience: new to SQL, new to Postgres, data analysts

  Problem to be solved: draw conclusions from large sets of data without exporting to dedicated analysis system

  Source material:

  • https://katacoda.com/enterprisedb/scenarios/window-functions-1
  • https://katacoda.com/enterprisedb/scenarios/window-functions-2

• pl/pgsql

  Path: /building/procedural/pl-pgsql/

  Source material:

  • https://www.enterprisedb.com/postgres-tutorials/everything-you-need-know-about-postgres-stored-procedures-and-functions
  • https://www.enterprisedb.com/postgres-tutorials/stored-procedures-postgresql-how-create-stored-procedure-and-invoke-it
  • https://www.enterprisedb.com/postgres-tutorials/how-develop-user-defined-function-postgresql-stored-procedures
  • https://www.enterprisedb.com/postgres-tutorials/10-examples-postgresql-stored-procedures
  • https://www.enterprisedb.com/postgres-tutorials/how-declare-variables-plpgsql-stored-procedures
  • https://www.enterprisedb.com/postgres-tutorials/how-work-control-structures-postgresql-stored-procedures-using-if-case-and-loop
  • https://www.enterprisedb.com/postgres-tutorials/how-raise-errors-and-report-messages-within-stored-procedures-and-functions
  • https://www.enterprisedb.com/postgres-tutorials/how-easily-convert-oracle-procedures-postgresql

• pl/python

  Path: /building/procedural/pl-python/

• anonymous functions

  Path: /building/procedural/anonymous-functions-in-postgres.mdx

  Source material:

  • https://www.enterprisedb.com/postgres-tutorials/how-use-block-structure-write-anonymous-blocks-and-divide-larger-blocks-logical

Modelling:

• Views

  Path: /building/sql/views-in-postgres.mdx

  Source material:

  • https://www.enterprisedb.com/postgres-tutorials/how-create-modify-delete-and-manage-views-postgresql
  • https://www.enterprisedb.com/postgres-tutorials/how-create-and-refresh-data-materialized-views-postgresql
  • https://www.enterprisedb.com/postgres-tutorials/using-materialized-views-and-foreign-data-wrappers-together

• JSON/JSONB

• FTS

  Source material:

  • https://www.enterprisedb.com/postgres-tutorials/indexing-documents-full-text-search-postgresql
  • https://www.enterprisedb.com/postgres-tutorials/how-implement-faceted-search-django-and-postgresql

• FDWs

  Source material:

  • https://www.enterprisedb.com/postgres-tutorials/using-foreign-data-wrappers-access-remote-postgresql-and-oracle-databases
  • https://www.enterprisedb.com/postgres-tutorials/using-materialized-views-and-foreign-data-wrappers-together
  • https://www.enterprisedb.com/blog/how-plan-queries-involving-foreign-postgresql-tables

Securing:

• auth & ACL concepts (build guide on Dave’s blog posts)

  Source material:

  • https://www.enterprisedb.com/blog/how-to-secure-postgresql-security-hardening-best-practices-checklist-tips-encryption-authentication-vulnerabilities

• developing

  Source Material:

  • https://www.youtube.com/watch?v=2Nhbk1lFLy4
  • https://www.enterprisedb.com/postgres-tutorials/how-implement-column-and-row-level-security-postgresql
  • https://stackoverflow.com/questions/41537825/restricted-postgresql-permissions-for-web-app
  • https://www.2ndquadrant.com/en/blog/auditing-users-and-roles-in-postgresql/

Tuning:

• pg_stat_statements
• auto_explain

Deploying:

... TBD (Vibhor etc)

High Availability:

... TBD (Vibhor etc)