Safely rerun SQL DDL against an existing schema, quickly see historical changes in SQL resources in Git, and treat SQL as a first-class language. The Opinionated TLDR The Opinionated Architect’s TLDR: All SQL DDL must be idempotent. Only use migrations tools that support or can generate idempotent SQL DDL. Checkout sql-watch, a command…

A story of confusing documentation, standards, and the misunderstanding of CORS. Introduction Understanding CORS (Cross-Origin Resource Sharing) might seem simple on the surface. …

How to build a reliable ELT solution for RESTful services, taking into account everything from RESTful API throttling to maintaining support for foreign key constraints. Introduction We’ve been working on a fully functional ELT solution on top of a data platform in PostgreSQL for the past few months. A 100% PostgreSQL-based…

Introducing a PostgreSQL library (lib) that boosts SQL code reuse, improves readability, and efficiently enforces data constraints for PostgreSQL domains. Introduction Introducing the first of our reusable SQL libraries for your projects — the PostgreSQL domains SQL library, now available in the lib repository managed by SQL Package Manager (sqlpm). In…

ChatGPT helps provide code snippets, but right now, it's much better at helping with documentation and explaining business domains. What is ChatGPT Let’s ask chatgpt: > What is ChatGPT? ChatGPT is a chatbot built using the GPT-3 language model developed by OpenAI. GPT-3 stands for “Generative Pretrained Transformer 3”, and it is a…

Write fully-tested and documented functions worth publishing in their own packages. The Opinionated TLDR The Opinionated Architect’s TLDR: To write a typescript function, start hacking and using the function where needed. Next, we are doing Behavior Driven Development, so comment out that code and any initial tests! Don't inject features into your code…

Gain insights into performance costs of generating primary key values for the following types: bigint (identity/bigserial), uuid v1, uuid v1, uuid v3, uuid v4, uuid v5, and timestamptz. The Opinionated TLDR The Opinionated Architect’s TLDR: See benchmark gist, primitive_benchmark_01.sql, and primitive_benchmark_02.sql for benchmarking code on your specific hardware. Don't optimize prematurely but do…

What does it take to measure the difference between two functions in PostgreSQL? Can we get accurate execution times within hundreds of nanoseconds? The Opinionated TLDR The Opinionated Architect’s TLDR for benchmarking specific PostgreSQL features*: Run benchmarking on a dedicated PostgreSQL server to minimize standard deviation. …

Lockdown historical timestamps to improve the quality and reliability of data. The Opinionated TLDR The Opinionated Architect’s TLDR: Set historical timestamps at the database layer. DO NOT let the client set these values. Exceptions are for tools that ingest data from third parties which already have historical timestamp columns. …