This page of the guide to Resident Evil Village will teach you how to use lockpicks. You'll learn what kinds of containers can be opened with lockpicks, and whether a single lockpick can be used more than once.
The lockpicks in Resident Evil Village are used to open certain containers. When attempting to interact with the container, Ethan will comment on this action and hint that he could use a lockpick. Locations where you found locked containers are marked on the map with a lockpick icon and a Lockpick signature.
The firs lockpick is in Duke's possession, who is a mobile trader that appears throughout the game. You should be able to get the lockpick for free.
You will have to find the next lockpicks by exploring the area. They may, for example, lie in plain sight. Each lockpick needs 1 free slot in your inventory.
Lockpicks in RE8 are disposable which means that when you use a lockpick, it will be removed from your inventory. In order to open the next locked container, you will be required to obtain a new lock pick.
If you are not sure whether opening a container will be worth it, you can save the game and load the previous save if you find a low-value item. Fortunately, the game allows you to revisit selected locations, making it possible for you to open the locked containers you found earlier.