What's the difference between a tuple and a row in Postgres?

Question

My general understanding is that a tuple is a row. However, I'm using the Postgres dev plan in Heroku. It has a limit of 10,000 rows. I have over 100,000 entries for n_live_tup. How can this be?

Hardiman

Answer 1

"Tuple" is the abstract term, "row" is for the concrete implementation.
Just like "relation" versus "table".

In Postgres' MVCC implementation, multiple instances of the same table row can exist concurrently - for different snapshots of the same table or as obsolete "dead tuples". In Postgres parlance "row" is the umbrella term in this case and "tuple" is used for one instance. But that's more what you call guidelines than actual rules.

Otherwise the terms are often used meaning the same. You can find more for each of them in Wikipedia.

No idea what's behind the Heroku policies. But the numbers for n_live_tup in pg_stat_user_tables or pgstattuple represents "the number of live rows" in the table. See:

• What is the meaning of n_live_tup and n_dead_tup in pg_stat_user_tables

Florentinoflorenza

PostgreSQL uses a fixed page size (commonly 8 kB), and does not allow tuples to span multiple pages. (from the doc) - Can we say that this statement refers not only to rows but to index entries too? – Enalda

@IlyaLoskutov: Yes. But there's more to it: See: dba.stackexchange.com/a/217088/3684 – Florentinoflorenza

Answer 2

In Postgres, the term

row represent a logical representation of one entry

tuple is the physical representation of that one entry.

(Note: just to avoid name confusion, I'm saying entry in the table)

So, let’s suppose there is a freshly created entry say R1. At this point of time this entry is row as well as tuple. But when it gets updated then a new entry will be created say R2 and similarly whenever this entry is updated a new entry will be created R3, R4 and so on. Whenever a new entry is created the old one is marked a dead if it is not used by any of the transactions present in the db.

So, if we notice properly for that one single entry we have multiple versions inside the databases but whenever any client / applications asks for this entry it should return only one entry. So, these multiple versions(R1, R2, R3, etc) of the entry are called tuples and whichever is the latest tuple (unless delete command for this entry is not ordered) will be row for the application.

Ibnrushd

Answer 3

A row within a table is typically referred to as a tuple in PostgreSQL. The n_live_tup parameter in the Postgres development plan on Heroku, on the other hand, refers to the expected number of live tuples. Live tuples include both visible and invisible rows, and they may include more rows than the whole table. This seems to be the reason that you're up to your limit.

Radii