Hot answers tagged

22

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 ...


21

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 ...


21

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, ...


14

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 ...


12

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 ...


12

tl;dr: The first process that reads data after it is committed will set hint bits. That will dirty the page, creating write activity. The other thing VACUUM (but not other commands) does is marks the page as all-visible, if appropriate. VACUUM will eventually have to hit the table to freeze the tuples. The work that needs to be done after an insert isn't ...


11

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 ...


10

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).


8

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


7

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 ...


7

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 ...


7

The size of the physical table is typically (except for opportunistic pruning of removable pages from the end of the table) not reduced by running VACUUM (or VACUUM ANALYZE). You need to run VACUUM FULL to actually shrink the table. That's not necessarily what you want to do on a regular basis if you have write load on your table. Dead rows provide wiggle ...


6

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 ...


6

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. ...


5

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 ...


5

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 ...


5

VACUUM rewrites the entire block, efficiently packing the remaining rows and leaving a single contiguous block of free space (though this space isn't zeroed and the physical disk file might contain the remnants of deleted rows which of course are in no way visible to the database user). test schema: --#psql postgres postgres select oid from pg_database ...


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 ...


5

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 # ...


5

If you use anything but CLUSTER / VACUUM FULL / pg_repack (which all manage locks automatically) 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. ...


5

VACUUM can only remove dead tuples which are long-dead, that is, dead to all possible uses. If you have long-lived transactions, they may prevent the recently-dead tuples from being removed. This is an example of a situation where a long-lived transaction prevented removal: INFO: "pgbench_accounts": found 0 removable, 2999042 nonremovable row versions in ...


5

Normally we'd expect that when postgres was restarted, the crash recovery process would have removed files related to a rollback'ed index from the data directory. Let's assume that it didn't work, or at least that it has to be checked manually. The list of files that should be in the datadir can be established with a query like this: select ...


4

ALTER TABLE .. DROP COLUMN ... marks the column as deleted in the system table pg_attribute. The table itself is not otherwise manipulated until rows are rewritten some way or another. The drop itself is very fast, but it does take a brief ACCESS EXCLUSIVE lock. I would not call that "downtime", though. Actually reclaiming disk space is the tricky part. You ...


4

NOTE: I have tested this on 9.1. I have no 9.0 server lying around here. I am preeeeeeeeettty sure though it will work on 9.0 though. You can do this with pretty much no down-time by using a temporary tablespace. The down-time will be in the form of exclusive locks. But only on the table you are vacuuming. So all that will happen is that client queries ...


4

You're probably not running autovacuum. You might want to enable that. But more importantly, you should consider upgrading to the 9.1.x branch.


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

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+ ...


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

To further explain what Jayadevan wrote. The way that Postgres works with transactions, and to keep track of visible data is by comparing internal Transaction IDs. However, since those transactions are a 32-bit integer sooner or later they will wrap around, and therefore the new transaction will look like it was made in the past (and thus be visible in a ...



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