01What a database is
A database keeps an application's data in tables. Each table holds one kind of thing: one for customers, one for invoices, one for tickets. Each row is one record, each column one property, such as a name, a date or an amount. At first sight this resembles a spreadsheet.
The difference lies in the rules. A database checks every change before it stores it. An invoice cannot point to a customer who does not exist. An amount has to be a number. A date has to be a date. Two people who save at the same moment do not overwrite each other, and a change that fails halfway is rolled back completely, so the data is never left half-written.
02How it works
Tables are linked to each other. An invoice row stores the number of its customer, and the database can join the two tables at any time to answer a question such as which customers have unpaid invoices older than thirty days. These questions are written in SQL, a query language that almost every database understands in a similar form.
To answer quickly, the database keeps indexes, much like the index at the back of a book. With an index on the customer name, finding one customer among a million takes milliseconds.
Postgres runs as a server program. The application connects to it, sends queries and receives the results. The people using the application never see the database directly. They see screens that the application builds from the data.
03An example from a business application
Take a ticket system for client support. Postgres holds the companies, their contacts, the projects, the tickets and the hours logged on each ticket. When a support request arrives by email, the application creates a ticket row linked to the right company and project.
When an hour is logged, it is linked to the ticket and to the person who worked it. When an invoice is written, the hours on it are marked as billed, and the database refuses any later change to them. All of this happens inside one transaction: either every step is saved or none is.
Reports come from the same tables. Hours per client this month, open tickets per project, revenue per quarter: each is one query, and the numbers always match what the screens show, because there is only one copy of the data.
04Postgres in particular
PostgreSQL, Postgres for short, began in 1986 as a research project at the University of California, Berkeley. It has been developed as open source since 1996 by a community of companies and individual contributors, with a new major version every year.
Nobody owns it. There are no licence fees, and the licence allows any use, including commercial products. Every large cloud provider offers Postgres as a managed service, so the data can move from one provider to another with standard tools.
Postgres is also extensible. Additional features can be installed into the database itself. Two that matter for our work:
- pgvector stores vectors, lists of numbers that describe the meaning of a text or an image. With it, the database can find documents that are similar in meaning to a question.
- Full-text search finds words in long texts, including different forms of the same word.
05Alternatives and how they compare
MySQL and its offshoot MariaDB are also open source and widely used, especially for websites. They cover similar ground; Postgres tends to offer more data types, stricter checks and more extensions.
Microsoft SQL Server and Oracle Database are commercial products with licence fees. They are common in large companies that already run other software from the same vendor.
Document databases such as MongoDB store records as flexible documents without a fixed table structure. That is convenient at the start of a project and harder to keep consistent later, when many parts of an application rely on the same data.
For business applications with customers, invoices and permissions, a relational database such as Postgres is the usual choice, because the rules that keep the data consistent are built in.
06Costs and what to watch out for
The software itself is free. The costs come from where it runs: a managed Postgres service is billed by computing power, storage and backups. For a small business application this starts at a few euros a month and grows with the amount of data and the number of users.
Points worth checking when a database is set up:
- Location: the data centre should be in the EU if the data includes personal data.
- Backups: automatic, regular, and tested by actually restoring one.
- Account ownership: the database should run in an account that belongs to you, so you keep control of the data.
- Access: only the application and the named people who maintain it should be able to connect.
- Structure changes: changes to tables should be scripted and repeatable, so the same change runs the same way in testing and in production.
07How we use it
Our applications keep their data in Postgres, usually at Neon in Frankfurt. Neon separates storage from computing power, so a database that is idle costs very little.
In Kadvern One, our own operating system, every proposed change gets its own branch of the database: a separate copy of the structure, with test data and no client data. A new feature is tried out there before it touches the real data.
For search by meaning we use pgvector inside the same database. For a document search we are building for a client, we chose Azure Database for PostgreSQL in Frankfurt with pgvector, so the data stays with one provider in Germany.
At handover, the database runs in the client's own account, together with the backups.
Frequently asked questions
Can we get our data out of Postgres again?
Yes. A complete export takes one standard command and produces a file that any other Postgres server can read. Individual tables can also be exported as CSV and opened in Excel.
Is Postgres fast enough for large amounts of data?
Postgres handles tables with many millions of rows. Speed depends mostly on sensible indexes and well-written queries, which are part of building the application.
Where is our data stored?
In the data centre of the provider that runs the database. We set it up in the EU, usually in Frankfurt, and in an account that belongs to you.
What happens if the database is damaged or someone deletes something by mistake?
Managed Postgres services keep continuous backups. The database can be restored to its state at a chosen point in time within the retention period, often down to the second.
Do we need a database administrator?
For a managed service, usually not. The provider takes care of updates, backups and hardware. Changes to the structure come with the application, as part of its code.