I like engine=memory tables. Compared to data structures found in programming languages, memory tables are more powerful: arbitrary columns, indices. The DB server solves concurrency with transactions and row-level locks; need B-tree primary key which is not the default for memory engine but easy enough to do at table creation.
I think they save quite an amount of software complexity, delegating these problems to the DB server.
Postgres is the materially better (more performant and ergonomic) choice if those are your requirements.
IMO the only place Maria wins is in ease of use / ops.
MariaDB's MEMORY engine has annoying limitations like no variable-length columns, no BLOB/TEXT support, and data loss on restart.
Postgres handles this much better… Unlogged tables skip write-ahead logging so they're fast, but still support all data types, full transactions, and B-tree indexes by default. you can point the data directory at a tmpfs RAM disk and get full in-memory speed with zero feature compromises.
Both varchar and varbinary columns work fine there. Blobs are indeed missing.
> data loss on restart
That’s OK, collections in memory do as well yet we use them pretty much everywhere.
> Unlogged tables skip write-ahead logging
I don’t want any disk I/O for my memory tables.
Another thing, aren’t PostgreSQL tables without write-ahead logging cause consistency bugs after restart when normal tables are current due to the logging, unlogged tables are old?
I like engine=memory tables. Compared to data structures found in programming languages, memory tables are more powerful: arbitrary columns, indices. The DB server solves concurrency with transactions and row-level locks; need B-tree primary key which is not the default for memory engine but easy enough to do at table creation.
I think they save quite an amount of software complexity, delegating these problems to the DB server.
Postgres is the materially better (more performant and ergonomic) choice if those are your requirements.
IMO the only place Maria wins is in ease of use / ops.
MariaDB's MEMORY engine has annoying limitations like no variable-length columns, no BLOB/TEXT support, and data loss on restart.
Postgres handles this much better… Unlogged tables skip write-ahead logging so they're fast, but still support all data types, full transactions, and B-tree indexes by default. you can point the data directory at a tmpfs RAM disk and get full in-memory speed with zero feature compromises.
> no variable-length columns
Both varchar and varbinary columns work fine there. Blobs are indeed missing.
> data loss on restart
That’s OK, collections in memory do as well yet we use them pretty much everywhere.
> Unlogged tables skip write-ahead logging
I don’t want any disk I/O for my memory tables.
Another thing, aren’t PostgreSQL tables without write-ahead logging cause consistency bugs after restart when normal tables are current due to the logging, unlogged tables are old?