PostgreSQL database development

When PostgreSQL is the right choice

PostgreSQL is an open-source relational database. It keeps data in tables, and the database itself enforces the links between them (a student’s class, an exam’s questions, a payment’s invoice). It is our default for business software where records depend on each other and the same data has to produce different reports for different people.

Where PostgreSQL fits well

Your records are linked: users, classes, exams, orders, payments
The same data has to produce different reports for admins, teachers or customers
You keep records that must never be half-written, such as money, stock or grades
You want similarity search for an AI feature without moving it to a separate service

Where we would suggest another tool

The app mainly needs live sync between screens and has no complex server-side queries: a service like Firebase gets you started faster
All you need is a cache or a job queue: Redis suits that better, and in Hippotenus caching and queues run on Redis next to PostgreSQL
What you store is mostly files, images or video: those belong in object storage, with only their records in PostgreSQL

Relational or document database

We run both approaches in live products. Hippotenus, Logaritim and Atölye Tasarım are on PostgreSQL; SoruVerse keeps its data in Firestore, and Rigel’s back end uses MongoDB. We choose by the shape of the data and the questions that will be asked of it later.

	PostgreSQL (relational)	Document database (Firestore, MongoDB)
Data model	Tables and the links between them; rules live in the database	Self-contained documents; rules live in application code
Reporting	Joining several tables in one query is natural	Joins mostly happen in code or by duplicating data
Schema changes	Controlled, through migration files	Flexible; consistency is the application’s job
Flexible fields	Possible with a JSONB column	Native
Our example	Hippotenus, Logaritim, Atölye Tasarım	SoruVerse (Firestore), Rigel’s back end (MongoDB)

The line is not always sharp. In Atölye Tasarım a school’s 3D workshop design is stored as a single document in a JSONB column, while the fields we query, such as the date, city and price range, are ordinary columns. The record gets the flexibility of a document and the queryability of a table at once.

Schemas and migrations: changing the database with the code

Every change to the database structure sits in the repository as a migration file and is versioned with the application. In Hippotenus the Prisma schema is split into files by domain, such as academics, finance and surveys; the API’s Docker image applies pending migrations as it starts, and if one fails the new version does not come up and the old one keeps running.

In Logaritim, migrations are numbered SQL files; each runs inside a transaction, is written to be safe to run twice, and is applied before the server starts.
In a client project, a separate one-off “migrate” service applies the Drizzle migrations, and the application only starts once it has finished successfully.
In the same project, a trigger makes the audit-log table append-only: records cannot be changed or deleted, not even by the application.
In Hippotenus several institutions share one system; records carry an institution ID and queries filter on it. Most deleted records are not removed but marked with a deletion date.
In Hippotenus we check that constraints (uniqueness, foreign keys) really hold with integration tests that run against a real PostgreSQL database.

Reporting queries, vector search and the tools we use

We write most of an application with an ORM, but report screens get plain SQL. The mock-exam analysis and report cards in Hippotenus compute each student’s correct, wrong and blank counts with one query that joins the exam, question, class and branch tables. The table holding raw usage events is partitioned by date. In Logaritim, vector representations of questions sit in the same database through pgvector, and the similarity search used to pick examples during question generation and to match topics across different exams runs there.

Prisma

Type-safe queries and migration management. We use it in the Hippotenus NestJS API.

Drizzle

A lightweight TypeScript ORM that stays close to SQL. We use it in Atölye Tasarım and in a client project.

pgvector

Vector similarity search inside the database, with an HNSW index. We use it in Logaritim.

psycopg and asyncpg

Connecting Python services to PostgreSQL. We use them in Logaritim’s FastAPI back end.

PostGIS

For location and boundary data. We use it in a client project with .NET and Entity Framework Core.

Redis

Caching and job queues next to PostgreSQL. We use it in Hippotenus with BullMQ.

How a PostgreSQL project runs

01
Free scoping call
30 min
We talk about the information you keep, who needs which report, and what data you already have (spreadsheets, an old system).
02
Written scope and quote
We write down the tables and how they relate, the reports you need and the acceptance criteria, together with the quote.
03
Weekly releases
Every week you try the working version on sample data; schema changes arrive as migration files with that week’s release.
04
Testing and release
A senior QA tests the build before release, and report figures are checked against sample data.
05
The 30 days after launch
30 days
We fix the bugs that surface in this period free of charge; after that you can continue with monthly maintenance.

Timelines and budget ranges are published on our service and pricing pages; everything on this page follows the same figures.

Questions about PostgreSQL

Do I pay a licence fee for PostgreSQL?

No. PostgreSQL is open source and free; the cost is the server or managed service that runs it. Atölye Tasarım uses a managed, serverless PostgreSQL service, while the Hippotenus database runs in Docker on our own server.

Can you move my data over from spreadsheets or an old system?

Yes. We settle the scope of the data to migrate during discovery and write it into the quote. In Hippotenus we loaded the YÖK Atlas data and the question pool with import scripts written for the job.

Is my data backed up?

We set up the backup plan as part of the project. In Hippotenus the databases are backed up every night, the backups are encrypted and copied to separate storage in Türkiye, and a restore has been tested. The database is not exposed to the internet; only the application reaches it.

What do I keep when the project ends?

A perpetual licence to use the software and user documentation. If you want the source code, migration files included, transferred to you, that is possible for an extra fee, stated separately in the quote.

PostgreSQL database development

What we have built with it

Hippotenus

Logaritim

Atölye Tasarım