Insight is an additional currency besides Blood Echoes and you will mostly gain it by seeing and defeating bosses, or by playing online. Helping another player to defeat a boss or killing them by invasion both give you one insight. You can also increase it by using your "Madman's Knowledge".
Once you have 10 insight, another messenger store will open in the Hunter's Dream where you can spend your insight. More items will become available there as you progress in the game. For example, I could buy Father Gascoigne's clothing there after I beat him. I'm not 100% sure, but I believe killing bosses makes new items available. After killing a few bosses, I can buy materials like Bloodstone shards and a few consumables like Fire Paper. Keep in mind that the store closes again if your insight drops below 10.
Besides spending it on items, you can use insight to ring the Beckoning Bell, which enables other players, who rung a Small Resonant Bell, to join your game for a round of jolly co-operation. By spending one insight, you will invite other players into your game until you die, use the Silencing Blank or return to the Hunter's Dream.
The downside of gaining insight is, that enemy behaviour will change, which makes them more difficult to fight. Most of this is still unexplored, but it seems that the changes occur at 15 insight. One I noticed for example is an enemy in front of the Grand Cathedral, which gains a ranged magic attack. I found a video of it here.
Gaining insight also makes you more vulnerable to frenzy inflicting attacks.
Most of the effects of insight are still unknown, so there may be a lot more effects than we know of yet. For example, a few enemies stopped spawning and some were found in different places in my game, but I don't know if that was caused by gaining insight or because the game world changes as you kill more bosses.