I'm using MySQL 5.6 and innodb file per table has been enabled. I noticed that one file named as tablename.ibd is 211GB, then query the information schema there it's showing 21GB. I have done a huge delete operation on this table. And daily archiving some old data. Is this because of that? Optimize table - command is the only way to reclaim the space?

We have this directory: $ORACLE_HOME/EMStagePatches_$ORACLE_SID We are running out of space and this directory is using up a lot of space. Is it safe to delete it? I think it is something to do with the Enterprise Manager patches. How can I stop Enterprise Manager from downloading stuff into that directory? We are using Oracle Database 11g Enterprise Edition Release 11.1.0.6 running on Red Hat Enterprise Linux Server release 5.2.

I have upgraded Postgresql 9.5 to Postgresql 12.4 a few days back using pg_upgrade utility with link (-k) option. So basically I am having two data directories i.e. One is old data directory (v9.5) and the current one in running state (v12.4). Yesterday I have dropped two tables of size 700GB and 300GB. After connecting to postgres using psql utility I can see database size whose tables was dropped got decreased (with \l+ ) but what is making me worry is that only a few ~50GBs have been freed from storage partition. I have run vacuumdb only on that database but no luck. I have checked if any deleted open file is there on OS level using lsof but there is none. Note : Still I have not deleted old data directory (v9.5). I am not sure if deleting will impact my running postgres 12 as I have used 'link' option in pg_upgrade. Looking for the solution.

I'm working in Oracle 19c DB. Block size is 8k. I'm trying to insert new data in my table. I have enough free space (according to dba_free_space more than 2 GB). But I've got ORA-01654: unable to extend index by 8192. The question is why I need **8192*8k=64 MB** free space if **NEXT_EXTENT = 1048576 = 1MB**? And how to reuse my free space? I've checked that i need free extents 64 MB or above, byt I have only 1-3 MB extents: select count(*) as extents_qnt from dba_free_space where tablespace_name = 'MY_TABLESPACE' and bytes/1024/1024 >= 64 -- 64 MB EXTENTS_QNT ------- 0 select count(*) as extents_qnt from dba_free_space where tablespace_name = 'MY_TABLESPACE' and bytes/1024/1024 BETWEEN 1 AND 3 EXTENTS_QNT ------- 1642 I've got an error because of the lack of large enough free extents. But I still don't understand why I need 64 MB (not 1 MB).

I recently run out of disc space no my server due to implementing some audit tools on Postgresql database. Two tables holding data about changes made by users quickly reached 16GB each, and totally filled 40GB server. I've checked size of this tables by command in PGAdmin4:

SELECT
   relname as "Table",
   pg_size_pretty(pg_total_relation_size(relid)) As "Size",
   pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) as "External Size"
   FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC;

Following that, I deleted these two tables to save space, but despite that I haven't actually recovered any disc space. I run df and du commands in debian to check disc usage:

root@vps673587:/home/maciejzak# df -h
Filesystem      Size  Used Avail Use% Mounted on
/dev/sda1        40G   36G  2.4G  94% /
udev             10M     0   10M   0% /dev
tmpfs           774M   81M  694M  11% /run
tmpfs           1.9G  8.0K  1.9G   1% /dev/shm
tmpfs           5.0M     0  5.0M   0% /run/lock
tmpfs           1.9G     0  1.9G   0% /sys/fs/cgroup
tmpfs           387M     0  387M   0% /run/user/1003
tmpfs           387M     0  387M   0% /run/user/1001


root@vps673587:/home/maciejzak# sudo du -sh /*
8.9M    /bin
72M     /boot
8.0K    /dev
7.0M    /etc
92M     /home
0       /initrd.img
0       /initrd.img.old
537M    /lib
4.0K    /lib64
16K     /lost+found
8.0K    /media
4.0K    /mnt
4.0K    /opt
du: cannot access ‘/proc/10127/task/10127/fd/4’: No such file or directory
du: cannot access ‘/proc/10127/task/10127/fdinfo/4’: No such file or directory
du: cannot access ‘/proc/10127/fd/4’: No such file or directory
du: cannot access ‘/proc/10127/fdinfo/4’: No such file or directory
0       /proc
124K    /root
81M     /run
4.9M    /sbin
12K     /srv
0       /sys
44K     /tmp
968M    /usr
3.6G    /var
0       /vmlinuz
0       /vmlinuz.old

So as you can see, 94% (36GB) on sda1 is used. Meanwhile only 3,6 GB is actually used on /var. What happened with rest of that disc space? I've run lsof command:

root@vps673587:/home/maciejzak#  lsof | grep -i deleted
sudo       2529            root    0u      CHR              136,0        0t0          3 /dev/pts/0 (deleted)
sudo       2529            root    1u      CHR              136,0        0t0          3 /dev/pts/0 (deleted)
sudo       2529            root    2u      CHR              136,0        0t0          3 /dev/pts/0 (deleted)
proftpd    2530            root    0u      CHR              136,0        0t0          3 /dev/pts/0 (deleted)
proftpd    2530            root    1u      CHR              136,0        0t0          3 /dev/pts/0 (deleted)
proftpd    2530            root    2u      CHR              136,0        0t0          3 /dev/pts/0 (deleted)
proftpd    2530            root   10r      REG                8,1       5262      29171 /etc/init.d/proftpd (deleted)
systemctl  2540            root    0u      CHR              136,0        0t0          3 /dev/pts/0 (deleted)
systemctl  2540            root    1u      CHR              136,0        0t0          3 /dev/pts/0 (deleted)
systemctl  2540            root    2u      CHR              136,0        0t0          3 /dev/pts/0 (deleted)
systemd-t  2541            root    0u      CHR              136,0        0t0          3 /dev/pts/0 (deleted)
systemd-t  2541            root    1u      CHR              136,0        0t0          3 /dev/pts/0 (deleted)
systemd-t  2541            root    2u      CHR              136,0        0t0          3 /dev/pts/0 (deleted)
postgres   8927        postgres   19u      REG                8,1      24576     262826 /var/lib/postgresql/10/main/base/18036/68886_fsm (deleted)
postgres   8927        postgres   58u      REG                8,1          0     263332 /var/lib/postgresql/10/main/base/18036/68886 (deleted)
postgres   8927        postgres   59u      REG                8,1       8192     263850 /var/lib/postgresql/10/main/base/18036/68886_vm (deleted)
postgres   8927        postgres   60u      REG                8,1          0     263783 /var/lib/postgresql/10/main/base/18036/68894 (deleted)
postgres   8927        postgres   61u      REG                8,1          0     263782 /var/lib/postgresql/10/main/base/18036/68893 (deleted)
postgres   8927        postgres   62u      REG                8,1    4251648     263049 /var/lib/postgresql/10/main/base/18036/68889_fsm (deleted)
postgres   8927        postgres   64u      REG                8,1    4251648     263320 /var/lib/postgresql/10/main/base/18036/69116_fsm (deleted)
postgres   8927        postgres   65u      REG                8,1      24576     262943 /var/lib/postgresql/10/main/base/18036/69110_fsm (deleted)
postgres   8927        postgres   66u      REG                8,1          0     262940 /var/lib/postgresql/10/main/base/18036/69110 (deleted)
postgres   8927        postgres   67u      REG                8,1       8192     263851 /var/lib/postgresql/10/main/base/18036/69110_vm (deleted)
postgres   8927        postgres   68u      REG                8,1          0     263336 /var/lib/postgresql/10/main/base/18036/69119 (deleted)
postgres   8927        postgres   69u      REG                8,1          0     263341 /var/lib/postgresql/10/main/base/18036/69120 (deleted)
postgres   8927        postgres   70u      REG                8,1   16777216     263791 /var/lib/postgresql/10/main/pg_wal/000000010000000B0000003A (deleted)
postgres   8927        postgres   72u      REG                8,1          0     263759 /var/lib/postgresql/10/main/base/18036/68891 (deleted)
postgres   8927        postgres   73u      REG                8,1          0     263781 /var/lib/postgresql/10/main/base/18036/68892 (deleted)
postgres   8927        postgres   74u      REG                8,1          0     263340 /var/lib/postgresql/10/main/base/18036/68889 (deleted)
postgres   8927        postgres   75u      REG                8,1 1073741824     263822 /var/lib/postgresql/10/main/base/18036/68889.1 (deleted)
postgres   8927        postgres   76u      REG                8,1 1073741824     263824 /var/lib/postgresql/10/main/base/18036/68889.2 (deleted)
postgres   8927        postgres   77u      REG                8,1 1073741824     263826 /var/lib/postgresql/10/main/base/18036/68889.3 (deleted)
postgres   8927        postgres   78u      REG                8,1 1073741824     263828 /var/lib/postgresql/10/main/base/18036/68889.4 (deleted)
postgres   8927        postgres   79u      REG                8,1 1073741824     263831 /var/lib/postgresql/10/main/base/18036/68889.5 (deleted)
postgres   8927        postgres   80u      REG                8,1 1073741824     263834 /var/lib/postgresql/10/main/base/18036/68889.6 (deleted)
postgres   8927        postgres   81u      REG                8,1 1073741824     263838 /var/lib/postgresql/10/main/base/18036/68889.7 (deleted)
postgres   8927        postgres   82u      REG                8,1 1073741824     263841 /var/lib/postgresql/10/main/base/18036/68889.8 (deleted)
postgres   8927        postgres   83u      REG                8,1 1073741824     263844 /var/lib/postgresql/10/main/base/18036/68889.9 (deleted)
postgres   8927        postgres   84u      REG                8,1 1073741824     263847 /var/lib/postgresql/10/main/base/18036/68889.10 (deleted)
postgres   8927        postgres   85u      REG                8,1          0     263308 /var/lib/postgresql/10/main/base/18036/69118 (deleted)
postgres   8927        postgres   86u      REG                8,1          0     263085 /var/lib/postgresql/10/main/base/18036/68896 (deleted)
postgres   8927        postgres   87u      REG                8,1          0     263305 /var/lib/postgresql/10/main/base/18036/69116 (deleted)
postgres   8927        postgres   88u      REG                8,1 1073741824     263823 /var/lib/postgresql/10/main/base/18036/69116.1 (deleted)
postgres   8927        postgres   89u      REG                8,1 1073741824     263825 /var/lib/postgresql/10/main/base/18036/69116.2 (deleted)
postgres   8927        postgres   90u      REG                8,1 1073741824     263827 /var/lib/postgresql/10/main/base/18036/69116.3 (deleted)
postgres   8927        postgres   91u      REG                8,1 1073741824     263829 /var/lib/postgresql/10/main/base/18036/69116.4 (deleted)
postgres   8927        postgres   92u      REG                8,1 1073741824     263832 /var/lib/postgresql/10/main/base/18036/69116.5 (deleted)
postgres   8927        postgres   93u      REG                8,1 1073741824     263835 /var/lib/postgresql/10/main/base/18036/69116.6 (deleted)
postgres   8927        postgres   94u      REG                8,1 1073741824     263839 /var/lib/postgresql/10/main/base/18036/69116.7 (deleted)
postgres   8927        postgres   95u      REG                8,1 1073741824     263842 /var/lib/postgresql/10/main/base/18036/69116.8 (deleted)
postgres   8927        postgres   96u      REG                8,1 1073741824     263845 /var/lib/postgresql/10/main/base/18036/69116.9 (deleted)
postgres   8927        postgres   97u      REG                8,1 1073741824     263848 /var/lib/postgresql/10/main/base/18036/69116.10 (deleted)
postgres   8927        postgres   99u      REG                8,1          0     263342 /var/lib/postgresql/10/main/base/18036/69121 (deleted)
postgres   8927        postgres  100u      REG                8,1 1073741824     263016 /var/lib/postgresql/10/main/base/18036/68889.11 (deleted)
postgres   8927        postgres  101u      REG                8,1 1073741824     263074 /var/lib/postgresql/10/main/base/18036/69116.11 (deleted)
postgres   8927        postgres  102u      REG                8,1 1073741824     263020 /var/lib/postgresql/10/main/base/18036/68889.12 (deleted)
postgres   8927        postgres  103u      REG                8,1 1073741824     263113 /var/lib/postgresql/10/main/base/18036/69116.12 (deleted)
postgres   8927        postgres  104u      REG                8,1 1073741824     263082 /var/lib/postgresql/10/main/base/18036/68889.13 (deleted)
postgres   8927        postgres  105u      REG                8,1 1073741824     263133 /var/lib/postgresql/10/main/base/18036/69116.13 (deleted)
postgres   8927        postgres  106u      REG                8,1 1073741824     263131 /var/lib/postgresql/10/main/base/18036/68889.14 (deleted)
postgres   8927        postgres  107u      REG                8,1 1073741824     263136 /var/lib/postgresql/10/main/base/18036/69116.14 (deleted)
postgres   8927        postgres  108u      REG                8,1 1073741824     259461 /var/lib/postgresql/10/main/base/18036/68889.15 (deleted)
postgres   8927        postgres  109u      REG                8,1 1073741824     263153 /var/lib/postgresql/10/main/base/18036/69116.15 (deleted)
postgres   8927        postgres  110u      REG                8,1   50221056     263135 /var/lib/postgresql/10/main/base/18036/68889.16 (deleted)
postgres   8927        postgres  111u      REG                8,1   49745920     263155 /var/lib/postgresql/10/main/base/18036/69116.16 (deleted)
postgres   9058        postgres   78u      REG                8,1      24576     262826 /var/lib/postgresql/10/main/base/18036/68886_fsm (deleted)
postgres   9058        postgres   79u      REG                8,1          0     263332 /var/lib/postgresql/10/main/base/18036/68886 (deleted)
postgres   9058        postgres   80u      REG                8,1       8192     263850 /var/lib/postgresql/10/main/base/18036/68886_vm (deleted)
postgres   9058        postgres   81u      REG                8,1          0     263781 /var/lib/postgresql/10/main/base/18036/68892 (deleted)
postgres   9058        postgres   82u      REG                8,1          0     263782 /var/lib/postgresql/10/main/base/18036/68893 (deleted)
postgres   9058        postgres   83u      REG                8,1          0     263783 /var/lib/postgresql/10/main/base/18036/68894 (deleted)
postgres   9058        postgres   84u      REG                8,1          0     263085 /var/lib/postgresql/10/main/base/18036/68896 (deleted)
postgres   9058        postgres   85u      REG                8,1      24576     262943 /var/lib/postgresql/10/main/base/18036/69110_fsm (deleted)
postgres   9058        postgres   86u      REG                8,1          0     262940 /var/lib/postgresql/10/main/base/18036/69110 (deleted)
postgres   9058        postgres   87u      REG                8,1       8192     263851 /var/lib/postgresql/10/main/base/18036/69110_vm (deleted)
postgres   9058        postgres   88u      REG                8,1          0     263336 /var/lib/postgresql/10/main/base/18036/69119 (deleted)
postgres   9058        postgres   89u      REG                8,1          0     263341 /var/lib/postgresql/10/main/base/18036/69120 (deleted)
postgres   9058        postgres   90u      REG                8,1          0     263342 /var/lib/postgresql/10/main/base/18036/69121 (deleted)
postgres   9058        postgres   91u      REG                8,1   16777216     263313 /var/lib/postgresql/10/main/pg_wal/000000010000000B000000E8 (deleted)
postgres   9087        postgres  106u      REG                8,1          0     263332 /var/lib/postgresql/10/main/base/18036/68886 (deleted)
postgres   9087        postgres  107u      REG                8,1          0     263781 /var/lib/postgresql/10/main/base/18036/68892 (deleted)
postgres   9087        postgres  108u      REG                8,1          0     263782 /var/lib/postgresql/10/main/base/18036/68893 (deleted)
postgres   9087        postgres  109u      REG                8,1          0     263783 /var/lib/postgresql/10/main/base/18036/68894 (deleted)
postgres   9087        postgres  110u      REG                8,1          0     263085 /var/lib/postgresql/10/main/base/18036/68896 (deleted)
postgres   9087        postgres  111u      REG                8,1          0     262940 /var/lib/postgresql/10/main/base/18036/69110 (deleted)
postgres   9087        postgres  112u      REG                8,1          0     263336 /var/lib/postgresql/10/main/base/18036/69119 (deleted)
postgres   9087        postgres  113u      REG                8,1          0     263341 /var/lib/postgresql/10/main/base/18036/69120 (deleted)
postgres   9087        postgres  114u      REG                8,1          0     263342 /var/lib/postgresql/10/main/base/18036/69121 (deleted)
postgres   9500        postgres   56u      REG                8,1          0     263332 /var/lib/postgresql/10/main/base/18036/68886 (deleted)
postgres   9500        postgres   57u      REG                8,1          0     263781 /var/lib/postgresql/10/main/base/18036/68892 (deleted)
postgres   9500        postgres   58u      REG                8,1          0     263782 /var/lib/postgresql/10/main/base/18036/68893 (deleted)
postgres   9500        postgres   59u      REG                8,1          0     263783 /var/lib/postgresql/10/main/base/18036/68894 (deleted)
postgres   9500        postgres   60u      REG                8,1          0     263085 /var/lib/postgresql/10/main/base/18036/68896 (deleted)
postgres   9500        postgres   62u      REG                8,1          0     263759 /var/lib/postgresql/10/main/base/18036/68891 (deleted)
postgres   9500        postgres   63u      REG                8,1          0     263340 /var/lib/postgresql/10/main/base/18036/68889 (deleted)
postgres   9500        postgres   64u      REG                8,1 1073741824     263822 /var/lib/postgresql/10/main/base/18036/68889.1 (deleted)
postgres   9500        postgres   65u      REG                8,1 1073741824     263824 /var/lib/postgresql/10/main/base/18036/68889.2 (deleted)
postgres   9500        postgres   66u      REG                8,1 1073741824     263826 /var/lib/postgresql/10/main/base/18036/68889.3 (deleted)
postgres   9500        postgres   67u      REG                8,1 1073741824     263828 /var/lib/postgresql/10/main/base/18036/68889.4 (deleted)
postgres   9500        postgres   68u      REG                8,1 1073741824     263831 /var/lib/postgresql/10/main/base/18036/68889.5 (deleted)
postgres   9500        postgres   69u      REG                8,1 1073741824     263834 /var/lib/postgresql/10/main/base/18036/68889.6 (deleted)
postgres   9500        postgres   70u      REG                8,1 1073741824     263838 /var/lib/postgresql/10/main/base/18036/68889.7 (deleted)
postgres   9500        postgres   71u      REG                8,1 1073741824     263841 /var/lib/postgresql/10/main/base/18036/68889.8 (deleted)
postgres   9500        postgres   72u      REG                8,1 1073741824     263844 /var/lib/postgresql/10/main/base/18036/68889.9 (deleted)
postgres   9500        postgres   73u      REG                8,1 1073741824     263847 /var/lib/postgresql/10/main/base/18036/68889.10 (deleted)
postgres   9500        postgres   74u      REG                8,1          0     262940 /var/lib/postgresql/10/main/base/18036/69110 (deleted)
postgres   9500        postgres   75u      REG                8,1          0     263336 /var/lib/postgresql/10/main/base/18036/69119 (deleted)
postgres   9500        postgres   76u      REG                8,1          0     263341 /var/lib/postgresql/10/main/base/18036/69120 (deleted)
postgres   9500        postgres   77u      REG                8,1          0     263342 /var/lib/postgresql/10/main/base/18036/69121 (deleted)
postgres   9500        postgres   80u      REG                8,1       8192     263851 /var/lib/postgresql/10/main/base/18036/69110_vm (deleted)
postgres   9500        postgres   81u      REG                8,1          0     263308 /var/lib/postgresql/10/main/base/18036/69118 (deleted)
postgres   9500        postgres   82u      REG                8,1          0     263305 /var/lib/postgresql/10/main/base/18036/69116 (deleted)
postgres   9500        postgres   83u      REG                8,1 1073741824     263823 /var/lib/postgresql/10/main/base/18036/69116.1 (deleted)
postgres   9500        postgres   84u      REG                8,1 1073741824     263825 /var/lib/postgresql/10/main/base/18036/69116.2 (deleted)
postgres   9500        postgres   85u      REG                8,1 1073741824     263827 /var/lib/postgresql/10/main/base/18036/69116.3 (deleted)
postgres   9500        postgres   86u      REG                8,1 1073741824     263829 /var/lib/postgresql/10/main/base/18036/69116.4 (deleted)
postgres   9500        postgres   87u      REG                8,1 1073741824     263832 /var/lib/postgresql/10/main/base/18036/69116.5 (deleted)
postgres   9500        postgres   88u      REG                8,1 1073741824     263835 /var/lib/postgresql/10/main/base/18036/69116.6 (deleted)
postgres   9500        postgres   89u      REG                8,1 1073741824     263839 /var/lib/postgresql/10/main/base/18036/69116.7 (deleted)
postgres   9500        postgres   90u      REG                8,1 1073741824     263842 /var/lib/postgresql/10/main/base/18036/69116.8 (deleted)
postgres   9500        postgres   91u      REG                8,1 1073741824     263845 /var/lib/postgresql/10/main/base/18036/69116.9 (deleted)
postgres   9500        postgres   92u      REG                8,1 1073741824     263848 /var/lib/postgresql/10/main/base/18036/69116.10 (deleted)
postgres   9573        postgres   31u      REG                8,1          0     262940 /var/lib/postgresql/10/main/base/18036/69110 (deleted)
postgres   9573        postgres   32u      REG                8,1          0     263336 /var/lib/postgresql/10/main/base/18036/69119 (deleted)
postgres   9573        postgres   33u      REG                8,1          0     263341 /var/lib/postgresql/10/main/base/18036/69120 (deleted)
postgres   9573        postgres   34u      REG                8,1          0     263342 /var/lib/postgresql/10/main/base/18036/69121 (deleted)
postgres   9589        postgres   22u      REG                8,1          0     262940 /var/lib/postgresql/10/main/base/18036/69110 (deleted)
postgres   9589        postgres   23u      REG                8,1          0     263336 /var/lib/postgresql/10/main/base/18036/69119 (deleted)
postgres   9589        postgres   24u      REG                8,1          0     263341 /var/lib/postgresql/10/main/base/18036/69120 (deleted)
postgres   9589        postgres   25u      REG                8,1          0     263342 /var/lib/postgresql/10/main/base/18036/69121 (deleted)

It seems that a lot of space is still used up by (deleted) files from Postgres database. How can I free that disc space?

I"m using Postgres 9.5.4 on Ubuntu 14.04. I have my Postgres data on a separate disk partiion, which is getting pretty full. myuser@myproject:~$ df -h /mnt/volume-nyc1-01/ Filesystem Size Used Avail Use% Mounted on /dev/sda 99G 93G 413M 100% /mnt/volume-nyc1-01 I want to delete some data from my partition, but this has become challenging. In particular, when I run some queries, i get results like this myproject_production=> select count(*) FROM my_object_times rt1, my_object_times rt2 where rt1.my_object_id = rt2.my_object_id and rt1.name = rt2.name and rt1.time_in_ms = rt2.time_in_ms and rt1.id > rt2.id;; ERROR: could not write block 52782 of temporary file: No space left on device I want to free up some temp space so I can run queries and identify what data I need to delete. I have some other free space on another partition. How do I point my pgsql_tmp variable there so that I can run the queries I need? Edit: As the symlink option seemed to be the least invasive, I gave it a go, setting up things like this myuser@myproject:~$ sudo ls -al /mnt/volume-nyc1-01/postgresql/9.5/main/base/pgsql_tmp lrwxrwxrwx 1 root root 14 Apr 10 18:01 /mnt/volume-nyc1-01/postgresql/9.5/main/base/pgsql_tmp -> /opt/pgsql_tmp myuser@myproject:~$ cd /opt myuser@myproject:/opt$ df -h / Filesystem Size Used Avail Use% Mounted on /dev/disk/by-uuid/050e1e34-39e6-4072-a03e-ae0bf90ba13a 40G 24G 15G 62% / You can see that I have over 16GB available on the partition where I pointed it to, but still I get the errors ERROR: could not write block 1862514 of temporary file: No space left on device

If I have a Windows disk with a capacity of 1T, for example, and the primary datafile (.mdf) for my database reaches the disk's maximum size, I want to add a second datafile to the primary group. As this second datafile is isolated on another disk, can it be of type .ndf? If I create this second file of type .mdf, could there be any performance or operational problems other than just the fact that conventionally only one .mdf should exist? (I'm not talking here about the logs (.ldf), which are separate on their own disk).

This question is in the context of the helpcovid GPLv3+ project (work in progress in March 2020). With PostgreSQL 11 on Debian/Buster/x86-64. See the file hcv_database.cc there. I have a table of web users: ---- TABLE tb_user CREATE TABLE IF NOT EXISTS tb_user ( user_id SERIAL PRIMARY KEY NOT NULL, -- unique user_id user_firstname VARCHAR(31) NOT NULL, -- first name, in capitals, UTF8 user_familyname VARCHAR(62) NOT NULL, -- family name, in capitals, UTF8 user_email VARCHAR(71) NOT NULL, -- email, in lowercase, UTF8 user_gender CHAR(1) NOT NULL, -- 'F' | 'M' | '?' user_crtime DATE NOT NULL -- user entry creation time ); --- end TABLE tb_user Imagine I have a million rows in that table, and 99% of users have a user_firstname of less than 16 bytes, and 99% of users have a user_family of less than 32 bytes. Assume that this table is only filled by INSERT SQL statements . What would be the estimated disk space consumption of the PostgreSQL files (under /var/lib/postgreql) containing that database. As measured by du -h For simplicity, assume that this table is the only one in the database. To ask the question otherwise, the VARCHAR(31) size is used for *every* row on disk, or not? In other words, how practically important is the width of columns in tables, w.r.t to disk space? Related question: https://dba.stackexchange.com/questions/258898

In my env, I had created **MongoDB replica set** conf for high availability so one server fails should be handled by other also in my conf I have 3 DB server and 1 arbiter too but, my slave's server fails due to **disk utilization** by this, my master node also fails to provide data to api's which create disrupts my whole system. So, is this event occurs due to **configuration** fail or its mongo property? Or some conf other than replica set creation is required to prevent this?? currently, I am using MongoDB shell version v4.0.3 Followed this doc some of this command used in the replica set setup https://docs.mongodb.com/manual/tutorial/deploy-replica-set/

I am working with a PostgreSQL table with a large TEXT field, which is theoretically updated on a regular basis. I have thought about storing the data directly in the filesystem, but with TOAST, the data is already being stored off-page and compressed in the database so I figured I would keep things simple and just use the database storage. To give some context, I am indexing RSS feeds. I will have a script run every 24h that pulls the RSS feeds and potentially updates the table. This can lead to a lot of dead tuples, and thus lots of space being used on disk. Of course, autovacuum will take care of this eventually but it has the potential to be a lot of data (many GB) and I want to make sure I know what will happen when I am doing lots of updates on this very large table. One solution I have is to only update the TEXT field (storing the RSS data) if there are certain substantial changes to the feed, e.g. a new post on the website. This means that I could avoid doing the UPDATE unless I *really* have to. However, I still want to update the table (to keep track of when I most recently did an HTTP request). This will create a dead tuple with the old version of the row's data. What will happen to the TEXT data, if it isn't actually changed? Will the UPDATE also duplicate the TEXT data, when it creates a dead tuple? Or will the TEXT data be left untouched, because it wasn't changed and it is stored off-page?

im using postgresql database and currently im experimenting with some vacuum/autovacuum/full vacuum stuff. As far as i know, the updated/removed rows in table still occupy space (and are only marked as hidden) until vacuum is called. After vacuum is called those dead tuples are marked as free space, and this space can be resued by same table (or maybe different tables? im not sure), but space isnt returned to OS/FS and is still reserved by database, until VACUUM FULL is executed. And here comes my question. Im able to determine how much space table occupy on disk SELECT pg_size_pretty(pg_relation_size('fluffy_table')) AS data_only, pg_size_pretty(pg_total_relation_size('fluffy_table')) AS with_indexes; This should be space, that is "reserved" for table, and after executing VACUUM FULL this value will probably change (if there are some deleted rows). But is here some option, how to get size of this table after VACUUM FULL before executing VACUUM FULL? Or some usage in percents maybe? So i can say that this table is 100MB large, but only 60% (60MB) is used by actual data, so i can add 40% more rows to it before it will grow again? And is it possible with native postgresql (without extensions)? I thought about sum of live + dead tuples, calculating average row size and then multiple live/dead tuples by this value. But i already called VACUUM, so i dont have information about dead tuples anymore.

I'm using AWS RDS PostgreSQL 13 database in a master-slave configuration using logical replication with [pglogical](https://www.2ndquadrant.com/en/resources/pglogical/) extension. Recently, one of the application migrations has updated the schema of the master database, which in turn severed the replication process. No problems here, this is pretty much expected. However, after the replication stopped, the database has started to consume enormous amount of disk space: In just one day it consumed around 17 GB of the free disk space and caused the entire server to fail (all disk space was used). What makes it weird is that entire database is only ~150 MB in size and has a very low number of write requests. What strategy should I use to prevent such misbehavior from happening in the future? What would be the recommended approach to limit the amount of disk space used for replication? I don't want the master server to fail in any circumstances (however, I'm willing to "sacrifice" the replica if the need arises).

I am running my simulations for my phd thesis and writing data to Postgres db on a local machine. Yesterday I executed vacuum full by mistake. In the morning I could not login to my Postgres db vm. It completely filled space. How can I restore my data? I am looking for 2 days and could not figure it out. I am not very familiar with database

I have a single table with an approximate size of 85gb and roughly around 120 million of rows (in a PostgreSQL (12.8) database). I need to update all 120 something million rows with an update query to get rid of certain characters existing in all rows of a column by using the function of regexp_replace. And another constraint I have is that I don't have much disk space (around 20 gb), considering the necessity of duplication of the table while running the update operation. But this is a secondary issue for me. For the time being, the main problem is the slowness of this operation during the run and **more importantly** increasing size of table once the run has been completed. This increase in the size leaves no other option for me other than running a **full vacuum** on the table, which is something I cannot do due to the aforementioned disk space limitations. I should also add that this table is indexed (with different methods including btree) and also has a primary key. I also tried to create cluster on the index but nothing has changed. In order to reduce the run time I selected only 20 million rows from the main table and tried to work on that one, however that also didn't help. Also related to the **full vacuum** necessity I mentioned above, even if I try to update the table by applying a where condition to update only a smaller portion at each run, that also causes an growth in size, which means that I have to run full vacuum afterwards, as normal vacuum does not help reducing the size back to what it was before the run.

I have a table of almost one million rows with two columns of datatype varbinary. These two columns store binary data that causes the database to grow to 1 TB. As this database is also restored in other QA environments and a dev environment, we now have the task of reclaiming space as much as possible to save costs. After engaging the vendor, they advised that these two columns can be dropped as they are no longer in use. The application writing to these two columns has been modified accordingly. I have followed two options to approach the situation in my dev environment but I need help on the right approach. ### Option 1 1. Drop the two binary columns 2. Run DBCC CLEANTABLE - This step took almost 24 hours. I had to stop it because I will not have that much time in the production environment. 3. Shrink the filesystem - A bit reluctant to do this because of fragmentation. I got stuck on no. 2 above and then tried the second option below. ### Option 2 1. Drop the two binary columns 2. Create a new table and copy the data over using SSIS - This operation took almost 12 hours to complete (I will not have this much time in production) 3. Create a different filesystem and move all tables and other objects to this new table filesystem except the concerned old table in question using the CREATE INDEX…WITH DROP_EXISTING = ON, ONLINE = ON command 4. Drop the old table in the primary filesystem 5. Shrink the primary filesystem - I expect this to shrink faster since there are not many objects in there. Item 2 took almost 12 hours to complete. Does anyone know a better approach to getting rid of these two columns and reclaiming the space? The production environment has AOAG (Always On), meaning that I have to be in full recovery. Using DBCC CLEANTABLE took so long and my log was growing. I had to stop it to try option 2. After running for more than 24 hrs, I stopped it. It took so long to eventually stop. It was at that point that I thought DBCC CLEANTABLE was not a good option for me.

How do one *limit* the size of a PostgreSQL table? (or schema) Limit in either bytes or rows would be OK. I'm starting to think that there's no easy and obvious solution. I have a lot of identical tables in multiple schemas (my take on multi-tenancy) and I would like to restrict each schema to a certain max size. I have yet to find anything in Postgres that allows me to turn on any kind of quota. So I suppose you need to build this functionality yourself. The naive 'solution' would be to do something like: insert only if select count(*) < max quota. But that does not feel right. Anyone with better solutions in mind?

My riak cluster have 10-node running version 2.9.8. All nodes same version. The node which named node09@riak.local had used about 95% space of disk. And other nodes only used less than 50% space of disk. I'd tried to find out the data Compaction error like this [post](https://www.tiot.jp/riak-docs/riak/kv/2.9.10/using/repair-recovery/repairs/) says:

find . -name "LOG" -exec grep -l 'Compaction error' {} \;

./308285501624487334308589769401090949458673270784/LOG
./336830455478606531929755488790080852186328203264/LOG
./365375409332725729550921208179070754913983135744/LOG
./793549717144513693868406999013919295828807122944/LOG

The error messages in partition logs like below:

2024/05/25-16:30:51.332435 7f04c47f8700 Finalize level: 5, grooming 1
2024/05/25-16:30:51.332506 7f04c47f8700 Finalize level: 6, grooming 0
2024/05/25-16:30:51.332570 7f04c3ff7700 Compacting 1@6 + 0@7 files
2024/05/25-16:30:51.333295 7f04c3ff7700 compacted to: files[ 3 0 3 765 482 109 126 ]
2024/05/25-16:30:51.333312 7f04c3ff7700 Compaction error: IO error: /data/riak/leveldb/308285501624487334308589769401090949458673270784/sst_7/307388.sst: No such file or directory
2024/05/25-16:30:51.333319 7f04c3ff7700 Waiting after background compaction error: IO error: /data/riak/leveldb/308285501624487334308589769401090949458673270784/sst_7/307388.sst: No such file or directory
2024/05/25-16:30:52.334919 7f04c3ff7700 Finalize level: 5, grooming 1
2024/05/25-16:30:52.335003 7f04c3ff7700 Finalize level: 6, grooming 0
2024/05/25-16:30:52.335061 7f04c37f6700 Compacting 1@6 + 0@7 files
2024/05/25-16:30:52.335507 7f04c37f6700 compacted to: files[ 3 0 3 765 482 109 126 ]
2024/05/25-16:30:52.335522 7f04c37f6700 Compaction error: IO error: /data/riak/leveldb/308285501624487334308589769401090949458673270784/sst_7/307389.sst: No such file or directory
2024/05/25-16:30:52.335528 7f04c37f6700 Waiting after background compaction error: IO error: /data/riak/leveldb/308285501624487334308589769401090949458673270784/sst_7/307389.sst: No such file or directory
2024/05/25-16:30:53.337142 7f04c37f6700 Finalize level: 5, grooming 1

All partitions used about 30GB each one, except which nodes have compation erros. Below the size of these partitions:

1.3T  ../308285501624487334308589769401090949458673270784
67G  ../336830455478606531929755488790080852186328203264
159G  ../365375409332725729550921208179070754913983135744
577G  ../793549717144513693868406999013919295828807122944

Did the disk keep growing caused by these compation errors? After repair these partitions/vnodes, will the space been released? If not, what can I do?

So I have a test db server that was setup on a replication stream. Over the name an optimize came through that quickly filled up the space on the slaves datadir. Mysql dutifully was just waiting for some more space. This datadir is a file system used ONLY as mysql's datadir so there wasn't anything else to free up. I had a 4 gig innodb test table that wasn't part of the replication stream so I figured I'd try something to see if it'd work, and being a test environment I wasn't too worried if things went horribly wrong. Here's the steps I took 1. Flushed the table I was about to move 2. Placed a read lock on it (even though nothing was writing to it and it wasn't in the replication stream) 3. Copied the .frm and .ibd over to a filesystem w/ some spare room 4. Unlocked the table 5. Truncated that table - this freed up enough space for the optimize to finish have replication start chugging along again. 6. Stop slaving/shutdown mysql 7. Copy the file out of tmp back to the data dir 8. Restart mysql Nothing shows up in the .err log, things look good. I connect and use mydb; and see the table I was messing with in show tables. But, if I try select * from testtable limit 10; I get the error ERROR 1146 (42S02): Table 'mydb.testtable' doesn't exist From what I can tell so far I can read from all the other tables just fine and replication started back up w/o any complaints. Is there anything I can do to recover from this point? I can rebuild it from scratch if need be but was curious what others thought about this venture in general. Was there anything about the series of steps I took that would have ended up w/ more flawless results? What if this wasn't a test server I couldn't just 'do it live' and see what happens? What would have the best way to free up space temporarily on a production slave if I had to like that?

Suppose you have a PostgreSQL database of total size X hosted on a local server (i.e. expanding disk space in an emergency is difficult). How much space should the server actually be allocated to be safe? I recently ran into unexpected issues with such a server running out of space unexpectedly due to various background processes, WAL generation, vacuuming, etc. causing temporary ballooning of the amount of space consumed by the database, so you clearly need some headroom. Given that things like doing a full vacuum of a table can double the space usage of the table, I'm tempted to say that you should just have 2X disk space on the server to be safe, but maybe that's overkill and I'm wondering if there are any rules of thumb for how much disk space should the server should actually be given?

I frequently find the output of sp_spaceused misleading. What appears to be the size of all data in a database is actually [the combined size of both the data file and the log file](https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-spaceused-transact-sql?view=sql-server-ver16#result-set) . I have recently discovered sys.database_files and I have found it superior in every way. It is so much better than sp_spacedused that I am planning to drink until I forget about that procedure. This give me my question: **When checking the size of a database, is there any feature or return value offered by sp_spacedused that cannot be obtained from sys.database_files?** I've checked over the documentation and I'm pretty sure that there isn't.