14 DAY TRIAL // The next recommended procedure is reindexing the database. If you want to create a new table, the fastest procedure is to create the table, load the data using COPY command and at the end, and create the indexes needed for the table. Creating an index on pre-existing data is quicker than updating it incrementally as each row is loaded.
If you want to recover some space, use the VACUUM command. When you use VACUUM, not only do you recover the space occupied by updated or deleted rows, but you also update data statistics used by the PostgreSQL query planner and you protect yourself against loss of very old data due to transaction ID wraparound.
If you want to recover more space, you can dump the database and then restore it. Dump the database using pg_dump command, then drop it using dropdb. command and finally recreate it using createdb command with the same encoding and owner. Using pg_dump you will extract the database into a script or a file. For example, if you want to dump a database use: $ pg_dump softpedia > softpedia.sql .
For createdb command I specified that you must use the same encoding and owner. This should look like this: $ createdb -p 5000 -o softpedia -h softpedia -E LATIN1 -e softpedia CREATE DATABASE "demo" WITH ENCODING = 'LATIN1' CREATE DATABASE
Using this command, you create the database softpedia on host softpedia on port 5000 using encoding LATIN1 and with the owner softpedia.
dropdb removes the catalog entries for the database and deletes the directory containing the data. If you want to use this command, you must have owner rights or superuser rights. An example of this command: $ dropdb -p 5000 -h softpedia -i -e softpedia Database "softpedia" will be permanently deleted. Are you sure? (y/n) y DROP DATABASE "softpedia" DROP DATABASE
Using this command you will drop the database softpedia from host softpedia on port 5000 and we will activate interactive mode, to be sure you will not make a mistake.