PostgreSQL
An open-source relational SQL database, ACID-compliant, the default under Payload CMS and Medusa.js.
Backend & APIsUsing since 2022 · Featured project: Budzowska Fiutowski i Partnerzy
- Category
- Backend & APIs
- Using since
- 2022
- Featured project
- Budzowska Fiutowski i Partnerzy
In practice
PostgreSQL is my default database when the data has relations and has to stay consistent: content, users, orders. At Budzowska Fiutowski i Partnerzy it holds the whole law firm site under Payload CMS. When a mobile app has to sync data live, I reach for Firebase.
What is PostgreSQL?
PostgreSQL is an open-source relational database with SQL. Data lives in tables linked by foreign keys, and transactions are ACID-compliant, so an interrupted write never leaves the database half changed.
Besides tables it offers a jsonb type for JSON documents, which can be indexed. It runs on all major operating systems, so it sits the same way on a VPS, in a container or in a cloud, and its licence is permissive, similar to BSD and MIT.
- Model
- Relational, SQL language
- Transactions
- ACID-compliant
- JSON data
- jsonb type with indexes
- License
- The PostgreSQL License
How I work with PostgreSQL
I start with the data model, not the tool. I settle which objects relate to each other and which data must always stay consistent, because the schema follows from it.
Schema and constraints
I describe tables, foreign keys and constraints so the database itself rejects bad data. Schema changes live in migrations in the repository.
Database under a framework
Under Payload CMS and Medusa.js PostgreSQL is the default database, so the framework provides the base schema. I add my own tables and indexes where queries start to slow down.
Deployment and backups
I deploy a project with its database in Docker on a VPS. I agree backups and database updates at the start, because they are easy to forget later.
When it fits, and when it doesn't
PostgreSQL fits where the data has structure and its correctness matters more than a quick start.
A good fit when
- Data with relations: users, orders, content with links between items.
- Reports, filters and sorting across several fields at once.
- A shop or CMS that supports PostgreSQL out of the box.
- A project where the data should stay with you and stay portable.
Not a fit when
- A mobile app that has to sync data live without a server of its own.
- A weekend prototype where nobody wants to run a database.
- A team with nobody to look after backups and updates.
In those cases I usually pick Firebase, and the differences are laid out in the comparison below.
PostgreSQL or Firebase?
PostgreSQL is a relational database you run wherever you like. Firebase is a Google platform with document databases, sign-in and functions. I compare PostgreSQL with Realtime Database and Firestore, and leave out SQL Connect, the separate Firebase service built on PostgreSQL.
| Criterion | PostgreSQL | Firebase |
|---|---|---|
| Data model | Tables linked by relations | JSON documents in collections or a tree |
| Queries | The full SQL language | Queries over collections and paths |
| Live updates | Need your own server layer | Sync built into the client SDK |
| Hosting | Your server or any cloud | Google cloud, no server of your own |
| Offline mode | Handled in the app, you write it | The SDK persists data on the device |
| Access rules | Roles and grants in the database or code | Security Rules set in Firebase |
Choose PostgreSQL when
the data has relations, reports or payments, and the database should be yours and movable to another server. In return you do the backups and updates yourself.
Choose Firebase when
a mobile app has to sync data live and work offline, and nobody can run a server. You get ready-made sign-in and functions, but tie your data to one vendor.
On Budzowska Fiutowski i Partnerzy I used PostgreSQL 17 under Payload CMS, and on Blanket I used Firebase Realtime Database for policies and claims that update live.
Frequently asked questions
Choosing a database for a new app?
Describe what data you have and who will use it. I will tell you whether PostgreSQL is enough, or whether the app needs live sync and Firebase suits it better.