This nomenclature all comes down to knowing about MVCC, or Multi-Version Concurrency Control, which is how PostgreSQL handles transactions and concurrency.
When you update or delete a row, doesn't actually remove the row. When you do an UPDATE
or DELETE
, the row isn't actually physically deleted. For a DELETE
, the database simply marks the row as unavailable for future transactions, and for UPDATE
, under the hood it's a combined INSERT
then DELETE
, where the previous version of the row is marked unavailable. These previousnew versions of rows are generally referred to as the "live" rows, and the older versions are referred to as "dead" rows.
The statistics that you are looking at, where it shows 213,324,422 inserted tuples
are the number of new data inserts that has been done on that table. The 124,510,280 live tuples
are the number of tuples that are up to date in the table and available to be read or modified in transactions. The 3,087,919 dead tuples
are the number of tuples that have been changechanged and are unavailable to be used in future transactions. The autovacuum daemon, or a manual vacuum will eventually come along and mark the space of those "dead" tuples available for future use, which means that new INSERTS
can overwrite the data in them.
Side note, since these are just statistics that are gathered by the statistics collector, they aren't going to be exact, but they're good enough to give you an idea of how active the table is, and how much bloat there is (live vs dead tuples), and how well the autovacuum daemon is keeping up with your workload.
Hopefully this makes it clearer. You can read more about this in detail in the PostgreSQL Manual in the chapter on Concurrency Control