Simple definition
PostgreSQL is the software that organizes, stores and queries an application’s data. A database can hold customers, orders or devices in related tables. Applications use SQL to retrieve and change that information. Unlike a shared file, the database server also coordinates concurrent access and enforces rules that keep related records consistent.
Technical definition
PostgreSQL uses a client-server model. An application connects, authenticates with a role and sends queries. Constraints and transactions help preserve data integrity. Multiversion concurrency control, or MVCC, maintains different row versions to support concurrent work; it does not eliminate every lock or transaction conflict.
An index speeds up suitable searches, but consumes storage and adds work when data changes. The usual TCP port is 5432 and can be changed. Listening addresses, pg_hba.conf rules, permissions and TLS configuration determine which connections are accepted. A reachable port alone does not prove that the application can authenticate or query its tables.
What is it used for?
PostgreSQL serves business applications, websites and services that need structured records and reliable relationships. Operating it also requires maintenance, monitoring and a recovery plan. Replication can improve availability or support some read workloads, but cannot replace an independent backup: unwanted changes can reach replicas as well.
Practical example
An order management application creates an order and its line items within one transaction. If a step fails, it can roll back the operation instead of leaving an incomplete order. The application uses an account with the permissions it needs, while administrative credentials remain separate from routine processing.
Key points
| Element | Role |
|---|---|
| Table | Stores rows and business relationships |
| Index | Speeds up suitable reads at a write cost |
| Transaction | Groups consistent operations |
| Replication | Copies changes; does not independently protect against logical deletion |
Common problems
- Connection failure: separate DNS, network listening, firewall and authentication problems using the server logs.
- Slow query: inspect its execution plan, data volumes and indexes before adding resources.
- Connection exhaustion or lock waits: identify long-lived sessions and transactions, then trace the responsible application.
Operational checks and limitations
Match the backup method to the recovery objective: logical export, physical backup and, where configured, WAL archiving for point-in-time recovery. Test an actual restore. Monitor free space, errors, connection usage and replication delay so that a healthy-looking service does not hide a growing recovery problem.