M
Michael Paquier
michael.otacoo.com · Personal blog · on HN since 2012
Michael Paquier is a PostgreSQL contributor who writes feature highlights for each Postgres release, covering JSON, replication and security.
What HN saysCommenters call it a great source on new Postgres features, citing his posts on row-level security, JSON operators, and synchronous replication in 9.6.
34HN people shared it
13linked it in comments
31stories submitted
667story points
10 years agolast shared
2012people per year2026
Most-shared posts
- 1Postgres 9.3 feature highlight: JSON operators4 people · 86 points · first shared 2013 · 29 comments on HN
- 2Postgres 9.6 feature highlight: Multiple synchronous standbys3 people · 7 points · first shared 2016 · discussion on HN
- 3Postgres 9.5 feature highlight: row-level security and policies2 people · 153 points · first shared 2014 · 61 comments on HN
- 4Postgres 9.3 feature highlight: Materialized views2 people · 12 points · first shared 2013 · discussion on HN
- 5Postgres 9.5: New JSONB functions and operators2 people · 5 points · first shared 2015 · discussion on HN
- 6Postgres 9.5 feature highlight: New WAL format2 people · 4 points · first shared 2015 · discussion on HN
- 7Postgres 9.5 feature: Foreign Tables now part of Inheritance Trees2 people · 2 points · first shared 2015 · discussion on HN
- 8Postgres 9.4 feature highlight: Indexing JSON data with jsonb data type1 people · 200 points · first shared 2014 · 39 comments on HN
- 9Postgres 9.2 highlight: JSON data type1 people · 73 points · first shared 2012 · 24 comments on HN
- 10Postgres 9.5 feature highlight: Import Foreign Schema1 people · 64 points · first shared 2014 · 10 comments on HN
- 11Postgres 9.5 feature highlight: BRIN indexes1 people · 8 points · first shared 2014 · discussion on HN
- 12Postgres 9.3 feature highlight: custom background workers1 people · 7 points · first shared 2013 · discussion on HN
- 13Postgres 9.3 feature highlight: JSON data generation1 people · 7 points · first shared 2013 · discussion on HN
- 14Postgres 9.2 highlight: range types1 people · 6 points · first shared 2012 · 2 comments on HN
- 15Postgres 9.3 feature highlight: IF EXISTS and IF NOT EXISTS1 people · 4 points · first shared 2013 · discussion on HN
What HN people wrote when they linked it
craigkerstiens · 2014-02-02
Agreed, despez is a great source as is Michael Paquier's blog (http://michael.otacoo.com/). The goal here was to boil it up a bit more to the practical uses and benefits versus the technical details of it. Further there was a talk at FOSDEM just yesterday which highlighted much of whats committed in 9.4, though slides aren't online yet.
koolba · 2016-05-12
Parallel query will probably win for sexiest new feature (rightfully so!) but remote apply is very useful in the real world. It will greatly simplify reading newly written data off replicas: http://michael.otacoo.com/postgresql-2/postgres-9-6-feature-highlight-…
y0ghur7_xxx · 2015-08-11
INSERT ON CONFLICT and Row Level Security are awesome new additions.
If you don't know about RLS I recommend you read up on them[1], as they would allow you to handle select/update/delete rights on rows directly on the data itself.
[1]http://michael.otacoo.com/postgresql-2/postgres-9-5-feature-highlight-…
brissmyr · 2013-09-09
Love the way they are heading with the JSON stuff http://michael.otacoo.com/postgresql-2/postgres-9-3-feature-highlight-…
dijit · 2016-04-14
Better to wait for postgresql 9.6 which will have synchronous replication, write latency but not read.
http://michael.otacoo.com/postgresql-2/postgres-9-6-feature-highlight-…
vog · 2016-02-27
> Index scans imply extra disk seeks to fetch the index.
This is no longer true in this generality, because since PostgreSQL 9.2 there are also index-only scans. Those scans take the data directly from the index, without going through the original rows at all. Of course, this works only under certain conditions. See also:
http://michael.otacoo.com/postgresql-2/postgresql-9-2-highlight-index-…
http://www.postgresql.org/docs/9.2/static/index-scanning.html
"If the index stores the original indexed data values (and not some lossy representation of them), it is useful to support index-only scans, in which the index returns the actual data not just the TID of the heap tuple. This will only work if the visibility map shows that the TID is on an all-visible page; else the heap tuple must be visited anyway to check MVCC visibility. But that is no concern of the access method's."
Twisell · 2015-08-16
Well something not mentioned in this article is the that 9.5 should also introduce inheritance from/to Foreign Tables which is one step closer to nice and easy horizontal scaling.
Found this detailed example : http://michael.otacoo.com/postgresql-2/postgres-9-5-feature-foreign-ta…
Maybe thats not a No-SQL trendy way of doing things, but for I am more used to PostgreSQL this look pretty neat. I think as the old and reliable project it is it will come to BigData at its own pace and quietly... but its already moving toward this and FDW was the first step.
yangyang · 2014-10-21
Apologies - I was thinking of ALTER SYSTEM [1]. That writes to postgresql.auto.conf. It sounds like that file has priority over postgresql.conf [2]. You might well be able to just prevent writes by the postgres user (or whatever user you're running postgres as).
Regarding superusers overwriting files - COPY can write to anywhere on the filesystem. From the docs "COPY naming a file is only allowed to database superusers, since it allows reading or writing any file that the server has privileges to access." [3].
[1] http://www.postgresql.org/docs/9.4/static/sql-altersystem.html
[2] http://michael.otacoo.com/postgresql-2/postgres-9-4-feature-highlight-…
[3] http://www.postgresql.org/docs/current/static/sql-copy.html
itodd · 2013-11-07
This is how you do it in 9.2 using PLV8.
http://people.planetpostgresql.org/andrew/index.php?/archives/249-Usin…
For 9.3, see more info at http://michael.otacoo.com/postgresql-2/postgres-9-3-feature-highlight-…
alternize · 2013-08-22
while there are a lot of different improvements, i'm really looking forward to the enhanced JSON functionalities (particularly the new operators [0]) and the LATERAL JOIN [1]
[0] http://michael.otacoo.com/postgresql-2/postgres-9-3-feature-highlight-…
[1] http://www.postgresql.org/docs/9.3/static/queries-table-expressions.ht…
iso8859-1 · 2013-05-13
Here's a blog post about it: http://michael.otacoo.com/postgresql-2/postgres-9-3-feature-highlight-…
rtfeldman · 2013-04-08
Good news! It's slated for the next release. :) http://michael.otacoo.com/postgresql-2/postgres-9-3-feature-highlight-…
BenoitP · 2016-03-18
One can hope that this will get fixed over time. 9.4 introduced some flexibility over streaming out data changes from the Write Ahead Log, from [1]:
> Add support for logical decoding of WAL data, to allow database changes to be streamed out in a customizable format.
Also 9.5 added new metadata to it [2]:
> Each WAL record now carries information about the modified relation and (s) in a standardized format. That makes it easier to write tools that need that information, like pg_rewind, prefetching the blocks to speed up recovery, etc.
Hopefully we will see the ecosystem pick up on those changes. Percona made a killing in helping replication take place in MySQL. I don't see why this would not happen with postgres.
I'm confident things will improve.
[1] http://www.postgresql.org/docs/9.4/static/release-9-4.html
[2] http://michael.otacoo.com/postgresql-2/postgres-9-5-feature-highlight-…