PostgreSQL

pgvector is the real, specific extension that lets PostgreSQL store and query vector embeddings directly alongside your relational data -- genuinely relevant to current AI/RAG (retrieval-augmented generation) workloads, since it means you don't need a completely separate vector database just to do similarity search if PostgreSQL already holds your application data.

Practical Details

pgvector supports approximate nearest-neighbor indexing (via IVFFlat or HNSW index types) for performant similarity search at scale, though a dedicated vector database can still outperform pgvector at very large embedding volumes -- worth benchmarking your actual scale rather than assuming pgvector is always sufficient. PostgreSQL itself doesn't perform model training or inference; it's the data layer that AI application code queries against.

Actionable Considerations

1. Use pgvector when you want vector similarity search alongside existing relational data in one database, rather than standing up a separate vector database by default
2. Choose an appropriate pgvector index type (IVFFlat or HNSW) based on your actual query performance needs, rather than using the default unindexed configuration
3. Benchmark pgvector against a dedicated vector database at your actual embedding volume before assuming it scales sufficiently for a very large dataset

Row-Level Security (RLS, added in PostgreSQL 9.5) is PostgreSQL's actual mechanism for fine-grained, per-row access policies -- letting you restrict which rows a given role can see or modify based on a policy expression, genuinely more granular than table-level GRANT/REVOKE permissions alone. Client authentication rules (who can connect, from where, using what method) are configured in pg_hba.conf, a separate file from the general server configuration.

Practical Details

PostgreSQL doesn't include built-in transparent data encryption at rest -- that's a real, specific limitation worth knowing, since encryption at rest typically has to be handled at the filesystem/disk level or through a managed cloud provider's offering, not a native PostgreSQL setting. The pgcrypto extension provides column-level encryption functions for cases needing encryption within the database itself.

Actionable Considerations

• Use Row-Level Security specifically when you need per-row access control, not just table-level GRANT/REVOKE permissions
• Configure pg_hba.conf deliberately for connection authentication rules, since this is a separate file from general server settings
• Confirm encryption-at-rest is handled at the filesystem or cloud-provider level, since PostgreSQL itself doesn't provide this natively

A PostgreSQL cluster is created with initdb, which initializes the data directory and default databases, and the two files that actually govern behavior afterward are postgresql.conf (server settings like shared_buffers and work_mem, controlling memory allocation) and pg_hba.conf (client authentication rules -- who can connect, from where, with what method). PostgreSQL listens on port 5432 by default.

Practical Details

Default memory settings in postgresql.conf are conservative and generally need tuning (shared_buffers, work_mem, maintenance_work_mem) based on actual available server RAM and workload, since the out-of-the-box defaults are set for compatibility with minimal hardware, not production performance. Setting up automated backups (via pg_dump for logical backups or WAL archiving for point-in-time recovery) from day one avoids discovering a gap only when you actually need to restore.

Actionable Considerations

• Tune shared_buffers, work_mem, and maintenance_work_mem in postgresql.conf based on actual server resources, rather than leaving conservative defaults in production
• Configure pg_hba.conf deliberately for your actual network and authentication requirements, not an overly permissive default
• Set up automated backups (pg_dump or WAL archiving) during initial setup, not after you first need to restore something

PostgreSQL is an object-relational database with full ACID compliance and MVCC (Multi-Version Concurrency Control), which lets readers and writers operate on a table concurrently without blocking each other by maintaining multiple row versions rather than locking on every write -- a genuine architectural distinction from simpler locking models. Native JSONB support (a binary, indexable JSON type) is one of PostgreSQL's most significant differentiators, letting you store and efficiently query semi-structured data alongside traditional relational tables in the same database.

Practical Details

MVCC's tradeoff is that updates and deletes leave behind "dead tuples" (old row versions) that need periodic cleanup via VACUUM, rather than being reclaimed automatically and instantly -- a genuinely important operational reality for anyone new to PostgreSQL coming from a different database engine. JSONB columns can be indexed (via GIN indexes) for efficient querying, distinct from PostgreSQL's plain JSON type, which stores an exact text copy without the same query performance.

Actionable Considerations

1. Use JSONB (not plain JSON) for semi-structured data you'll actually query, since only JSONB supports efficient GIN indexing
2. Understand that MVCC requires VACUUM for cleanup rather than assuming dead row versions disappear automatically
3. Evaluate PostgreSQL specifically when you need both traditional relational structure and flexible semi-structured data in one database

PostgreSQL is released under the PostgreSQL License, a permissive license distinct from MySQL's GPL -- there's no licensing fee for the software itself, so real cost comes entirely from hosting and operations, not a vendor license. Managed offerings (Amazon RDS/Aurora, Google Cloud SQL, Supabase, Neon, and others) trade some operational control for handling backups, patching, and failover, versus self-hosting where your team owns that work directly.

Practical Details

Self-hosting can be genuinely cheaper at scale but shifts real operational burden (backup strategy, VACUUM tuning, failover testing) onto your own team, while managed services charge a premium specifically for offloading that operational work -- the right choice depends on whether your team actually has the database operations expertise to run it well, not just raw infrastructure cost comparison.

Actionable Considerations

1. Compare self-hosted PostgreSQL against managed options based on your team's actual database operations capacity, not just sticker price
2. Factor in the real ongoing cost of backup management, monitoring, and VACUUM tuning if self-hosting, since these aren't free even without a license fee
3. Confirm what a specific managed provider actually includes (automated backups, point-in-time recovery, failover) before assuming feature parity across providers

Autovacuum is PostgreSQL's background process for cleaning up dead tuples left behind by MVCC's update/delete model, and misconfigured or overwhelmed autovacuum is a genuinely common, specific cause of table bloat and degraded performance for teams new to PostgreSQL -- not a rare edge case, but one of the most common real operational issues.

Practical Details

The Write-Ahead Log (WAL) is what actually provides crash recovery and point-in-time recovery capability -- every change is written to the WAL before being applied to data files, which is the real mechanism behind both replication and backup/restore, not an abstract durability guarantee. Monitoring autovacuum activity and WAL generation rate directly (not just query response time) surfaces operational problems before they show up as user-facing slowness.

Actionable Considerations

• Monitor autovacuum activity directly and tune its settings for tables with heavy update/delete traffic, rather than assuming defaults handle every workload
• Set up WAL archiving for point-in-time recovery capability, not just periodic full backups
• Watch for table bloat as a concrete, checkable symptom of autovacuum falling behind, rather than only noticing general slowness

Foreign Data Wrappers (FDW), especially postgres_fdw, let PostgreSQL query tables in another PostgreSQL instance (or other data sources) as if they were local tables -- a genuine, specific mechanism for cross-database querying, not a generic "integrations" claim. Logical replication (built into core PostgreSQL since version 10) replicates selected tables to another instance, which is what actually powers change-data-capture and selective replication use cases, distinct from full-cluster streaming replication.

Practical Details

PostgreSQL's extension ecosystem is genuinely deep -- PostGIS adds geospatial data types and queries, TimescaleDB adds time-series optimizations, and pgvector adds vector similarity search for embeddings, each a real, separately-installed extension rather than a built-in core feature. Choosing the specific extension for your actual workload (geospatial, time-series, vector search) matters more than assuming PostgreSQL handles every specialized case identically out of the box.

Actionable Considerations

• Use postgres_fdw specifically for querying data across separate PostgreSQL instances, rather than building custom sync logic
• Use logical replication (not full streaming replication) when you only need to replicate specific tables or build change-data-capture pipelines
• Install the specific extension matching your actual workload (PostGIS, TimescaleDB, pgvector) rather than assuming core PostgreSQL covers specialized use cases natively

PostgreSQL scales vertically well (more CPU/RAM on a single instance), but it doesn't natively shard data across multiple write nodes -- genuine horizontal write scaling requires an extension like Citus (a distributed-PostgreSQL extension, now owned by Microsoft) or an entirely different architecture, not a built-in feature you just enable. Read scaling is more straightforward via streaming replication to read replicas, which is native and well-supported.

Practical Details

PostgreSQL's connection-per-process model is a real, specific scaling gotcha: each client connection forks a separate OS process rather than using lightweight threads, so a few thousand direct connections can exhaust server resources well before query load itself becomes the bottleneck -- a connection pooler like PgBouncer or Pgpool is standard practice at scale, not an optional optimization.

Actionable Considerations

1. Deploy a connection pooler (PgBouncer or Pgpool) before connection count becomes a problem, since PostgreSQL's per-connection process model doesn't scale to thousands of direct connections
2. Use streaming replication read replicas for read-heavy scaling before reaching for a sharding extension like Citus
3. Evaluate Citus or a similar distributed-PostgreSQL extension specifically when genuine multi-node write scaling is required, since it's not native functionality