Re: [GENERAL] Reindexing and tablespaces
You were right. I just reindexed the DB and I saw no real changes in drive storage (those tablespaces are on separate hard disk volumes). Thanks -- View this message in context: http://postgresql.1045698.n5.nabble.com/Reindexing-and-tablespaces-tp5789827p5789853.html Sent from the PostgreSQL - general mailing list archive at Nabble.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] Reindexing and tablespaces
On Fri, Jan 31, 2014 at 1:24 PM, alexandros_e wrote: > Hello to all, > > I have done ALTER DATABASE "[database_name]" SET default_tablespace = > [new_tablespace]; I am wondering, if I reindex this entire DB would the > indexes automatically moved into the [new_tablespace] or will they remain in > the tablespace they were originally created on; They remain on the tablespace they have been created on. Note as well that the new value of default_tablespace takes effect only after reloading the session. Regards, -- Michael -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Reindexing and tablespaces
Hello to all, I have done ALTER DATABASE "[database_name]" SET default_tablespace = [new_tablespace]; I am wondering, if I reindex this entire DB would the indexes automatically moved into the [new_tablespace] or will they remain in the tablespace they were originally created on; -- View this message in context: http://postgresql.1045698.n5.nabble.com/Reindexing-and-tablespaces-tp5789827.html Sent from the PostgreSQL - general mailing list archive at Nabble.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] reindexing
On Mon, Feb 7, 2011 at 17:28, akp geek wrote: > thanks.. the index I was having is gist on a to_tsvector column . version we > have is 8.3 What minor version? I sounds like you _could_ be hitting any of the below: - (8.3.14) Fix detection of page splits in temporary GiST indexes (Heikki Linnakangas) - (8.3.10) Fix incorrect WAL data emitted during end-of-recovery cleanup of a GIST index page split - (8.3.9) Fix incorrect logic for GiST index page splits, when the split depends on a non-first column of the index (Paul Ramsey) - (8.3.6) Fix whole-index GiST scans to work correctly (Teodor) - (8.3.5) Fix GiST index corruption due to marking the wrong index entry "dead" after a deletion (Teodor) ... I got bored of doing your homework after this point. :-( -- 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] reindexing
thanks.. the index I was having is gist on a to_tsvector column . version we have is 8.3 On Mon, Feb 7, 2011 at 7:23 PM, Alex Hunsaker wrote: > On Mon, Feb 7, 2011 at 17:12, akp geek wrote: > > Hi all - > > I ran query this morning, I got a wrong results. I have run the > same > > query in an other environment with same data and I got the result set I > was > > expecting. > >After that I did a re index and on the table I was getting > incorrect > > results, the data then came out fine, > > Do I have to reindex periodically to make sure the data retrieval > > would be correct? > > In general, no. That would be silly. However, if you are using hash > indexes, per the fine manual > (http://www.postgresql.org/docs/9.0/interactive/indexes-types.html): > "Hash index operations are not presently WAL-logged, so hash indexes > might need to be rebuilt with REINDEX after a database crash. They are > also not replicated over streaming or file-based replication. For > these reasons, hash index use is presently discouraged." > > REINDEX will also 'fix' a btree index if it somehow got corrupted. > Depending on the type of corruption, I would expect postgres to > complain (or segfault) in most cases instead of returning the wrong > results. Anything interesting in your server logs? > > Also you failed to note what version of postgres you are using-- its > hard to tell if you are hitting a known bug or not. >
Re: [GENERAL] reindexing
On Mon, Feb 7, 2011 at 17:12, akp geek wrote: > Hi all - > I ran query this morning, I got a wrong results. I have run the same > query in an other environment with same data and I got the result set I was > expecting. > After that I did a re index and on the table I was getting incorrect > results, the data then came out fine, > Do I have to reindex periodically to make sure the data retrieval > would be correct? In general, no. That would be silly. However, if you are using hash indexes, per the fine manual (http://www.postgresql.org/docs/9.0/interactive/indexes-types.html): "Hash index operations are not presently WAL-logged, so hash indexes might need to be rebuilt with REINDEX after a database crash. They are also not replicated over streaming or file-based replication. For these reasons, hash index use is presently discouraged." REINDEX will also 'fix' a btree index if it somehow got corrupted. Depending on the type of corruption, I would expect postgres to complain (or segfault) in most cases instead of returning the wrong results. Anything interesting in your server logs? Also you failed to note what version of postgres you are using-- its hard to tell if you are hitting a known bug or not. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] reindexing
Hi all - I ran query this morning, I got a wrong results. I have run the same query in an other environment with same data and I got the result set I was expecting. After that I did a re index and on the table I was getting incorrect results, the data then came out fine, Do I have to reindex periodically to make sure the data retrieval would be correct? Thanks for you help Regards
Re: [GENERAL] reindexing
Greg Smith wrote: If some big-iron shop who is so blind to security issues that they want to keep 7.4 on life support, they certainly can find someone to deliver such a support agreement on a contract basis. But they shouldn't expect the public project to keep them afloat for free, and saying this project "must be ready" to handle them is quite debatable. Given the limited resources of the public volunteers here, supporting ancient versions is a drain it's hard to justify outside the context of such a support agreement. Using your own examples, Oracle and Sun sure don't, why should PostgreSQL? I am not arguing that Postgres, Oracle, Sun or anyone else should have to support such obsolete products, or that they are the only source for that support. I only state the fact that many organizations are slow to move off even obsolete products - this is something I have observed more than once in more than one contract. I only claimed in my post that "we must be ready to deal with that", since it is a fact, not that vendors should have to support those products for free. For example, in my work I deal with that by strongly urging my clients not to use obsolete software, after explaining that the software in question is actually obsolete. They don't always agree with my recommendation, then I deal with that in turn. It's not like they make me their decision maker. I agree that no one should have to support obsolete products for free, and that these organizations should upgrade. -- Lew ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] reindexing
Greg Smith <[EMAIL PROTECTED]> writes: > If some big-iron shop who is so blind to security issues that they want to > keep 7.4 on life support, they certainly can find someone to deliver such > a support agreement on a contract basis. But they shouldn't expect the > public project to keep them afloat for free, and saying this project "must > be ready" to handle them is quite debatable. Well, whether we *must* do it or not is arguable; but the point in this thread is that we *do* do it. The 7.4 branch is up to 7.4.19. But the OP was (apparently) still running 7.4.1, and his failure to take advantage of that free support was what I was lecturing him about ;-) regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] reindexing
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Wed, 27 Feb 2008 11:28:32 -0500 Lew <[EMAIL PROTECTED]> wrote: > In my experience at various "big-iron" shops (government agencies, > large health-care organizations and the like), four years is not a > long time for enterprise software - a version often has to be at > least four years old before the powers-that-be decide to try it. One > has only to look at how many organizations still use Oracle 8, or > Java 1.3, for example, to see how conservative many shops are with > respect to upgrades. Yes but Tom wasn't talking about upgrades. He was talking about maintenance. You can bet that any respectable enterprise shop is at least running the latest service packs for the respective releases. The community does support 7.4 still. However the version that is supported is service release (or service pack) 19. Thus 7.4.19. > > I'm not saying they should be that conservative, but many > organizations are and we must be ready to deal with that. > And we already do, far more than we should IMO. This idea that the volunteer community should somehow provide enterprise class support is a non starter. That is what the companies surrounding the community are for. If companies want the community and not the companies surrounding the community to provide that kind of support, those companies need to start paying for it. Sincerely, Joshua D. Drake - -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL SPI Liaison | SPI Director | PostgreSQL political pundit -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHxbU/ATb/zqfZUUQRAt1hAJ9NCAK6xTQtF6hcI95rwolqlNpXoACdHIoJ IVfVRiN5PTpwxAWH6ohY1us= =L7ov -END PGP SIGNATURE- ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] reindexing
On Wed, 27 Feb 2008, Lew wrote: One has only to look at how many organizations still use Oracle 8, or Java 1.3, for example, to see how conservative many shops are with respect to upgrades. I'm not saying they should be that conservative, but many organizations are and we must be ready to deal with that. Companies that act so conversatively are already getting nailed by lack of support in the public versions of software. For example, in 2007 DST was moved around in the US for no good reason, requiring an update to the Olson Timezone Database. If you're a Java user, and you're on 1.3, you couldn't get that update unless you have a support contract--the free version won't do it. (ref: http://java.sun.com/javase/timezones/DST_faq.html ) Even there only a small subset of platforms are supported. Getting older Oracle versions to work there obviously requires the appropriate support contract to see the Metalink update, and as I can tell only 8.1 was updated, people running 8.0 were left out. If some big-iron shop who is so blind to security issues that they want to keep 7.4 on life support, they certainly can find someone to deliver such a support agreement on a contract basis. But they shouldn't expect the public project to keep them afloat for free, and saying this project "must be ready" to handle them is quite debatable. Given the limited resources of the public volunteers here, supporting ancient versions is a drain it's hard to justify outside the context of such a support agreement. Using your own examples, Oracle and Sun sure don't, why should PostgreSQL? -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] reindexing
Lew <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> There never was a 7.1.4 release, so I suspect the OP meant 7.4.1 >> not that that speaks very much better for his software maintenance >> habits. Even with the more charitable interpretation, it's a version >> that was obsoleted four years ago next week. > In my experience at various "big-iron" shops (government agencies, large > health-care organizations and the like), four years is not a long time for > enterprise software - a version often has to be at least four years old > before > the powers-that-be decide to try it. One has only to look at how many > organizations still use Oracle 8, or Java 1.3, for example, to see how > conservative many shops are with respect to upgrades. This is not equivalent to "still using Oracle 8". This is "still using Oracle 8 and we haven't applied any of Oracle's updates for it". Is it even possible for a shop to do that? I can hardly believe that Oracle would honor a support contract for a version that's missing four years worth of bug fixes. As for the "not wanting to adopt too quickly" argument, why'd they adopt 7.4.1 in the first place? If you're of the view that no software is acceptably stable till it's been out a couple years, you should be using something with a minor number rather higher than 1. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] reindexing
Lew wrote: Tom Lane wrote: There never was a 7.1.4 release, so I suspect the OP meant 7.4.1 not that that speaks very much better for his software maintenance habits. Even with the more charitable interpretation, it's a version that was obsoleted four years ago next week. In my experience at various "big-iron" shops (government agencies, large health-care organizations and the like), four years is not a long time for enterprise software - a version often has to be at least four years old before the powers-that-be decide to try it. One has only to look at how many organizations still use Oracle 8, or Java 1.3, for example, to see how conservative many shops are with respect to upgrades. I'm not saying they should be that conservative, but many organizations are and we must be ready to deal with that. This is completely the opposite of my experience at a very large global financial company. They are extremely concerned with staying current, and in fact audit regulations require it for any software not written in-house. If they were still running Oracle 8, for example, they would fail internal audit precisely because it is no longer a supported Oracle version, and thus security and such patches are no longer available. The same would go for operating system patches, firmware, whatever. The release cycle does tend to be slower (from quarterly to yearly) for, say, things like AIX or z/OS or DB2, but updates are coming out routinely [including security and bug fixes, as well as feature additions], and in my experience these shops are definitely keeping up. The only places I've had direct experience with that tend to run very old versions of things are doing so for all the wrong reasons. They seem to be learning, albeit slowly and painfully, the demerits of not keeping current. Just my $0.02, Paul ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] reindexing
Tom Lane wrote: There never was a 7.1.4 release, so I suspect the OP meant 7.4.1 not that that speaks very much better for his software maintenance habits. Even with the more charitable interpretation, it's a version that was obsoleted four years ago next week. In my experience at various "big-iron" shops (government agencies, large health-care organizations and the like), four years is not a long time for enterprise software - a version often has to be at least four years old before the powers-that-be decide to try it. One has only to look at how many organizations still use Oracle 8, or Java 1.3, for example, to see how conservative many shops are with respect to upgrades. I'm not saying they should be that conservative, but many organizations are and we must be ready to deal with that. -- Lew ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] reindexing
"Scott Marlowe" <[EMAIL PROTECTED]> writes: > On Fri, Feb 22, 2008 at 12:24 PM, LARC/J.L.Shipman/jshipman > <[EMAIL PROTECTED]> wrote: >> I am reindexing my 7.1.4 postgres database. > My pgsql-fu regarding obsolete versions is obsolete. You do realize > that 7.1.x hasn't been supported for a very long time, and for very > good reasons, right? There never was a 7.1.4 release, so I suspect the OP meant 7.4.1 ... not that that speaks very much better for his software maintenance habits. Even with the more charitable interpretation, it's a version that was obsoleted four years ago next week. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] reindexing
On Fri, Feb 22, 2008 at 12:24 PM, LARC/J.L.Shipman/jshipman <[EMAIL PROTECTED]> wrote: > Hi, > I am reindexing my 7.1.4 postgres database. The postmaster seems to > create processes for each reindex request. Is there any way to find > out more about the processes. > > ps -aef | grep postgres > > yields the following, but does not tell me which table is being > reindexed or anything meaningful > about the process. My pgsql-fu regarding obsolete versions is obsolete. You do realize that 7.1.x hasn't been supported for a very long time, and for very good reasons, right? It's not just obsolete in terms of being outdated by more modern versions, but is known to have a few data eating bugs, not to mention the txid wraparound issue. You should be planning on how to upgrade it first, then things like this might be less necessary and / or less of a problem to work with. In later versions of pgsql you've got a stats collector that can do things like tell you what queries are running. As well as autovacuuming and non-full vacuums that make things like reindexing mostly uneeded. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] reindexing
Hi, I am reindexing my 7.1.4 postgres database. The postmaster seems to create processes for each reindex request. Is there any way to find out more about the processes. ps -aef | grep postgres yields the following, but does not tell me which table is being reindexed or anything meaningful about the process. postgres 605 604 0 Feb 18 ? 0:00 /usr/local/pgsql/ bin/postmaster postgres 5599 579 0 Feb 21 ? 39:12 /usr/local/pgsql/ bin/postmaster postgres 20101 579 0 10:56:52 ? 0:58 /usr/local/pgsql/ bin/postmaster postgres 579 1 0 Feb 18 ? 0:02 /usr/local/pgsql/ bin/postmaster postgres 604 579 0 Feb 18 ? 0:00 /usr/local/pgsql/ bin/postmaster dspace 21563 21391 0 13:27:00 pts/3 0:00 grep postgres postgres 5645 579 0 Feb 21 ? 35:29 /usr/local/pgsql/ bin/postmaster postgres 19695 579 0 10:13:22 ? 2:51 /usr/local/pgsql/ bin/postmaster postgres 19713 579 0 10:15:02 ? 2:43 /usr/local/pgsql/ bin/postmaster postgres 7441 579 0 Feb 21 ? 33:49 /usr/local/pgsql/ bin/postmaster postgres 19963 579 0 10:42:25 ? 1:43 /usr/local/pgsql/ bin/postmaster postgres 19658 579 0 10:09:56 ? 2:52 /usr/local/pgsql/ bin/postmaster postgres 19981 579 0 10:44:43 ? 2:20 /usr/local/pgsql/ bin/postmaster postgres 6276 579 0 Feb 21 ? 39:12 /usr/local/pgsql/ bin/postmaster postgres 19667 579 0 10:10:56 ? 2:25 /usr/local/pgsql/ bin/postmaster postgres 5654 579 0 Feb 21 ? 36:36 /usr/local/pgsql/ bin/postmaster postgres 5657 579 20 Feb 21 ? 33:06 /usr/local/pgsql/ bin/postmaster postgres 5656 579 0 Feb 21 ? 39:17 /usr/local/pgsql/ bin/postmaster postgres 6216 579 0 Feb 21 ? 31:02 /usr/local/pgsql/ bin/postmaster postgres 7508 579 0 Feb 21 ? 29:03 /usr/local/pgsql/ bin/postmaster postgres 20159 579 0 11:03:25 ? 2:22 /usr/local/pgsql/ bin/postmaster postgres 6275 579 0 Feb 21 ? 35:12 /usr/local/pgsql/ bin/postmaster postgres 7474 579 0 Feb 21 ? 32:07 /usr/local/pgsql/ bin/postmaster postgres 19884 579 0 10:33:52 ? 1:38 /usr/local/pgsql/ bin/postmaster postgres 5655 579 0 Feb 21 ? 35:42 /usr/local/pgsql/ bin/postmaster postgres 20100 579 0 10:56:43 ? 2:04 /usr/local/pgsql/ bin/postmaster postgres 5598 579 0 Feb 21 ? 40:22 /usr/local/pgsql/ bin/postmaster postgres 20259 579 0 11:15:33 ? 2:04 /usr/local/pgsql/ bin/postmaster postgres 19696 579 19 10:13:57 ? 2:07 /usr/local/pgsql/ bin/postmaster postgres 7509 579 0 Feb 21 ? 34:43 /usr/local/pgsql/ bin/postmaster postgres 19946 579 0 10:40:11 ? 1:12 /usr/local/pgsql/ bin/postmaster postgres 20006 579 0 10:47:06 ? 2:17 /usr/local/pgsql/ bin/postmaster postgres 6258 579 0 Feb 21 ? 40:08 /usr/local/pgsql/ bin/postmaster ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: index bloat WAS: [GENERAL] reindexing pg_shdepend
Michael Fuhr <[EMAIL PROTECTED]> writes: > On Thu, Aug 02, 2007 at 10:40:24PM -0400, Joseph S wrote: >> I do use autovac. Like I said they don't get really out of hand, only >> up to 20 megs or so before I noticed that it was weird. The large >> indexes are what tipped me off that something strange was going on. > Unexpected bloat in pg_shdepend led me to discover a problem with > statistics for shared tables a couple of months ago: > http://archives.postgresql.org/pgsql-hackers/2007-06/msg00190.php > http://archives.postgresql.org/pgsql-hackers/2007-06/msg00245.php Hmm ... that problem would have caused autovac to mostly ignore the shared tables, but in such a scenario you'd expect the table itself and both indexes to all be bloated. The thing that struck me about Joseph's report was that the one index was so much more bloated than the other. The index entries are only slightly larger (3 OIDs not 2) so there's no obvious reason for this. The fact that the indexes are bloated and the table itself not can be explained by a history of manual VACUUM FULLs, but that should have had similar effects on both indexes. We know that vacuum's inability to merge mostly-but-not-entirely-empty index pages can lead to index bloat given a sufficiently unfriendly usage pattern, and I think that must be what happened here, but I'm not clear what that usage pattern is. If we had those details we could possibly work around it by changing the column ordering in the index --- AFAIR there isn't any particular reason for pg_shdepend_depender_index to have one column order rather than another. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: index bloat WAS: [GENERAL] reindexing pg_shdepend
On Thu, Aug 02, 2007 at 10:40:24PM -0400, Joseph S wrote: > Tom Lane wrote: > >Heavy use of temp tables would expand pg_class, pg_type, and especially > >pg_attribute, but as long as you have a decent vacuuming regimen (do you > >use autovac?) they shouldn't get out of hand. > > I do use autovac. Like I said they don't get really out of hand, only > up to 20 megs or so before I noticed that it was weird. The large > indexes are what tipped me off that something strange was going on. Unexpected bloat in pg_shdepend led me to discover a problem with statistics for shared tables a couple of months ago: http://archives.postgresql.org/pgsql-hackers/2007-06/msg00190.php http://archives.postgresql.org/pgsql-hackers/2007-06/msg00245.php > I only noticed this because I was making an effort to monitor index > bloat on my regular tables. It could be there are a lot of people out > there who are experiencing this but don't notice because 20 megs here > and there don't cause any noticeable problems. Anybody making heavy use of temporary tables and relying on autovacuum is probably suffering bloat in pg_shdepend because no released version of PostgreSQL has the fix for the statistics bug (it has been fixed in CVS, however). As I mention in the second message above, vacuuming pg_shdepend resulted in an immediate performance improvement in an application I was investigating. -- Michael Fuhr ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: index bloat WAS: [GENERAL] reindexing pg_shdepend
Joseph S <[EMAIL PROTECTED]> writes: > ... and when I notice that the tuplesperpage for the indexes is low (or > that the indexes are bigger then the tables themselves) I know it is > time for a VACUUM FULL and REINDEX on that table. If you are taking the latter as a blind must-be-wrong condition, you are fooling yourself -- it's not true for small tables. For instance, in a freshly initdb'd database: postgres=# vacuum verbose pg_opclass; INFO: vacuuming "pg_catalog.pg_opclass" INFO: index "pg_opclass_am_name_nsp_index" now contains 107 row versions in 4 pages DETAIL: 0 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.01s/0.00u sec elapsed 0.00 sec. INFO: index "pg_opclass_oid_index" now contains 107 row versions in 2 pages DETAIL: 0 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: "pg_opclass": found 0 removable, 107 nonremovable row versions in 2 pages DETAIL: 0 dead row versions cannot be removed yet. There were 0 unused item pointers. 1 pages contain useful free space. 0 pages are entirely empty. CPU 0.01s/0.00u sec elapsed 0.00 sec. VACUUM postgres=# Have you checked whether the VACUUM FULL + REINDEX actually makes anything smaller? regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
index bloat WAS: [GENERAL] reindexing pg_shdepend
Tom Lane wrote:
Joseph S <[EMAIL PROTECTED]> writes:
Me too. I don't change my db schema that much, but I experience bloat
in the pg_tables that I don't expect. For instance pg_opclass needs a
VACUUM FULL/REINDEX once a week or I notice the indexes are larger than
the table itself. Could it be my heavy use of temp tables?
pg_opclass? That's read-only for most people. What are you doing with
operator classes?
I know. I can't figure it out. I barely know what operator classes
are, but I'm pretty sure I'm not modifying them in any way.
Heavy use of temp tables would expand pg_class, pg_type, and especially
pg_attribute, but as long as you have a decent vacuuming regimen (do you
use autovac?) they shouldn't get out of hand.
I do use autovac. Like I said they don't get really out of hand, only
up to 20 megs or so before I noticed that it was weird. The large
indexes are what tipped me off that something strange was going on.
I only noticed this because I was making an effort to monitor index
bloat on my regular tables. It could be there are a lot of people out
there who are experiencing this but don't notice because 20 megs here
and there don't cause any noticeable problems.
So how about it list? Do you know how bloated your indexes are getting?
I use this sql:
select (select nspname FROM pg_catalog.pg_namespace where oid =
relnamespace) AS schema, relname, CASE c.relkind WHEN 'r' THEN 'table'
WHEN 'v' THEN 'view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN
's' THEN 'special' END as "Type" ,CASE c.relkind IN ('i','r','S','')
WHEN true THEN pg_relation_size(relname) END AS bytes, CASE relpages >
0 WHEN true THEN reltuples/relpages END AS tuplesperpage FROM
pg_catalog.pg_class c WHERE pg_catalog.pg_table_is_visible(c.oid) order
by schema, relname;
... and when I notice that the tuplesperpage for the indexes is low (or
that the indexes are bigger then the tables themselves) I know it is
time for a VACUUM FULL and REINDEX on that table.
If you really want to get fancy you can save the results of that into a
table with a timestamp. Then every (insert time period here) run VACUUM
FULL/REINDEXs on the individual tables and store the new sizes with
timestamps.
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] reindexing pg_shdepend
Me too. I don't change my db schema that much, but I experience bloat in the pg_tables that I don't expect. For instance pg_opclass needs a VACUUM FULL/REINDEX once a week or I notice the indexes are larger than the table itself. Could it be my heavy use of temp tables? Today I noticed that pg_statistic (which I actually expect to be updated in the normal course of operations) was over 20 meg (with large indexes as well) so I gave it a VACUUM FULL/REINDEX and it now stands at 344,064. These tables never get *really* large, so I've never noticed a big performance hit, but they still get bigger than they should be and could be slowing everything down a little. around 3pm today I did a VACUUM FULL/REINDEX of all the trouble tables I have in my list, and I didn't save the before/after sizes, bit it is 9:30 pm now and I can tell you how much they've grown since then: BEFORE VACUUM FULL/REINDEX at 9:30: pg_catalog pg_classtable 196,608 21.526 pg_catalog pg_class_oid_index index 49,152 81.8 pg_catalog pg_class_relname_nsp_index index 172,032 21.526 pg_catalog pg_type table 180,224 15.045 pg_catalog pg_type_oid_index index 40,960 66.2 pg_catalog pg_type_typname_nsp_index index 106,496 25.462 AFTER: pg_catalog pg_classtable 81,920 41.1 pg_catalog pg_class_oid_index index 32,768 102.5 pg_catalog pg_class_relname_nsp_index index 57,344 58.714 pg_catalog pg_type table 65,536 41.375 pg_catalog pg_type_oid_index index 16,384 165.5 pg_catalog pg_type_typname_nsp_index index 49,152 55.167 My apologies for the tabs. That was a cut & paste from a web page I set up to monitor the database size. The columns are: schema relname Type bytes tuplesperpage The list of tables I have in my list are: pg_attribute pg_class pg_depend pg_index pg_shdepend pg_proc pg_statistic pg_type pg_trigger pg_shdepend I put them in my list bec. I once noticed that their indexes seemed big relative to the size of the table itself. I didn't really analyze if they were indeed recurring problems or just one time problems, but I know pg_class and pg_opclass are ones where this is a recurring problem. BTW Tom do you prefer the replies to go to you directly as well as to the list? Most of the time I just hit 'Reply' and since this list doesn't set the Reply-to: the replies go to the OP as well. Tom Lane wrote: Joseph S <[EMAIL PROTECTED]> writes: My pg_shdepend table has a size of 16,384, but pg_shdepend_depender_index has a size of 19,169,280 and pg_shdepend_reference_index has a size of 49,152. I'd be interested to see the usage pattern that made it get like that ... regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] reindexing pg_shdepend
Joseph S <[EMAIL PROTECTED]> writes: > Me too. I don't change my db schema that much, but I experience bloat > in the pg_tables that I don't expect. For instance pg_opclass needs a > VACUUM FULL/REINDEX once a week or I notice the indexes are larger than > the table itself. Could it be my heavy use of temp tables? pg_opclass? That's read-only for most people. What are you doing with operator classes? Heavy use of temp tables would expand pg_class, pg_type, and especially pg_attribute, but as long as you have a decent vacuuming regimen (do you use autovac?) they shouldn't get out of hand. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] reindexing pg_shdepend
Joseph S <[EMAIL PROTECTED]> writes: > My pg_shdepend table has a size of 16,384, but > pg_shdepend_depender_index has a size of 19,169,280 and > pg_shdepend_reference_index has a size of 49,152. I'd be interested to see the usage pattern that made it get like that ... regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] reindexing pg_shdepend
Joseph S wrote: > My pg_shdepend table has a size of 16,384, but pg_shdepend_depender_index > has a size of 19,169,280 and pg_shdepend_reference_index has a size of > 49,152. When I try to reindex the table I get: > > ERROR: shared table "pg_shdepend" can only be reindexed in stand-alone > mode > > So is there any way I can clear this bloat w/o restarting the server? Nope :-( -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] reindexing pg_shdepend
My pg_shdepend table has a size of 16,384, but pg_shdepend_depender_index has a size of 19,169,280 and pg_shdepend_reference_index has a size of 49,152. When I try to reindex the table I get: ERROR: shared table "pg_shdepend" can only be reindexed in stand-alone mode So is there any way I can clear this bloat w/o restarting the server? ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] reindexing keys in postgres
In response to "Harpreet Dhaliwal" <[EMAIL PROTECTED]>: > Hi, > Lately i was searching for a way I could reindex all my keys. Primary Keys > in particular. > Really didn't find any manual that could guide me through. > > Reason i wanted to reindex my PK is that whenever i insert a record in the > table, even though that record is unique, i get an error saying violation of > primary key. > So i thought of reindexing my keys. > > Can anyone give me pointers for the same http://www.postgresql.org/docs/8.2/static/sql-reindex.html -- Bill Moran http://www.potentialtech.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] reindexing keys in postgres
Hi, Lately i was searching for a way I could reindex all my keys. Primary Keys in particular. Really didn't find any manual that could guide me through. Reason i wanted to reindex my PK is that whenever i insert a record in the table, even though that record is unique, i get an error saying violation of primary key. So i thought of reindexing my keys. Can anyone give me pointers for the same Thanks, ~Harpreet
Re: [GENERAL] reindexing sequences
"mike" wrote: >is there a way to reindex a sequence? >if so how and is it in the curent docs? If you wish to reset its value, the function is setval(). -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47 GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "Do not be anxious about anything, but in everything, by prayer and supplication, with thanksgiving, present your requests to God. And the peace of God, which transcends all understanding, will guard your hearts and your minds in Christ Jesus." Philippians 4:6,7 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] reindexing sequences
From: "mike" <[EMAIL PROTECTED]>
is there a way to reindex a sequence?
if so how and is it in the curent docs?
Mike
Reindex a sequence? Not sure what you mean by that. You can set the value to
something else:
select setval('mysequence',12345);
If you mean compact the values used so there aren't any gaps in the
sequence - no there's nothing that does that. There's not really any way for
Postgres to know what you've done with the sequence values.
- Richard Huxton
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] reindexing sequences
"mike" <[EMAIL PROTECTED]> writes: > is there a way to reindex a sequence? Sequences don't have indexes, so they don't need reindexing. regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] reindexing sequences
is there a way to reindex a sequence? if so how and is it in the curent docs? Mike
