PostgreSQL

PostgreSQL is an open-source relational database with strong standards support, reliable transactions and a broad feature set.

Where it fits

For a typical business system the main questions are whether the database will keep data safe, whether it can express the queries needed, and whether it will still be supported in ten years. PostgreSQL does well on each of these, and it has no licence fee.

It can also reduce the number of systems to run. JSON columns handle semi-structured data without a separate document store. Full-text search covers many search needs without a search cluster. Window functions and common table expressions support reporting queries that might otherwise call for a separate warehouse. Fewer components means less to secure, back up and keep in sync.

Its limits

Very high write volumes spread across many nodes, or large-scale horizontal scaling, are where distributed databases are worth their extra complexity. Many systems never reach that point. Heavy analytics over large data volumes may also run better in a dedicated warehouse.

How we use it

We test backups by restoring them, add connection pooling (for example PgBouncer) for systems under load, and turn on slow query logging from the start.

Related

Enterprise Software Development, Web Application Development, Data & Analytics