473,842 Members | 1,896 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Does REINDEX TABLE lock the table?

I apologize if this is a RTFM question, but I have not been able
to find a definitive answer elsewhere.

Does a "REINDEX TABLE" lock the table while it is working?
Can applications write data to the table during the REINDEX?

I am working on a real-time application that handles over 2 million
"events" per day and runs 7-by-24. Most of these events involve an
UPDATE to one or two tables, though a small subset involve a bit
more work. Naturally, I am performing VACUUM nearly constantly and
VACUUM FULL rather more often than I'd prefer. Anyway, testing has
shown that certain important queries happen a LOT faster shortly
after a REINDEX on the relevant tables. Apparently, the frequent
UPDATES are battering the indices to the point where their
performance degrades. I am currently doing
a REINDEX once-per-day (during a relative lull in the event rate),
but would like to do it more often. The problem is that at the
high event rate I need to process, I can't afford to have my
application stall while waiting for a REINDEX. Will it do so?

Thanks in advance for your help.
Nov 11 '05 #1
2 11363
G.W. Lucas wrote:
I apologize if this is a RTFM question, but I have not been able
to find a definitive answer elsewhere.

Does a "REINDEX TABLE" lock the table while it is working?
Can applications write data to the table during the REINDEX?

I am working on a real-time application that handles over 2 million
"events" per day and runs 7-by-24. Most of these events involve an
UPDATE to one or two tables, though a small subset involve a bit
more work. Naturally, I am performing VACUUM nearly constantly and
VACUUM FULL rather more often than I'd prefer. Anyway, testing has
shown that certain important queries happen a LOT faster shortly
after a REINDEX on the relevant tables. Apparently, the frequent
UPDATES are battering the indices to the point where their
performance degrades. I am currently doing
a REINDEX once-per-day (during a relative lull in the event rate),
but would like to do it more often. The problem is that at the
high event rate I need to process, I can't afford to have my
application stall while waiting for a REINDEX. Will it do so?

Thanks in advance for your help.


According to the FM, REINDEX is used to rebuild corrupted indexes, so it
does indeed lock the table. The documentation also suggests that, unless
you need the index you're trying to rebuild, you may be better off just
deleting the index and creating a new one - I'm not sure if this locks
the table or not, but it may be worth a try.

Here is the relevant section of REINDEX documentation:
'Note: Another approach to dealing with a corrupted user-table index is
just to drop and recreate it. This may in fact be preferable if you
would like to maintain some semblance of normal operation on the table
meanwhile. REINDEX acquires exclusive lock on the table, while CREATE
INDEX only locks out writes not reads of the table. '

I would recommend that you look at installing pgAdminII on a windows box
for it's superior help features, if for nothing else. It includes
documentation for version 7.3 and it is very easy to find relevant
documentation when you need it.

HTH
Ron

Nov 11 '05 #2
Ron,

Thanks for your help. Following your lead, I found the FM at

http://www.postgresql.com/docs/7.3/s...-commands.html

and it was very helpful. Serves me right for looking in books.

It's funny how these things work out. Looking at the text there
was a suggestion to "see the section on Routine Reindexing" (section 8.3)
which provided a very good description about why reindexing might
be required. I've quoted it below. Looking at the discussion,
I began to wonder if PostgreSQL's particular B-tree implementation might
show a performance degradation if you indexed a column that was
steadily increasing (just as some sorts break down when you try
to sort a nearly-ordered list). The main field I'm indexing
on is a SERIAL PRIMARY KEY (also used as a foreign key in a number
of child tables). Since the table implements a sequence and uses
it as the primary key, the indexed element is almost by definition
monotonically increasing... something that may not be healthy for
an intensely accessed index.

Anyway, I made a quick hack to use something else as a key
and early testing looks like it gains me a non-trivial performance
boost. Of course I'm NOT SURE YET, but it looks promising.
I'll have to do a bit more testing and implement a proper design.
So thanks again for your suggestion. It may have paid off even
better than I'd hoped.

Gary

Here's the text cited above
PostgreSQL is unable to reuse B-tree index pages in certain cases. The
problem is that if indexed rows are deleted, those index pages can only be
reused by rows with similar values. For example, if indexed rows are deleted
and newly inserted/updated rows have much higher values, the new rows can't
use the index space made available by the deleted rows. Instead, such new
rows must be placed on new index pages. In such cases, disk space used by the
index will grow indefinitely, even if VACUUM is run frequently.

rstp <rs**@linuxwave s.com> wrote in message news:<3F******* *******@linuxwa ves.com>... G.W. Lucas wrote:
I apologize if this is a RTFM question, but I have not been able
to find a definitive answer elsewhere.

Does a "REINDEX TABLE" lock the table while it is working?
Can applications write data to the table during the REINDEX?

I am working on a real-time application that handles over 2 million
"events" per day and runs 7-by-24. Most of these events involve an
UPDATE to one or two tables, though a small subset involve a bit
more work. Naturally, I am performing VACUUM nearly constantly and
VACUUM FULL rather more often than I'd prefer. Anyway, testing has
shown that certain important queries happen a LOT faster shortly
after a REINDEX on the relevant tables. Apparently, the frequent
UPDATES are battering the indices to the point where their
performance degrades. I am currently doing
a REINDEX once-per-day (during a relative lull in the event rate),
but would like to do it more often. The problem is that at the
high event rate I need to process, I can't afford to have my
application stall while waiting for a REINDEX. Will it do so?

Thanks in advance for your help.


According to the FM, REINDEX is used to rebuild corrupted indexes, so it
does indeed lock the table. The documentation also suggests that, unless
you need the index you're trying to rebuild, you may be better off just
deleting the index and creating a new one - I'm not sure if this locks
the table or not, but it may be worth a try.

Here is the relevant section of REINDEX documentation:
'Note: Another approach to dealing with a corrupted user-table index is
just to drop and recreate it. This may in fact be preferable if you
would like to maintain some semblance of normal operation on the table
meanwhile. REINDEX acquires exclusive lock on the table, while CREATE
INDEX only locks out writes not reads of the table. '

I would recommend that you look at installing pgAdminII on a windows box
for it's superior help features, if for nothing else. It includes
documentation for version 7.3 and it is very easy to find relevant
documentation when you need it.

HTH
Ron

Nov 11 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
22543
by: Matt Liverance | last post by:
I REALLY dont want to switch to oracle :( but I cant get these tables working any faster. I've got 2 dedicated servers, each with a slave, all run 32gig 15k rpm raid 5 on u320 perc raid cards, dell 2600/4600's with single channel backplanes (new ones will have dual channel) All have 2 gig of ram, but I've never seen mysql use more than 300mb of ram.
0
1767
by: robberjohn | last post by:
Hello all, I have a SQL 2000 Standard SP3 install. I was running low on free space so added a secondary data file on a separate hard drive. I did this for each of two databases. Since then, and I guess it could be only a coincidence, my weekly reindex maintenance plan has been failing, but only for one of the two databases I added the secondary file to. The 1st weeks emailed report shows that it rebuilds indices for 12 tables and then...
1
2403
by: Sally Sally | last post by:
I just started a REINDEX on a non-system table because I was getting "failed to re-find parent key" while analyzing. I realized a little too late that the docs said the pg server should have been running as a standalone. Is this something I should worry about? It's taking a bit of a while too! Thanx Sally _________________________________________________________________ Stay informed on Election 2004 and the race to Super Tuesday....
5
7186
by: Clodoaldo Pinto Neto | last post by:
I suspect there is something wrong because it takes 73s to delete a single line from a table whith 140 lines. So I tried to reindex the database: bash-2.05b$ postgres -P -O -D /var/lib/pgsql/data KakaoStats LOG: database system was shut down at 2004-04-11 19:55:10 BRT LOG: checkpoint record is at 7/9505D650 LOG: redo record is at 7/9505D650; undo record is at 0/0; shutdown TRUE LOG: next transaction id: 24438; next oid: 1850892 LOG:...
5
3438
by: Edmund Dengler | last post by:
Howdy all! Quick question regarding REINDEX. I have a large table with 1077455 rows. I have an index on the table with a WHERE clause that limits the rows to around 10-50. When I REINDEX this index, it takes on the order of a minute to do the reindex. Question: Does a REINDEX use the old version to build the new version, or is it the semantic equivalent to "drop index; build new index". If the first, any thoughts on why it is taking so...
1
4154
by: Ulrich Wisser | last post by:
Hello, the maintainance chapter in the docs gave me the impression that a reindex of some tables would be a good idea. Last night I took the database down, started up in single user mode and tried to reindex. The reindex of tables did work fine, but a reindex of the database did not. Please see a session transscript below. How can I reindex the database?
22
18821
by: RayPower | last post by:
I'm having problem with using DAO recordset to append record into a table and subsequent code to update other tables in a transaction. The MDB is Access 2000 with the latest service pack of JET 4. The system is client/server, multiusers based. The MDBs are using record locking. Here is part of the code: Dim wkSpace As Workspace, db As Database Dim rstTrans As DAO.Recordset Set wkSpace = DBEngine.Workspaces(0)
3
8067
by: Raziq Shekha | last post by:
Hi Folks, SQL Server 2000 SP3 on Windows 2000. I have a database on which I ran the command : dbcc dbreindex ('tablename') go for all tables in the database. Then I compared the dbcc showcontig with all_index output from before and after the reindex and on the
5
3852
by: wugon.net | last post by:
question: db2 LUW V8 UNION ALL with table function month() have bad query performance Env: db2 LUW V8 + FP14 Problem : We have history data from 2005/01/01 ~ 2007/05/xx in single big table, we try separate this big table into twelve tables and create a view
0
9865
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9709
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10936
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10609
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
10303
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
7853
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7025
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5882
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
3
3140
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.