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 alexandros...@gmail.com 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
Re: [GENERAL] reindexing
On Mon, Feb 7, 2011 at 17:12, akp geek akpg...@gmail.com 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
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 bada...@gmail.com wrote: On Mon, Feb 7, 2011 at 17:12, akp geek akpg...@gmail.com 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:28, akp geek akpg...@gmail.com 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
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
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
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
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
-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
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
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
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
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 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
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
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 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
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
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]
