Tomas, * Tomas Vondra (tomas.von...@2ndquadrant.com) wrote: > On 01/05/2017 02:23 PM, Magnus Hagander wrote: > >It's easy enough to construct a benchmark specifically to show the > >difference, but of any actual "normal workload" for it. Typically the > >optimization applies to things like bulk loading, which typically never > >done alone and does not lend itself to that type of benchmarking very > >easily. > > Not sure if I understand correctly what you're saying. You're saying > that although it'd be easy to construct a benchmark showing > significant performance impact, it won't represent a common > workload. Correct?
I think he's saying that it's not very easy to construct a good example of typical bulk-loading workloads using just pgbench. Bulk loading certainly happens with PG and I don't think we'll make very many friends if we break optimizations when wal_level is set to minimal like those you get using: BEGIN; CREATE TABLE x (c1 int); COPY x FROM STDIN; COMMIT; or: BEGIN; TRUNCATE x; COPY x FROM STDIN; COMMIT; Changing the wal_level from 'minimal' to 'replica' or 'logical' with such a benchmark is going to make the WAL go from next-to-nothing to size-of-database. One doesn't typically *just* do bulk loads, however, often it's a bulk load into a table and then the contents of that table are merged with another table or perhaps joined to it to produce some report or something along those lines. In many of those cases, our more-recently added capability to have UNLOGGED tables will work, but not all (in particular, it can be very handy to load everything in using the above technique and then switch the wal_level to replica, which avoids having to have the bulk of the data sent through WAL, something you can't avoid if you want to turn an unlogged table into a logged one). Thanks! Stephen
Description: Digital signature