Stack Overflow
Stack Overflow

Questions tagged [vacuum]

In SQLite and PosgreSQL, the VACUUM command rebuilds the entire database.

232 questions
0
votes
2 answers

Redshift VACUUM effect on concurrent queries

I have a process that runs VACUUM manually on a list of redshift tables on a daily basis to maintain consistent query performance. But sometimes, vacuuming one table takes about 2 hours. Is this normal? I was thinking of the effect of the vacuum…
thox
  • 129
  • 4
  • 13
0
votes
0 answers

postgres vacuum_freeze_min_age vs autovacuum_freeze_min_age

I fail to understand why pg_settings parameter vacuum_freeze_min_age is named differently than the table parameter autovacuum_freeze_min_age. Isn't the purpose of this setting on table level to overwrite the general setting? Shouldn't they share the…
Peter
  • 932
  • 5
  • 19
0
votes
1 answer

Autovacuum struggles with 2000 tables reaching autovacuum_freeze_max_age simultaneously

My goal is to keep autovacuum very responsive to current events, mainly so that tables are always properly analyzed. At the same time, I have huge batches of ~2000 tables that need wraparound-prevention vacuum. These batches delay autovacuum…
Peter
  • 932
  • 5
  • 19
0
votes
0 answers

can I run vacuum on live Postgresql database

We want to clean up some dead tuples from tables in our application PostgreSql RDS instance. So we are planning to run "Vacuum" command (not Vacuum full) on live DB after taking a Snapshot. Does this have any impact on the application? Do we need to…
Rob Wilkinson
  • 1,131
  • 5
  • 18
  • 34
0
votes
0 answers

Postgresql : Maintaining the database and knowing more about relation/impact with each other Autovacuum , backend_xmin, datfrozenxid

I am newbie to Postgresql, My project is in financial transactions having a few tables with huge transaction data which will have frequent insert/update/delete on it. Initially when I started, came across an error that auto-vacuum is not working…
Anuya Varde
  • 1
  • 2
0
votes
1 answer

How to vacuum an sqlite database within R?

I have created and filled a sqlite database within R using packages DBI and RSQLite. E.g. like that: con_sqlite <- DBI::dbConnect(RSQLite::SQLite(), "../mydatabase.sqlite") DBI::dbWriteTable(con_sqlite, "mytable", d_table, overwrite = TRUE) ... Now…
panuffel
  • 624
  • 1
  • 8
  • 15
0
votes
1 answer

In Redshift, is it possible to pause/resume a VACUUM FULL job?

I'm working with on an exceptionally large table which due to some data issue, I have to re-insert data on a couple of historical dates. After the insertion, I wanted to perform a manually triggered VACUUM FULL operation. However, unfortunately, the…
sasawatc
  • 417
  • 1
  • 4
  • 16
0
votes
1 answer

Postgres autovacuum to prevent wraparound (difference between usual autovacuum)

I've got a Postgres 11 database. The database has logical dumps (REPLICA using streaming replication). Am I wright in thinking that if I've got tables in which information is inserted, but not modified, then usual autovacuum will not be…
Gerzzog
  • 95
  • 7
0
votes
1 answer

A lot of bloat in system postresql tables

I have PgSql 13.7 at Amazon RDS, this is production, so can't stop everything there. I run into strange behaviour with dead tuples, there is a lot of bloat in system tables. For example: pg_attribute: Live tuples=823, Dead tuples=750939 …
Stanislav Markin
  • 3
  • 2
0
votes
0 answers

PostgreSQL 9.6 vacuum using 100% CPU

I am running PostgreSQL 9.6 on Centos 7. The database was migrated from a PostgreSQL 9.4 server that did not have the issue. With autovacuum on Postgres is using 100% of one core constantly (10% of total CPU). With autovacuum off it does not use any…
James
  • 17,965
  • 11
  • 91
  • 146
0
votes
0 answers

RDS Postgres "canceling statement due to conflict with recovery"

I using 3 RDS Postgres Instance (1 master, 2 slave, version 13). And my Postgres config is using the default parameter group. max_standby_archive_delay = 30s max_standby_streaming_delay = 30s Recently, the following error occurs frequently. ERROR:…
prospective developer
  • 13
  • 4
0
votes
2 answers

Overhead of Redshift materialized view full REFRESH Vs Redshift VACUUM

I’m working on a project where I want to have a fixed/consistent amount of data in terms of a specific time period (eg. 365 days). Which approach is more efficient and will cost lesser workload between the two? Using a dedicated table and…
thox
  • 129
  • 4
  • 13
0
votes
1 answer

Why Redshift vacuum + analyse do not update stats_off value in svv_table_info?

Checking stats_off in svv_table_info I saw that some tables have around 10.0 of this stats off. In AWS docs the description for that field is: Number that indicates how stale the table's statistics are; 0 is current, 100 is out of date. In other…
mrc
  • 2,845
  • 8
  • 39
  • 73
0
votes
1 answer

Vacuum HDF5 dataset (to remove rows of data and resize)

Let say I have HDF5 dataset with maxshape=(None,1000), chunk=(1,1000). Then whenever I need to delete a some row I just zero-it (many): ds[ix,:] = 0 What is the fastest way to vacuum-zeroth-rows and resize the array ? Now lets add a twist. I…
sten
  • 7,028
  • 9
  • 41
  • 63
0
votes
0 answers

Postgres: 'Wrong tuple length' on Vacuum. Disk space running full

We have a PGSQL server (v13) with alot of data in it. The database contains documents. The total database is around 1.5 TB. Today, someone called me telling me the disk space was almost full. They put in 1 TB extra storage some time ago but that…
Morph
  • 33
  • 3
1 2 3
…
15 16