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 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 apply good architecting and…

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 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 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. Exceptions are for applications that require extreme update…

PostgreSQL offers multiple ways to get the current time of a database. These are similar in intent but differ in usage. It’s possible to generate a unique timestamp, but we’re not sure when you would use it. The Opinionated TLDR Use CLOCK_TIMESTAMP() when non-SQL standard functions are acceptable. Note that CURRENT_TIMESTAMP is about…

Can we write useful articles on software architecting when solutions are often too specific? Concerns about Writing I’ve wanted to write about some of my thoughts on software engineering and architecting for quite some time. However, a concern of mine is being able to add value to readers without creating controversy. …

Idempotent Focused Architecting is an approach to software engineering that handles multiple exactly-similar calls to a system and can be used as a forcing function to help you build better software. The Opinionated TLDR Greatly favor idempotent solutions for RESTful APIs, GraphQL, messaging systems, etc. If idempotence is not an option then document…

Messaging and logging are similar in implementation but different in usage. Structured Message Format The implementation of a logging or messaging system can be the same. We recommend you standardize on a Structured Message Format for both logging and messaging. However, messaging and logging differ significantly by intent. Logging Logging intends to provide a…

We use Materialized views to cache the results of SQL queries. However, to refresh a materialized view, we need to run REFRESH MATERIALIZED VIEW {view_name}. Leveraging pg_cron, we can automate the refreshing of materialized views without the need for 3rd party tools. All source code for this post is available…

Logging and Messaging aren’t the most glamorous aspects of software engineering but are necessary. Getting logging right, out of the gate, for your project is essential. Specifically, what should be in a structured log message? We’ve built a semi-opinionated structured-messaging library that enables you to create structured logs using Winston. …