PostgreSQL database development for business records
If your app has customers, invoices and the links between them, a relational database enforces them. Without those rules, an invoice can end up pointing at a customer record that was deleted.
Reviewed by: Rahul Jaiswar, founder
The database
PostgreSQL is an open-source relational database. It stores data in tables with enforced relationships and transactions, and also handles JSON documents, full-text search and extensions.
Where we use it
Microsive ATS and other apps we build store their data in PostgreSQL. The support chatbot inside Microsive ATS answers using PostgreSQL full-text search rather than vectors; more on that approach on the RAG page.
What we check
Internal and staff screens get the same per-client filter as customer screens, and that is checked before release. We also store time zone information with each timestamp and check durations against a known event, because IST stored without a zone and read as UTC is five and a half hours out and still looks plausible.
Where PostgreSQL fits
- Records relate to each other: a candidate placed at a company, an invoice with GST lines.
- Changes must be all-or-nothing. A payment marked received and an invoice marked paid succeed together or not at all.
- You want reports in plain SQL, and the freedom to change hosts later.
- Several client organisations share one app and their data has to stay apart.
Where another store fits better
Large analytics event logs and temporary data such as queue entries belong elsewhere. For how far PostgreSQL alone can take an app, see our article on when PostgreSQL is enough.
Two more data-safety risks
- Lost updates. Two people saving the same record at once can silently overwrite each other. Use a version column checked on save, or row locks.
- Backups nobody has restored. A platform’s automatic backup is only a start. Write down the restore steps and try them, whichever host runs the database.
Questions to ask about your database
- How is one customer’s data kept away from another’s, and is that tested with two customers?
- When was a backup last restored, and how long did it take?
- How are schema changes written, reviewed and applied?
- Are timestamps stored in UTC, and where are they shown in IST?
- Which slow queries have indexes, and how would new slow ones be found?
- Where are backups stored, and who can reach them?
Where the database itself sits is a per-project choice, decided with you at the start; the infrastructure page covers what pulls it closer or further away.
To commission an app built on PostgreSQL, see web apps.
Need a database designed, or an existing one sorted out?
Tell us what the data is and who needs to see it, or what the database you already have is doing wrong. Someone from our team replies within one business day.