PostgreSQL for those who came from tables

What does this solve?

The table breaks predictably. A file of several hundred thousand lines opens for a minute and crashes during recalculation. The formula refers to a range that has moved out after inserting a row, and the numbers quietly lie. There is “report_final” and “report_final_2”, forty lines apart, and no one remembers which one is correct. Someone deleted a column, but they found out about it a month later. PostgreSQL removes exactly these four pains: the volume ceases to be limited by the memory of one machine, several people write simultaneously without copies of the file, data is protected by rules at the storage level, and any figure is reproduced by a query, and not by a sequence of clicks.

How it works

The easiest way to enter is through correspondence to familiar things.

The main difference is in behavior: the database does not have formulas inside the cells. The data is stored, calculations are made at the time of the request. This removes the ranges that have moved out - the request describes what needs to be obtained, and not along which coordinates to crawl.

Column type and constraint are data validation; everything that is not prohibited by the scheme will sooner or later end up in the table.

Types should be chosen once and consciously. text for strings of any length - the length limit in Postgres does not provide a gain in speed, only an extra reason for an error when importing. integer i bigint for counters and identifiers. numeric for money: this type counts decimals accurately. double precision is not suitable for money at all - the sum of three hundred lines of 0.1 each will give a tail in the fifteenth digit, and then there will be a discrepancy with the accounting department by a penny, which will take a week to look for. date for dates without time, timestamptz for points in time: it stores the value in UTC and translates to the session time zone when read. Regular timestamp stores what is recorded, without any zone - when working from different cities, this is a source of silent errors. boolean instead of "yes"/"no" string. jsonb for irregular attributes that do not have a common set of fields.

Separate topic - NULL. It means "no value" and it is not equal to the empty string, null, or anything else NULL. Comparison where comment = null will not find anything, needed is null. Aggregates sum i avg skip NULL, and count(*) counts entire lines, whereas count(comment) —filled only. Half of the moving variance in the tables is explained by this pair.

Relationships instead of VLOOKUP are built through a foreign key. In the table orders lies client_id, which refers to clients.id. The database will not allow you to record an order for a non-existent client and will not allow you to delete a client who has orders until the desired behavior is described. The connection is made explicitly:

select c.name, count(*) as orders, sum(o.total) as revenue
from orders o
join clients c on c.id = o.client_id
where o.created_at >= date_trunc('month', now())
group by c.name
order by revenue desc;

This is a summary table, only written in text and repeated at least every hour.

Indices replace manual sorting. By default, a B-tree is created - it speeds up equality, ranges and sorting. To search inside jsonb and full text search require GIN. The index is not free: it takes up space and slows down recording, so adding it to every column is pointless. The unique index also acts as a limitation and does not allow you to have two clients with the same TIN.

The transaction is “save a copy before editing”, only automatic. Between begin i commit changes are either applied in their entirety or not at all. Transfer of balance from warehouse to warehouse is two operations within one transaction, and there is no intermediate state where the goods have disappeared from both warehouses.

Changing the structure is done by migrations - SQL files that are located in the repository next to the code. alter table orders add column comment text; is a record in history, and not a silent edit of someone else's file.

Work from the terminal goes through psql: \dt shows tables, \d orders —structure, types and restrictions of a specific table, \copy orders from 'data.csv' csv header uploads CSV. For those who need a familiar grid view, there are graphical clients and BI tools that read the same database.

Useful scripts

• Accounting that has outgrown the Excel workbook: clients, orders, payments in related tables instead of three sheets with VLOOKUP on top of each other.
• Showcase for BI: a database as a single source, on top of which the dashboard builds graphs without manual uploads.
• Gluing uploads from different systems: CRM, bank, advertising are uploaded into separate tables and connected by key instead of manual comparison.
• Event log and audit: who changed the record and when, with history instead of an overwritten cell.
• Directory for a website or application: one table of prices and availability, from which all channels are read at once.

Limitations

The database does not store the design. Color fills, notes in the margins, merged cells, manual notes “check with Sergey” - all this will either have to be turned into separate columns or lost. For many tables, this is half the point of the file.

You can’t view the database without a client. You won’t be able to open it by double-clicking and look through it with your eyes: you need psql, a graphical client, or a BI add-in. Entry threshold for colleagues who have never written select, real, and it must be included in the plan.

Changing the structure on a large table takes a lock. Adding a column with a constant default value is fast since PostgreSQL 11, but changing the column type overwrites the entire table and temporarily makes it unwritable. Live indexes are created via create index concurrently, otherwise the recording will stop.

CSV import is unforgiving of dirty data. Space in numeric field, date in format 31.02.2024, thousand separator within the sum - all this will stop the download. Cleaning happens once and painfully, usually through a staging table where all columns are declared as text.

The database does not back up or monitor itself. Managed hosting takes some of the worry off, but your own server does not. A backup that has never been restored is not considered a copy.

It is still faster to do a one-time calculation for two hundred rows in a table. Moving is justified when data lasts longer than one task and is accessed by more than one person.

How to check the result

After the transfer, checksum verification. For each source, the number of rows and the sum of key monetary fields are calculated, the same is calculated in the database, the numbers must match down to the penny. The discrepancy is almost always explained by two things: NULL instead of zero or missing rows on import due to a type error.

\d имя_таблицы indicates whether the restrictions have been reached. If not null, unique and there are no foreign keys, the scheme exists only on paper. The functionality of the restrictions is checked by trying to violate them: inserting a duplicate on a unique field must return an error, and not be recorded.

explain (analyze, buffers)
select * from orders where client_id = 42;

Index access should appear in the plan. Seq Scan on a large table means that the index is missing or does not meet the condition. Chronically slow requests are caught by the extension pg_stat_statements —it accumulates statistics on all completed requests and shows what exactly is eating up time.

Time zones are checked in advance: show timezone; i select now(); from the same connection from which the application is running. If the application and the database are separated by time zones, daily reports will lie at the boundaries of the day.

Last check - recovery. The backup copy is deployed to an empty database, and the same checksums are run on it. Until this procedure has been completed manually at least once, the data cannot be considered protected.