Tag Info

Hot answers tagged

13

VACUUM is only needed on updated or deleted rows in non-temporary tables. Obviously you're doing lots of INSERTs but it's not obvious from the description that you're also doing lots of UPDATEs or DELETEs. These operations can be tracked with the pg_stat_all_tables view, specifically the n_tup_upd and n_tup_del columns. Also, even more to the point, there ...


11

Eelke is almost certainly correct that your locking is blocking autovacuum. Autovacuum is designed to give way to user activity, deliberately. If those tables are locked, autovacuum cannot vacuum them. For posterity, however, I wanted to give an example set of settings for hyper-aggressive autovacuum, since the settings you gave don't quite do it. Note ...


10

I see nothing in your question that auto-vacuum would not take care of. It largely depends on the pattern or your writing activities. You mention 3 million new rows per week, but INSERT (or COPY) is largely irrelevant for VACUUM. UPDATE and DELETE are relevant, especially if they pick random rows (as opposed to bulks of data most likely occupying pages ...


7

To return space to the OS, use VACUUM FULL. While being at it, I suppose you run VACUUM FULL ANALYZE. I quote the manual: FULL Selects "full" vacuum, which can reclaim more space, but takes much longer and exclusively locks the table. This method also requires extra disk space, since it writes a new copy of the table and doesn't release the ...


7

The key words here are: "heavily updated" "in the table for 2-3 hours". Point 1. is indication for a lower fill factor, while 2. is the opposite. It helps performance if multiple row versions are stored on the same data page. H.O.T. updates would achieve that. Read here or here. They need some wiggle room on the data page - like dead tuples or space ...


7

There are a least three major reasons for why you should upgrade to a more recent version, preferably to the current version 9.1. 1.) As has been mentioned by @a_horse_with_no_name the autovacuum mechanism has been improved in many places since version 8.2. 2.) You don't see frequent DELETEs, so the table bloat most probably comes from UPDATEs as @Milen ...


5

This is very hard to determine. You can tune autovacuuming to be more agressive or to be milder. But when set to mild and it is lagging behind and the base I/O load is too high, it can happen that it never reaches a proper vacuumed state - then you see the process running and running and running. Furthermore, later PostreSQL editions have much improved ...


5

Yes it is a locking issue. According to this page (non full) VACUUM needs SHARE UPDATE EXCLUSIVE access which is blocked by the lock level you are using. Are you certain you need this lock? PostgreSQL is ACID compliant so concurrent writes are in most cases not a problem as PostgreSQL will abort one of the transactions if a serialization violation would ...


5

VACUUM does not necessarily make unused space available to the filesystem, it merely makes the blocks re-usable for further INSERTs (or UPDATEs). If I'm not mistaken the only way to also actively reduce the size on the file system would be a VACUUM FULL, but beware that needs an exclusive lock on the table(s). Do you expect the table to not get any new ...


4

Just to see which tables qualify for autovacuum at all, the following query may be used (based on http://www.postgresql.org/docs/current/static/routine-vacuuming.html). Note however, that the query does not look for table specific settings: SELECT psut.relname, to_char(psut.last_vacuum, 'YYYY-MM-DD HH24:MI') as last_vacuum, ...


4

The space should be freed as soon as the index is dropped. Possible explanations for what you show above are: The index is in a tablespace on a different filesystem from the one you're checking. The OS/filesystem has some lag in providing up-to-date free space information. Something else is eating the free space as soon as it becomes available. Something ...


4

Increasing the number of autovacuum processes and reducing the naptime will probably help. Here is the configuration for a PostgreSQL 9.1 that I use on a server that stores backup information and as a result gets a lot of insert activity. http://www.postgresql.org/docs/current/static/runtime-config-autovacuum.html autovacuum_max_workers = 6 # ...


4

If you don't have concurrent transactions that would prohibit you from getting an exclusive lock on the table, I would: Select the (relatively few) surviving rows into a temporary table. Make sure you have enough RAM available for the temporary tables (for this session only). Read about temp_buffers in this related answer: Optimizing bulk update ...


4

VACUUM FULL rewrites the entire contents of the table into a new disk file with no extra space, allowing unused space to be returned to the operating system. This method also requires extra disk space, since it writes a new copy of the table and doesn't release the old copy until the operation is complete. Usually this should only be used when a ...


3

On one hand, you can have a look at one of my previous answers to see how you can keep a table size more or less steady. There you will find a solution with triggers - of course, this can be solved using a cron job as well. In the latter case I would first check if the row number exceeded a certain limit and the either delete the oldest rows or drop a ...


3

1) If you don't count your own time as a resource, then you should always be able to hand-craft a vacuum schedule which uses fewer total resources than autovacuum does. If you do count your own time, this is almost surely not worthwhile. 2) Other than manually or algorithmically turning it on or off, no. Nor would it make sense to do that. A database ...


3

If you use anything but CLUSTER / VACUUM FULL / pg_repack you need to make sure there are no concurrent writes to the table. Take an exclusive lock on the table and do everything in a single transaction or, better yet, shut out all connections to avoid concurrent changes. TABLESPACE Yes, your last idea could work. Create a new tablespace on the other disk. ...


2

In current versions of PostgreSQL, you can look at the *pg_stat_activity* view to find autovacuum tasks. They will have *current_query* fields that start with "Mark autovacuum entries in pg_stat_activity that look like this: autovacuum: VACUUM t A query to count how many of those you have might look like this: SELECT count(*) FROM pg_stat_activity WHERE ...


2

Since you don't have enough space to run a vacumm or rebuild, you can always rebuild your postgresql databases by restoring them. Restoring the databases, tables, indexes will free up space and defragment. Afterwards, you can setup automated maintenance to vacumm your databases on a regular basis. 1 Backup all of the databases on your postgresql server ...


2

Because you haven't specified your PostgreSQL version it's hard to answer. VACUUM has been improved in many ways on newer versions. Presuming you're on 9.0 or 9.1 - so you don't need a max_fsm_pages setting, you have the improved VACUUM FULL, you have the visibility map, etc - then subsequent VACUUM operations should be quite fast. However, you should not ...


2

For the general settings use: select * from pg_settings where name like 'autovacuum%' for table specific settings, check out the column reloptions in pg_class: select relname, reloptions from pg_class (you probably want to join that to pg_namespace to limit this to a specific schema).


2

1) I would have tried cluster followed by analyze. My only hesitation is I am not 100% sure what happened here. Is it possible there was some index corruption as well? Reindex might have helped? Given that the stats entries were way off, is it possible something was corrupt elsewhere regarding the relation's OID? 2) I have no idea. I have never seen ...


2

I agree with ETL that there is no short answer. Size is not the only thing that matters - we run quite large PostgreSQL OLTP Databases (with some tables > 100.000.000 rows) under heavy load and currently we rely on autovacuum only. Yet, two things seem important to me: There seems to be a consensus, that autovacuum should never be switched off, unless you ...


2

To check what CLUSTER does, I took a table fo mine from an earlier experiment which basically contained the first 10 million positive integers. I already deleted some rows and there is an other column as well but these only affect the actual table size, so it is not that interesting. First, having run VACUUM FULL on the table fka, I took its size: \dt+ ...


2

I don't know what resources you're getting this from. Not just that PgAdmin page given some of what you're saying. The information you're relying on is either outdated or incomplete; all this is pretty much unnecessary. Make sure that autovacuum is keeping up with the database workload and you're pretty much done. These days you should not generally need to ...


2

Autovacuum won't run against the table if it not changing. In your case, you are appending data to the table so autovacuum will run, but it is the ANALYZE portion that is worth mentioning. The DELETEs will kick of autovacuum runs of course. Are you seeing performance problems when autovacuum runs? If not, I would recommend leaving it enabled. After your ...


2

Here's a short concise answer. Vacuum full takes out an exclusive lock and rebuilds the table so that it has no empty blocks (we'll pretend fill factor is 100% for now). Vacuum freeze marks a table's contents with a very special transaction timestamp that tells postgres that it does not need to be vacuumed, ever. Next update this frozen id will disappear. ...


2

A manual vacuumdb should only be needed for special circumstances, like after running pg_upgrade or after doing some kind of bulk load or major maintenance. Otherwise, just let autovacuum do its thing. If your database is mostly idle at night, you can still do vacuumdb and it will concentrate the maintenance into the off hours so that autovacuum is less ...


1

Don't disable autovacuum unless you have a very good reason. At the absolute most, set per-table vacuum parameters to limit how it runs on a particular table. Autovacuum maintains table statistics and you actually want it to run very often on append-only tables to make sure you get good query plans; that's a known problem area for statistics- based ...


1

The default settings will probably be fine. That will limit the IO write usage to about 4MB/sec, that is, 4kB/msec = (8KB * vacuum_cost_limit / vacuum_cost_page_dirty / autovacuum_vacuum_cost_delay) And RAM usage to about 48 MB (autovacuum_max_workers * maintenance_work_mem)



Only top voted, non community-wiki answers of a minimum length are eligible