Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

PostgreSQL is enough

gist.github.com

Post reply on HN
Print view View original

301–310 of 323 posts

Re: PostgreSQL is enough

#301

Post by randomdata »

sgarland wrote: post #295
randomdata wrote: post #284

Earlier quoted context omitted.

For what purpose? I can find no source of value in your request.

To prove that you have an inkling about the subject you have wandered into. Engineers and Scientists do that; they don’t hide behind airy and vague language – that is the realm of conmen. You stated that you cannot implement a tree-like structure with tables, so I set about proving you wrong, and posted a gist that does so. Until you can back up your claims with data, your words are meaningless, and no one here is go…

Prove to who? You?

Engineers and scientists are paid professionals. They are financially incentivized to help other people. Maybe you somehow managed to not notice, but I am but a person on an Internet forum. There is no incentive offered for me to do anything for anyone else. To have the gall to even ask for me to work for you without any compensation in kind is astounding.

Of what difference does it make if anyone takes me seriously or not? That's the most meaningless attribute imaginable. Oh noes, a random nobody on the internet doesn't believe me! It's the end of the world as we know it... How droll.

Perhaps it is that you do not understand what value means? Or how did you manage to write all those words and not come up with any suggestion of value whatsoever?

Re: PostgreSQL is enough

#302

Post by randomdata »

dventimi wrote: post #285
randomdata wrote: post #165

Earlier quoted context omitted.

> it's just frustrating that there isn't a standardized pattern for this. Be the change you want to see, I suppose. No doubt convergence will come, but it is still early days. Six months ago, most developers didn't even know what a vector database is, let alone consider it something to add to their stack. It took SQL well into the 1990s to fully solidify itself as "the standard" for relational querying. Even PostgreS…

> It took SQL well into the 1990s to fully solidify itself as "the standard" for relational querying. IBM had SQL in their database product in 1981, Oracle had it by v4 in 1984, ANSI picked SQL as its standard that same year, and completed the first version by 1986. Some time scientists say that the 1980s occurred before "well into the 1990s" but I mean, who can really say, right?

"The standard", not "a standard". I know, reading is hard.

Re: PostgreSQL is enough

#303

Post by dventimi »

randomdata wrote: post #302
dventimi wrote: post #285

Earlier quoted context omitted.

> It took SQL well into the 1990s to fully solidify itself as "the standard" for relational querying. IBM had SQL in their database product in 1981, Oracle had it by v4 in 1984, ANSI picked SQL as its standard that same year, and completed the first version by 1986. Some time scientists say that the 1980s occurred before "well into the 1990s" but I mean, who can really say, right?

"The standard", not "a standard". I know, reading is hard.

> I know, reading is hard.

We're done here.

Re: PostgreSQL is enough

#304

Post by randomdata »

dventimi wrote: post #281
randomdata wrote: post #138

Earlier quoted context omitted.

> that is the fault of you (or perhaps your ORM) for not writing a JOIN. It's your fault for not writing a join if you need a join. But that's not where the n+1 problem comes into play. Often in the real world you need tree-like structures, which are fundamentally not able to be represented by a table/relation. No amount of joining can produce anything other than a table/relation. The n+1 problem is introduced when y…

> No amount of joining can produce anything other than a table/relation. The n+1 problem is introduced when you try to build those types of structures from tables/relations. A trivial amount of lateral joins plus JSON aggregates will give you a relation with on record, containing a nested JSON value with a perfectly adequate tree structure, with perfectly adequate performance, in databases that support these operatio…

Of course. I know reading is hard, but nobody has suggested there aren't solutions. Obviously there are, else Postgres would be pretty much unusable. But the question remains: Why resort to hacks that require extra complexity in the application when you can just as easily use the database as it was designed to be used? What are you getting in return?

Re: PostgreSQL is enough

#306

Post by cpursley »

Rudism wrote: post #298

I worked for years on a massive PostGraphile project where 99% of the backend application code was written as stored procedures and table triggers, and let me tell you, once the complexity of the system moves beyond basic CRUD operations this kind of system is an absolute chore to work with. The code base is extremely difficult to organize and manage, code completion and compile-time error safety is virtually non-exi…

How does PostGraphile handle migrations?

Hasura, a similar tool - has really nice migration tooling inspired by Rails.

But your point is well taken, even with their nice migrations, I find myself struggling with changes to objects with dependencies (you have to drop all dependents and recreate which is a pita).

Re: PostgreSQL is enough

#307

Post by Rudism »

cpursley wrote: post #306
Rudism wrote: post #298

I worked for years on a massive PostGraphile project where 99% of the backend application code was written as stored procedures and table triggers, and let me tell you, once the complexity of the system moves beyond basic CRUD operations this kind of system is an absolute chore to work with. The code base is extremely difficult to organize and manage, code completion and compile-time error safety is virtually non-exi…

How does PostGraphile handle migrations? Hasura, a similar tool - has really nice migration tooling inspired by Rails. But your point is well taken, even with their nice migrations, I find myself struggling with changes to objects with dependencies (you have to drop all dependents and recreate which is a pita).

PostGraphile didn't have any first-class migration solution back when we started using it. It looks like they have a `migrate` tool, but I can't speak to that since I've never used it.

The way I set everything up, there's one schema containing just the bare tables and data, and then everything else (stuff that PostGraphile generates and all of our stored procedures with business logic) are in a separate schema. That way the migration process is a shell script that essentially 1. drops the entire non-table schema, 2. runs through all of the SQL files that define the application (there's some custom logic to control dependencies by looking for a `-- PRIORITY N` comment on the first line of the source files), 3. attach all the triggers to the tables (since they live in the schema that was dropped). It means we didn't have to worry about figuring out what all needs to be dropped to make a change because literally everything is dropped and recreated from scratch each time we deploy a new version.

I'm speaking in present tense because this application still exists, we've just (more or less) frozen the code base and are slowly migrating all its functionality piecemeal into a ASP.NET Core WebAPI project.

Re: PostgreSQL is enough

#308

Post by BenjieGillam »

cpursley wrote: post #306
Rudism wrote: post #298

I worked for years on a massive PostGraphile project where 99% of the backend application code was written as stored procedures and table triggers, and let me tell you, once the complexity of the system moves beyond basic CRUD operations this kind of system is an absolute chore to work with. The code base is extremely difficult to organize and manage, code completion and compile-time error safety is virtually non-exi…

How does PostGraphile handle migrations? Hasura, a similar tool - has really nice migration tooling inspired by Rails. But your point is well taken, even with their nice migrations, I find myself struggling with changes to objects with dependencies (you have to drop all dependents and recreate which is a pita).

You can use absolutely any migration framework you like with PostGraphile, it’s completely unopinionated about that.

Re: PostgreSQL is enough

#309

Post by klibertp »

scaryclam wrote: post #157
klibertp wrote: post #85

Earlier quoted context omitted.

> PostgreSQL is ALSO dead simple to get going I'm not saying it's hard to set up Postgres locally, but sqlite is a single binary with almost no dependencies and no config, easily buildable from source for every platform you can think of. You can grab a single file from sqlite.org, and you're all set. Setting up Postgres is much more complicated in comparison (while still pretty simple in absolute terms - but starting…

Except for when your data is in it. Migrating data on a running app is one of the worst things to deal with. I can understand using something simple and cut down for other things, but the DB is not the place I'd want to do that. Postgres isn't exactly hard to get going with, and will grow with you easily, so why trade that off for saving an hour or two at the start of the project?

> Except for when your data is in it.

Replacing the DB before it gets any actual data inserted into it solves this problem. You just switch to Postgres before you go anywhere beyond staging, at the latest - in practice, you need Postgres-exclusive functionality sooner than that in many cases, anyway. Even when that happens, you might still prefer having SQLite around as an in-memory DB in unit tests. The Postgres-specific methods are pretty rare, and you can mock them out, enjoying 100x faster setup and teardown in tests that don't need those methods (with big test suites, this quickly becomes important).

Unless you really want to use Postgres for everything like the Gist here suggests, the DB is just a normal component, and some degree of flexibility in which kind of component you use for specific purposes is convenient.

Re: PostgreSQL is enough

#310

Post by emseetech »

rrr_oh_man wrote: post #280
emseetech wrote: post #204

Earlier quoted context omitted.

> Needs to be accessible, with redundancy, across multiple AWS zones How many projects start with these requirements?

> How many projects start with these requirements? In a world fueled by cheap money and expensive dreams, you'd be surprised.

I’m not counting on a cheap money future. Not near future at least.
Post reply on HN