Use BIGINT and twice the storage space or INTEGER but with extra operations? Suppose I've a table named agency with some columns:
  internal_id(integer, unique)
, external_id(bigint, unique)
, name, location, created_at, ...

internal_id and external_id are each one unique and candidates for being as the primary key column.
There are some other tables (says A, B, C, D, E) that reference to this table. Suppose each of these tables may contains millions or billions of rows.
Normally I have the external_id when I need to filter the tables A, B, C, D, E data.
Which of the following scenarios are the best way to go, considering performance and storage space:

Use internal_id as primary key in agency, and as foreign key in other tables. Because this field takes 4 bytes of storage space, we can save billion of bytes. However as I normally have the external_id, I have to do an extra JOIN for each query as a penalty:

    SELECT A.* FROM A
         INNER JOIN agency ON A.internal_id=agency.internal_id
    WHERE agency.external_id=5;


Use internal_id as primary key in agency, and as foreign key in other tables. But to get rid of an extra JOIN, in my application I could first map external_id to internal_id with a simple query (SELECT internal_id FROM agency WHERE external_id=5), and then use the fetched internal_id for another simple query:

    SELECT * FROM A
    WHERE internal_id=59; -- 59 is the fetched internal_id from the other query

Does it have better performance than JOIN considering an extra round trip between app and database?

forgetting internal_id and use external_id as the primary key and foreign key, with the penalty of 4 more extra bytes per record in each other tables (A, B, C, D, E) and cost of billions of more storage space or potentially even slower database operations (because of bigger database files):

    SELECT * FROM A
    WHERE external_id=5

Update:

agency table may contains 10s of thousands or at most a few millions of rows.
internal_id and external_id will not change over time, but other non-identity columns may rarely change.
There are about 5 to 7 related tables (A, B, C, D, E, ...) that a few of them may get too large over time, say a few million rows per day (billions of rows over a year)
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.