Re: [GENERAL] Questions regarding JSON processing

[John R Pierce]

On 4/25/2017 9:21 PM, Glen Huang wrote:
For updating db using JSON requests from clients, that I'm not so 
sure. Should I directly pass the request JSON to PostgreSQL and ask it 
to parse this JSON and execute a transaction all by itself, or should 
I parse it in the server and generate the transaction SQL and execute 
that on PostgreSQL? The former sounds optimal, but I'm not sure 
if PostgreSQL is able to walk a JSON structure and run a transaction 
along the way? Should I do it with PL/pgSQL? It seems functions can't 
execute a transaction?


what does "walk a JSON structure and run a transaction along the way"   
actual entail?Normally, the application starts a transaction, 
executes one or more SQL commands, then does a commit or rollback.   any 
JSON would be within this transaction. functions are called within a 
transaction.


If your JSON includes instructions as well as data, I'd be rather 
cautious of letting a remote client send that directly to the database 
server unless you can ensure that nothing hostile can be done with it, 
or completely trust all clients.



--
john r pierce, recycling bits in santa cruz



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Questions regarding JSON processing

[Pavel Stehule]

2017-04-26 6:21 GMT+02:00 Glen Huang :

> Hi all,
>
> I have a RESTful API server that sends and receives JSON strings. I'm
> wondering what might be the best way to leverage PostgreSQL's JSON
> capability.
>
> For sending JSON responses to clients. I believe the best way is to ask
> PostgreSQL to generate the JSON string and then pass that directly to
> clients, instead of making multiple queries to construct the JSON and then
> send it,  which doesn't seem optimal. Is that the case?
>
> For updating db using JSON requests from clients, that I'm not so sure.
> Should I directly pass the request JSON to PostgreSQL and ask it to parse
> this JSON and execute a transaction all by itself, or should I parse it in
> the server and generate the transaction SQL and execute that on PostgreSQL?
> The former sounds optimal, but I'm not sure if PostgreSQL is able to walk a
> JSON structure and run a transaction along the way? Should I do it with
> PL/pgSQL? It seems functions can't execute a transaction?
>

The PLpgSQL is static language and is good for static processing JSON doc,
but it is unfit for iteration over any generic nested document. You can use
PLPerlu, PLPythonu. Lot of people uses PLV8 for JSON processing.

The functions in PostgreSQL are executed under transaction - you cannot to
explicitly control transaction, but there are possibility to implicitly
handle transactions with exception handling. There is workaround via dblink
to emulate autonomous transactions.

Regards

Pavel


>
> Would like to hear some thoughts on this. Thanks.
>
> Glen
>

[GENERAL] Questions regarding JSON processing

[Glen Huang]

Hi all,

I have a RESTful API server that sends and receives JSON strings. I'm
wondering what might be the best way to leverage PostgreSQL's JSON
capability.

For sending JSON responses to clients. I believe the best way is to ask
PostgreSQL to generate the JSON string and then pass that directly to
clients, instead of making multiple queries to construct the JSON and then
send it,  which doesn't seem optimal. Is that the case?

For updating db using JSON requests from clients, that I'm not so sure.
Should I directly pass the request JSON to PostgreSQL and ask it to parse
this JSON and execute a transaction all by itself, or should I parse it in
the server and generate the transaction SQL and execute that on PostgreSQL?
The former sounds optimal, but I'm not sure if PostgreSQL is able to walk a
JSON structure and run a transaction along the way? Should I do it with
PL/pgSQL? It seems functions can't execute a transaction?

Would like to hear some thoughts on this. Thanks.

Glen

Re: [GENERAL] pg_test_fsync performance

[Andy Colson]

On 04/25/2017 05:40 AM, Karri Niemelä wrote:

Hi. Wondering what sort of results are people seeing on modern servers when 
running pg_test_fsync?

Thanks,
Karri



18 sata laptop hd's (some 5400, some 7200), hardware raid 10
12 core Xeon E5645

5 seconds per test
O_DIRECT supported on this platform for open_datasync and open_sync.

Compare file sync methods using one 8kB write:
(in wal_sync_method preference order, except fdatasync is Linux's default)
open_datasync 24903.540 ops/sec  40 usecs/op
fdatasync 21800.982 ops/sec  46 usecs/op
fsync 20762.443 ops/sec  48 usecs/op
fsync_writethroughn/a
open_sync 23394.041 ops/sec  43 usecs/op

Compare file sync methods using two 8kB writes:
(in wal_sync_method preference order, except fdatasync is Linux's default)
open_datasync 12301.561 ops/sec  81 usecs/op
fdatasync 17146.136 ops/sec  58 usecs/op
fsync 15853.102 ops/sec  63 usecs/op
fsync_writethroughn/a
open_sync 11182.711 ops/sec  89 usecs/op

Compare open_sync with different write sizes:
(This is designed to compare the cost of writing 16kB in different write
open_sync sizes.)
 1 * 16kB open_sync write 18419.537 ops/sec  54 usecs/op
 2 *  8kB open_sync writes11215.533 ops/sec  89 usecs/op
 4 *  4kB open_sync writes 6250.670 ops/sec 160 usecs/op
 8 *  2kB open_sync writes 3245.170 ops/sec 308 usecs/op
16 *  1kB open_sync writes 1648.117 ops/sec 607 usecs/op

Test if fsync on non-write file descriptor is honored:
(If the times are similar, fsync() can sync data written on a different
descriptor.)
write, fsync, close   17929.396 ops/sec  56 usecs/op
write, close, fsync   17782.500 ops/sec  56 usecs/op

Non-sync'ed 8kB writes:
write421898.978 ops/sec   2 usecs/op



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_test_fsync performance

[Andy Colson]

On 04/25/2017 05:40 AM, Karri Niemelä wrote:

Hi. Wondering what sort of results are people seeing on modern servers when 
running pg_test_fsync?

Thanks,
Karri


4 sata HD, hardware raid 10
4 core Xeon E5504
Box is Busy


5 seconds per test
O_DIRECT supported on this platform for open_datasync and open_sync.

Compare file sync methods using one 8kB write:
(in wal_sync_method preference order, except fdatasync is Linux's default)
open_datasync 15984.370 ops/sec  63 usecs/op
fdatasync 16100.336 ops/sec  62 usecs/op
fsync 15109.988 ops/sec  66 usecs/op
fsync_writethroughn/a
open_sync 16849.201 ops/sec  59 usecs/op

Compare file sync methods using two 8kB writes:
(in wal_sync_method preference order, except fdatasync is Linux's default)
open_datasync  8638.443 ops/sec 116 usecs/op
fdatasync 12036.359 ops/sec  83 usecs/op
fsync 11807.447 ops/sec  85 usecs/op
fsync_writethroughn/a
open_sync  8532.569 ops/sec 117 usecs/op

Compare open_sync with different write sizes:
(This is designed to compare the cost of writing 16kB in different write
open_sync sizes.)
 1 * 16kB open_sync write 14205.165 ops/sec  70 usecs/op
 2 *  8kB open_sync writes 8506.969 ops/sec 118 usecs/op
 4 *  4kB open_sync writes 4979.092 ops/sec 201 usecs/op
 8 *  2kB open_sync writes 2658.138 ops/sec 376 usecs/op
16 *  1kB open_sync writes 1343.359 ops/sec 744 usecs/op

Test if fsync on non-write file descriptor is honored:
(If the times are similar, fsync() can sync data written on a different
descriptor.)
write, fsync, close   13331.347 ops/sec  75 usecs/op
write, close, fsync   13435.198 ops/sec  74 usecs/op

Non-sync'ed 8kB writes:
write297771.626 ops/sec   3 usecs/op



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Questionaire: Common WAL write rates on busy servers.

[Jerry Sievers]

Andres Freund  writes:

> Hi,
>
> I've lately seen more and more installations where the generation of
> write-ahead-log (WAL) is one of the primary bottlenecks.  I'm curious
> whether that's primarily a "sampling error" of mine, or whether that's
> indeed more common.
>
> The primary reason I'm curious is that I'm pondering a few potential
> optimizations, and would like to have some guidance which are more and
> which are less important.

I have a busy warehouse spitting out about 400k
segments/week... ~10MB/second :-)

We have resorted to a rather complex batch/parallel compressor/shipper
to keep up with the volume.

>
> Questions (answer as many you can comfortably answer):
> - How many MB/s, segments/s do you see on busier servers?

Our busiest system Avg 10MB/second but very burst.  Assume it'w many
times that during high churn periods.

> - What generates the bulk of WAL on your servers (9.5+ can use
>   pg_xlogdump --stats to compute that)?

Simply warehouse incremental loading and/or full table delete/trunc and
reload, plus dirived data being created.  Many of the transient tables
are on NVME and unlogged.

> - Are you seeing WAL writes being a bottleneck?OA
> - What kind of backup methods are you using and is the WAL volume a
>   problem?

I do not know if basic local WAL writing itself is a problem of or not
but as mentioned, we are scarcely able to handle the necessary archiving
to make backups and PITR possible.

> - What kind of replication are you using and is the WAL volume a

Th;are 2 streamers both feeding directly from master.  We use a fairly
large 30k keep-segments value to help avoid streamers falling behind and
then having to resort to remote archive fetching.

It does appear that since streaming WAL reception and application as
well as of course remote fetching are single threaded, this is a
bottleneck as well.  That is, a totally unloded and well outfitted
(hardware wise) streamer can barely keep up with master.

> - What are your settings for wal_compression, max_wal_size (9.5+) /
>   checkpoint_segments (< 9.5), checkpoint_timeout and wal_buffers?

 checkpoint_timeout  | 5min
 max_wal_size| 4GB 
 wal_buffers | 16MB
 wal_compression | off 

> - Could you quickly describe your workload?

warehouse with user self-service reporting creation/storage allowed in
same system.

>
> Feel free to add any information you think is pertinent ;)

Great idea!!  Thanks

>
> Greetings,
>
> Andres Freund

-- 
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consult...@comcast.net
p: 312.241.7800


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Surprising results from array concatenation

[Mike Blackwell]

On Tue, Apr 25, 2017 at 12:53 PM, Tom Lane  wrote:

> Yeah.  The core problem here is that the parser has to disambiguate the
> || operator: is it "anyarray || anyelement" or "anyarray || anyarray"?
>
​<...>​


> Peeking at the contents of the literal would make the behavior very
> unpredictable/data-dependent, so we don't.


​Fair enough.

Would a note in that section of the docs pointing out this behavior be
worthwhile?

Re: [GENERAL] Surprising results from array concatenation

[Tom Lane]

"David G. Johnston"  writes:
> On Tue, Apr 25, 2017 at 9:26 AM, Mike Blackwell 
> wrote:
>> The docs (section 9.18 for PG 9.6) show as an example for array
>> concatenation
>> ARRAY[4,5,6] || 7
>> which works fine.  However, trying the same with an array of text doesn't
>> work:
>> # select array['a','b','c'] || 'd';
>> ERROR:  malformed array literal: "d"
>>
>> The assumption that the second argument is an array constant seems
>> surprising

> ​It has to assume something.  And for better and worse it has to assume it
> without looking at the actual value.

Yeah.  The core problem here is that the parser has to disambiguate the
|| operator: is it "anyarray || anyelement" or "anyarray || anyarray"?
In your first example the array can be seen to be int[] and 7 is taken
to be type int, so only "anyarray || anyelement" works.  In the second
case it's looking at "int[] || unknown", and the relevant heuristic is
to assume that the "unknown" is the same type as the operator's other
input.

Peeking at the contents of the literal would make the behavior very
unpredictable/data-dependent, so we don't.

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Surprising results from array concatenation

[David G. Johnston]

On Tue, Apr 25, 2017 at 9:26 AM, Mike Blackwell 
wrote:

> The docs (section 9.18 for PG 9.6) show as an example for array
> concatenation
>
> ARRAY[4,5,6] || 7
>
> which works fine.  However, trying the same with an array of text doesn't
> work:
>
> # select array['a','b','c'] || 'd';
> ERROR:  malformed array literal: "d"
> LINE 1: select array['a','b','c'] || 'd';
>  ^
> DETAIL:  Array value must start with "{" or dimension information.
>
> Casting the second value to TEXT works.
>
> # select array['a','b','c'] || 'd'::TEXT;
>  ?column?
> ---
>  {a,b,c,d}
> (1 row)
>
> The assumption that the second argument is an array constant seems
> surprising
>

​It has to assume something.  And for better and worse it has to assume it
without looking at the actual value.​  Choosing the scalar variant here
would be more convenient but choosing the same type as the left-hand side
is logical.  Note that the concatenation operator/function isn't the one
complaining - if it was then intelligence could be inserted.  The type
conversion code doesn't have the luxury.

I don't suppose one would get far arguing to modify the array input
function to convert a value that doesn't look like an array into a single
element text array.  The "implicit conversion" that involves is something
we've gotten away from and seems like it would be worse that requiring the
explicit typing.

David J.

[GENERAL] Surprising results from array concatenation

[Mike Blackwell]

The docs (section 9.18 for PG 9.6) show as an example for array
concatenation

ARRAY[4,5,6] || 7

which works fine.  However, trying the same with an array of text doesn't
work:

# select array['a','b','c'] || 'd';
ERROR:  malformed array literal: "d"
LINE 1: select array['a','b','c'] || 'd';
 ^
DETAIL:  Array value must start with "{" or dimension information.

Casting the second value to TEXT works.

# select array['a','b','c'] || 'd'::TEXT;
 ?column?
---
 {a,b,c,d}
(1 row)

The assumption that the second argument is an array constant seems
surprising.

__
*Mike Blackwell | Technical Analyst, Distribution Services/Rollout
Management | RRD*
1750 Wallace Ave | St Charles, IL 60174-3401
Office: 630.313.7818
mike.blackw...@rrd.com
http://www.rrdonnelley.com



* *

Re: [GENERAL] [PERFORM] Questionaire: Common WAL write rates on busy servers.

[Claudio Freire]

On Tue, Apr 25, 2017 at 1:17 AM, Andres Freund  wrote:
> Questions (answer as many you can comfortably answer):
> - How many MB/s, segments/s do you see on busier servers?

~20MB/s with FPW compression, with peaks of ~35MB/s. Writes become the
bottleneck without compression and it tops at about 40-50MB/s, WAL
archiving cannot keep up beyond that point.

> - What generates the bulk of WAL on your servers (9.5+ can use
>   pg_xlogdump --stats to compute that)?

Type   N  (%)
Record size  (%) FPI size  (%)Combined
size  (%)
   -  ---
---  ---   ---
-  ---
XLOG   0 (  0.00)
  0 (  0.00)0 (  0.00)0 (
0.00)
Transaction   30 (  0.00)
960 (  0.00)0 (  0.00)  960 (
0.00)
Storage0 (  0.00)
  0 (  0.00)0 (  0.00)0 (
0.00)
CLOG   0 (  0.00)
  0 (  0.00)0 (  0.00)0 (
0.00)
Database   0 (  0.00)
  0 (  0.00)0 (  0.00)0 (
0.00)
Tablespace 0 (  0.00)
  0 (  0.00)0 (  0.00)0 (
0.00)
MultiXact110 (  0.01)
   7456 (  0.02)0 (  0.00) 7456 (
0.00)
RelMap 0 (  0.00)
  0 (  0.00)0 (  0.00)0 (
0.00)
Standby2 (  0.00)
368 (  0.00)0 (  0.00)  368 (
0.00)
Heap2   2521 (  0.22)
  78752 (  0.24)  4656133 (  2.82)  4734885 (
2.39)
Heap  539419 ( 46.52)
15646903 ( 47.14) 98720258 ( 59.87)114367161 (
57.73)
Btree 606573 ( 52.31)
15872182 ( 47.82) 57514798 ( 34.88) 73386980 (
37.05)
Hash   0 (  0.00)
  0 (  0.00)0 (  0.00)0 (
0.00)
Gin 2866 (  0.25)
 134330 (  0.40)  4012251 (  2.43)  4146581 (
2.09)
Gist   0 (  0.00)
  0 (  0.00)0 (  0.00)0 (
0.00)
Sequence7970 (  0.69)
1450540 (  4.37)0 (  0.00)  1450540 (
0.73)
SPGist 0 (  0.00)
  0 (  0.00)0 (  0.00)0 (
0.00)
BRIN   0 (  0.00)
  0 (  0.00)0 (  0.00)0 (
0.00)
CommitTs   0 (  0.00)
  0 (  0.00)0 (  0.00)0 (
0.00)
ReplicationOrigin  0 (  0.00)
  0 (  0.00)0 (  0.00)0 (
0.00)

    
Total1159491
33191491 [16.76%]164903440 [83.24%]198094931
[100%]


> - Are you seeing WAL writes being a bottleneck?OA

Sometimes, more so without FPW compression

> - What kind of backup methods are you using and is the WAL volume a
>   problem?
> - What kind of replication are you using and is the WAL volume a
>   problem?

Streaming to hot standby + WAL archiving, delayed standby as backup
and PITR. Backups are regular filesystem-level snapshots of the
delayed standby (with postgres down to get consistent snapshots).

WAL volume getting full during periods where the hot standby lags
behind (or when we have to stop it to create consistent snapshots) are
an issue indeed, and we've had to provision significant storage to be
able to absorb those peaks (1TB of WAL)

We bundle WAL segments into groups of 256 segments for archiving and
recovery to minimize the impact of TCP slow start. We further gzip
segments before transfer with pigz, and we use mostly rsync (with a
wrapper script that takes care of durability and error handling) to
move segments around. Getting the archive/recovery scripts to handle
the load hasn't been trivial.

> - What are your settings for wal_compression, max_wal_size (9.5+) /
>   checkpoint_segments (< 9.5), checkpoint_timeout and wal_buffers?

wal_compression = on
max_wal_size = 12GB
min_wal_size = 2GB
checkpoint_timeout = 30min

Re: [GENERAL] Failed dependencies for Pgadmin4 Web in Centos 7

[Adrian Klaver]

On 04/24/2017 07:56 AM, Clodoaldo Neto wrote:


On Mon, Apr 24, 2017 at 11:15 AM, Adrian Klaver
> wrote:








To be clear I am not a Yum/RPM expert.

Where is the spec file coming from?


From the pgadmin4-v1-web package:
https://yum.postgresql.org/srpms/9.6/redhat/rhel-7-x86_64/pgadmin4-v1-1.4-1.rhel7.src.rpm


I see there is a proposed fix:
"#FIXME: add pgadmin4- prefix to related dependencies"





The second problem is that the other packages do not exist in
pgdg-96 in
either form.


Looks to me they do:

https://yum.postgresql.org/9.6/redhat/rhel-7-x86_64/



Missing packages: python-dateutil, python-mimeparse,
python-flask-security, python-flask-wtf


Yeah, that would be a problem. My suggestion is to contact Devrim 
directly, his email is at bottom of the spec file.





Clodoaldo








--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_dump: creates dumps that cannot be restored

[David G. Johnston]

On Tue, Apr 25, 2017 at 4:15 AM, Thorsten Glaser  wrote:

>
>
③ hack pg_dump to invalidate constraints before and revalidate them
>   after the fact.
>

​I suspect there are many people who'd rather take the dump at face value
then expending considerably amounts of time re-validating everything that
is known to already be valid.  dump-restore people are usually less
concerned about downtime but its not unimportant to them.​


>
> This would allow me to express what I want in a more natural and
> easier to validate (pun intended this time) way.
>
> It feels “right” to use a trigger on the referenced table preventing
> the field from changing, but it feels more right for the referencing
> table to simply use a CHECK constraint.
>
>
​This isn't a bug, isn't the first time its come up, and hasn't been
changed in the many years of awareness.  While your feelings are important
we cannot commit feelings.  If those get translated into a feature then the
necessary trade-off analysis can be performed and a decision can be made as
to whether the benefit of such clean syntax outweighs the costs such a
capability would impose at runtime.​  Triggers do work and while they
constrain what PostgreSQL can support in terms of architecture and promises
the current setup no one has felt strongly enough to volunteer time or
money to lessen the constraints.

For my current use case, the ship has sailed, but (especially given
> that such CHECK constrains are currently, while not officially
> supported, at least “tolerated” and (except in pg_dump) work) this
> is something to consider for PostgreSQL 10 in my opinion.
>

​No matter the merits of this feature the ship for 10 also sailed, about 3
weeks ago: feature freeze is now in effect.

David J.

Re: [GENERAL] Questionaire: Common WAL write rates on busy servers.

[bricklen]

On Mon, Apr 24, 2017 at 9:17 PM, Andres Freund  wrote:

>
> Questions (answer as many you can comfortably answer):
> - How many MB/s, segments/s do you see on busier servers?
> - What generates the bulk of WAL on your servers (9.5+ can use
>   pg_xlogdump --stats to compute that)?
> - Are you seeing WAL writes being a bottleneck?OA
> - What kind of backup methods are you using and is the WAL volume a
>   problem?
> - What kind of replication are you using and is the WAL volume a
>   problem?
> - What are your settings for wal_compression, max_wal_size (9.5+) /
>   checkpoint_segments (< 9.5), checkpoint_timeout and wal_buffers?
> - Could you quickly describe your workload?
>

* Postgresql 9.3
* 1500+ db servers
* Daily peak for busy databases: 75 WALs switched per second (less than 10%
of the servers experience this)
* Avg per db: 2 WALs/s
* Mainly generated by large batch sync processes that occur throughout the
day, and by a legacy archiving process to purge older data (potentially
many millions of cascading deletes).
*Half the servers have (encrypted) pg_dump backups, WAL volume hasn't
proved to be a problem there, though dump size is a problem for a few of
the larger databases (less than 1TB).
* Inter-data-centre replication is all streaming, across DC's (over the
WAN) WAL shipping is over compressed SSH tunnels.
Occasionally the streaming replication falls behind, but more commonly it
is the cross-DC log shipping that becomes a problem. Some of the servers
will generate 50+ GBs of WAL in a matter of minutes and that backs up
immediately on the masters. Occasionally this has a knock-on effect for
other servers and slows down their log shipping due to network saturation.
* checkpoint_segments: 64, checkpoint_timeout: 5 mins, wal_buffers: 16MB

Workload:
70% of servers are generally quiet, with occasional bursty reads and writes.
20% are medium use, avg a few hundred transactions/second
10% average around 5k txns/s, with bursts up to 25k txns/s for several
minutes.
All servers have about 80% reads / 20% writes, though those numbers flip
during big sync jobs and when the purging maintenance kicks off.

Re: [GENERAL] pg_dump: creates dumps that cannot be restored

[Thorsten Glaser]

Hi again,

one, possibly, last, thing. I wrote:

> I still find the CHECK constraint
> to be a more natural way to express what I want, though.

Now let me extend on this a bit.

The CHECK constraint says nicely and natively, what constraints (no
pun intended) I want the data to fulfil. With both the CHECK constraint
and the trigger, we need an equivalent constraint on the referenced
foreign table, which we have, which we always had; in the schema
example I gave, this is not allowing the field “standalone” to change.

I can, however, VALIDATE a CHECK constraint after the dump has been
restored; I cannot do that with a trigger (or I haven’t found out
how to do it). This means that if a user manually edited the dump
prior to restoring I have no way to make the restoring transaction
fail if the data is bogus.

Sure, SOL on the user, but I created the various CHECK constraints
to cover against user errors in the first place.


I would very much prefer for PostgreSQL to
① formally allow and support such CHECK constraints,
② add an ALTER TABLE … INVALIDATE CONSTRAINT command, to pair with
  the existing ALTER TABLE … VALIDATE CONSTRAINT command, and
③ hack pg_dump to invalidate constraints before and revalidate them
  after the fact.

This would allow me to express what I want in a more natural and
easier to validate (pun intended this time) way.

It feels “right” to use a trigger on the referenced table preventing
the field from changing, but it feels more right for the referencing
table to simply use a CHECK constraint.
As for validation, see above.


For my current use case, the ship has sailed, but (especially given
that such CHECK constrains are currently, while not officially
supported, at least “tolerated” and (except in pg_dump) work) this
is something to consider for PostgreSQL 10 in my opinion.

Thank you for listening.

bye,
//mirabilos
-- 
tarent solutions GmbH
Rochusstraße 2-4, D-53123 Bonn • http://www.tarent.de/
Tel: +49 228 54881-393 • Fax: +49 228 54881-235
HRB 5168 (AG Bonn) • USt-ID (VAT): DE122264941
Geschäftsführer: Dr. Stefan Barth, Kai Ebenrett, Boris Esser, Alexander Steeg


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] pg_test_fsync performance

[Karri Niemelä]

Hi. Wondering what sort of results are people seeing on modern servers when 
running pg_test_fsync?

Thanks,
Karri

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] [PERFORM] Questionaire: Common WAL write rates on busy servers.

[Vladimir Borodin]

Hi Andres.

> 25 апр. 2017 г., в 7:17, Andres Freund  написал(а):
> 
> Hi,
> 
> I've lately seen more and more installations where the generation of
> write-ahead-log (WAL) is one of the primary bottlenecks.  I'm curious
> whether that's primarily a "sampling error" of mine, or whether that's
> indeed more common.
> 
> The primary reason I'm curious is that I'm pondering a few potential
> optimizations, and would like to have some guidance which are more and
> which are less important.
> 
> Questions (answer as many you can comfortably answer):
> - How many MB/s, segments/s do you see on busier servers?

Nearly one WAL (16 MB) per second most of the time and 3 WALs per second in the 
beginning of checkpoint (due to full_page_writes).

> - What generates the bulk of WAL on your servers (9.5+ can use
>  pg_xlogdump --stats to compute that)?

Here is the output from a couple of our masters (and that is actually two hours 
before peak load):

$ pg_xlogdump --stats 000100012B280089 000100012B300088 | fgrep 
-v 0.00

Type   N  (%)  Record size  
(%) FPI size  (%)Combined size  (%)
   -  ---  ---  
---   ----  ---
Heap2   55820638 ( 21.31)   1730485085 
( 22.27)   1385795249 ( 13.28)   3116280334 ( 17.12)
Heap74366993 ( 28.39)   2288644932 
( 29.46)   5880717650 ( 56.34)   8169362582 ( 44.87)
Btree   84655827 ( 32.32)   2243526276 
( 28.88)   3170518879 ( 30.38)   5414045155 ( 29.74)
    
  
Total  2619337907769663301 
[42.67%]  10437031778 [57.33%]  18206695079 [100%]
$

$ pg_xlogdump --stats 0001D17F00A5 0001D1910004 | fgrep 
-v 0.00
Type   N  (%)  Record size  
(%) FPI size  (%)Combined size  (%)
   -  ---  ---  
---   ----  ---
Heap2   13676881 ( 18.95)422289539 
( 19.97)  15319927851 ( 25.63)  15742217390 ( 25.44)
Heap22284283 ( 30.88)715293050 
( 33.83)  17119265188 ( 28.64)  17834558238 ( 28.82)
Btree   27640155 ( 38.30)725674896 
( 34.32)  19244109632 ( 32.19)  19969784528 ( 32.27)
Gin  6580760 (  9.12)172246586 
(  8.15)   8091332009 ( 13.54)   8263578595 ( 13.35)
    
  
Total   721729832114133847 
[3.42%]   59774634680 [96.58%]  61888768527 [100%]
$

> - Are you seeing WAL writes being a bottleneck?OA

We do sometimes see WALWriteLock in pg_stat_activity.wait_event, but not too 
often.

> - What kind of backup methods are you using and is the WAL volume a
>  problem?

We use fork of barman project. In most cases that’s not a problem.

> - What kind of replication are you using and is the WAL volume a
>  problem?

Physical streaming replication. We used to have problems with network bandwidth 
(1 Gbit/s was consumed by transferring WAL to two replicas and one archive) but 
that became better after 1. upgrading to 9.5 and turning wal_compression on, 2. 
changing archive command to doing parallel compression and sending WALs to 
archive, 3. increasing checkpoint_timeout.

> - What are your settings for wal_compression, max_wal_size (9.5+) /
>  checkpoint_segments (< 9.5), checkpoint_timeout and wal_buffers?

xdb301e/postgres M # SELECT name, current_setting(name) FROM pg_settings
WHERE name IN ('max_wal_size', 'checkpoint_timeout', 'wal_compression', 
'wal_buffers');
name| current_setting
+-
 checkpoint_timeout | 1h
 max_wal_size   | 128GB
 wal_buffers| 16MB
 wal_compression| on
(4 rows)

Time: 0.938 ms
xdb301e/postgres M #

> - Could you quickly describe your workload?

OLTP workload with 80% reads and 20% writes.

> 
> Feel free to add any information you think is pertinent ;)

Well, we actually workarounded issues with WAL write rate by increasing 
checkpoint_timeout to maximum possible (in 9.6 it can be even more). The 
downside of this change is recovery time. Thanks postgres