9.3: load path to mitigate load penalty for checksums

Introduction:
=================================
A lot of progress has been made on the checksums issue, with Simon's
excellent work beginning to gain consensus:

http://archives.postgresql.org/message-id/CA
+U5nMKw_GBs6qQ_Y8-RjGL1V7MVW2HWBHartB8LoJhnPfxL8g(at)mail(dot)gmail(dot)com

For the purposes of this proposal, I'll assume that's the general
direction we'll be taking for CRCs.

The major drawback of that proposal is that it increases the amount of
work to be done after a large data load by requiring more WAL.

Proposal:
=================================
I propose a special user-initiated loading mode at the table
granularity. During this time, readers must ignore PD_ALL_VISIBLE,
HEAP_XMIN_COMMITTED, and the visibility map entirely. However, writers
may set all of those bits before the writing transaction commits,
obviating the need to rewrite (and WAL) the data again later. Ideally,
there would be no work for VACUUM to do after the data load (unless a
transaction aborted).

This would also help the non-CRC case of course, but I expect CRCs to
make this significantly more important.

Goals:
=================================

* Table granularity (doesn't affect other tables at all)
* Allows concurrent loaders
* Allows loading into existing tables with existing data
* Online (allow reads to proceed, even if degraded)

Rough user interaction:
=================================

INITIATE LOAD ON foo AS 'job name';

-- run concurrent loading sessions

FINALIZE LOAD 'job name';

High-level design:
=================================

By "hints" I mean the VM bit, PD_ALL_VISIBLE, and HEAP_XMIN_COMMITTED.

By "ignorehints" I mean a flag in pg_class indicating that readers
should ignore hints.

By "optimistichints" I mean a flag in pg_class indicating that writers
can optimistically set hints.

Obviously, readers and writers would need a mechanism to honor those
flags, but I haven't dug into the details yet (additional routines in
tqual.c?).

States:
0: normal
* ignorehints = false
* optimistichints = false
1: trying to enter data load mode, waiting on existing
lockers (who might be scanning) to finish
* ignorehints = true
* optimistichints = false
2: data load mode
* ignorehints = true
* optimistichints = true
3: trying to leave data load mode, waiting on old snapshots to
be released and aborted transactions to be cleaned up
* ignorehints = true
* optimistichints = false

INITIATE LOAD would first transition from state 0 to 1 by acquiring a
ShareUpdateExclusiveLock on the table (to be sure no concurrent INITIATE
or FINALIZE LOAD is going on) and setting ignorehints = true.

Then it moves from state 1 to state 2 by waiting for all transactions
that hold a lock on that table. Any transactions that don't already have
a lock will see the new flag when they try to get it. Now we're sure
that all readers will see the "ignorehints" flag, so we can set the
"optimistichints" flag to indicate that writers can write hints
optimistically.

FINALIZE LOAD would first move from state 2 to state 3 by acquiring a
ShareUpdateExclusiveLock on the table setting optimistichints = false.

Then, it would move from state 3 to state 0 by first waiting for all
transactions that currently hold a lock on the table, to ensure they see
the optimistichints=false flag. Then, it would remember the current xid
as max_loader_xid, and wait until the global xmin is greater than
max_loader_xid. This should ensure that all snapshots regard all loading
transactions as complete. Also, it would need to run a lazy VACUUM to
remove any tuples from aborted transactions.

Details and optimizations
=================================
* We probably want a graceful way to handle multiple data loads
happening on the same table. Rather than erroring out, we could treat it
more like a reference count, and only do the work to move in to data
load mode if not there already, and only move out of data load mode if
we're the last loading job on the table.
* In general, there are some usability issues to sort out, to make sure
a table isn't put into data load mode and left that way. Right now, I'm
mostly concerned with getting a working design, but those will be
important, too.
* We could optimize away the VACUUM going from 3 -> 0 if we are sure no
writing transactions aborted.
* INITIATE and FINALIZE probably need to use PreventTransactionChain()
and multiple transactions, to avoid holding the ShareUpdateExclusiveLock
for too long. Also, we want to keep people from using it in the same
transaction as the loading xact, because they might not realize that
they would get a concurrency of 1 that way (because of the
ShareUpdateExclusiveLock).

Thoughts?

Regards,
Jeff Davis

On Mon, Jun 4, 2012 at 9:26 PM, Jeff Davis <pgsql(at)j-davis(dot)com> wrote:
> Thoughts?

Simon already proposed a way of doing this that doesn't require
explicit user action, which seems preferable to a method that does
require explicit user action, even though it's a little harder to
implement. His idea was to store the XID of the process creating the
table in the pg_class row, which I think is *probably* better than
your idea of having a process that waits and then flips the flag.
There are some finicky details though - see previous thread for
discussion of some of the issues.

It would be very nice to have a method that detects whether or not
there is only one open snapshot in a particular backend. Any time
that condition is met, tuples written into a table created or
truncated in the current transaction can be written with
HEAP_XMIN_COMMITTED already set. That is not as nice as being able to
set HEAP_XMIN_COMMITTED *and* PD_ALL_VISIBLE *and* the visibility map,
but it would still be a big improvement over the status quo. I would
like to see us get that part done and committed and then worry about
writing the tuples with PD_ALL_VISIBLE set as a separate project. In
many cases it would also be nice to write the tuples pre-frozen, so I
think we should look for a design that will support that.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Wed, Jun 06, 2012 at 03:08:05PM -0400, Robert Haas wrote:
> On Mon, Jun 4, 2012 at 9:26 PM, Jeff Davis <pgsql(at)j-davis(dot)com> wrote:
> > Thoughts?
>
> Simon already proposed a way of doing this that doesn't require
> explicit user action, which seems preferable to a method that does
> require explicit user action, even though it's a little harder to
> implement. His idea was to store the XID of the process creating the
> table in the pg_class row, which I think is *probably* better than
> your idea of having a process that waits and then flips the flag.
> There are some finicky details though - see previous thread for
> discussion of some of the issues.

I think both improvements have a place. Loads to a new heap should take as
many automatic shortcuts as practical. Loads to an existing heap also matter,
and Jeff's proposal helps those.

> It would be very nice to have a method that detects whether or not
> there is only one open snapshot in a particular backend. Any time
> that condition is met, tuples written into a table created or
> truncated in the current transaction can be written with
> HEAP_XMIN_COMMITTED already set.

Perhaps criteria like those in CheckTableNotInUse() would suffice?

On Wed, 2012-06-06 at 15:08 -0400, Robert Haas wrote:
> On Mon, Jun 4, 2012 at 9:26 PM, Jeff Davis <pgsql(at)j-davis(dot)com> wrote:
> > Thoughts?
>
> Simon already proposed a way of doing this that doesn't require
> explicit user action, which seems preferable to a method that does
> require explicit user action, even though it's a little harder to
> implement. His idea was to store the XID of the process creating the
> table in the pg_class row, which I think is *probably* better than
> your idea of having a process that waits and then flips the flag.
> There are some finicky details though - see previous thread for
> discussion of some of the issues.

My goals include:

* The ability to load into existing tables with existing data
* The ability to load concurrently

My understanding was that the proposal to which you're referring can't
do those things, which seem like major limitations. Did I miss
something?

> In
> many cases it would also be nice to write the tuples pre-frozen, so I
> think we should look for a design that will support that.

You're right, that would be nice.

Regards,
Jeff Davis

On Mon, Jun 04, 2012 at 06:26:04PM -0700, Jeff Davis wrote:
> I propose a special user-initiated loading mode at the table
> granularity. During this time, readers must ignore PD_ALL_VISIBLE,
> HEAP_XMIN_COMMITTED, and the visibility map entirely. However, writers
> may set all of those bits before the writing transaction commits,
> obviating the need to rewrite (and WAL) the data again later. Ideally,
> there would be no work for VACUUM to do after the data load (unless a
> transaction aborted).

Note that, currently, only VACUUM sets PD_ALL_VISIBLE and visibility map bits.
Would you make something else like heap_multi_insert() be able to do so?

> Goals:
> =================================
>
> * Table granularity (doesn't affect other tables at all)
> * Allows concurrent loaders
> * Allows loading into existing tables with existing data
> * Online (allow reads to proceed, even if degraded)

+1

> Obviously, readers and writers would need a mechanism to honor those
> flags, but I haven't dug into the details yet (additional routines in
> tqual.c?).

Avoiding measurable overhead in tuple visibility checks when the feature is
inactive may well prove to be a key implementation challenge.

> FINALIZE LOAD would first move from state 2 to state 3 by acquiring a
> ShareUpdateExclusiveLock on the table setting optimistichints = false.
>
> Then, it would move from state 3 to state 0 by first waiting for all
> transactions that currently hold a lock on the table, to ensure they see
> the optimistichints=false flag.

This is certainly necessary, but ...

> Then, it would remember the current xid
> as max_loader_xid, and wait until the global xmin is greater than
> max_loader_xid. This should ensure that all snapshots regard all loading
> transactions as complete.

... this might not be. Each backend could decide, based on its own xmin,
whether to ignore PD_ALL_VISIBLE in a given table. In other words, your
ignorehints flag could be an xmin set to InvalidTransactionId during stages 1
and 2 and to the earliest safe xmin during stages 0 and 3.

> * INITIATE and FINALIZE probably need to use PreventTransactionChain()
> and multiple transactions, to avoid holding the ShareUpdateExclusiveLock
> for too long. Also, we want to keep people from using it in the same
> transaction as the loading xact, because they might not realize that
> they would get a concurrency of 1 that way (because of the
> ShareUpdateExclusiveLock).

Yes. You need to commit the transaction modifying pg_class so other backends
can observe the change, at which point you can gather the list to wait on.

Consider splitting the INITIATE UI into two interfaces, one that transitions
from state 0 to state 1 and another that expects state 1 and blocks until we
reach state 2. You then have no need for PreventTransactionChain(), and the
interfaces could even be normal functions. It's less clear how reasonably you
could do this for the FINALIZE step, given its implicit VACUUM. It could be
achieved by having the user do the VACUUM and making the new interface merely
throw an error if a VACUUM is still needed. The trivial usage pattern might
look like this:

SELECT pg_initiate_load('bigtbl');
SELECT pg_wait_load('bigtbl'); -- not a great name
COPY bigtbl FROM STDIN;
SELECT pg_stop_load('bigtbl');
VACUUM bigtbl;
SELECT pg_finalize_load('bigtbl');

It's definitely less elegant, alas. Perhaps offer the interface you've
proposed and have it do the above under the hood. That way, users with
complex needs have the flexibility of the lower-level interfaces while those
who can tolerate PreventTransactionChain() have simplicity.

Thanks,
nm

On Wed, 2012-06-06 at 22:16 -0400, Noah Misch wrote:
> Note that, currently, only VACUUM sets PD_ALL_VISIBLE and visibility map bits.
> Would you make something else like heap_multi_insert() be able to do so?

That was the plan (roughly). I was thinking about doing it at the time a
new page was allocated.

> Avoiding measurable overhead in tuple visibility checks when the feature is
> inactive may well prove to be a key implementation challenge.

Perhaps a rudimentary CLOG cache, or some other way to mitigate CLOG
access could make it bearable.

Although I would like it to be an online operation, I'm not quite as
concerned about reads. I'd like to mitigate any major penalty, but if
reads are expensive during a load, than so be it.

> > Then, it would remember the current xid
> > as max_loader_xid, and wait until the global xmin is greater than
> > max_loader_xid. This should ensure that all snapshots regard all loading
> > transactions as complete.
>
> ... this might not be. Each backend could decide, based on its own xmin,
> whether to ignore PD_ALL_VISIBLE in a given table. In other words, your
> ignorehints flag could be an xmin set to InvalidTransactionId during stages 1
> and 2 and to the earliest safe xmin during stages 0 and 3.

That's a good idea. It might make it easier to implement, and removing a
step from finalization is certainly a big plus.

> > * INITIATE and FINALIZE probably need to use PreventTransactionChain()
> > and multiple transactions, to avoid holding the ShareUpdateExclusiveLock
> > for too long. Also, we want to keep people from using it in the same
> > transaction as the loading xact, because they might not realize that
> > they would get a concurrency of 1 that way (because of the
> > ShareUpdateExclusiveLock).
>
> Yes. You need to commit the transaction modifying pg_class so other backends
> can observe the change, at which point you can gather the list to wait on.
>
> Consider splitting the INITIATE UI into two interfaces, one that transitions
> from state 0 to state 1 and another that expects state 1 and blocks until we
> reach state 2. You then have no need for PreventTransactionChain(), and the
> interfaces could even be normal functions. It's less clear how reasonably you
> could do this for the FINALIZE step, given its implicit VACUUM. It could be
> achieved by having the user do the VACUUM and making the new interface merely
> throw an error if a VACUUM is still needed. The trivial usage pattern might
> look like this:
>
> SELECT pg_initiate_load('bigtbl');
> SELECT pg_wait_load('bigtbl'); -- not a great name
> COPY bigtbl FROM STDIN;
> SELECT pg_stop_load('bigtbl');
> VACUUM bigtbl;
> SELECT pg_finalize_load('bigtbl');
>
> It's definitely less elegant, alas. Perhaps offer the interface you've
> proposed and have it do the above under the hood. That way, users with
> complex needs have the flexibility of the lower-level interfaces while those
> who can tolerate PreventTransactionChain() have simplicity.

I think that's a reasonable suggestion. I am going back and forth a
little on this one. It's got the benefit that you can see the internal
states more clearly, and it's easier to tell what's going on, and it's
better if we want to do more sophisticated testing.

The main drawback here is that it's exposing more to the user. I
imagined that we might want to push other kinds of optimizations into
the load path, and that might upset the interface you've described
above. Then again, we'll probably need the normal, load, and transition
states regardless, so maybe it's an empty concern.

Regards,
Jeff Davis

On Mon, Jun 11, 2012 at 2:16 AM, Jeff Davis <pgsql(at)j-davis(dot)com> wrote:
> On Wed, 2012-06-06 at 22:16 -0400, Noah Misch wrote:
>> Note that, currently, only VACUUM sets PD_ALL_VISIBLE and visibility map bits.
>> Would you make something else like heap_multi_insert() be able to do so?
>
> That was the plan (roughly). I was thinking about doing it at the time a
> new page was allocated.
>
>> Avoiding measurable overhead in tuple visibility checks when the feature is
>> inactive may well prove to be a key implementation challenge.
>
> Perhaps a rudimentary CLOG cache, or some other way to mitigate CLOG
> access could make it bearable.
>
> Although I would like it to be an online operation, I'm not quite as
> concerned about reads. I'd like to mitigate any major penalty, but if
> reads are expensive during a load, than so be it.
>
>> > Then, it would remember the current xid
>> > as max_loader_xid, and wait until the global xmin is greater than
>> > max_loader_xid. This should ensure that all snapshots regard all loading
>> > transactions as complete.
>>
>> ... this might not be.  Each backend could decide, based on its own xmin,
>> whether to ignore PD_ALL_VISIBLE in a given table.  In other words, your
>> ignorehints flag could be an xmin set to InvalidTransactionId during stages 1
>> and 2 and to the earliest safe xmin during stages 0 and 3.
>
> That's a good idea. It might make it easier to implement, and removing a
> step from finalization is certainly a big plus.
>
>> >  * INITIATE and FINALIZE probably need to use PreventTransactionChain()
>> > and multiple transactions, to avoid holding the ShareUpdateExclusiveLock
>> > for too long. Also, we want to keep people from using it in the same
>> > transaction as the loading xact, because they might not realize that
>> > they would get a concurrency of 1 that way (because of the
>> > ShareUpdateExclusiveLock).
>>
>> Yes.  You need to commit the transaction modifying pg_class so other backends
>> can observe the change, at which point you can gather the list to wait on.
>>
>> Consider splitting the INITIATE UI into two interfaces, one that transitions
>> from state 0 to state 1 and another that expects state 1 and blocks until we
>> reach state 2.  You then have no need for PreventTransactionChain(), and the
>> interfaces could even be normal functions.  It's less clear how reasonably you
>> could do this for the FINALIZE step, given its implicit VACUUM.  It could be
>> achieved by having the user do the VACUUM and making the new interface merely
>> throw an error if a VACUUM is still needed.  The trivial usage pattern might
>> look like this:
>>
>> SELECT pg_initiate_load('bigtbl');
>> SELECT pg_wait_load('bigtbl'); -- not a great name
>> COPY bigtbl FROM STDIN;
>> SELECT pg_stop_load('bigtbl');
>> VACUUM bigtbl;
>> SELECT pg_finalize_load('bigtbl');
>>
>> It's definitely less elegant, alas.  Perhaps offer the interface you've
>> proposed and have it do the above under the hood.  That way, users with
>> complex needs have the flexibility of the lower-level interfaces while those
>> who can tolerate PreventTransactionChain() have simplicity.
>
> I think that's a reasonable suggestion. I am going back and forth a
> little on this one. It's got the benefit that you can see the internal
> states more clearly, and it's easier to tell what's going on, and it's
> better if we want to do more sophisticated testing.
>
> The main drawback here is that it's exposing more to the user. I
> imagined that we might want to push other kinds of optimizations into
> the load path, and that might upset the interface you've described
> above. Then again, we'll probably need the normal, load, and transition
> states regardless, so maybe it's an empty concern.

Instead of trying to maintain MVCC semantics, maybe we should just
have something like COPY (FROZEN) that just writes frozen tuples into
the table and throws MVCC out the window. Seems like that would be a
lot easier to implement and satisfy basically the same use cases.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Tue, Jun 12, 2012 at 7:42 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Mon, Jun 11, 2012 at 2:16 AM, Jeff Davis <pgsql(at)j-davis(dot)com> wrote:
>> On Wed, 2012-06-06 at 22:16 -0400, Noah Misch wrote:
>>> Note that, currently, only VACUUM sets PD_ALL_VISIBLE and visibility map bits.
>>> Would you make something else like heap_multi_insert() be able to do so?
>>
>> That was the plan (roughly). I was thinking about doing it at the time a
>> new page was allocated.
>>
>>> Avoiding measurable overhead in tuple visibility checks when the feature is
>>> inactive may well prove to be a key implementation challenge.
>>
>> Perhaps a rudimentary CLOG cache, or some other way to mitigate CLOG
>> access could make it bearable.
>>
>> Although I would like it to be an online operation, I'm not quite as
>> concerned about reads. I'd like to mitigate any major penalty, but if
>> reads are expensive during a load, than so be it.
>>
>>> > Then, it would remember the current xid
>>> > as max_loader_xid, and wait until the global xmin is greater than
>>> > max_loader_xid. This should ensure that all snapshots regard all loading
>>> > transactions as complete.
>>>
>>> ... this might not be.  Each backend could decide, based on its own xmin,
>>> whether to ignore PD_ALL_VISIBLE in a given table.  In other words, your
>>> ignorehints flag could be an xmin set to InvalidTransactionId during stages 1
>>> and 2 and to the earliest safe xmin during stages 0 and 3.
>>
>> That's a good idea. It might make it easier to implement, and removing a
>> step from finalization is certainly a big plus.
>>
>>> >  * INITIATE and FINALIZE probably need to use PreventTransactionChain()
>>> > and multiple transactions, to avoid holding the ShareUpdateExclusiveLock
>>> > for too long. Also, we want to keep people from using it in the same
>>> > transaction as the loading xact, because they might not realize that
>>> > they would get a concurrency of 1 that way (because of the
>>> > ShareUpdateExclusiveLock).
>>>
>>> Yes.  You need to commit the transaction modifying pg_class so other backends
>>> can observe the change, at which point you can gather the list to wait on.
>>>
>>> Consider splitting the INITIATE UI into two interfaces, one that transitions
>>> from state 0 to state 1 and another that expects state 1 and blocks until we
>>> reach state 2.  You then have no need for PreventTransactionChain(), and the
>>> interfaces could even be normal functions.  It's less clear how reasonably you
>>> could do this for the FINALIZE step, given its implicit VACUUM.  It could be
>>> achieved by having the user do the VACUUM and making the new interface merely
>>> throw an error if a VACUUM is still needed.  The trivial usage pattern might
>>> look like this:
>>>
>>> SELECT pg_initiate_load('bigtbl');
>>> SELECT pg_wait_load('bigtbl'); -- not a great name
>>> COPY bigtbl FROM STDIN;
>>> SELECT pg_stop_load('bigtbl');
>>> VACUUM bigtbl;
>>> SELECT pg_finalize_load('bigtbl');
>>>
>>> It's definitely less elegant, alas.  Perhaps offer the interface you've
>>> proposed and have it do the above under the hood.  That way, users with
>>> complex needs have the flexibility of the lower-level interfaces while those
>>> who can tolerate PreventTransactionChain() have simplicity.
>>
>> I think that's a reasonable suggestion. I am going back and forth a
>> little on this one. It's got the benefit that you can see the internal
>> states more clearly, and it's easier to tell what's going on, and it's
>> better if we want to do more sophisticated testing.
>>
>> The main drawback here is that it's exposing more to the user. I
>> imagined that we might want to push other kinds of optimizations into
>> the load path, and that might upset the interface you've described
>> above. Then again, we'll probably need the normal, load, and transition
>> states regardless, so maybe it's an empty concern.
>
> Instead of trying to maintain MVCC semantics, maybe we should just
> have something like COPY (FROZEN) that just writes frozen tuples into
> the table and throws MVCC out the window.  Seems like that would be a
> lot easier to implement and satisfy basically the same use cases.

-1: The situation with hint bit i/o patterns on many workloads is
untenable but it's not safe to assume MVCC can be ditched in those
workloads. Also, COPY does nothing about deletes. Neither does the
proposal as stated but I think it's easier to generalize into 'I want
to put hint bits in now so I don't have to deal with them later'.

merlin

Merlin Moncure <mmoncure(at)gmail(dot)com> writes:
> On Tue, Jun 12, 2012 at 7:42 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>> Instead of trying to maintain MVCC semantics, maybe we should just
>> have something like COPY (FROZEN) that just writes frozen tuples into
>> the table and throws MVCC out the window. Seems like that would be a
>> lot easier to implement and satisfy basically the same use cases.

> -1: The situation with hint bit i/o patterns on many workloads is
> untenable but it's not safe to assume MVCC can be ditched in those
> workloads. Also, COPY does nothing about deletes. Neither does the
> proposal as stated but I think it's easier to generalize into 'I want
> to put hint bits in now so I don't have to deal with them later'.

Dunno, I think Robert's idea has a fair amount of merit: mainly because
it will probably satisfy 90% of use cases for 1% of the work.

regards, tom lane

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> I think Robert's idea has a fair amount of merit: mainly because
> it will probably satisfy 90% of use cases for 1% of the work.

+1, especially if we include a pg_dump option to write the COPY
statements with that option.

-Kevin

On Tue, Jun 12, 2012 at 11:12 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Merlin Moncure <mmoncure(at)gmail(dot)com> writes:
>> On Tue, Jun 12, 2012 at 7:42 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>>> Instead of trying to maintain MVCC semantics, maybe we should just
>>> have something like COPY (FROZEN) that just writes frozen tuples into
>>> the table and throws MVCC out the window.  Seems like that would be a
>>> lot easier to implement and satisfy basically the same use cases.
>
>> -1: The situation with hint bit i/o patterns on many workloads is
>> untenable but it's not safe to assume MVCC can be ditched in those
>> workloads.  Also, COPY does nothing about deletes.  Neither does the
>> proposal as stated but I think it's easier to generalize into 'I want
>> to put hint bits in now so I don't have to deal with them later'.
>
> Dunno, I think Robert's idea has a fair amount of merit: mainly because
> it will probably satisfy 90% of use cases for 1% of the work.

90%? Hint bits i/o issues are not limited to bulk loads. They apply
to all many-record-per-transaction DML including (and especially)
deletes. Also it's not safe to assume that insertion heavy clients
can be migrated to COPY. For example, JDBC bulk loading AFAIK does
not use COPY and even if it did wouldn't be able to decorate the
command for a long time for most production workloads.

Vs Jeff's proposal you have a point -- I'm just very skeptical it's
going to do enough to mitigate the performance hit.

merlin

On Tue, Jun 12, 2012 at 5:41 PM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
> Also it's not safe to assume that insertion heavy clients
> can be migrated to COPY.  For example, JDBC bulk loading AFAIK does
> not use COPY and even if it did wouldn't be able to decorate the
> command for a long time for most production workloads

A non-mvcc table would only be useful if you're loading the data all
at once and don't plan to load any more. So that wouldn't even be
attempting to cover all "insertion heavy clients". Just specific use
cases.

I had in mind to implement something like this for archived data such
as old partitions in partitioned table. I think data loaded in a copy
that you know is never going to be modified would be a reasonable
other case for the same codepath.

Note that this particular use case is covered by FDW as well. If the
source data is in a dense enough format you could save even the load
step by reading directly from the source. I don't think this
eliminates the idea of having a denser read-only format though.
Postgres has lots of other features like indexing and different data
representations that might be useful and would be missing from, say, a
csv source file.

--
greg

On Tue, Jun 12, 2012 at 12:41 PM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
>>> -1: The situation with hint bit i/o patterns on many workloads is
>>> untenable but it's not safe to assume MVCC can be ditched in those
>>> workloads.  Also, COPY does nothing about deletes.  Neither does the
>>> proposal as stated but I think it's easier to generalize into 'I want
>>> to put hint bits in now so I don't have to deal with them later'.
>>
>> Dunno, I think Robert's idea has a fair amount of merit: mainly because
>> it will probably satisfy 90% of use cases for 1% of the work.
>
> 90%?  Hint bits i/o issues are not limited to bulk loads.   They apply
> to all many-record-per-transaction DML including (and especially)
> deletes.  Also it's not safe to assume that insertion heavy clients
> can be migrated to COPY.  For example, JDBC bulk loading AFAIK does
> not use COPY and even if it did wouldn't be able to decorate the
> command for a long time for most production workloads.
>
> Vs Jeff's proposal you have a point -- I'm just very skeptical it's
> going to do enough to mitigate the performance hit.

I don't think it's going to solve the problem in general, but TBH I
don't think Jeff's proposal is, either. I mean, ignoring
xmin-committed, xmax-committed, and all-visible bits is going to come
with a pretty steep performance penalty all of its own. I don't doubt
that you can construct situations in which it's better than incurring
the I/O associated with setting those bits after the fact, but I also
don't doubt that the reverse is true. Furthermore, any system that
involves sometimes ignoring those things is going to add cost in
extremely hot code paths even when the user doesn't elect to take
advantage of the new feature.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> I don't think it's going to solve the problem in general, but TBH I
> don't think Jeff's proposal is, either. I mean, ignoring
> xmin-committed, xmax-committed, and all-visible bits is going to come
> with a pretty steep performance penalty all of its own. I don't doubt
> that you can construct situations in which it's better than incurring
> the I/O associated with setting those bits after the fact, but I also
> don't doubt that the reverse is true. Furthermore, any system that
> involves sometimes ignoring those things is going to add cost in
> extremely hot code paths even when the user doesn't elect to take
> advantage of the new feature.

Yeah; the notion of adding cycles to the visibility-check code paths,
which would add cost even for users who have no use at all for this
feature, is close to being a deal-breaker for me. I would rather see
us designing this around the idea of "what can we do without adding
any new complexity in visibility checks?".

At least for MVCC cases (hence, user tables only), it seems like we
could pre-set XMIN_COMMITTED hint bits if there were a way to be sure
that the XID in question would be seen as still running in the followup
snapshot check that every MVCC visibility test must make anyway. The
hard part of that seems to be post-crash behavior, but maybe it'd be
acceptable to incur crash-recovery-time cost to run around and unset
such bits?

This doesn't do anything for pre-freezing of course, but I basically
don't see any way that we could pre-freeze without breaking MVCC
semantics anyhow. So there's still attraction in the idea of offering
users the choice of not sustaining MVCC behavior in exchange for cheaper
bulk loads.

regards, tom lane

On Wed, Jun 6, 2012 at 8:42 PM, Jeff Davis <pgsql(at)j-davis(dot)com> wrote:
> On Wed, 2012-06-06 at 15:08 -0400, Robert Haas wrote:
>> On Mon, Jun 4, 2012 at 9:26 PM, Jeff Davis <pgsql(at)j-davis(dot)com> wrote:
>> > Thoughts?
>>
>> Simon already proposed a way of doing this that doesn't require
>> explicit user action, which seems preferable to a method that does
>> require explicit user action, even though it's a little harder to
>> implement.  His idea was to store the XID of the process creating the
>> table in the pg_class row, which I think is *probably* better than
>> your idea of having a process that waits and then flips the flag.
>> There are some finicky details though - see previous thread for
>> discussion of some of the issues.
>
> My goals include:
>
> * The ability to load into existing tables with existing data
> * The ability to load concurrently
>
> My understanding was that the proposal to which you're referring can't
> do those things, which seem like major limitations. Did I miss
> something?

No, you're correct. I misread your original email, sorry.

I'm just thinking about this a little more. It strikes me that the
core trade-off here is between doing more post-commit work and doing
more post-abort work. For example, in your proposal, we've got to run
a lazy vacuum before exiting bulk load mode, because if a transaction
has aborted, we've got to get rid of its tuples before letting anyone
trust hint bits again. That is, abort cleanup gets harder. OTOH,
commit cleanup gets easier, because all the hint bits and visibility
map bits are already set: the only thing left is to freeze. In
general, I think that's a good trade-off. Commits are much more
common than aborts, and so we ought to be optimizing for the commit
case.

But maybe there are other ways of doing it, besides what you've
proposed here. Not sure exactly what, but it might be worth thinking
about.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Tue, 2012-06-12 at 08:42 -0400, Robert Haas wrote:
> Instead of trying to maintain MVCC semantics, maybe we should just
> have something like COPY (FROZEN) that just writes frozen tuples into
> the table and throws MVCC out the window. Seems like that would be a
> lot easier to implement and satisfy basically the same use cases.

The reason that doesn't work is because there's really no answer for
ABORTs except to delete the whole table, or ask the user to try to
figure out what made it or what didn't. (That I can see, anyway; you may
have a better idea.)

In practice, I think the user would need to use this only for new tables
and use only one loader so they would know what to do if there is an
abort. That makes this very similar to the proposal for optimizing loads
by the transaction that creates the table; except with fewer safeguards.
It's basically just saying "don't abort, and be careful with concurrent
reads (which are now dirty)".

I think this problem largely has to do with freezing though, because
that's what makes it impossible to differentiate after an abort. Let's
set that aside for now, and just focus on setting HEAP_XMIN_COMMITTED,
PD_ALL_VISIBLE, and the VM bit. Those account for a major part of the
problem; being able to freeze also is really a bonus (and if the table
is really that static, then maybe creating it in the loading transaction
is reasonable).

In those terms, we can reframe the questions as: what do we do about
reads during the load?

Answers could include:
(a) nothing -- reads during a load are potentially dirty
(b) readers ignore hint bits during the load, and pay the penalty
(c) allow only INSERT/COPY, and block unsafe SELECT/UPDATE/DELETE

(a) is your proposal except without the freezing. (b) was my proposal.
(c) was a backup I had in mind (if reads are expensive anyway, maybe
people would rather just block).

These aren't exclusive. Maybe we can do (c) in READ COMMITTED and above,
and (a) in READ UNCOMMITTED.

Regards,
Jeff Davis

On Tue, 2012-06-12 at 13:31 -0400, Tom Lane wrote:
> Yeah; the notion of adding cycles to the visibility-check code paths,
> which would add cost even for users who have no use at all for this
> feature, is close to being a deal-breaker for me. I would rather see
> us designing this around the idea of "what can we do without adding
> any new complexity in visibility checks?".

[waves hands wildly]

We could create a different HeapTupleSatisfiesMVCC routine that ignores
hint bits, and select that at the time the scan is started. That would
at least avoid affecting existing code paths.

> At least for MVCC cases (hence, user tables only), it seems like we
> could pre-set XMIN_COMMITTED hint bits if there were a way to be sure
> that the XID in question would be seen as still running in the followup
> snapshot check that every MVCC visibility test must make anyway. The
> hard part of that seems to be post-crash behavior, but maybe it'd be
> acceptable to incur crash-recovery-time cost to run around and unset
> such bits?

Hmm... but that still leaves PD_ALL_VISIBLE, which will also cause a
rewrite of the data. Under OLTP, we can assume that PD_ALL_VISIBLE is
much more rare than HEAP_XMIN_COMMITTED; but for bulk loads, setting
HEAP_XMIN_COMMITTED doesn't help us much without PD_ALL_VISIBLE.

> This doesn't do anything for pre-freezing of course, but I basically
> don't see any way that we could pre-freeze without breaking MVCC
> semantics anyhow. So there's still attraction in the idea of offering
> users the choice of not sustaining MVCC behavior in exchange for cheaper
> bulk loads.

That may be reasonable, but it's much more dangerous than just breaking
MVCC (which to me implies isolation rules) -- pre-freezing would break
atomicity if you have any aborts. And I can't think of very many cases
where losing atomicity is reasonable (although I'm sure there are some).

Regards,
Jeff Davis

On Tue, Jun 12, 2012 at 01:31:05PM -0400, Tom Lane wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> > I don't think it's going to solve the problem in general, but TBH I
> > don't think Jeff's proposal is, either. I mean, ignoring
> > xmin-committed, xmax-committed, and all-visible bits is going to come
> > with a pretty steep performance penalty all of its own. I don't doubt
> > that you can construct situations in which it's better than incurring
> > the I/O associated with setting those bits after the fact, but I also
> > don't doubt that the reverse is true. Furthermore, any system that
> > involves sometimes ignoring those things is going to add cost in
> > extremely hot code paths even when the user doesn't elect to take
> > advantage of the new feature.
>
> Yeah; the notion of adding cycles to the visibility-check code paths,
> which would add cost even for users who have no use at all for this
> feature, is close to being a deal-breaker for me. I would rather see
> us designing this around the idea of "what can we do without adding
> any new complexity in visibility checks?".
>
> At least for MVCC cases (hence, user tables only), it seems like we
> could pre-set XMIN_COMMITTED hint bits if there were a way to be sure
> that the XID in question would be seen as still running in the followup
> snapshot check that every MVCC visibility test must make anyway. The
> hard part of that seems to be post-crash behavior, but maybe it'd be
> acceptable to incur crash-recovery-time cost to run around and unset
> such bits?

Well, truncating tables that were empty on the load would certainly be
a easy to do --- not sure if that helps us, though.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

On Tue, 2012-06-12 at 13:10 -0400, Robert Haas wrote:
> I don't think it's going to solve the problem in general, but TBH I
> don't think Jeff's proposal is, either. I mean, ignoring
> xmin-committed, xmax-committed, and all-visible bits is going to come
> with a pretty steep performance penalty all of its own.

I think we'd certainly have to discourage users from launching lots of
full table scans during a data load. We could go so far as blocking
reads, as I said in my other response, and still (mostly) meet my
primary goals.

But allowing reads without hint bits might be useful for highly
selective index access, which is a substantial use case for the kind of
large tables we'd be bulk-loading.

Regards,
Jeff Davis

Jeff Davis <pgsql(at)j-davis(dot)com> writes:
> In those terms, we can reframe the questions as: what do we do about
> reads during the load?

> Answers could include:
> (a) nothing -- reads during a load are potentially dirty
> (b) readers ignore hint bits during the load, and pay the penalty
> (c) allow only INSERT/COPY, and block unsafe SELECT/UPDATE/DELETE

Or (d) it's not a problem, since the inserting XID is still busy
according to the readers' snapshots.

The part I think is actually hard is how to clean up if the inserting
xact doesn't reach commit. I think what we're basically looking at here
is pushing more cost into that path in order to avoid cost in successful
cases. The first design that comes to mind is

(1) the inserting xact remembers which tables it's inserted pre-hinted
tuples into, and if it has to abort, it first seqscans those tables to
reset the hint bits;
(2) it also emits WAL entries that will cause crash recovery to perform
an identical scan, if WAL ends without finding a commit or abort record
for the inserting xact.

But there may be other better ways. One problem with the above sketch
is that you can't checkpoint till the insertion is committed, since a
checkpoint would prevent crash recovery from seeing the warning WAL
records.

regards, tom lane

On Tue, 2012-06-12 at 17:53 -0400, Bruce Momjian wrote:
> Well, truncating tables that were empty on the load would certainly be
> a easy to do --- not sure if that helps us, though.

This goes back to the single-loader-into-an-empty-table case, which I'd
like to get away from. I think we already have some reasonable proposals
on the table for optimizing that case.

Ultimately, that pushes us toward partitioning with the same granularity
as the data load. We can try to open that discussion up, but I think
it's a bad idea to tie the data load granularity to the partitioning
granularity, unless we have a way to coalesce them later without
rewriting. It's too bad filesystems don't allow us to just reallocate
some blocks from one file to another. [ OK, I guess I just opened this
discussion myself ;) ]

Regards,
Jeff Davis

On Tue, 2012-06-12 at 18:02 -0400, Tom Lane wrote:
> Or (d) it's not a problem, since the inserting XID is still busy
> according to the readers' snapshots.

How much of a savings did we get from PD_ALL_VISIBLE when it was added
into the page-at-a-time visibility check?

>From 608195a3a3656145a7eec7a47d903bc684011d73:

"In addition to the visibility map, there's a new PD_ALL_VISIBLE flag on
each heap page, also indicating that all tuples on the page are visible
to all transactions. It's important that this flag is kept up-to-date.
It is also used to skip visibility tests in sequential scans, which
gives a small performance gain on seqscans."

If "small" means that it's something we can give up, then focusing on
HEAP_XMIN_COMMITTED makes sense. But if we can't give it up, then we
need to take it into account in the proposal.

Regards,
Jeff Davis

On Tue, Jun 12, 2012 at 6:02 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> The part I think is actually hard is how to clean up if the inserting
> xact doesn't reach commit.  I think what we're basically looking at here
> is pushing more cost into that path in order to avoid cost in successful
> cases.  The first design that comes to mind is
>
> (1) the inserting xact remembers which tables it's inserted pre-hinted
> tuples into, and if it has to abort, it first seqscans those tables to
> reset the hint bits;

I don't think we can count on that to be safe in an arbitrarily chosen
abort path. Anything FATAL, for instance. I think we're going to
need to keep track of some kind table-xmin value, representing the
oldest operation on the table that's not cleaned up yet, and make it
autovacuum's job to clean any that precede OldestXmin. If the backend
can clean itself up, great, but there has to be some kind of allowance
for the case where that doesn't happen.

I'm also skeptical about the notion that "scan the whole table" is
going to be a good idea. It really will have to be a full sequential
scan, if we're setting visibility map bits as we go, not just a scan
of pages that are not-all-visible, as vacuum normally does. I think
if we want to go this route, we need to log the TID of every tuple we
write into the heap into some kind of undo fork (or maybe just the
block numbers), so that if the transaction aborts, we (or autovacuum)
can go back and find all of those TIDs and mark the tuples dead
without having to scan through (potentially) terabytes of data.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Tue, Jun 12, 2012 at 6:26 PM, Jeff Davis <pgsql(at)j-davis(dot)com> wrote:
> On Tue, 2012-06-12 at 18:02 -0400, Tom Lane wrote:
>> Or (d) it's not a problem, since the inserting XID is still busy
>> according to the readers' snapshots.
>
> How much of a savings did we get from PD_ALL_VISIBLE when it was added
> into the page-at-a-time visibility check?
>
> >From 608195a3a3656145a7eec7a47d903bc684011d73:
>
> "In addition to the visibility map, there's a new PD_ALL_VISIBLE flag on
> each heap page, also indicating that all tuples on the page are visible
> to all transactions. It's important that this flag is kept up-to-date.
> It is also used to skip visibility tests in sequential scans, which
> gives a small performance gain on seqscans."
>
> If "small" means that it's something we can give up, then focusing on
> HEAP_XMIN_COMMITTED makes sense. But if we can't give it up, then we
> need to take it into account in the proposal.

It's significant.

rhaas=# create table foo (a int, b text);
ERROR: relation "foo" already exists
rhaas=# create table bar (a int, b text);
CREATE TABLE
rhaas=# insert into bar select g,
random()::text||random()::text||random()::text||random()::text from
generate_series(1,1000000) g;
INSERT 0 1000000
rhaas=# \timing
Timing is on.
rhaas=# select sum(1) from bar; sum
---------
1000000
(1 row)

Time: 257.500 ms
rhaas=# select sum(1) from bar;
sum
---------
1000000
(1 row)

Time: 140.763 ms
rhaas=# select sum(1) from bar;
sum
---------
1000000
(1 row)

Time: 142.760 ms
rhaas=# select sum(1) from bar;
sum
---------
1000000
(1 row)

Time: 140.603 ms
rhaas=# vacuum bar;
VACUUM
Time: 133.084 ms
rhaas=# select sum(1) from bar;
sum
---------
1000000
(1 row)

Time: 123.591 ms
rhaas=# select sum(1) from bar;
sum
---------
1000000
(1 row)

Time: 123.096 ms
rhaas=# select sum(1) from bar;
sum
---------
1000000
(1 row)

Time: 122.653 ms

So vacuuming to set the PD_ALL_VISIBLE bits is buying us more than 10% here.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Tue, 2012-06-12 at 22:06 -0400, Robert Haas wrote:
> > How much of a savings did we get from PD_ALL_VISIBLE when it was added
> > into the page-at-a-time visibility check?
> >
> > >From 608195a3a3656145a7eec7a47d903bc684011d73:
> >
> > "In addition to the visibility map, there's a new PD_ALL_VISIBLE flag on
> > each heap page, also indicating that all tuples on the page are visible
> > to all transactions. It's important that this flag is kept up-to-date.
> > It is also used to skip visibility tests in sequential scans, which
> > gives a small performance gain on seqscans."
> >
> > If "small" means that it's something we can give up, then focusing on
> > HEAP_XMIN_COMMITTED makes sense. But if we can't give it up, then we
> > need to take it into account in the proposal.
>
> It's significant.

In that case, the proposals that only involve HEAP_XMIN_COMMITTED don't
seem viable to me. To get maxiumum read speed, we will need to set
PD_ALL_VISIBLE also, and that means rewriting the entire table anyway
(for the workload that I'm describing).

However, maybe if we combine the approaches, readers could ignore
PD_ALL_VISIBLE during the load, which looks like maybe a 10% penalty,
without having to ignore HEAP_XMIN_COMMITTED (which seems much more
costly to ignore).

Regards,
Jeff Davis