Concurrent Reindex on Primary Key for large table
От | rverghese |
---|---|
Тема | Concurrent Reindex on Primary Key for large table |
Дата | |
Msg-id | 1328724449781-5467243.post@n5.nabble.com обсуждение исходный текст |
Ответы |
Re: Concurrent Reindex on Primary Key for large table
|
Список | pgsql-sql |
I have a large table with about 60 million rows, everyday I add 3-4 million, remove 3-4 million and update 1-2 million. I have a script that reindexes concurrently a couple of times a week, since I see significant bloat. I have autovac on and the settings are below. I can't concurrently reindex the primary key, since there can be only one on a table. I can't take the table offline to do a drop/recreate. I assumed the autovac would take care of the bloat there, but I checked the size of the index and dropped it and recreated it and the size went from 2.5GB to 1.3GB and my daily import into that table took almost half the time after recreating the primary key. I was wondering what the options are to reindex it. Should i not have a primary key and just a unique index so that I can concurrently reindex? Do I lose anything by replacing the primary key with a unique index? I prefer to have a primary key on the table for Slony, but I suppose I could make the OID column the primary key just for SLONY purposes. Any thoughts about the best approach would be appreciated. Note that this bloat on the primary key is from about 2-3 weeks of daily deletes/updates/inserts. I have multiple tables with similar structure and data size. Here are my autovac settings. I am on PostgreSQL 9.1.1 #autovacuum_max_workers = 3 # max number of autovacuum subprocesses #autovacuum_naptime = 1min # time between autovacuum runs autovacuum_vacuum_threshold = 20000 # min number of row updates before # vacuum default 50 autovacuum_analyze_threshold = 20000 # min number of row updates before # analyze default 50 autovacuum_vacuum_scale_factor = 0.1 # fraction of table size before vacuum autovacuum_analyze_scale_factor = 0.1 # fraction of table size before analyze #autovacuum_freeze_max_age = 200000000 # maximum XID age before forced vacuum autovacuum_vacuum_cost_delay = 5ms #autovacuum_vacuum_cost_limit = -1 Thanks RV -- View this message in context: http://postgresql.1045698.n5.nabble.com/Concurrent-Reindex-on-Primary-Key-for-large-table-tp5467243p5467243.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
В списке pgsql-sql по дате отправления: