473,322 Members | 1,232 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

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 11323
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**@linuxwaves.com> wrote in message news:<3F**************@linuxwaves.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
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...
0
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...
1
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...
5
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...
5
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...
1
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...
22
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....
3
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...
5
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...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

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.