Today I Learned

Postgres will not run any CTEs not referenced in the main query. It’s a form of optimization in newer versions.

E.g.,

WITH will_not_run AS (SELECT 1/0), ok AS (SELECT 1)
SELECT * FROM ok;

In Postgres, you can have a primary read/write instance, and a read only hot-standby to serve requests at a high volume. When you update the primary replica, it sends updates via a stream of logs to the replica.

If the replica has a long-running query, some issues can arise during the replication process. E.g., if the primary deletes a row that is being queried, the replica can fail that process and return a:

ERROR: canceling statement due to conflict with recovery

To fix this, we can adjust max_standby_streaming_delay and max_standby_archive_delay. They allow us to wait a bit more before we kill that query and replicate the data.