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

PostgreSQL for Everything

raphaelbauer.com

Post reply on HN
Print view View original

181–190 of 286 posts

Re: PostgreSQL for Everything

#181

Post by Tostino »

sgarland wrote: post #151
sgt wrote: post #148

Intrigued by this > After some performance checks it became clear that PostgreSQL was even faster than reading from the file system for our use-case. PostgreSQL uses the file system very efficiently for its data - and it adds a lot of caching and efficient reading and writing strategies that can outperform writing and reading raw data on a file system. This goes against conventional knowledge. I've always heard (and…

The primary reason to avoid doing so is avoiding thrashing your buffers, along with increased size of backups, WAL bloat, etc. Can you? Yes. Should you? Not at anything beyond a toy scale, unless you want to pay for more RAM to ensure that your normal OLTP queries don’t take a performance hit.

Listen to this advice.

I had a system that has ~600gb of blob data in bytea that could have easily been an S3 bucket + db reference. It made backups way more of a pain than necessary.

It was intentional in the design, because I wanted total consistency with a single backup for the system. It worked great for years. But as we got more and more clients, it really should have been migrated to the above design to make sure our backups could be taken / restored faster.

Re: PostgreSQL for Everything

#182

Post by akulkarni »

ethagnawl wrote: post #78

> Timescale lately released the pgvector extension, that turns your PostgreSQL into a vector database. I don't think this is accurate and smells like an LLM hallucination to me. From the Timescale/Tiger Data _pgvectorscale_ project's README: > pgvectorscale builds on pgvector with higher performance embedding search and cost-efficient storage for AI applications. I think this is where the confusion originates. I beli…

Thanks for the kind words.

And yes, Andrew Kane (et al) are the people to thank for pgvector.

We (Tiger Data) developed pgvectorscale and pg_textsearch (and timescaledb, and some others)

Re: PostgreSQL for Everything

#183

Post by tyg13 »

lelanthran wrote: post #166
tyg13 wrote: post #150

Earlier quoted context omitted.

Yup, and every time it breaks, you've got to pester someone whose job probably isn't maintaining that thing actively. I worked at a startup with massive NIH syndrome, once. We even used our own in-house programming language, because it was "better than anything else out there on the market." It did have a lot of nifty features that others don't have: a pretty novel type system, programmatic macros, a built-in build s…

> e even used our own in-house programming language, because it was "better than anything else out there on the market." It did have a lot of nifty features that others don't have: a pretty novel type system, programmatic macros, a built-in build system and other fun bells and whistles A DSL can work, but not for the features you list. Those features you already get from existing languages anyway! If you need general…

Ah, but you see, it was a general purpose programming language. A general purpose, functional, optionally-typed programming language, with its own optimizing compiler and build system. In fairness, it wasn't originally developed to be our in-house language, but it was the creation of the CTO.

It was fun while it lasted and I had a lot of fun working on it. But it was really not a good business fit. The programmatic macro system was supposed to allow us to build customer-facing DSLs on top of it, but everybody just wanted Python anyways.

Re: PostgreSQL for Everything

#184

Post by tpetry »

ericpauley wrote: post #57
molf wrote: post #40

Earlier quoted context omitted.

I don't think the point is that PostgreSQL is great for everything. But you may get by with a single piece of infrastructure instead of 7. In most of the applications we build or maintain we use PostgreSQL + cloud storage. That's it. And it works very well, also for: storing JSON, full text search, as a queue, as a vector database. Other software may be better at providing those features, but I'm extremely happy we o…

The article says verbatim “PostgreSQL Replaces Clickhouse”. Coming from storing billions of rows in Clickhouse and performing dozens of materialized operations I shudder to think about what that would look like in a DB that doesn’t even support declarative IVM.

The article suggested using TimescaleDB which has its own concept of IVM: continuos aggregates. And compared to the approach by ClickHouse it can also update the materialized views when you update/delete old raw data

https://sqlfordevs.com/books+courses/timescale/05-continuous...

Re: PostgreSQL for Everything

#185

Post by lelanthran »

tyg13 wrote: post #183
lelanthran wrote: post #166

Earlier quoted context omitted.

> e even used our own in-house programming language, because it was "better than anything else out there on the market." It did have a lot of nifty features that others don't have: a pretty novel type system, programmatic macros, a built-in build system and other fun bells and whistles A DSL can work, but not for the features you list. Those features you already get from existing languages anyway! If you need general…

Ah, but you see, it was a general purpose programming language. A general purpose, functional, optionally-typed programming language, with its own optimizing compiler and build system. In fairness, it wasn't originally developed to be our in-house language, but it was the creation of the CTO. It was fun while it lasted and I had a lot of fun working on it. But it was really not a good business fit. The programmatic m…

> A general purpose, functional, optionally-typed programming language, with its own optimizing compiler and build system.

> The programmatic macro system was supposed to allow us to build customer-facing DSLs on top of it,

Honestly, it sounds a lot like Lisp.

As a former Lisper, I don't doubt that it was a bundle of fun :-)

Re: PostgreSQL for Everything

#186

Post by sgarland »

dwedge wrote: post #154
cyh555 wrote: post #105

Earlier quoted context omitted.

100% except when Kafka goes wrong, who maintains it?

This problem doesn't go away with postgres. It's totally anecdotal but this is one thing that I've noticed different in mysql shops and postgres shops - with mysql there is usually at least one person on staff who knows MySQL DBA and scaling pretty well, with postgres it's rarely the case to have someone who knows the internals well - like you said, the person capable of maintaining it when it goes wrong. You could a…

MySQL will generally run fairly well with default tuning, assuming you've sized the buffer pool well relative to the amount of RAM you have (cloud providers do this automatically, but it's also not that hard to calculate). There are some knobs you can turn to eke out more performance in certain situations, and there are some defaults that are truly terrible (lock_wait_timeout is set to 1 year...), but all in all, it doesn't take a lot of care and feeding to run reasonably well.

Postgres, on the other hand, has a million knobs, many of them interact, you'll find conflicting advice for some of them, and it can rapidly fall over if you aren't keeping a close eye on long-running transactions. It's also more performant than MySQL in _most_ situations (hello, clustered index), if you've tuned it correctly. It also of course has far more extensibility out of the box, with tons of index types that are extremely helpful, if you know how and when to use them.

This difference is why I'm always frustrated when people parrot "just use Postgres" as though that solves all problems. It's an extremely powerful tool that can replace most of your stack, yes, but it also would really, really like you to RTFM. Not random Medium blog posts, the canonical documentation.

Re: PostgreSQL for Everything

#187

Post by switchbak »

anarazel wrote: post #47
devin wrote: post #30

This kind of post (Postgres! It's all you need!) is getting pretty tiresome. Postgres does not even come close to a full replacement for Elastic, and that's just the first bullet. Looking down the list it is pretty easy to go: Yes, postgres can be used instead of that for extremely basic use cases, but it all goes out the window you actually need any of the power of these other tools.

Fwiw, I, as someone who has worked on Postgres for a long time, also find it quite tiresome. Like there's plenty stuff I wouldn't use Postgres for, and I can probably get get more out of it than most.

Exactly - these recommendations often come with no context or scale provisions.

Yes Postgres can work in the small for a lot of things, it can even work at surprising scale if you use it according to its strengths.

But if you use it for things it doesn't shine at, at inappropropriate scale - you'll almost certainly run into issues. And resolving those can often be a bigger challenge than choosing a more suitable solution in the first place. But often I think younger/less experienced engineers just have to burn themselves, thus why this never seems to die.

Re: PostgreSQL for Everything

#188

Post by switchbak »

onesandofgrain wrote: post #36
devin wrote: post #30

This kind of post (Postgres! It's all you need!) is getting pretty tiresome. Postgres does not even come close to a full replacement for Elastic, and that's just the first bullet. Looking down the list it is pretty easy to go: Yes, postgres can be used instead of that for extremely basic use cases, but it all goes out the window you actually need any of the power of these other tools.

if you need elastic youre doing something wrong

Or something big. Which is often not wrong.

Re: PostgreSQL for Everything

#189

Post by sgarland »

orev wrote: post #90
erlich wrote: post #26

It's more "what one tool can do everything", not that its ideal. Like why people use Microsoft Teams even though its terrible. The relational model and sql force us to simplify our data models too much by eliminating relationships or just not dealing with them. Think about a nested json blob from some web service api and storing it in SQL in normalized tables. No one is going to do that. Everything just becomes a den…

I think you’re responding to the general idea of a relational database, not Postgres, and definitely not what’s in the article (DR;CA). Postgres has built in data types and functions that allows it to work with unstructured json documents, like you would use in MongoDB.

"Work with" != "work well." GIN indices aren't the same as B+tree, and even then, you'll have to decide / know about jsonb_path_ops vs. the default operator class. Or you just accept sub-optimal performance, I suppose.

The lack of a rigid schema makes it super fun as well. Does this attribute exist in this row? Who knows! Maybe there's a long-forgotten version lurking, waiting to be retrieved, that will utterly bork the calling app.

Re: PostgreSQL for Everything

#190

Post by sgarland »

tux3 wrote: post #71
fabian2k wrote: post #32

Earlier quoted context omitted.

If you run a single application, or a few instances of the same application, you don't need an external pool and most frameworks have an internal connection pool anyway. Not sure if I'm missing anything here, but if I want case-insensitive search I simply create an index on lower(column) and use that to query. VACUUM is something you need to pay attention to at scale. And at that point you need to know your DB anyway…

>if I want case-insensitive search I simply create an index on lower(column) and use that to query Or even pg_trgm trigram indexes, which are case-insensitive by default and support similarity search to accept typos and misspellings.

They're also massive.
Post reply on HN