Top all-time · #3017
Michael Paquier
Personal blog · paquier.xyzprogrammingdata
Michael Paquier's personal blog at paquier.xyz covers programming and data. Since 2017, 26 different Hacker News readers have linked it 28 times, most often “Postgres 12 highlight – WITH clause and materialization”. The last post was in 2023, but the archive is still widely read.
Too few sampled comments (14) to say how often HN recommends it.?
Why HN links it
HN readers link Michael Paquier's posts on PostgreSQL release features, such as libpq target_session_attrs, SCRAM authentication and CTE materialization in Postgres 12, as references on how these features work.
Summarised by Claude from a sample of 14 HN comments that link here. Each comment is marked recommending, critical or neutral (citing without judging); how this works.
Blog about PostgreSQL and other things
Most-linked posts?
- Postgres 12 highlight – WITH clause and materialization9 people · 9 mentions · first linked 2019-03-01
- Postgres 12 Highlight – Reindex Concurrently2 people · 2 mentions · first linked 2019-04-24
- Postgres 10 highlight – SCRAM authentication2 people · 2 mentions · first linked 2017-03-09
- Postgres 10 highlight – Quorum set of synchronous standbys2 people · 2 mentions · first linked 2017-01-06
- SQL/JSON Path and Postgres 122 people · 2 mentions · first linked 2019-06-21
What Hacker News says?
This is a bit outside my area of expertise. Not sure about the best way to do the automated promotion process, but looking through these docs there's mention of a change to libpq that allows you to specify all the potential hosts and then have it only connect to whichever one is accepting writes (target_session_attrs). http://paquier.xyz/postgresql-2/postgres-10-libpq-read-write... Edit to add: I'm also interested t…
Comment linking /postgresql-2/postgres-10-libpq-read-write/
on “New in PostgreSQL 10”Submitted Postgres 12 highlight – WITH clause and materialization as a story · 5 points
HN discussionSCRAM authentication looks like a nice security improvement also. No description on the site but http://paquier.xyz/postgresql-2/postgres-10-scram-authentica... gives a good overview.
Comment linking /postgresql-2/postgres-10-scram-authentication/
on “New in PostgreSQL 10”Submitted SQL/JSON Path and Postgres 12 as a story · 4 points
HN discussionYep! A lot of older posts about CTEs largely advice against them for this reason. Postgres 12 introduced controllable materialization behaviour: https://paquier.xyz/postgresql-2/postgres-12-with-materializ... By default, it'll _not_ materialise unless it's recursive, or if there are >1 other CTEs consuming it. When not materializing, filters may push through the CTEs.
Comment linking /postgresql-2/postgres-12-with-materialize/
on “Lesser-known Postgres features”Submitted Postgres 12 highlight – SQL/JSON path as a story · 3 points
HN discussionAwesome news! thank you for sharing this. I found this post which confirms IRC and suggests it was an improvement in PG 12: https://paquier.xyz/postgresql-2/postgres-12-with-materializ... Today is a great day to have been wrong on the Internet. :)
Comment linking /postgresql-2/postgres-12-with-materialize/
on “PRQL – A proposal for a better SQL”Submitted Postgres 12 Highlight – Reindex Concurrently as a story · 3 points
HN discussionSorry. When you said "really proud" and "so great", it was perhaps an emotive choice of words. In the end the semantics of CTEs should be much the same throughout. There may be small differences, and perhaps larger ones such as being able to update through a CTE, something like with x as (...) update x set ... I don't know if PG supprts this, and it wouldn't break my heart if it didn't. It would be easy to work arou…
Comment linking /postgresql-2/postgres-12-with-materialize/
on “PostgreSQL is the worlds’ best database”Submitted Postgres 12 highlight – WITH clause and materialization as a story · 3 points
HN discussionAs of Postgres 12 this has changed substantially. Instead of being materialized, CTEs are inlined and optimized with the rest of the query. Exceptions: 1. If the results of the CTE are used more than once then it is materialized by default, though you can override this by adding "NOT MATERIALIZED" to the call. 2. Recursive and INSERT/UPDATE/DELETE CTEs are always materialized. https://paquier.xyz/postgresql-2/postgr…
Comment linking /postgresql-2/postgres-12-with-materialize/
on “We Can Do Better Than SQL”Submitted PostgreSQL 11 Highlight – More Partition Pruning as a story · 3 points
HN discussioncan confirm. That said, one can often capture much of the performance benefit with Postgres indexes - basically, instead of trying to tightly-pack the base tables, copy the data into indexes which are tightly packed exactly as you need. Put another way, Postgres indexes are synchronously updated materialized views - and yes, the Postgres planner/optimizer will automatically answer queries from an index if it's faste…
Comment linking /postgresql-2/postgres-11-covering-indexes/
on “At 22 years old, Postgres might just be the most advanced database yet”Submitted Postgres 10 highlight – SCRAM authentication as a story · 3 points
HN discussionTake a look at https://github.com/hapostgres/pg_auto_failover it's quite simple to use and manage. You can use libpq support for target_session_attrs (been there since Pg 10 - https://paquier.xyz/postgresql-2/postgres-10-libpq-read-writ... ) so you don't need active loadbalancer in front of your Pg cluster. You can however create your own health/status check service on top of pg_autoctl show state and use HAProxy if…
Comment linking /postgresql-2/postgres-10-libpq-read-write/
on “PostgreSQL is the Database Management System of the Year 2024”Submitted Postgres 10 highlight – Quorum set of synchronous standbys as a story · 3 points
HN discussionUntil postgres 12 (if you were using postgres) CTEs were an optimization fence. Where filters would not get pushed down into the CTE if they were only specified outside the CTE (but in fields from the CTE). https://paquier.xyz/postgresql-2/postgres-12-with-materializ...
Comment linking /postgresql-2/postgres-12-with-materialize/
on “Introduction to Window Functions in SQL”Submitted Postgres 16 highlight – JSON predicates as a story · 2 points
HN discussionOne of the big changes in 12 allows the optimizer to work properly with CTEs, which was a major barrier to using the more expressive language features. There a good writeup here: https://paquier.xyz/postgresql-2/postgres-12-with-materializ...
Comment linking /postgresql-2/postgres-12-with-materialize/
on “Oracle vs. PostgreSQL: First Glance”Submitted Postgres 12 highlight – More progress reporting as a story · 2 points
HN discussionThat's weird, what's your checkpoint_timeout? The default is 5 minutes, so you certainly shouldn't be pushing to S3 every 10s if the DB is idle. Apparently PG 10 will improve your use case, though: http://paquier.xyz/postgresql-2/postgres-10-checkpoint-skip/ EDIT: Also, wal-e compresses by default, so even those regular WAL files should be much smaller than 10MB. Are you sure the DB is really idle?
Comment linking /postgresql-2/postgres-10-checkpoint-skip/
on “Wal-E: Continuous Archiving for Postgres”Submitted Postgres 12 to get concurrent rebuilding of indexes as a story · 2 points
HN discussionThe state can never fit on just a single machine, unless you're perfectly confident that single machine will never fail :) Distributed consistency as discussed in this blog post is not really about scaling write throughput (which is the problem solved by horizontal/vertical partitioning), but rather about keeping replicas in sync with each other, seeing the same state. The usual suspects, MySQL and Postgres, don't r…
Comment linking /postgresql-2/postgres-10-quorum-sync/
on “Spanner vs. Calvin: distributed consistency at scale”Submitted Postgres 12 highlight – pg_promote as a story · 2 points
HN discussionYou mean event triggers[1] right? For the problem mentioned in the blog post — protecting roles — it's not possible to use event triggers because PostgreSQL doesn't fire[2] a DDL event for `CREATE/ALTER/DROP ROLE`. If an event for role modification was added, then maybe the same could be accomplished with event triggers(perhaps in pure SQL). Though hooks will always be more strict, since you can enforce conditions b…
Comment linking /postgresql-2/hooks-in-postgres-super-superuser-restrictions/
on “Protecting reserved roles with PostgreSQL Hooks”Submitted Postgres 12 highlight: DOS prevention as a story · 2 points
HN discussionDid you happen to try it with a CTE/WITH expression? Prior versions are not supposed to inline those, and the current version allows you to explicitly {pre,pro}scribe inlining: https://paquier.xyz/postgresql-2/postgres-12-with-materializ...
Comment linking /postgresql-2/postgres-12-with-materialize/
on “One word in PostgreSQL unlocked a 9x performance improvement”Submitted Postgres 11 Highlight – Channel Binding for SCRAM as a story · 1 points
HN discussion
