4 ms·
Postgres focuses more on this use case. It's probably still going to do a better job with concurrency, or at least have more features. It'll also cost more to p
by hot_gril 4y ago
Postgres focuses more on this use case. It's probably still going to do a better job with concurrency, or at least have more features. It'll also cost more to pay for as a service, so you decide if it's worth. Whereas HC-tree is an obvious win if you're already using SQLite. It's fine for two things to approach similar use cases from different sides.
Personally I want the opposite direction, a sort of Postgres-lite that uses a single flat file and lives inside a lib, for things where the power of the full thing isn't needed yet but I want to design my backend code against a Postgres interface. I've seen Wasm Postgres examples, and maybe there are others, but nothing mainstream. And yes there was this SQL standard that everything was supposed to conform to, but that went out the window long ago.
- samuel 4y agoDuckdb should be compatible with postgres to a high degree. https://duckdb.org/why_duckdb.html https://duckdb.org/why_duckdb.html > SQL Parser: We use the PostgreSQL parser that was repackaged as a stand-alone library. The translation to our own parse tree is inspired by Peloton.
- pbowyer 4y agoWatch out with DuckDB that if you have an open reader you can't open a writer. A writer must be the only connection. https://duckdb.org/faq#how-does-duckdb-handle-concurrency https://duckdb.org/faq#how-does-duckdb-handle-concurrency
- nl 4y ago> direction, a sort of Postgres-lite that uses a single flat file and lives inside a lib, Can you expand on this? How did you package postgres as a lib?
- hot_gril 4y agoI haven't done this, but I'd want it. I'd like to write my code to use a Postgres database, except swap in something similar to a SQLite database instead when I'm either testing or not yet at scale. SQLite does everything in just a library linked with your code, unlike Postgres which is a separate running server. I can't simply use SQLite for this because its API is different from Postgres's.
- nick__m 4y agoYou should read on the ancient tech called ODBC.
- hot_gril 4y agoI almost said ODBC too but wasn't sure if I fully understood it.
- nl 4y agoDoesn't almost every language have some kind of abstraction layer that allows this? JDBC/ODBC/DB-API/Sequelize ORM (ignoring the ORM part). This used to be one of the major use cases for SQLite - running automated tests and being able to throw away the database file (or manage it at different points in the tests).
- hot_gril 4y agoYeah, but the abstraction layers get in the way if/when I need to optimize queries or use special features, plus it's easier to just write SQL. I'd rather target a specific DBMS and rewrite my queries if I ever change it. > ignoring the ORM part As it should be :)
- nl 4y agoNo, all (?) these things let you pass raw SQL straight to the database. In the case of JDBC and ODBC (and I think DB-API) thats all they do.
- hot_gril 4y agoBut raw SQL made for Postgres doesn't necessarily work for SQLite and vice versa. That's what I mean by the SQL standard being thrown out the window. You might be able to write SQL that works in both if you're careful and avoid a lot of features.
- deredede 4y ago
- switchbak 4y agoI use an embedded postgres testing library for the JVM that does something along those lines. Well no actually it just unpacks the tar file in a temp dir and runs the full postgres, but it mostly feels like what you describe (minus the single file part) and starts surprisingly fast. That would totally work for a little proof of concept (https://github.com/zonkyio/embedded-postgres https://github.com/zonkyio/embedded-postgres)
- hot_gril 4y agoThat's pretty neat, but not the same level of portability as SQLite.
- prpl 4y agoI used hypersql/hsqldb for unit tests. The Oracle mode was usually close enough. It’s pretty close for postgres. Sometimes there’s small issues (I think using “INSERT INTO … RETURNING” required column name in caps for some reason) but overall it’s almost always what you want for unit testing with java.
- kitd 4y agoH2db has a Postgres-compatibility mode too that would work for local testing. https://www.h2database.com/html/features.html#compatibility https://www.h2database.com/html/features.html#compatibility
- amluto 4y agoI have an absolutely horrifying Python script that makes a little sort-of-container that spins up a brand new MySQL instance, initializes it with a provided set of statements (from mysqldump), and runs it. And cleans it up when it’s done. And I have a wrapper that does it in a shell. This makes it possible to run MySQL-dependent tests, in parallel, on a dev machine, without too much misery. (MySQL is awful. Don’t use it for new designs. MariaDB isn’t obviously better. AWS RDS MySQL is, in my considered opinion after wasting days on it, even worse. RDS CANNOT INGEST THE OUTPUT OF mysqldump WITHOUT ILL-CONSIDERED AND POORLY DOCUMENTED KLUDGES!!! WTF!! Don’t get me started on how defective every MySQL replication mode is.)
- LAC-Tech 4y ago(Don’t get me started on how defective every MySQL replication mode is.) I was actually reading up on this this the other day (doing a bit of a breadth first search of multi-master replication in industry). I thought the way it worked was... interesting. Sounds like it's just as interesting in production.
- hot_gril 4y agoYeah, I use Postgres. It's good. MySQL only has the advantage of being offered by more cloud providers and thus being cheaper sometimes.
- tehbeard 4y agoDoesn't MySQL also not need you to configure a bouncer/proxy if you want more than a dozen connections at a time?
- hot_gril 4y agoPostgres in my experience can handle like 100 connections with the default config I get installing it on my Ubuntu server, but at some point you want pg-bouncer, and I vaguely remember MySQL having higher limits. Idk, it's been a while cause I've almost always limited the number of connections in my own code instead.