Hacker News Reader

Comment by hot_gril | Hacker News Reader

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.

View on HN

I 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.)

(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.

Yeah, I use Postgres. It's good. MySQL only has the advantage of being offered by more cloud providers and thus being cheaper sometimes.

Doesn't MySQL also not need you to configure a bouncer/proxy if you want more than a dozen connections at a time?

Postgres 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.

I 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)

That's pretty neat, but not the same level of portability as SQLite.

I 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.

H2db has a Postgres-compatibility mode too that would work for local testing.

https://www.h2database.com/html/features.html#compatibility

Duckdb should be compatible with postgres to a high degree.

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.

Watch 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

> 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?

I 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.

You should read on the ancient tech called ODBC.

I almost said ODBC too but wasn't sure if I fully understood it.

Doesn'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).

ODBC doesn't help you if you use any sort of pgsql-specific extension, however.

Yeah, 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 :)

No, 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.

But 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.

Reply on news.ycombinator.com