0

I have a database with an extremely large table in postgresql.

I'm aware that the only way to shrink the on disk size is VACUUM FULL, but I cannot do that, as I don't have anywhere near enough free space (it's a 920 GB table on a 1TB disk, and I can't afford another 1TB SSD ATM).

However, I did run VACUUM VERBOSE ANALYZE web_pages, and it completed, but the table size (as reported in psql) didn't reduce at all.

Basically, is there a way to shrink a table without VACUUM FULL or complete dump/load? I have the space for a dump/load, but at this point I expect it would take more then a week.

Vacuum output:

webarchive=# VACUUM VERBOSE ANALYZE web_pages;
INFO:  vacuuming "public.web_pages"
INFO:  scanned index "ix_web_pages_distance_filtered" to remove 145580643 row versions
DETAIL:  CPU 4.46s/165.77u sec elapsed 324.63 sec
INFO:  scanned index "ix_web_pages_netloc" to remove 145580643 row versions
DETAIL:  CPU 40.65s/4686.88u sec elapsed 5387.13 sec
INFO:  scanned index "ix_web_pages_priority" to remove 145580643 row versions
DETAIL:  CPU 29.59s/1018.71u sec elapsed 1452.67 sec
INFO:  scanned index "ix_web_pages_state" to remove 145580643 row versions
DETAIL:  CPU 22.08s/303.12u sec elapsed 712.94 sec
INFO:  scanned index "ix_web_pages_url" to remove 145580643 row versions
DETAIL:  CPU 283.45s/673.39u sec elapsed 7583.39 sec
INFO:  scanned index "web_pages_pkey" to remove 145580643 row versions
DETAIL:  CPU 51.69s/90.19u sec elapsed 1461.37 sec
INFO:  scanned index "ix_web_pages_id" to remove 145580643 row versions
DETAIL:  CPU 63.13s/99.77u sec elapsed 1529.22 sec
INFO:  scanned index "web_pages_netloc_fetchtime_idx" to remove 145580643 row versions
DETAIL:  CPU 77.04s/5080.52u sec elapsed 6287.14 sec
INFO:  scanned index "id_web_pages_id_state" to remove 145580643 row versions
DETAIL:  CPU 64.52s/107.81u sec elapsed 1695.07 sec
INFO:  scanned index "web_pages_fetchtime_idx" to remove 145580643 row versions
DETAIL:  CPU 12.06s/99.66u sec elapsed 408.36 sec
INFO:  "web_pages": removed 145580643 row versions in 8584664 pages
DETAIL:  CPU 226.70s/140.17u sec elapsed 5019.28 sec
INFO:  index "ix_web_pages_distance_filtered" now contains 16007295 row versions in 814166 pages
DETAIL:  38738938 index row versions were removed.
570268 index pages have been deleted, 385915 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.02 sec.
INFO:  index "ix_web_pages_netloc" now contains 27370778 row versions in 3181634 pages
DETAIL:  67244989 index row versions were removed.
2669376 index pages have been deleted, 1876620 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.01 sec.
INFO:  index "ix_web_pages_priority" now contains 27370960 row versions in 2006220 pages
DETAIL:  67218177 index row versions were removed.
1056657 index pages have been deleted, 786603 are currently reusable.
CPU 0.01s/0.00u sec elapsed 0.03 sec.
INFO:  index "ix_web_pages_state" now contains 27370969 row versions in 1532024 pages
DETAIL:  67244989 index row versions were removed.
986826 index pages have been deleted, 700367 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.01 sec.
INFO:  index "ix_web_pages_url" now contains 27382514 row versions in 7555366 pages
DETAIL:  78562001 index row versions were removed.
4290425 index pages have been deleted, 225461 are currently reusable.
CPU 0.02s/0.00u sec elapsed 0.04 sec.
INFO:  index "web_pages_pkey" now contains 27401242 row versions in 2421605 pages
DETAIL:  78000787 index row versions were removed.
1068399 index pages have been deleted, 373558 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.01 sec.
INFO:  index "ix_web_pages_id" now contains 27411627 row versions in 2874706 pages
DETAIL:  82612172 index row versions were removed.
1290296 index pages have been deleted, 442226 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.01 sec.
INFO:  index "web_pages_netloc_fetchtime_idx" now contains 27556711 row versions in 4482440 pages
DETAIL:  80962513 index row versions were removed.
3373490 index pages have been deleted, 1873800 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.02 sec.
INFO:  index "id_web_pages_id_state" now contains 27558627 row versions in 3094617 pages
DETAIL:  81497647 index row versions were removed.
1735454 index pages have been deleted, 631419 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.01 sec.
INFO:  index "web_pages_fetchtime_idx" now contains 27559941 row versions in 656103 pages
DETAIL:  67710984 index row versions were removed.
228974 index pages have been deleted, 95938 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  "web_pages": found 32297714 removable, 26459019 nonremovable row versions in 14298550 out of 14827067 pages
DETAIL:  1671 dead row versions cannot be removed yet.
There were 378926914 unused item pointers.
Skipped 0 pages due to buffer pins.
0 pages are entirely empty.
CPU 1149.21s/12598.17u sec elapsed 35893.00 sec.
INFO:  vacuuming "pg_toast.pg_toast_38269400"
INFO:  scanned index "pg_toast_38269400_index" to remove 178956680 row versions
DETAIL:  CPU 33.85s/139.43u sec elapsed 774.95 sec
INFO:  "pg_toast_38269400": removed 178956680 row versions in 47342563 pages
DETAIL:  CPU 1267.31s/752.22u sec elapsed 22404.29 sec
INFO:  scanned index "pg_toast_38269400_index" to remove 162873580 row versions
DETAIL:  CPU 20.65s/43.54u sec elapsed 216.38 sec
INFO:  "pg_toast_38269400": removed 162873580 row versions in 39900140 pages
DETAIL:  CPU 1085.52s/716.33u sec elapsed 13775.48 sec
INFO:  index "pg_toast_38269400_index" now contains 91453965 row versions in 1622691 pages
DETAIL:  341830260 index row versions were removed.
540140 index pages have been deleted, 1626 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.02 sec.
INFO:  "pg_toast_38269400": found 275718152 removable, 85526893 nonremovable row versions in 102611808 out of 104048880 pages
DETAIL:  1031 dead row versions cannot be removed yet.
There were 14286891 unused item pointers.
Skipped 0 pages due to buffer pins.
0 pages are entirely empty.
CPU 4786.16s/3240.77u sec elapsed 79646.66 sec.
INFO:  analyzing "public.web_pages"
INFO:  "web_pages": scanned 90000 of 14840002 pages, containing 166193 live rows and 1769 dead rows; 90000 rows in sample, 27403383 estimated total rows
VACUUM
webarchive=#

Size report before:

webarchive=# \d+
                                List of relations
 Schema |                Name      |   Type   |    Owner    |    Size    | Description
--------+--------------------------+----------+-------------+------------+-------------
..... 
public | web_pages                | table    | webarchuser | 920 GB     |
.....

After:

webarchive=# \d+
                                List of relations
 Schema |                Name      |   Type   |    Owner    |    Size    | Description
--------+--------------------------+----------+-------------+------------+-------------
 ........
 public | web_pages                | table    | webarchuser | 920 GB     |
 ........

I realize the "correct" solution here would be a bigger disk, but this is a hobby project (albeit at a very large scale), and I just don't have the money for more huge SSD storage.

  • @a_horse_with_no_name - See comment: I'm aware that the only way to shrink the on disk size is VACUUM FULL. Is there no way to shrink the reported size (e.g. what you get with \d+ in psql) either? I assumed that \d+ reported the size of the used tuples, rather then just the disk primitives. – Fake Name Oct 22 '18 at 5:31
  • \d+ uses pg_table_size() internally (which you can verify if you start psql with the -E switch. pg_table_size() is documented as "Disk space used by the specified table, excluding indexes" – a_horse_with_no_name Oct 22 '18 at 5:36
  • If possible to use partition then create partitions one by one. When you complete your partitions, vacuum full them one by one. But be careful about wals. It may fill your disk. – Yavuz Selim Oct 22 '18 at 12:34
2

Is there no way to shrink the reported size (e.g. what you get with \d+ in psql) either? I assumed that \d+ reported the size of the used tuples, rather then just the disk primitives

No, \d+ reports the size on disk. If you want to know how much space is available for internal reuse, see the core pg_freespacemap extension. If that shows a lot of internal space, and you want to actually try to shrink the table and return space to the OS, try the non-core pg_repack extension. But if a dump and reload will take a week, I doubt pg_repack will finish swiftly.

  • Ooooh, pg_repack looks very interesting. – Fake Name Oct 23 '18 at 1:24
  • Oooor not: For example, if the total size of the tables and indexes to be reorganized is 1GB, an additional 2GB of disk space is required. I have maybe 50 GB of 1TB free. – Fake Name Oct 23 '18 at 1:26
  • How much internal freespace does the table have? I thought pg_repack had a way to repack only the tail of the table, but I don't see it documented. Is the table naturally clustered on some column? – jjanes Oct 23 '18 at 9:27
  • I wish I could tell you, but I gave up and wound up just dropping the table, and recreating via pg_restore. I can tell you that the reported table size was 920 GB, and the dump uncompressed is 748 GB, and it's mostly text. – Fake Name Oct 23 '18 at 23:21
1

VACUUMfrees up space inside the tables, if you want to reduce the disk size of the tables you must use VACUUM FULL

0

vacuum will trim tables if the last records in the table are dead. so with frequent enough vacuums and sufficient churn (rewriting of records) the table will gradually shrink.

The manual: https://www.postgresql.org/docs/current/static/routine-vacuuming.html

The standard form of VACUUM removes dead row versions in tables and indexes and marks the space available for future reuse. However, it will not return the space to the operating system, except in the special case where one or more pages at the end of a table become entirely free and an exclusive table lock can be easily obtained.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.