MariaDB and granting rights
In a MariaDB getting the rights to most objects set just right is not a problem, but there are exceptions, one of which is the right to change Triggers.
MySql losing an auto-increment ?
Something byzare happened: a table with lots of daily inserts and deletes (somehow!) lost its auto increment, unique and primary key restrictions. That had some far reaching consequences.
Using timeseries to simplify calculations.
Say you want to sum up some figures per user per day, but there are holes in the events. Timeseries to the rescue.
Migrating dates and datetimes that are set to 0000-00-00 in MySql / MariaDB.
When migrating from an old version of MySql to a newer version, or a modern MariaDB you may encounter date fields that have a value of 0000-00-00 or datetime fields that contain 0000-00-00 00:00:00.
These values are no longer allowed.
These values are no longer allowed.
Aggregation and statistics, part 1.
Aggregation can be an absolute lifesaver, but properly handling aggregated values and especially combining them can be tricky.
Aggregation and statistics, part 2: combining and cascading.
How to do a quick count(*) estimate in PostgeSQL.
A count(*) has to actually do a tablescan to get an accurate answer
If the result can be an estimate (that usually is spot on) then this trick is a lifesaver.
If the result can be an estimate (that usually is spot on) then this trick is a lifesaver.
Understanding Cardinality.
Cardinality is one of those concepts that are often overlooked, but an absolute essential part of tuning databases.
A partitioning-by-date trick.
Partitioning tables on data-age is a neat way to deal with limited retention, but partitioning on a timestamp or date brings its own problems. In this episode I present a nifty trick to get around that and make partitioning on data-age a lot simpler.
Correctly handing a date-embedded-sequence.
Correctly setting and updating the sequence with a date embedded into it seems trivial, but this is not actually the case.
Grouping pg_stat_statistics correctly.
Everybody uses (or should use) pg_stat_statements, but the issue of similar queries taking up individual rows in the results is non-trivial, and can hinder when looking for the performance-hogs.
Correctly handing a date-embedded Identity.
Correctly setting and updating the sequence with a date embedded into it was non-trivial, but is also possible on a generated always as identity field.
Some tips and gotchas of PostgreSQL replication between docker containers.
Setting up PostgreSQL replication in docker containers, and especially starting up a replica node, is less straightforward than one would assume. Here are some tips and some of the gotchas that I have encountered.
Setting up PostgreSQL replication in docker on MacOS.
Setting up PostgreSQL replication in docker on MacOS requires some unexpected extra steps. In this blog a complete walkthrough of how to set up PostgreSQL replication between docker containers on MacOS.
General guidelines for PostgreSQL database architecture.
Over the years I have collected many snippets of wisdom, from experiments and mistakes. I bundeled up the ten most usable of these into one short document. Have a look!