PostgreSQL, often called Postgres, is a free, community-led, standards-compliant database system valued for reliability, extensibility, and broad commercial and technical adoption. Its flexibility supports transactional workloads, advanced querying and reporting, JSONB, and specialized work such as geospatial processing—but flexibility does not make every design or workload a good fit. Choose it against actual requirements rather than treating it as an embedded, distributed, log, video-storage, in-memory, or graph solution by default. section 1.1 section 1.4.2 section 11.4
A durable mental model starts with its client-server, multiprocess design: client applications connect to a server on one host, where backend processes handle connections and other internal processes perform database work. This architecture enables parallelization and use of available CPUs, while differing in important ways from multithreaded database systems. section 1.5.1
Figure 1.1 How the PostgreSQL client-server architecture works
Use the architecture diagram to distinguish client applications, backend processes, and the database server before diagnosing connection or process behavior. section 1.5.1
Use PostgreSQL safely by treating common surprises as design and operational checks:
- Do not carry assumptions from another DBMS: a new USER does not necessarily create a same-named SCHEMA, and numeric 1 or 0 is not silently accepted as BOOLEAN. section 1.4.1
- Treat default configuration as a starting point, not a production configuration; suitability depends on the intended workload. section 1.4.1
- Read documentation for the PostgreSQL version actually in use, including notes and related context that may affect feature behavior. section 1.4.3
- Prevent technical debt through early planning, query optimization, effective indexing, monitoring, maintenance, security, redundancy, and backup or failover strategies. section 11.4 section 11.5
Migrating numeric booleans
A dump from MariaDB or MySQL can contain 0 and 1 for a column declared BOOLEAN because those systems store BOOLEAN as TINYINT(1). PostgreSQL rejects the integer literal for a BOOLEAN column rather than silently converting it. Convert existing values explicitly, for example by dropping the old default, changing the type with a CASE expression that maps 1 to true and other values to false, then restoring a BOOLEAN default. Inspect the resulting schema and rows to confirm that the column is boolean and values display as t or f. section 10.4
Cleaning an encoding migration
A SQL dump may fail to load when historical data uses latin1 while the PostgreSQL target expects UTF8. Locate the offending bytes, convert the dump from latin1 to UTF-8, and remove embedded null bytes before retrying. Encoding, collation, and character-type settings must be considered as part of the target database setup. section 10.3
The practical goal is not merely to make commands succeed. Recognizing PostgreSQL-specific behavior early protects stored data, avoids costly live-system changes, replaces antipatterns with established solutions, and helps systems remain performant, stable, and reliable as data and usage grow. section 1.2 section 1.3 section 11.5
