There are several sources that can be used for contacts. I eg use CardDAV (and CalDAV for calendars) with my own Nextcloud install (an app named DAVx5, formerly called DAVDroid, takes care for the sync). There are more sync apps available, and also other, more light-weight server software (like Baïkal).
You can also use a local contact storage and take care for backups manually (using the Contact app's export/import feature). On-device they all use the same database (which has a field to mark the source, so data added to one does not get accidentally synced to another). If your device does not provide such local storage, apps like MyLocalStorage can provide it.
Should you want to hide contacts from other apps, you'd need to keep them away from the "ContactsStorageProvider" offered by the system. This is possible using apps that provide their own contact storage, such as Contact Book or OpenContacts.