PostgreSQL database development for business records

PostgreSQL is an open-source relational database. It stores data in tables with enforced relationships and transactions, and it also handles JSON documents, full-text search and extensions.

Yes, if every query is filtered by client and that filter is tested, including on the internal screens your own staff use. A 3PL running one app for dozens of clients is the typical case: each client’s inventory, orders and bills sit in the same tables, and the filter is what keeps them apart. PostgreSQL can also enforce it inside the database with row-level security, as a second line behind the app’s own checks.

Microsive ATS, the recruitment software we built and run, and other apps we build store their data in PostgreSQL. The support chatbot inside Microsive ATS finds its answers with PostgreSQL full-text search rather than vectors.

• Records relate to each other: a receipt against an advance ship notice (ASN), an invoice with storage and handling lines.
• Changes must be all or nothing. A payment recorded 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 organizations share one app and their data has to stay apart.

Very large analytics event logs, and short-lived data such as queue entries or cached sessions, usually belong somewhere else. For most business apps, PostgreSQL alone carries the records for a long time.

• 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 hosting platform’s automatic backup is only a start. Write down the restore steps and try them, whichever host runs the database.

1. How is one client’s data kept away from another’s, and has that been tested with two clients’ data side by side?
2. When was a backup last restored, and how long did it take?
3. How are schema changes written, reviewed and applied?
4. Are timestamps stored in UTC, and where are they shown in each site’s local time?
5. Which slow queries have indexes, and how would new slow ones be found?
6. Where are backups stored, and who can reach them?

Where the database runs is chosen with you at the start of the project; a US region is an option. To commission an app built on PostgreSQL, see software and web apps.