Use BIGINT and twice the storage space or INTEGER but with extra operations?
The third option for the reason you outline: you do not have to query agency every time.  It's not that the join/lookup would be particularly expensive for queries that return a small amount of rows, but:

Your database is easier to work with
Your code is more concise and easier to debug
It's easier to optimize queries that return more than a few rows
Avoids the overhead of the meaningless internal_id on the agency table.

That's certainly worth 4 bytes/row.  We're not storing data on tape any more, it's not as big of a consideration as it used to be.

You are right, but how about when the related tables contains trillions of rows. So for 1 trillion rows, it takes more 4TB data. Doesn't it affect the overall performance of database?

If you are reading the whole table, yes.  But most of the time we look for a few dozen/hundred rows at most.  And why extend to &quot;trillions&quot; of rows?  If you're dealing with that volume the hardware required isn't going to break due to an extra 4TB in the heap.