How to Create and Optimize a PostgreSQL Database for Scalable Apps

A useful database does not start with the CREATE DATABASE command, but with the rules that the data must preserve. This tutorial uses PostgreSQL and an example of customers and orders to demonstrate creation, relationships, querying, security, and backup.

Direct Answer

Install PostgreSQL, create a database and an application user, connect using psql, model entities and relationships, create tables with constraints, insert test data, query with JOIN, analyze indexes, limit privileges, and automate restorable backups.

1. Create the database and connect

With PostgreSQL installed and the service running:

createdb loja
psql loja

Or within an administrative session:

CREATE DATABASE loja;

If "permission denied" appears, the current user does not have CREATEDB. Do not turn the application account into a superuser; ask an administrator to create it.

2. Model before creating tables

Example rules:

• customer has a unique email;
• order belongs to a customer;
• value cannot be negative;
• status only accepts known states;
• dates are recorded with time zone.

Avoid storing a list of orders within a customer column. Relationships and constraints make inconsistencies harder to manage.

3. Create the tables

CREATE TABLE clientes (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  nome text NOT NULL,
  email text NOT NULL UNIQUE,
  criado_em timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE pedidos (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  cliente_id bigint NOT NULL REFERENCES clientes(id),
  valor numeric(12,2) NOT NULL CHECK (valor >= 0),
  status text NOT NULL CHECK (status IN ('pendente','pago','cancelado')),
  criado_em timestamptz NOT NULL DEFAULT now()
);

Use numeric for money when decimal precision is required. Do not use floating point for financial values. The foreign key prevents orders for non-existent customers.

4. Insert and query

INSERT INTO clientes (nome, email)
VALUES ('Ana', 'ana@example.com')
RETURNING id;

INSERT INTO pedidos (cliente_id, valor, status)
VALUES (1, 149.90, 'pago');

SELECT c.nome, p.id, p.valor, p.status
FROM pedidos p
JOIN clientes c ON c.id = p.cliente_id
ORDER BY p.criado_em DESC;

In applications, use prepared parameters. Never construct SQL by concatenating text received from the user.

5. Use transactions

When two changes need to happen together:

BEGIN;
UPDATE estoque SET quantidade = quantidade - 1
WHERE produto_id = 10 AND quantidade > 0;
INSERT INTO pedidos (cliente_id, valor, status)
VALUES (1, 149.90, 'pago');
COMMIT;

The example would still need to check if the update altered a row. In case of an error, use ROLLBACK. Transactions maintain atomicity, but concurrency and isolation need to be designed according to the process.

6. Create indexes with evidence

Primary keys and UNIQUE already create indexes. To search for recent orders from a customer:

CREATE INDEX pedidos_cliente_criado_idx
ON pedidos (cliente_id, criado_em DESC);

Validate with:

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM pedidos
WHERE cliente_id = 1
ORDER BY criado_em DESC
LIMIT 20;

Each index takes up space and increases write costs. Do not create an index for a column without observing real queries.

7. Separate the application user

CREATE ROLE loja_app LOGIN PASSWORD 'replace-with-strong-secret';
GRANT CONNECT ON DATABASE loja TO loja_app;
GRANT USAGE ON SCHEMA public TO loja_app;
GRANT SELECT, INSERT, UPDATE, DELETE ON clientes, pedidos TO loja_app;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO loja_app;

Store the credential in a secrets manager, not in the code. Adjust privileges to what the application actually does. Production and test environments and databases should be separated.

8. Version migrations

Do not alter production manually without a record. Create ordered migration files, review, and test upgrade and rollback when possible. Large changes may require expansion, data migration, and later removal to avoid disrupting old versions.

9. Backup and test restoration

pg_dump -Fc -d loja -f loja.dump
createdb loja_restore
pg_restore -d loja_restore loja.dump

A backup that has never been restored is just a hope. Define frequency, retention, encryption, access, separate location, and acceptable loss objectives and recovery time.

Production Checklist

• appropriate NOT NULL, UNIQUE, CHECK constraints and FKs;
• versioned and tested migrations;
• parameterized queries;
• user without administrative privileges;
• connections protected by TLS;
• sized pool;
• slow queries monitored;
• automated backups and tested restoration;
• personal data with defined retention and access.

For multi-tenant applications, read multi-tenant architecture. For the complete stack, see Next.js and Supabase.

Frequently Asked Questions

Is PostgreSQL good for beginners?

Yes. It offers standardized SQL, comprehensive documentation, and features that remain useful in large systems.

Does a spreadsheet replace a database?

Spreadsheets serve small analysis and processes. Concurrency, integrity, relationships, and access control typically justify a database.

Primary Sources

• PostgreSQL: official tutorial
• PostgreSQL: creating a database
• PostgreSQL: backup and restore