Learn

As you saw in a previous exercises with UPDATE, even when the total number of rows stored in the table is unchanged, disk utilization can increase. The behavior for DELETE operations is slightly different. Unlike updates, deletes don’t add space to a table. However, a DELETE statement will create dead tuples and leave the size of the table unchanged.

Imagine we have a table,promotions.contest_entries that contains 100,000 rows of data used for a virtual lottery drawing, and we’d like to run a query nightly to remove entrants that have not logged in since the contest started.

user_id contest_id num_logins
1 1 8
2 1 12
3 1 0

SELECT pg_size_pretty( pg_total_relation_size('contest_entries') ) as total_size;
total_size
784 kB

DELETE FROM contest_entries WHERE num_logins = 0
>> Delete 1000

Re-running pg_total_relation_size() on this table returns the following.

total_size
784 kB

The size of a table alone doesn’t give us all the information we need about maintenance status. We can query pg_stat_all_tables to help us understand the status of tuples in a table. Specifically, we can use the columns n_dead_tup, and n_live_tup from this table to asses the status of the table.

SELECT schemaname, relname, n_dead_tup, n_live_tup FROM pg_stat_all_tables LIMIT 3
schemaname relname n_dead_tup n_live_tup
promotions contest_entries 1000 99000
promotions contests 0 25000
promotions prizes 0 100

We can see that the update created 1000 dead tuples, the same as the number of rows we deleted. We can also use the column n_live_tup to determine that only 99,000 rows are being displayed (meaning that the 1000 deleted rows have been marked dead)

Depending on the settings on your database, you may need to ANALYZE <table name> a table to see accurate statistics in pg_stat_all_tables.

In other cases, your database’s autovacuum might be triggered by an update, in which case you could expect a result like the below, where the dead tuples have already been cleared.

schemaname relname n_dead_tup n_live_tup
promotions contest_entries 0 99000

Instructions

1.

In this exercise, we’ll use a table named mock.stock_prices which contains stock prices for a fictional company. To start, we have the following result from checking the table’s total size.

SELECT pg_size_pretty( pg_total_relation_size('mock.stock_prices') ) as total_size;
total_size
320 kB

Delete all values from this table that are recorded on weekends (identified with day_id = 5 or 6).

2.

Check the total size of the table including index and table data. Has the disk space of the table grown, stayed the same, or shrunk?

3.

Run an ANALYZE on mock.stock_prices to ensure that all the statistics in pg_stat_all_tables are up to date.

4.

Check the number of live (n_live_tup) and dead (n_dead_tup) tuples in the table after the delete by querying pg_stat_all_tables. Is this number consistent with the number of deletes we saw in checkpoint 1?

Take this course for free

Mini Info Outline Icon
By signing up for Codecademy, you agree to Codecademy's Terms of Service & Privacy Policy.

Or sign up using:

Already have an account?