What is PostgreSQL

Where a database helps a business

• Orders, payments and subscriptions must be stored reliably, not in a chat or a spreadsheet someone accidentally re-sorted.
• Several staff members or admins work with the same records at the same time.
• A bot and a website must see the same data.
• You need reports over history: by month, by year, by person and by category.

Types, and which one when

• SQLite: a database in a single file, with no separate server. For small bots: a club loyalty card, a birthday bot, a transcription cache. A backup is a copy of the file.
• MariaDB / MySQL: the WordPress database, and projects where the client already uses it. In the Tech Poly VPN billing, transactions and row locks in MariaDB stop a payment from being credited twice.
• PostgreSQL: our choice for new projects with money, history and analytics: bookkeeping, services with plans and roles, queues.

How we use it

• Finance assistant: PostgreSQL 17 in Docker for operations, accounts, rules, debts and budgets. A daily dump is checked for integrity and rotated, and a restore can be tried on a separate database without touching the live one.
• UNLOCK: PostgreSQL 17 for cards, replies, plans and sessions. Timestamps are stored as TIMESTAMPTZ, and the database publishes no port; only the service can see it.
• Electronic queue: PostgreSQL 15 through async SQLAlchemy, with the containers' time zone set explicitly so check-in times match Moscow time.

We design the database for the task under Telegram and VK bots and Websites.

Common problems

• An untested backup. A copy nobody has ever restored from may turn out to be empty. That is why we test restores on a separate database.
• A schema without migrations. The finance assistant has no migrations, so a version can be rolled back only from a database copy. Where there is a lot of data and the schema changes, migrations are needed.
• Time without a time zone. The club loyalty card stores time in UTC, and this is listed among its limitations. In new projects we store TIMESTAMPTZ and set the container's time zone.
• A database exposed to the outside. The database port is not published: only its own service sees it, inside an internal Docker network.

When you do not need it

A small bot for one club or one family does not need a separate database server: SQLite in one file is simpler to deploy and to back up. And a site of texts and cases needs no database at all: the studio website lives on files in git.