Re: code question: storing INTO relation
Bruce Momjian
Added to TODO:
* Eliminate WAL logging for CREATE INDEX/REINDEX/CREATE TABLE AS
when not doing WAL archiving
---------------------------------------------------------------------------
Greg Stark wrote:
> Simon Riggs <si...@2ndquadrant.com> writes:
>
> > I agree, hence why this should be a user option. The usage of this is
> > restricted to particular classes of database usage: data warehousing or
> > very large database applications. This isn't intended for use in OLTP or
> > web-site databases.
>
> Well a lot of users also just don't use online backups. For these users
> there's no downside to CREATE INDEX/REINDEX/CREATE TABLE AS not logging.
>
> The logging is not needed for a regular recovery, only for restoring from
> and a backup older than the table/index creation.
>
> --
> greg
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majo...@postgresql.org
>
--
Bruce Momjian | http://candle.pha.pa.us
pg...@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majo...@postgresql.org)
Greg Stark
Bruce Momjian <pg...@candle.pha.pa.us> writes:
> Added to TODO:
>
> * Eliminate WAL logging for CREATE INDEX/REINDEX/CREATE TABLE AS
> when not doing WAL archiving
I think that's already done for CREATE INDEX/REINDEX. The reason I mentioned
them was to provide precedent that CREATE TABLE AS could be treated that way.
--
greg
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
Bruce Momjian
>
> Bruce Momjian <pg...@candle.pha.pa.us> writes:
>
> > Added to TODO:
> >
> > * Eliminate WAL logging for CREATE INDEX/REINDEX/CREATE TABLE AS
> > when not doing WAL archiving
>
> I think that's already done for CREATE INDEX/REINDEX. The reason I mentioned
> them was to provide precedent that CREATE TABLE AS could be treated that way.
I don't think so. Can someone confirm?
--
Bruce Momjian | http://candle.pha.pa.us
pg...@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
Tom Lane
> Greg Stark wrote:
>> I think that's already done for CREATE INDEX/REINDEX.
> I don't think so. Can someone confirm?
Greg is correct --- at least for btree build, which is the only index
type we have WAL-ified at all :-(
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
Bruce Momjian
> Bruce Momjian <pg...@candle.pha.pa.us> writes:
> > Greg Stark wrote:
> >> I think that's already done for CREATE INDEX/REINDEX.
>
> > I don't think so. Can someone confirm?
>
> Greg is correct --- at least for btree build, which is the only index
> type we have WAL-ified at all :-(
OK, TODO updated:
* Eliminate WAL logging for CREATE TABLE AS when not doing WAL
archiving
FYI, we do have a TODO for the other index methods:
* Add WAL index reliability improvement to non-btree indexes
--
Bruce Momjian | http://candle.pha.pa.us
pg...@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
---------------------------(end of broadcast)---------------------------
Kevin Brown
>
> Added to TODO:
>
> * Eliminate WAL logging for CREATE INDEX/REINDEX/CREATE TABLE AS
> when not doing WAL archiving
But that means that these operations can't be performed in a
transaction unless WAL archiving is on, right? (If I misunderstand
and thus am wrong about this then you can ignore the rest of what
follows, of course)
Suppose I do the following within a transaction with WAL archiving
turned off:
CREATE TABLE blah (x integer primary key, y varchar(32));
INSERT INTO blah SELECT x, y FROM foo;
and then roll it back because something didn't work out the way I
wanted it to.
If CREATE INDEX isn't WAL logged, then either the rollback of the
above will result in an inconsistent database, or the fact that CREATE
INDEX isn't WAL logged will cause the CREATE TABLE to fail because the
index creation (and thus the table creation) can't be done within a
transaction.
Being able to do DDL within a transaction is one of the coolest and
(from the POV of a DBA) most useful features of the database. If
we're going to eliminate WAL logging of certain operations, it should
be done at the explicit request of the DBA, preferably through a GUC,
and preferably with a relevant GUC for each operation.
Since WAL archiving requires that the operations in question be WAL
logged, the GUCs that control WAL logging of those statements would
clearly be ineffective if WAL archiving is turned on.
--
Kevin Brown ke...@sysexperts.com
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
Alvaro Herrera
> Bruce Momjian wrote:
> >
> > Added to TODO:
> >
> > * Eliminate WAL logging for CREATE INDEX/REINDEX/CREATE TABLE AS
> > when not doing WAL archiving
>
> But that means that these operations can't be performed in a
> transaction unless WAL archiving is on, right?
No, because we ensure the transactionality of the operation by fsync'ing
the involved files. And the rollback-ability is ensured by registering
the files for deletion at rollback. (Just as we register the files for
deletion at commit in case of a DROP TABLE, to make sure we can roll it
back.)
So don't worry, because what you want to do still works.
--
Alvaro Herrera (<alvherre[@]dcc.uchile.cl>)
"En las profundidades de nuestro inconsciente hay una obsesiva necesidad
de un universo lógico y coherente. Pero el universo real se halla siempre
un paso más allá de la lógica" (Irulan)
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majo...@postgresql.org so that your
message can get through to the mailing list cleanly
Simon Riggs
> Bruce Momjian <pg...@candle.pha.pa.us> writes:
> > Greg Stark wrote:
> >> I think that's already done for CREATE INDEX/REINDEX.
>
> > I don't think so. Can someone confirm?
>
> Greg is correct --- at least for btree build, which is the only index
> type we have WAL-ified at all :-(
[well...at least they're optimized then... :) ]
With regard to the other index types, my opinion was:
HASH - works OK, but a pain to administer, no huge benefit in using
R-TREE - slightly broken in places, limited in usablity
GiST - index of choice for PostGIS, TSearch2, in need of optimization
Following recent optimization work on GiST, it now seems worth the
trouble to add WAL logging to it. ISTM that the other two aren't widely
used enough to make it worthwhile to spend time on, evidence for which
is also that no one ever has, up 'til now.
Time-management seems to be the key to making progress in the most
important areas...
--
Best Regards, Simon Riggs
Simon Riggs
> > Greg Stark wrote:
> >> I think that's already done for CREATE INDEX/REINDEX.
>
> > I don't think so. Can someone confirm?
>
> Greg is correct --- at least for btree build, which is the only index
> type we have WAL-ified at all :-(
>
Is there a place (or a single best place) to document this behaviour?
- with each command?
- in the backup section?
- in runtime?
Seems a shame to optimize and not tell anyone.
--
Best Regards, Simon Riggs
---------------------------(end of broadcast)---------------------------
Tom Lane
> On Fri, 2004-11-12 at 23:13, Tom Lane wrote:
>> Greg is correct --- at least for btree build, which is the only index
>> type we have WAL-ified at all :-(
> Is there a place (or a single best place) to document this behaviour?
If you're talking about the lack of WAL backup for non-btree indexes,
it is documented (at the end of the PITR section IIRC).
If you're talking about the optimization of not logging index builds,
I don't see a need to document that per se. Ordinary users shouldn't
need to care, mainly because they can't affect it one way or the other.
Anyone who does care can look at the code and see how it's done. (Open
source has a big advantage over closed source in that regard, and I
think it's reasonable to have different documentation practices than
closed-source products would use.)
regards, tom lane
---------------------------(end of broadcast)---------------------------
Neil Conway
> HASH - works OK, but a pain to administer, no huge benefit in using
At least in theory, I think this could offer better performance for
equality searches than b+-tree. Given how common those kinds of queries
are, I still think hash indexes are worth putting some time into. My
guess is that their relatively poor performance at present (relative to
b+-trees) is just a reflection of how much more tuning and design work
has gone into the b+-tree code than the hash code.
> R-TREE - slightly broken in places, limited in usablity
I agree. I hope that when we have a good GiST infrastructure,
implementing rtree via GiST will offer performance that is as good as or
better than the builtin rtree.
> GiST - index of choice for PostGIS, TSearch2, in need of optimization
I'm working on adding page-level locking and WAL safety, although this
is a pretty difficult project. Gavin and I are also looking at
algorithms for bulk loading GiST indexes, although I'm not yet sure how
possible that will be.
-Neil
Simon Riggs
> On Sun, 2004-11-14 at 11:06 +0000, Simon Riggs wrote:
> > HASH - works OK, but a pain to administer, no huge benefit in using
>
> At least in theory, I think this could offer better performance for
> equality searches than b+-tree. Given how common those kinds of queries
> are, I still think hash indexes are worth putting some time into. My
> guess is that their relatively poor performance at present (relative to
> b+-trees) is just a reflection of how much more tuning and design work
> has gone into the b+-tree code than the hash code.
Can be faster for equality searches on a fairly static table; on a
growing table, could be same or worse. IMHO The theoretical difference
in speed doesn't seem worth the effort of spending additional time in
that part of the code, given the inherent pain of REINDEX.
> > GiST - index of choice for PostGIS, TSearch2, in need of optimization
>
> I'm working on adding page-level locking and WAL safety, although this
> is a pretty difficult project.
Difficult, yes. I'm glad you're stepping up to the plate for the WAL
safety.
Two index types is sufficient, and ISTM should be the maximum therefore.
When you've finished tuning GiST, I wager that you will agree :)
--
Best Regards, Simon Riggs
---------------------------(end of broadcast)---------------------------