MariaDB vs PostgreSQL on FreeBSD: Database Comparison

MariaDB vs PostgreSQL on FreeBSD: Database Comparison

MariaDB and PostgreSQL are both mature, capable relational databases with long histories on FreeBSD. MariaDB is a MySQL-compatible fork created by MySQL's original developers after Oracle's acquisition of Sun Microsystems. PostgreSQL is an independent project with roots in the UC Berkeley POSTGRES research system. Both are available as FreeBSD packages, integrate with the rc.d service framework, and work well on ZFS. But they are fundamentally different in design philosophy, feature depth, and how they align with FreeBSD's strengths. This comparison covers every dimension that matters for choosing between them on FreeBSD.

Quick Verdict

PostgreSQL wins for most FreeBSD deployments. It has deeper FreeBSD integration, stricter SQL compliance, richer features (JSONB, full-text search, window functions, CTEs), better ZFS alignment, and a community that overlaps significantly with the FreeBSD ecosystem. Several PostgreSQL core developers run FreeBSD.

MariaDB has valid use cases. If your application requires MySQL compatibility (WordPress, Drupal, many PHP frameworks, Magento), or if you are running a read-heavy workload where MariaDB's MyRocks or Aria storage engines offer specific advantages, MariaDB is a solid choice.

The rest of this article explains why in detail.

Installation on FreeBSD

PostgreSQL

sh
pkg install postgresql16-server postgresql16-client
sysrc postgresql_enable="YES"
service postgresql initdb
service postgresql start

The data directory is /var/db/postgres/data16/. Configuration files live in the data directory.

MariaDB

sh
pkg install mariadb114-server mariadb114-client
sysrc mysql_enable="YES"
service mysql-server start
mysql_secure_installation

The data directory is /var/db/mysql/. Configuration is at /usr/local/etc/mysql/my.cnf.

Both installations are straightforward. PostgreSQL requires an explicit initdb step; MariaDB initializes automatically on first start.

SQL Standards Compliance

PostgreSQL: Strict by Design

PostgreSQL implements SQL:2023 aggressively. Window functions, CTEs (including recursive and writeable CTEs), lateral joins, MERGE, JSON_TABLE, row-level security, generated columns, and table partitioning all follow the standard. When you insert invalid data, PostgreSQL rejects it. When you write a GROUP BY query that references a non-aggregated column, PostgreSQL rejects it. This strictness catches bugs at the database layer.

MariaDB: Practical Compatibility

MariaDB inherits MySQL's historically permissive behavior. Modern versions (11.x) have tightened defaults significantly -- STRICT_TRANS_TABLES and ONLY_FULL_GROUP_BY are enabled by default. But the culture of MySQL/MariaDB compatibility means many applications, ORMs, and tutorials depend on non-standard behavior. MariaDB also supports its own SQL extensions (e.g., LIMIT in UPDATE and DELETE, INSERT ... ON DUPLICATE KEY UPDATE) that are not part of the SQL standard but are widely used.

Practical Impact

If you are building a new application and want the database to enforce data integrity, PostgreSQL's strictness is an advantage. If you are running an existing MySQL-dependent application, MariaDB's compatibility is essential.

Feature Comparison

JSON Support

PostgreSQL's JSONB type is binary-stored, indexable with GIN indexes, and supports a comprehensive set of operators and functions. You can create partial indexes on JSON fields, use SQL/JSON path queries (jsonb_path_query()), and combine JSON operations with relational joins. Many teams use PostgreSQL as a combined relational and document store.

sh
su - postgres -c "psql -c \"
CREATE TABLE events (id serial, data jsonb);
INSERT INTO events (data) VALUES ('{\"type\": \"login\", \"user\": \"admin\"}');
CREATE INDEX idx_events_type ON events USING gin (data);
SELECT * FROM events WHERE data @> '{\"type\": \"login\"}';
\""

MariaDB added JSON support but stores JSON as text internally (unlike PostgreSQL's binary format). MariaDB supports JSON_EXTRACT(), -> and ->> operators, and JSON_TABLE() for converting JSON to relational format. Performance for JSON queries is lower than PostgreSQL because the JSON is parsed on every access rather than stored in a pre-parsed binary format.

Full-Text Search

PostgreSQL includes native full-text search with tsvector/tsquery types, GIN/GiST indexing, language-aware stemming, phrase search, ranking, and custom dictionaries. It is powerful enough to replace Elasticsearch for many applications.

MariaDB offers FULLTEXT indexes on InnoDB tables with natural language mode, boolean mode, and query expansion. It is simpler but less powerful -- limited stemming control, fewer ranking options, and no phrase search capability comparable to PostgreSQL's.

Replication

PostgreSQL offers:

• Streaming replication (physical, byte-level)
• Logical replication (table-level, selective)
• Synchronous and asynchronous modes
• Cascading replication
• Built-in connection pooling via pgBouncer (external)

MariaDB offers:

• Binary log replication (statement or row-based)
• Semi-synchronous replication
• Galera Cluster for synchronous multi-master
• MariaDB MaxScale for proxy-based routing
• Global Transaction IDs (GTIDs)

MariaDB's Galera Cluster provides true multi-master synchronous replication out of the box -- something PostgreSQL does not have natively (it requires extensions like BDR or Citus). If your architecture requires multi-master writes, MariaDB with Galera is a compelling option.

PostgreSQL's logical replication is more flexible for selective table replication, data migrations, and cross-version upgrades.

Partitioning

PostgreSQL supports declarative partitioning (range, list, hash) with partition pruning, foreign key support on partitioned tables, and automatic partition routing. Partitions are first-class objects with full indexing support.

MariaDB supports range, list, hash, and key partitioning. Partition management is mature but the implementation is less flexible than PostgreSQL's -- for example, foreign keys are not supported on partitioned tables in MariaDB.

Window Functions and CTEs

Both databases support window functions (ROW_NUMBER(), RANK(), LAG(), LEAD(), etc.) and common table expressions. PostgreSQL's implementation is more complete:

• PostgreSQL supports writeable CTEs (CTEs with INSERT, UPDATE, DELETE).
• PostgreSQL supports GROUPS frame mode and EXCLUDE clauses in window specifications.
• PostgreSQL CTEs can be optimized (inlined) by the query planner since version 12.

MariaDB's window functions and CTEs cover the common cases but lack some advanced features available in PostgreSQL.

Storage Engines

PostgreSQL

PostgreSQL uses a single storage engine. All data goes through the same MVCC (Multi-Version Concurrency Control) mechanism. This simplicity means consistent behavior, consistent backup procedures, and consistent tuning regardless of table type.

MariaDB

MariaDB supports multiple storage engines:

• InnoDB -- default, ACID-compliant, row-level locking. The workhorse for most workloads.
• Aria -- crash-safe MyISAM replacement. Good for read-heavy temporary tables and system tables.
• MyRocks -- LSM-tree based engine optimized for write-heavy workloads with better compression. Based on Facebook's RocksDB.
• ColumnStore -- columnar storage for analytics workloads. Designed for OLAP queries on large datasets.
• MEMORY -- in-memory tables for temporary data.

The ability to choose a storage engine per table is both a strength and a complexity. MyRocks, in particular, offers excellent write performance and compression for specific workloads (logging, time-series data, large write-heavy tables).

Performance on FreeBSD

General Workloads

For OLTP (transactional) workloads with a mix of reads and writes, PostgreSQL and MariaDB (InnoDB) perform within 10-20% of each other on FreeBSD. The differences are workload-dependent -- neither is universally faster.

Read-Heavy Workloads

MariaDB has traditionally had an edge for simple SELECT queries on indexed tables, particularly for connection-per-query PHP workloads. The difference has narrowed as PostgreSQL has improved its connection handling and query execution.

Write-Heavy Workloads

PostgreSQL's MVCC implementation creates dead tuples that require VACUUM to reclaim. Under heavy write loads, the autovacuum process adds overhead. MariaDB's InnoDB uses undo logs and does not require an equivalent background process, giving it an edge for sustained write-heavy workloads.

MariaDB's MyRocks engine outperforms both InnoDB and PostgreSQL for write-heavy workloads due to its LSM-tree architecture -- it batches writes and compacts in the background.

Connection Handling

MariaDB uses one thread per connection. PostgreSQL uses one process per connection. PostgreSQL's per-process model uses more memory per connection. For applications with hundreds of concurrent connections, PostgreSQL typically requires a connection pooler (PgBouncer):

sh
pkg install pgbouncer

MariaDB handles high connection counts more efficiently with its thread pool feature:

sh
# In my.cnf
[mysqld]
thread_handling = pool-of-threads
thread_pool_size = 16

ZFS Integration

Both databases run on ZFS, but the tuning requirements differ.

PostgreSQL on ZFS

sh
zfs create -o recordsize=16k -o atime=off -o logbias=throughput tank/postgresql
zfs set primarycache=metadata tank/postgresql

PostgreSQL's 8 KB page size aligns well with ZFS recordsize=16k (which allows ZFS to batch two pages). Setting primarycache=metadata avoids double caching (PostgreSQL manages its own shared_buffers).

Set full_page_writes = off in postgresql.conf when running on ZFS -- ZFS's copy-on-write semantics make PostgreSQL's full-page write protection redundant:

sh
# In postgresql.conf
full_page_writes = off

MariaDB on ZFS

sh
zfs create -o recordsize=16k -o atime=off tank/mysql

MariaDB's InnoDB uses 16 KB pages by default. A ZFS recordsize=16k provides perfect alignment. Unlike PostgreSQL, MariaDB benefits from ZFS's ARC caching -- do not disable primarycache.

Disable InnoDB's doublewrite buffer on ZFS:

sh
# In my.cnf
[mysqld]
innodb_doublewrite = 0

ZFS's atomic copy-on-write makes InnoDB's doublewrite protection redundant.

Security Features

PostgreSQL

• Row-level security policies
• Column-level permissions
• SSL/TLS with client certificate authentication
• SCRAM-SHA-256 password authentication
• pg_hba.conf for granular connection control
• SELinux/MAC labels (less relevant on FreeBSD)

MariaDB

• Database and table-level permissions
• SSL/TLS connections
• PAM authentication
• ed25519 authentication plugin
• Role-based access (since MariaDB 10.0)
• Data-at-rest encryption with key management plugins

PostgreSQL's row-level security is a significant advantage for multi-tenant applications where different users should see different rows in the same table. MariaDB does not have an equivalent feature -- you must implement row-level filtering in application code.

Ecosystem and Tooling on FreeBSD

PostgreSQL

• pg_dump / pg_restore for logical backups
• pg_basebackup for physical backups
• PgBouncer for connection pooling
• pgAdmin (web-based management)
• PostGIS for spatial data
• TimescaleDB for time-series
• pg_stat_statements for query analysis
• Extensive FreeBSD ports collection

MariaDB

• mariadb-dump for logical backups
• Mariabackup for physical backups
• MaxScale for proxy/routing
• phpMyAdmin (web-based management)
• Galera Cluster for multi-master
• ColumnStore for analytics
• Performance Schema for query analysis

Migration Between the Two

MariaDB to PostgreSQL

Migration is non-trivial. The main challenges:

• SQL syntax differences (backtick quoting, LIMIT in UPDATE, auto_increment vs SERIAL/IDENTITY)
• Stored procedure language differences (SQL/PSM vs PL/pgSQL)
• Application query compatibility
• Data type mapping (ENUM, SET, unsigned integers)

Tools like pgloader automate data migration:

sh
pkg install pgloader
pgloader mysql://user:pass@localhost/mydb postgresql://user:pass@localhost/mydb

PostgreSQL to MariaDB

Less common but possible. Challenges include:

• No equivalent for JSONB indexes
• No row-level security
• No writeable CTEs
• Array types not supported
• Custom types not supported

Use Case Recommendations

Choose PostgreSQL When

• Building a new application with no MySQL dependency
• Need advanced SQL features (JSONB, full-text search, CTEs, window functions, row-level security)
• Running on FreeBSD where the PostgreSQL ecosystem is strongest
• Need strict SQL compliance and data integrity enforcement
• Want a combined relational and document database (JSONB)
• Need logical replication for selective table sync

Choose MariaDB When

• Running MySQL-dependent applications (WordPress, Drupal, Magento, many PHP frameworks)
• Need multi-master synchronous replication (Galera Cluster)
• Need the MyRocks engine for write-heavy, compression-sensitive workloads
• Need the ColumnStore engine for analytics on large datasets
• Team has deep MySQL operational expertise
• Need efficient handling of thousands of concurrent connections without external pooling

Verdict

For new FreeBSD deployments where you have a free choice of database, PostgreSQL is the stronger default. Its feature depth, SQL compliance, JSONB support, full-text search, and tight FreeBSD integration make it the more capable platform for most applications. The PostgreSQL community's overlap with the FreeBSD community means better documentation, more timely ports updates, and faster resolution of FreeBSD-specific issues.

MariaDB earns its place for MySQL-compatible applications and for specific technical requirements: Galera multi-master clustering, MyRocks write optimization, and ColumnStore analytics. These are genuine advantages that PostgreSQL does not match natively. If your workload fits one of these scenarios, MariaDB is the right choice.

For the majority of workloads on FreeBSD -- web applications, APIs, content management, data analysis, and general-purpose OLTP -- PostgreSQL delivers more capability, better standards compliance, and a more robust feature set.

Frequently Asked Questions

Can I run both MariaDB and PostgreSQL on the same FreeBSD server?

Yes. They use different ports (MariaDB: 3306, PostgreSQL: 5432) and different data directories. This is useful during migration or when running applications with different database requirements.

Is MariaDB a drop-in replacement for MySQL on FreeBSD?

For most applications, yes. MariaDB maintains MySQL wire protocol compatibility and supports the same SQL syntax for common operations. Some edge cases (specific stored procedures, plugins, or optimizer behaviors) may differ. Test your application before switching.

Which database performs better with ZFS on FreeBSD?

Both perform well on ZFS with proper tuning. PostgreSQL benefits from full_page_writes=off and ZFS's copy-on-write. MariaDB benefits from innodb_doublewrite=0 for the same reason. Set appropriate recordsize (16k for both) and disable atime. Neither has a decisive ZFS performance advantage -- tuning matters more than the database choice.

Should I use PgBouncer with PostgreSQL on FreeBSD?

If your application opens more than 100-200 concurrent database connections, yes. PgBouncer provides connection pooling that significantly reduces PostgreSQL's per-connection memory overhead. MariaDB's built-in thread pool handles high connection counts without external pooling.

Can I migrate from MySQL 8.x to MariaDB on FreeBSD?

Generally yes, but with caveats. MariaDB 11.x diverges from MySQL 8.x in some features (CTEs, window functions, JSON handling, authentication plugins). MySQL's caching_sha2_password default authentication is not supported by MariaDB -- you need to switch to mysql_native_password or MariaDB's ed25519 before migration. Test thoroughly.

Which is better for a WordPress site on FreeBSD?

MariaDB. WordPress is designed for MySQL/MariaDB and does not support PostgreSQL natively. While third-party plugins exist for PostgreSQL support, they are not officially supported and may break with WordPress updates. MariaDB provides full compatibility with WordPress on FreeBSD.