pg.ddx.io pgsql-hackers@postgresql.org mailing list archive
help / color / mirror / Atom feedsequence indexes
15+ messages / 7 participants
[nested] [flat]
* sequence indexes
@ 2002-01-25 20:24 Vince Vielhaber <vev@michvhf.com>
0 siblings, 1 reply; 15+ messages in thread
From: Vince Vielhaber @ 2002-01-25 20:24 UTC (permalink / raw)
To: pgsql-hackers
Is it safe to drop and recreate an index used by a sequence? Over
three databases I have these key indexes taking up about a gig of
disk space and I need to free it up (since the partition is getting
rather full).
Vince.
--
==========================================================================
Vince Vielhaber -- KA8CSH email: vev@michvhf.com http://www.pop4.net
56K Nationwide Dialup from $16.00/mo at Pop4 Networking
Online Campground Directory http://www.camping-usa.com
Online Giftshop Superstore http://www.cloudninegifts.com
==========================================================================
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: sequence indexes
@ 2002-01-25 20:39 Tom Lane <tgl@sss.pgh.pa.us>
parent: Vince Vielhaber <vev@michvhf.com>
0 siblings, 1 reply; 15+ messages in thread
From: Tom Lane @ 2002-01-25 20:39 UTC (permalink / raw)
To: Vince Vielhaber <vev@michvhf.com>; +Cc: pgsql-hackers
Vince Vielhaber <vev@michvhf.com> writes:
> Is it safe to drop and recreate an index used by a sequence?
Uh, sequences haven't got indexes. You mean an index on a "serial"
column, no? Sure, there's no magic there. Don't forget it's a
unique index, though, if you want to have the same error checking
as before.
regards, tom lane
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: sequence indexes
@ 2002-01-25 20:49 Vince Vielhaber <vev@michvhf.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 15+ messages in thread
From: Vince Vielhaber @ 2002-01-25 20:49 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-hackers
On Fri, 25 Jan 2002, Tom Lane wrote:
> Vince Vielhaber <vev@michvhf.com> writes:
> > Is it safe to drop and recreate an index used by a sequence?
>
> Uh, sequences haven't got indexes. You mean an index on a "serial"
> column, no? Sure, there's no magic there. Don't forget it's a
> unique index, though, if you want to have the same error checking
> as before.
It's a serial column.
| vev | newclaim_newclaimid_key | index |
| vev | newclaim_newclaimid_seq | sequence |
577527808 Jan 25 14:50 newclaim_newclaimid_key
select count(*) from newclaim;
count
-----
53747
(1 row)
select max(newclaimid) from newclaim;
max
-------
9907663
(1 row)
A bit much diskspace for that, isn't it? The data turns over alot.
Vince.
--
==========================================================================
Vince Vielhaber -- KA8CSH email: vev@michvhf.com http://www.pop4.net
56K Nationwide Dialup from $16.00/mo at Pop4 Networking
Online Campground Directory http://www.camping-usa.com
Online Giftshop Superstore http://www.cloudninegifts.com
==========================================================================
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: sequence indexes
@ 2002-01-25 20:56 Tom Lane <tgl@sss.pgh.pa.us>
parent: Vince Vielhaber <vev@michvhf.com>
0 siblings, 1 reply; 15+ messages in thread
From: Tom Lane @ 2002-01-25 20:56 UTC (permalink / raw)
To: Vince Vielhaber <vev@michvhf.com>; +Cc: pgsql-hackers
Vince Vielhaber <vev@michvhf.com> writes:
> A bit much diskspace for that, isn't it? The data turns over alot.
Yeah, this is one of the scenarios where we desperately need index
compaction. The index pages holding the lower serial numbers are
no doubt empty or nearly so, but there's no mechanism for reclaiming
that space short of rebuilding the index. (BTW you might consider
REINDEX instead of a manual drop/recreate.)
I've looked at the problem a little bit --- there's literature more
recent than Lehmann-Yao that talks about how to do btree compaction
without losing concurrency. But it didn't get done for 7.2.
regards, tom lane
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: sequence indexes
@ 2002-01-25 21:00 Vince Vielhaber <vev@michvhf.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 15+ messages in thread
From: Vince Vielhaber @ 2002-01-25 21:00 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-hackers
On Fri, 25 Jan 2002, Tom Lane wrote:
> Vince Vielhaber <vev@michvhf.com> writes:
> > A bit much diskspace for that, isn't it? The data turns over alot.
>
> Yeah, this is one of the scenarios where we desperately need index
> compaction. The index pages holding the lower serial numbers are
> no doubt empty or nearly so, but there's no mechanism for reclaiming
> that space short of rebuilding the index. (BTW you might consider
> REINDEX instead of a manual drop/recreate.)
I'm guessing reindex wasn't in 6.5.3. :(
Vince.
--
==========================================================================
Vince Vielhaber -- KA8CSH email: vev@michvhf.com http://www.pop4.net
56K Nationwide Dialup from $16.00/mo at Pop4 Networking
Online Campground Directory http://www.camping-usa.com
Online Giftshop Superstore http://www.cloudninegifts.com
==========================================================================
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: sequence indexes
@ 2002-01-25 21:01 Tom Lane <tgl@sss.pgh.pa.us>
parent: Vince Vielhaber <vev@michvhf.com>
0 siblings, 1 reply; 15+ messages in thread
From: Tom Lane @ 2002-01-25 21:01 UTC (permalink / raw)
To: Vince Vielhaber <vev@michvhf.com>; +Cc: pgsql-hackers
Vince Vielhaber <vev@michvhf.com> writes:
> I'm guessing reindex wasn't in 6.5.3. :(
Vince, surely you know better than to still be running 6.5.3 :-(
regards, tom lane
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: sequence indexes
@ 2002-01-25 21:02 Vince Vielhaber <vev@michvhf.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 15+ messages in thread
From: Vince Vielhaber @ 2002-01-25 21:02 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-hackers
On Fri, 25 Jan 2002, Tom Lane wrote:
> Vince Vielhaber <vev@michvhf.com> writes:
> > I'm guessing reindex wasn't in 6.5.3. :(
>
> Vince, surely you know better than to still be running 6.5.3 :-(
Actually until just a few minutes ago I thought it was 7.1.3, guess
this thing will be getting upgraded next month.
Vince.
--
==========================================================================
Vince Vielhaber -- KA8CSH email: vev@michvhf.com http://www.pop4.net
56K Nationwide Dialup from $16.00/mo at Pop4 Networking
Online Campground Directory http://www.camping-usa.com
Online Giftshop Superstore http://www.cloudninegifts.com
==========================================================================
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: sequence indexes
@ 2002-01-25 21:55 Zeugswetter Andreas SB SD <ZeugswetterA@spardat.at>
0 siblings, 1 reply; 15+ messages in thread
From: Zeugswetter Andreas SB SD @ 2002-01-25 21:55 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; Vince Vielhaber <vev@michvhf.com>; +Cc: pgsql-hackers
> I've looked at the problem a little bit --- there's literature more
> recent than Lehmann-Yao that talks about how to do btree compaction
> without losing concurrency. But it didn't get done for 7.2.
Yes, there must be. Informix handles this case perfectly.
(It uses a background btree cleaner)
Andreas
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: sequence indexes
@ 2002-01-25 22:05 Tom Lane <tgl@sss.pgh.pa.us>
parent: Zeugswetter Andreas SB SD <ZeugswetterA@spardat.at>
0 siblings, 1 reply; 15+ messages in thread
From: Tom Lane @ 2002-01-25 22:05 UTC (permalink / raw)
To: Zeugswetter Andreas SB SD <ZeugswetterA@spardat.at>; +Cc: Vince Vielhaber <vev@michvhf.com>; pgsql-hackers
"Zeugswetter Andreas SB SD" <ZeugswetterA@spardat.at> writes:
>> I've looked at the problem a little bit --- there's literature more
>> recent than Lehmann-Yao that talks about how to do btree compaction
>> without losing concurrency. But it didn't get done for 7.2.
> Yes, there must be. Informix handles this case perfectly.
> (It uses a background btree cleaner)
Right, I had hoped to fold it into lazy VACUUM, but ran out of time.
(Of course, had I known in August that we'd still not have released
7.2 by now, I might have kept after it :-()
regards, tom lane
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: sequence indexes
@ 2002-01-26 08:08 Justin Clift <justin@postgresql.org>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 15+ messages in thread
From: Justin Clift @ 2002-01-26 08:08 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Zeugswetter Andreas SB SD <ZeugswetterA@spardat.at>; Vince Vielhaber <vev@michvhf.com>; pgsql-hackers
Tom Lane wrote:
>
> "Zeugswetter Andreas SB SD" <ZeugswetterA@spardat.at> writes:
> >> I've looked at the problem a little bit --- there's literature more
> >> recent than Lehmann-Yao that talks about how to do btree compaction
> >> without losing concurrency. But it didn't get done for 7.2.
>
> > Yes, there must be. Informix handles this case perfectly.
> > (It uses a background btree cleaner)
As an idle thought, I wonder what other maintenance tasks we could have
a process in the background automatically doing when system activity is
low ?
Maintenance
***********
- Index compaction
- Vacuum of various flavours
Tuning
******
- cpu_tuple costings (and similar) recalculation(s)
Can't think of anything else off the top of my head though.
Regards and best wishes,
Justin Clift
>
> Right, I had hoped to fold it into lazy VACUUM, but ran out of time.
> (Of course, had I known in August that we'd still not have released
> 7.2 by now, I might have kept after it :-()
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
--
"My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there."
- Indira Gandhi
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: sequence indexes
@ 2002-01-28 20:30 mlw <markw@mohawksoft.com>
parent: Justin Clift <justin@postgresql.org>
0 siblings, 1 reply; 15+ messages in thread
From: mlw @ 2002-01-28 20:30 UTC (permalink / raw)
To: Justin Clift <justin@postgresql.org>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Zeugswetter Andreas SB SD <ZeugswetterA@spardat.at>; Vince Vielhaber <vev@michvhf.com>; pgsql-hackers
Justin Clift wrote:
>
> Tom Lane wrote:
> >
> > "Zeugswetter Andreas SB SD" <ZeugswetterA@spardat.at> writes:
> > >> I've looked at the problem a little bit --- there's literature more
> > >> recent than Lehmann-Yao that talks about how to do btree compaction
> > >> without losing concurrency. But it didn't get done for 7.2.
> >
> > > Yes, there must be. Informix handles this case perfectly.
> > > (It uses a background btree cleaner)
>
> As an idle thought, I wonder what other maintenance tasks we could have
> a process in the background automatically doing when system activity is
> low ?
>
> Maintenance
> ***********
> - Index compaction
> - Vacuum of various flavours
I had a couple thoughts about index compaction and vacuum in the
background:
Could one run a postgresql process in a lower priority process and
perform lazy vacuums without affecting performance all that much?
A live index compaction can be done by indexing the table with a
temporary name rename the old index, rename the new index to the old
name, and drop the old index.
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: sequence indexes
@ 2002-01-29 07:34 Hannu Krosing <hannu@tm.ee>
parent: mlw <markw@mohawksoft.com>
0 siblings, 1 reply; 15+ messages in thread
From: Hannu Krosing @ 2002-01-29 07:34 UTC (permalink / raw)
To: mlw <markw@mohawksoft.com>; +Cc: Justin Clift <justin@postgresql.org>; Tom Lane <tgl@sss.pgh.pa.us>; Zeugswetter Andreas SB SD <ZeugswetterA@spardat.at>; Vince Vielhaber <vev@michvhf.com>; pgsql-hackers
mlw wrote:
>
>
> Could one run a postgresql process in a lower priority process and
> perform lazy vacuums without affecting performance all that much?
One must be very careful not to introduce reverse priority problems -
i.e. a
lower priority process locking some resource and then not letting go
while
higher priority processes are blocked from running due to needing that
lock.
In my tests 1 vacuum process slowed down 100 concurrent pgbench
processes
by ~2 times.
> A live index compaction can be done by indexing the table with a
> temporary name rename the old index, rename the new index to the old
> name, and drop the old index.
Isn't this what REINDEX command does ?
---------------
Hannu
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: sequence indexes
@ 2002-01-29 12:43 mlw <markw@mohawksoft.com>
parent: Hannu Krosing <hannu@tm.ee>
0 siblings, 2 replies; 15+ messages in thread
From: mlw @ 2002-01-29 12:43 UTC (permalink / raw)
To: Hannu Krosing <hannu@tm.ee>; +Cc: Justin Clift <justin@postgresql.org>; Tom Lane <tgl@sss.pgh.pa.us>; Zeugswetter Andreas SB SD <ZeugswetterA@spardat.at>; Vince Vielhaber <vev@michvhf.com>; pgsql-hackers
Hannu Krosing wrote:
>
> mlw wrote:
> >
> >
> > Could one run a postgresql process in a lower priority process and
> > perform lazy vacuums without affecting performance all that much?
>
> One must be very careful not to introduce reverse priority problems -
> i.e. a
> lower priority process locking some resource and then not letting go
> while
> higher priority processes are blocked from running due to needing that
> lock.
I understand that, hmm. I wonder if the lock code could boost the priority of a
process which owns a lock.
>
> In my tests 1 vacuum process slowed down 100 concurrent pgbench
> processes
> by ~2 times.
Is that good or bad?
>
> > A live index compaction can be done by indexing the table with a
> > temporary name rename the old index, rename the new index to the old
> > name, and drop the old index.
>
> Isn't this what REINDEX command does ?
REINDEX can't be run on a live system, can it?
>
> ---------------
> Hannu
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: sequence indexes
@ 2002-01-29 13:17 Hannu Krosing <hannu@tm.ee>
parent: mlw <markw@mohawksoft.com>
1 sibling, 0 replies; 15+ messages in thread
From: Hannu Krosing @ 2002-01-29 13:17 UTC (permalink / raw)
To: mlw <markw@mohawksoft.com>; +Cc: Justin Clift <justin@postgresql.org>; Tom Lane <tgl@sss.pgh.pa.us>; Zeugswetter Andreas SB SD <ZeugswetterA@spardat.at>; Vince Vielhaber <vev@michvhf.com>; pgsql-hackers
mlw wrote:
>
> Hannu Krosing wrote:
> >
> > mlw wrote:
> > >
> > >
> > > Could one run a postgresql process in a lower priority process and
> > > perform lazy vacuums without affecting performance all that much?
> >
> > One must be very careful not to introduce reverse priority problems -
> > i.e. a
> > lower priority process locking some resource and then not letting go
> > while
> > higher priority processes are blocked from running due to needing that
> > lock.
> I understand that, hmm. I wonder if the lock code could boost the priority of a
> process which owns a lock.
>
> >
> > In my tests 1 vacuum process slowed down 100 concurrent pgbench
> > processes
> > by ~2 times.
>
> Is that good or bad?
I had hoped it to take somewhat proportional time, i.e. slow other
backends
down by 1/100.
> > > A live index compaction can be done by indexing the table with a
> > > temporary name rename the old index, rename the new index to the old
> > > name, and drop the old index.
> >
> > Isn't this what REINDEX command does ?
>
> REINDEX can't be run on a live system, can it?
It will probably lock something, but otherways I don't say why it can't.
You may have to add FORCE to the end of command thus:
reindex table tablename force;
-------------
Hannu
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: sequence indexes
@ 2002-01-29 16:28 Ross J. Reedstrom <reedstrm@rice.edu>
parent: mlw <markw@mohawksoft.com>
1 sibling, 0 replies; 15+ messages in thread
From: Ross J. Reedstrom @ 2002-01-29 16:28 UTC (permalink / raw)
To: mlw <markw@mohawksoft.com>; +Cc: Hannu Krosing <hannu@tm.ee>; Justin Clift <justin@postgresql.org>; Tom Lane <tgl@sss.pgh.pa.us>; Zeugswetter Andreas SB SD <ZeugswetterA@spardat.at>; Vince Vielhaber <vev@michvhf.com>; pgsql-hackers
On Tue, Jan 29, 2002 at 07:43:52AM -0500, mlw wrote:
> Hannu Krosing wrote:
> >
> > mlw wrote:
> >
> > One must be very careful not to introduce reverse priority problems -
> > i.e. a
> > lower priority process locking some resource and then not letting go
> > while
> > higher priority processes are blocked from running due to needing that
> > lock.
> I understand that, hmm. I wonder if the lock code could boost the priority of a
> process which owns a lock.
>
The classic approach to solving priority inversion is to allow for
priority inheritance: that is, the low-priority process stays low
priority, even when it locks a resource, until there is contention for
that resource from a higher priority process: then it inherits the higher
priority of the waiting process.
Ross
^ permalink raw reply [nested|flat] 15+ messages in thread
end of thread, other threads:[~2002-01-29 16:28 UTC | newest]
Thread overview: 15+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-01-25 20:24 sequence indexes Vince Vielhaber <vev@michvhf.com>
2002-01-25 20:39 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-01-25 20:49 ` Vince Vielhaber <vev@michvhf.com>
2002-01-25 20:56 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-01-25 21:00 ` Vince Vielhaber <vev@michvhf.com>
2002-01-25 21:01 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-01-25 21:02 ` Vince Vielhaber <vev@michvhf.com>
2002-01-25 21:55 Re: sequence indexes Zeugswetter Andreas SB SD <ZeugswetterA@spardat.at>
2002-01-25 22:05 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-01-26 08:08 ` Justin Clift <justin@postgresql.org>
2002-01-28 20:30 ` mlw <markw@mohawksoft.com>
2002-01-29 07:34 ` Hannu Krosing <hannu@tm.ee>
2002-01-29 12:43 ` mlw <markw@mohawksoft.com>
2002-01-29 13:17 ` Hannu Krosing <hannu@tm.ee>
2002-01-29 16:28 ` Ross J. Reedstrom <reedstrm@rice.edu>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox