PostgreSQL: A Free Database Built for Reliable Data

A database with an independent identity

PostgreSQL, often called Postgres, is a free object-relational database. It is not a variation of MySQL. Its lineage runs through the POSTGRES research project at the University of California, Berkeley, where implementation began in 1986. SQL support led to Postgres95, followed by the PostgreSQL name in 1996. That history helps explain a project culture interested in expressive data models and extensibility.

For a website, the practical question is whether its data model and operational needs fit the database. PostgreSQL can support a small application as well as complex services; complexity should be introduced because a requirement demands it, rather than because a feature appears on a checklist.

A permissive free software licence

The PostgreSQL License permits use, copying, modification and distribution under its stated conditions and is comparable to permissive BSD or MIT-style licences. This supports independent hosting and service providers. The useful distinction is between the database software and a particular managed service: access to a hosted account does not automatically provide every operational freedom that running your own server would.

Our recommendation is to evaluate both the software and the exit route from a provider. Can you export the data and restore it elsewhere? Are extensions available on the destination? Who controls backups, encryption keys and upgrade timing? Those are practical questions of independence.

Let the database defend the data model

Application validation is important, but data rules should not depend entirely on a single form. A primary key identifies a record; a foreign key protects a relationship; a check constraint can reject impossible values. That means a second application or a maintenance script cannot silently bypass every important rule.

CREATE TABLE community_projects (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name text NOT NULL,
    slug text NOT NULL UNIQUE,
    member_count integer NOT NULL DEFAULT 0
        CHECK (member_count >= 0)
);

This teaching example creates a table in a test database. It requires a role with permission to create objects and is not a migration for an existing production system.

Useful capabilities without abandoning SQL

PostgreSQL supports relational queries alongside types such as JSONB and arrays. Its extension system can add specialised capabilities; PostGIS is a well-known example for geographical workloads. These options are useful when the application has a concrete need. They do not mean every relationship should be packed into a single JSON document. Keep the structure understandable to the people who must maintain it.

Authentication and privileges are separate decisions

The client authentication configuration controls how a connection is admitted. Database privileges determine what the admitted role may do. Our operational preference is to keep database access on a deliberately restricted network, give applications their own limited roles and separate maintenance credentials from web credentials. A successful connection is not proof that its privilege set is appropriate.

Document how an application receives its credentials and what happens when they are rotated. Avoid designing a deployment in which every service depends on the same administrative password. The person investigating an incident should be able to identify which application was responsible for a connection.

Performance requires observation

EXPLAIN shows the execution plan chosen for a query. EXPLAIN ANALYZE actually executes it, so take particular care with statements that modify data. Measure on representative datasets, compare expected and actual row counts, and investigate before adding indexes everywhere. Each index also costs storage and maintenance.

Autovacuum is an ordinary part of keeping PostgreSQL healthy. Vacuuming makes space from obsolete row versions reusable, and analysis maintains statistics used by planning. Long-running transactions can make housekeeping harder. Watch sustained changes in behaviour rather than treating the service merely being “active” as a sufficient health check.

Replication is not a recovery plan

WAL archiving combined with a suitable base backup enables point-in-time recovery. The required log sequence must be available, and a recovery exercise must establish that the backup really works. A replica can repeat an unwanted change; it is not automatically a historical copy of the data.

For our own planning, we would specify an acceptable data-loss window, a restoration-time objective and a person responsible for practising recovery. Exporting a database is only one step. Applications, credentials, configuration and required extensions must also be recoverable. Compare this approach with the PHP and MariaDB web stack before choosing the system that best fits the service.