left join and return only the last record from right table

The JOIN condition for your join to second_table is incorrect; you need to join on both payment_instrument_id and id to get only the last row:

select p_i.id, p_i.user_id,  a_r.id, a_r.payment_instrument_id 
from payment_instruments p_i
left join (
  select a_r.payment_instrument_id, max(a_r.id) id
  from second_table a_r
  group by a_r.payment_instrument_id
) lastSD on lastSD.payment_instrument_id = p_i.id
left join second_table a_r on a_r.payment_instrument_id = lastSD.payment_instrument_id and a_r.id = lastSD.id  
where p_i.user_id = 1030

Output (for a truncated sample of your data):

id      user_id     id  payment_instrument_id
803     1030        86  803
958     1030        

Note you can simplify your code with a CTE using ROW_NUMBER():

WITH a_r AS (
  SELECT id,
         payment_instrument_id,
         ROW_NUMBER() OVER (PARTITION BY payment_instrument_id ORDER BY id DESC) AS rn
  FROM second_table
)
SELECT p_i.id, p_i.user_id, a_r.id, a_r.payment_instrument_id 
FROM payment_instruments p_i
LEFT JOIN a_r ON a_r.payment_instrument_id = p_i.id AND a_r.rn = 1

The output is the same as the first query.

Demo (of both queries) on dbfiddle