Michael Paquier

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-new-wal-format/

> 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-only-scans/

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."

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-alter-system-set-for-dynamic-configuration/

[3] http://www.postgresql.org/docs/current/static/sql-copy.html

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-table-inheritance/

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.