PostgreSQL vs MySQL on FreeBSD: Database Comparison
PostgreSQL and MySQL are both mature, capable relational databases. Both run on FreeBSD. Both have binary packages, rc.conf integration, and decades of production history. But they are fundamentally different in philosophy, feature depth, and how well they fit the FreeBSD ecosystem.
This guide gives you a direct, technical comparison -- not a rehash of generic benchmarks. Every detail here is specific to running these databases on FreeBSD with ZFS, jails, and the tools that FreeBSD administrators actually use. If you want the broader picture first, see our overview of the best databases for FreeBSD.
Quick Verdict
PostgreSQL wins for most FreeBSD deployments. It has deeper FreeBSD integration, stricter SQL compliance, richer features, better ZFS alignment, and a community that overlaps significantly with the FreeBSD community. Several PostgreSQL core committers run FreeBSD. The project has used FreeBSD as a development and testing platform for decades.
MySQL still has valid use cases. If your application explicitly requires MySQL (WordPress, many PHP frameworks, legacy enterprise software with MySQL-only stored procedures), or if your team has deep MySQL operational expertise, it remains a solid choice on FreeBSD. But if you are starting from scratch and choosing a relational database for a FreeBSD server, PostgreSQL is the stronger default.
The rest of this article explains why in detail.
SQL Standards Compliance
This is where the philosophical difference between the two projects shows most clearly.
PostgreSQL: Strict by Default
PostgreSQL tracks the SQL standard aggressively. It implements SQL:2023 features including window functions, common table expressions (CTEs), lateral joins, recursive queries, MERGE, JSON_TABLE, and row-level security. When the standard defines a behavior, PostgreSQL follows it. When PostgreSQL extends beyond the standard, it documents the deviation explicitly.
Strict mode is the default. If you insert a string into an integer column, PostgreSQL rejects it. If you reference a non-aggregated column in a GROUP BY query, PostgreSQL rejects it. If your CHECK constraint is violated, the transaction fails. This strictness catches bugs at the database level rather than letting bad data silently corrupt your tables.
MySQL: Permissive by Default
MySQL has historically been more permissive. In older versions and certain SQL modes, MySQL would silently truncate data, accept invalid dates like 0000-00-00, allow non-aggregated columns in GROUP BY results, and perform implicit type conversions that could produce surprising results.
MySQL 8.x improved this significantly by enabling ONLY_FULL_GROUP_BY and STRICT_TRANS_TABLES by default. But the culture of permissiveness means that many MySQL tutorials, legacy applications, and ORM-generated queries depend on non-standard behavior. Tightening SQL mode on an existing MySQL deployment can break things.
What This Means in Practice
If you are building a new application and want the database to enforce correctness, PostgreSQL is the better choice. If you are running legacy PHP applications that were written assuming MySQL's permissive behavior, switching to PostgreSQL requires query auditing. Neither is "wrong" -- but they represent genuinely different approaches to data integrity.
Feature Comparison
Common Table Expressions (CTEs)
PostgreSQL has supported CTEs since version 8.4 (2009) and added CTE optimization (inlining) in version 12. PostgreSQL CTEs can be recursive, updatable, and used with INSERT, UPDATE, and DELETE via writeable CTEs.
MySQL added CTE support in version 8.0 (2018). MySQL CTEs support recursion but do not support writeable CTEs. For most read queries, both implementations are functionally equivalent. For complex data manipulation pipelines that use CTEs to chain mutations, PostgreSQL is the only option.
Window Functions
Both databases support window functions (ROW_NUMBER(), RANK(), DENSE_RANK(), LAG(), LEAD(), NTILE(), etc.). PostgreSQL's implementation is more complete, supporting custom window frames with RANGE, ROWS, and GROUPS modes, plus EXCLUDE clauses. PostgreSQL also supports user-defined window functions.
MySQL's window function support covers the common cases well. Most applications will not hit the edges where PostgreSQL's implementation is broader.
Full-Text Search
PostgreSQL includes native full-text search with tsvector and tsquery types, GIN and GiST indexing, language-aware stemming, ranking functions, phrase search, and custom dictionaries. It is integrated directly into SQL and can be combined with other query features. For many applications, PostgreSQL's built-in full-text search eliminates the need for Elasticsearch or Solr.
MySQL offers full-text search through FULLTEXT indexes on InnoDB and MyISAM tables. It supports natural language mode, boolean mode, and query expansion. MySQL's full-text search is simpler to set up but less powerful -- it lacks phrase search capabilities comparable to PostgreSQL's, has limited stemming control, and offers fewer ranking options.
JSONB and Document Storage
PostgreSQL's JSONB type is binary-stored, indexable with GIN indexes, and supports a rich set of operators for querying, updating, and transforming JSON data. You can use jsonb_path_query() for SQL/JSON path expressions, create partial indexes on JSON fields, and combine JSONB queries with relational joins. Many teams use PostgreSQL as a combined relational and document store, eliminating the need for a separate document database.
MySQL added a JSON type in 5.7. It validates JSON on insert, supports path extraction with -> and ->> operators, and offers functions like JSON_EXTRACT(), JSON_SET(), and JSON_ARRAYAGG(). MySQL 8.0 added multi-valued indexes on JSON arrays. MySQL's JSON support is capable but less mature than PostgreSQL's JSONB -- particularly in indexing flexibility and the range of operators available.
Stored Procedures and Functions
Both databases support stored procedures and functions. PostgreSQL offers multiple procedural languages: PL/pgSQL (the default), PL/Python, PL/Perl, PL/Tcl, and PL/v8 (JavaScript). The ability to write database functions in Python or Perl is a significant advantage for complex data processing.
MySQL supports stored procedures and functions in SQL/PSM syntax. The language is capable but more limited than PL/pgSQL, and there are no alternative procedural languages.
Partitioning
PostgreSQL supports declarative partitioning (range, list, hash) since version 10, with partition pruning, automatic partition routing, and the ability to attach and detach partitions without downtime. Foreign tables can participate in partition schemes.
MySQL supports range, list, hash, and key partitioning. MySQL's partitioning is functional but does not support as many partition management operations online and has more restrictions on which columns can appear in partition keys.
Performance Characteristics
Read-Heavy Workloads
MySQL with InnoDB has historically been perceived as faster for simple read-heavy workloads (key-value lookups, simple SELECT statements). This was true in the MySQL 5.x era but the gap has narrowed substantially. PostgreSQL's query planner is more sophisticated and produces better plans for complex queries involving joins, subqueries, and aggregations.
For pure OLTP read workloads with simple primary key lookups, MySQL and PostgreSQL perform within a few percent of each other on modern hardware. For analytical queries, PostgreSQL is typically faster due to its more advanced planner and executor.
Write-Heavy Workloads
PostgreSQL uses MVCC (Multi-Version Concurrency Control) where old row versions accumulate in the table itself, requiring VACUUM to reclaim space. This is PostgreSQL's most significant operational overhead. On FreeBSD, autovacuum handles this automatically, but high-write tables need monitoring and tuning.
MySQL's InnoDB also uses MVCC but stores old versions in a separate undo log, which is generally more space-efficient for write-heavy workloads. InnoDB does not need a background vacuum process. However, long-running transactions in MySQL can cause undo log bloat that is harder to diagnose than PostgreSQL's table bloat.
Connection Handling
PostgreSQL uses a process-per-connection model. Each client connection gets a dedicated OS process. On FreeBSD, this maps cleanly to the kernel's process management -- procstat, truss, and dtrace work directly against individual connections. The downside is that connection creation is heavier than a thread model, and thousands of simultaneous connections consume significant memory.
For high-connection-count deployments on PostgreSQL, a connection pooler like PgBouncer is standard practice. PgBouncer is available in FreeBSD packages (pkg install pgbouncer) and runs well in a dedicated jail.
MySQL uses a thread-per-connection model. Thread creation is lighter than process creation, and MySQL handles higher connection counts without a pooler. MySQL 8.0's thread pool plugin (Enterprise edition) and the community ProxySQL add connection multiplexing.
On FreeBSD specifically, PostgreSQL's process model is arguably an advantage because it integrates more naturally with FreeBSD's process-centric tooling and jail isolation.
Replication
PostgreSQL Replication
PostgreSQL offers two replication modes:
Streaming replication sends the WAL (Write-Ahead Log) stream to standby servers for physical replication. It is built-in, reliable, and supports synchronous or asynchronous modes. Setting up streaming replication on FreeBSD is covered in our PostgreSQL setup guide.
Logical replication (PostgreSQL 10+) replicates at the row level and allows selective table replication, cross-version replication, and data transformation during replication. This is powerful for zero-downtime major version upgrades and for feeding data warehouses.
PostgreSQL does not natively support multi-master replication. Third-party solutions like BDR (Bi-Directional Replication) and Citus exist, but they add operational complexity.
MySQL Replication
MySQL offers several replication modes:
Binary log replication is MySQL's traditional replication method. It is mature, widely understood, and supports both statement-based and row-based replication. Configuration is straightforward.
Group Replication (MySQL 5.7.17+) provides built-in multi-master replication with automatic conflict detection. It uses a Paxos-based consensus protocol and supports single-primary and multi-primary modes. InnoDB Cluster wraps Group Replication with MySQL Router and MySQL Shell for automated failover.
MySQL's multi-master story is more mature out of the box. If you need multi-master write capability without third-party software, MySQL Group Replication is a significant advantage.
ZFS Integration
FreeBSD's native ZFS support is a major factor in database deployments. Both databases benefit from ZFS, but they require different tuning. For a deeper dive into ZFS itself, see our ZFS guide.
ZFS Tuning for PostgreSQL
PostgreSQL uses an 8 KB page size. Align ZFS accordingly:
sh# Create a dedicated dataset for PostgreSQL data zfs create -o mountpoint=/var/db/postgres/data17 tank/postgres/data # Set recordsize to match PostgreSQL's 8K page size zfs set recordsize=8K tank/postgres/data # Disable ZFS data caching -- PostgreSQL manages its own buffer pool zfs set primarycache=metadata tank/postgres/data # PostgreSQL handles its own write-ahead logging zfs set logbias=throughput tank/postgres/data # Disable atime to reduce unnecessary writes zfs set atime=off tank/postgres/data # Enable LZ4 compression -- it is fast and saves significant space zfs set compression=lz4 tank/postgres/data
For the WAL directory, use different settings:
shzfs create -o mountpoint=/var/db/postgres/wal17 tank/postgres/wal # WAL writes are sequential -- larger records are fine zfs set recordsize=128K tank/postgres/wal # WAL benefits from intent log optimization zfs set logbias=latency tank/postgres/wal zfs set primarycache=metadata tank/postgres/wal zfs set compression=lz4 tank/postgres/wal
Setting primarycache=metadata on data is important because PostgreSQL manages its own shared buffer pool via shared_buffers. Letting ZFS also cache data blocks wastes RAM -- the same data ends up cached twice.
ZFS Tuning for MySQL
MySQL's InnoDB uses a 16 KB page size by default. The ZFS tuning differs:
sh# Create a dedicated dataset for MySQL data zfs create -o mountpoint=/var/db/mysql tank/mysql/data # Set recordsize to match InnoDB's 16K page size zfs set recordsize=16K tank/mysql/data # InnoDB manages its own buffer pool -- avoid double caching zfs set primarycache=metadata tank/mysql/data # InnoDB handles its own logging zfs set logbias=throughput tank/mysql/data zfs set atime=off tank/mysql/data zfs set compression=lz4 tank/mysql/data
For InnoDB log files:
shzfs create -o mountpoint=/var/db/mysql/logs tank/mysql/logs zfs set recordsize=128K tank/mysql/logs zfs set logbias=latency tank/mysql/logs zfs set primarycache=metadata tank/mysql/logs zfs set compression=lz4 tank/mysql/logs
Important for MySQL on ZFS: Disable InnoDB's doublewrite buffer when running on ZFS. ZFS is a copy-on-write filesystem and already guarantees atomic writes, making InnoDB's doublewrite protection redundant. Disabling it improves write performance:
ini# In my.cnf [mysqld] innodb_doublewrite = 0
PostgreSQL does not have an equivalent doublewrite mechanism, so this optimization is MySQL-specific. PostgreSQL's full_page_writes setting serves a related purpose, and it can also be disabled on ZFS if you can guarantee no partial page writes reach disk (though the PostgreSQL community generally recommends leaving it enabled as a safety measure).
ZFS Snapshots for Backups
Both databases benefit from ZFS snapshots for fast, consistent backups. The approach differs slightly:
PostgreSQL: Use pg_start_backup() / pg_stop_backup() (or pg_backup_start() / pg_backup_stop() in version 15+) to bracket your ZFS snapshot. This ensures WAL consistency.
MySQL: Use FLUSH TABLES WITH READ LOCK before taking a ZFS snapshot, then UNLOCK TABLES after. For InnoDB-only databases, you can alternatively use innodb_force_recovery options after restoring a snapshot, but the lock approach is more reliable.
Ecosystem
ORMs and Drivers
Every major ORM and database driver supports both PostgreSQL and MySQL. Django, Rails, SQLAlchemy, Hibernate, Sequelize, Prisma -- all work with both. However, PostgreSQL-specific features like JSONB, arrays, range types, and LISTEN/NOTIFY often have better ORM support because they are more standardized.
MySQL's ecosystem is broader in the PHP world. WordPress, Drupal, Magento, and most PHP CMS platforms were built on MySQL. While many now support PostgreSQL, MySQL remains the path of least resistance for PHP-centric stacks.
Tools
PostgreSQL tools: pgAdmin, psql (the command-line client is excellent), pg_dump/pg_restore, pg_stat_statements, pgBadger for log analysis, pgbench for benchmarking, pgBackRest for advanced backups.
MySQL tools: MySQL Workbench, mysql CLI, mysqldump, mysqlpump, Percona Toolkit (a collection of essential operational tools), pt-query-digest for slow query analysis, MySQL Enterprise Monitor.
Community
PostgreSQL's community has significant overlap with the FreeBSD community. The mailing lists, conferences (PGCon, PostgreSQL Conference Europe), and developer base share people and culture. Getting FreeBSD-specific PostgreSQL help is straightforward.
MySQL's community is larger overall due to the LAMP stack era. Documentation is abundant. However, FreeBSD-specific MySQL resources are less common because MySQL's primary user base runs Linux. The Oracle stewardship of MySQL has also caused some community fragmentation, with MariaDB attracting developers who preferred the pre-Oracle governance model.
FreeBSD-Specific Considerations
Package Availability
Both databases are well-maintained in FreeBSD packages:
sh# PostgreSQL pkg install postgresql17-server postgresql17-contrib # MySQL pkg install mysql84-server mysql84-client
FreeBSD tracks multiple major versions of both. At the time of writing:
- PostgreSQL: 14, 15, 16, 17 available
- MySQL: 8.0, 8.4 available
Service Configuration
PostgreSQL rc.conf:
shsysrc postgresql_enable=YES sysrc postgresql_data="/var/db/postgres/data17"
MySQL rc.conf:
shsysrc mysql_enable=YES sysrc mysql_dbdir="/var/db/mysql"
Default Paths
| Component | PostgreSQL | MySQL |
|-----------|-----------|-------|
| Data directory | /var/db/postgres/data17 | /var/db/mysql |
| Config file | /var/db/postgres/data17/postgresql.conf | /usr/local/etc/mysql/my.cnf |
| Socket | /tmp/.s.PGSQL.5432 | /tmp/mysql.sock |
| Log file | Configured in postgresql.conf | /var/db/mysql/hostname.err |
| rc script | /usr/local/etc/rc.d/postgresql | /usr/local/etc/rc.d/mysql-server |
Jail Isolation
Both databases run well in FreeBSD jails. PostgreSQL's process model makes jail resource limits (via rctl) more predictable -- each connection is a process, and you can set per-jail process and memory limits with fine granularity. MySQL's thread model means all connections share one process, which makes per-connection resource tracking through jail mechanisms less granular.
For jail-based database deployments, create a dedicated jail with its own ZFS dataset. This gives you clean separation, independent snapshots, and the ability to migrate the jail (with its database) to another host.
Security Features
PostgreSQL
- Row-Level Security (RLS): Policies that restrict which rows a user can see or modify, enforced at the database level. Powerful for multi-tenant applications.
- pg_hba.conf: Fine-grained, host-based authentication configuration supporting password, certificate, GSSAPI, LDAP, RADIUS, and PAM methods.
- SSL/TLS: Built-in support with per-connection enforcement.
- SCRAM-SHA-256: Modern password authentication (default since PostgreSQL 14).
- Column-level permissions: GRANT and REVOKE on individual columns.
- Security labels: Integration with SELinux/MAC frameworks (more relevant on Linux, but the mechanism exists).
MySQL
- Account locking and password rotation: Built-in password expiration and account locking policies.
- Roles: Supported since MySQL 8.0, similar to PostgreSQL's role system.
- SSL/TLS: Built-in support with enforcement options.
- caching_sha2_password: Default authentication plugin since MySQL 8.0.
- Data masking: Enterprise edition includes data masking and de-identification.
- Audit plugin: Enterprise edition audit logging.
PostgreSQL has an edge here for open-source deployments. Row-Level Security alone is a significant feature that MySQL lacks entirely. MySQL's strongest security features (audit logging, data masking, transparent data encryption) are in the commercial Enterprise edition, while PostgreSQL includes its security features in the standard open-source release.
Comparison Table
| Feature | PostgreSQL | MySQL |
|---------|-----------|-------|
| SQL compliance | SQL:2023, strict by default | Improved in 8.x, historically permissive |
| JSONB/JSON | JSONB with GIN indexes, SQL/JSON path | JSON type, multi-valued indexes (8.0+) |
| Full-text search | Native tsvector/tsquery, ranking | FULLTEXT indexes, natural language mode |
| CTEs | Full support including writeable CTEs | Read-only CTEs (8.0+) |
| Window functions | Complete implementation | Good coverage since 8.0 |
| Partitioning | Declarative range/list/hash | Range/list/hash/key |
| Replication | Streaming + logical replication | Binlog + Group Replication |
| Multi-master | Third-party only | Native Group Replication |
| Connection model | Process-per-connection | Thread-per-connection |
| Default page size | 8 KB | 16 KB (InnoDB) |
| ZFS recordsize | 8K | 16K |
| Row-Level Security | Yes | No |
| Procedural languages | PL/pgSQL, PL/Python, PL/Perl, PL/v8 | SQL/PSM only |
| License | PostgreSQL License (permissive) | GPLv2 (with proprietary Enterprise) |
| FreeBSD community overlap | High | Low |
| Doublewrite on ZFS | Not needed (no doublewrite buffer) | Disable innodb_doublewrite |
When to Choose PostgreSQL
- You are starting a new project on FreeBSD and want the best-integrated database.
- Your application needs JSONB, full-text search, CTEs, or advanced SQL features.
- You need row-level security for multi-tenant isolation.
- You want to write database functions in Python or Perl, not just SQL.
- Your workload includes analytical queries with complex joins and aggregations.
- You value strict SQL compliance and want the database to catch bad data.
- You want ZFS integration that aligns naturally with the database's architecture.
- Your team plans to use extensions like PostGIS, TimescaleDB, or pgvector.
- You prefer fully open-source software with no "Enterprise-only" feature tiers.
For installation instructions, see our PostgreSQL setup guide.
When to Choose MySQL
- Your application explicitly requires MySQL (WordPress, Magento, many PHP CMS platforms).
- You need native multi-master replication without third-party software.
- Your workload is dominated by simple key-value reads and you want maximum throughput with minimal tuning.
- Your team has deep MySQL operational expertise and switching costs are not justified.
- You are running software that depends on MySQL-specific syntax or behaviors.
- You need InnoDB Cluster with integrated automatic failover through MySQL Router.
- Your organization requires Oracle commercial support.
FAQ
Is PostgreSQL faster than MySQL on FreeBSD?
It depends on the workload. For complex queries with joins, subqueries, window functions, and aggregations, PostgreSQL's query planner generally produces better execution plans. For simple primary-key lookups in high-concurrency OLTP workloads, the two are within a few percent of each other. The performance difference is rarely the deciding factor -- features and operational characteristics matter more.
Can I migrate from MySQL to PostgreSQL on FreeBSD?
Yes. Tools like pgloader automate schema and data migration from MySQL to PostgreSQL. The main challenges are application-level: MySQL-specific SQL syntax, implicit type conversions your application depends on, and stored procedures that need rewriting. Plan for a testing phase where you run both databases and compare query results. The database migration is the easy part; fixing the application queries takes longer.
Does FreeBSD favor PostgreSQL over MySQL?
FreeBSD does not officially favor either, but PostgreSQL has deeper roots in the FreeBSD ecosystem. Several PostgreSQL developers are also FreeBSD contributors. The FreeBSD ports and packages for PostgreSQL are well-maintained and frequently updated. MySQL packages are also available and functional, but the community overlap is smaller.
Should I use MariaDB instead of MySQL on FreeBSD?
MariaDB is a viable alternative to MySQL. It is wire-compatible with MySQL, has better default settings, and includes Galera Cluster for synchronous multi-master replication. On FreeBSD, MariaDB is available as pkg install mariadb1011-server. If you need MySQL compatibility but prefer community governance over Oracle's, MariaDB is the pragmatic middle ground.
How do I handle PostgreSQL's VACUUM overhead on FreeBSD with ZFS?
Configure autovacuum aggressively for high-write tables. Key settings in postgresql.conf:
shellautovacuum_vacuum_scale_factor = 0.05 autovacuum_analyze_scale_factor = 0.02 autovacuum_vacuum_cost_delay = 2ms autovacuum_max_workers = 4
ZFS compression helps mitigate table bloat's storage impact. Monitor bloat with pgstattuple and pg_stat_user_tables. On FreeBSD, ZFS snapshots make it safe to run VACUUM FULL on large tables -- snapshot the dataset first, and if something goes wrong, rollback takes seconds.
Can I run both PostgreSQL and MySQL on the same FreeBSD server?
Yes. Use separate ZFS datasets for each, with the appropriate recordsize settings. For proper isolation, run each in its own jail with dedicated resources. This is a common setup during migrations or when running applications that require different databases. FreeBSD jails make this clean and manageable -- each database gets its own filesystem, network address, and resource limits.
What about connection pooling on FreeBSD?
PostgreSQL benefits significantly from connection pooling because of its process-per-connection model. Install PgBouncer (pkg install pgbouncer) and run it between your application and PostgreSQL. PgBouncer handles hundreds of application connections with a small pool of actual database connections. On FreeBSD, PgBouncer runs well in the same jail as PostgreSQL or in a separate lightweight jail.
MySQL's thread-per-connection model handles more simultaneous connections natively, but ProxySQL (pkg install proxysql) adds connection multiplexing, query routing, and failover capabilities. ProxySQL is particularly useful with MySQL Group Replication setups.
Conclusion
For most new deployments on FreeBSD, PostgreSQL is the better choice. The combination of strict SQL compliance, JSONB, full-text search, row-level security, and deep FreeBSD community integration makes it the natural default. The ZFS tuning story is well-understood, the process model fits FreeBSD's tooling, and the extension ecosystem covers use cases from geospatial to machine learning.
MySQL remains the right choice when your application requires it or when you need native multi-master replication. It runs well on FreeBSD with proper ZFS tuning and continues to improve with each release.
The worst choice is not making one. Pick the database that fits your workload, set up the ZFS datasets correctly, and build your application. Both are production-ready on FreeBSD. Both will serve you well if configured properly.