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

261–270 of 286 posts

Re: PostgreSQL for Everything

#261

Post by CodesInChaos »

rafael-lua wrote: post #201
psadauskas wrote: post #139

My general rule of thumb is "Use Postgres until you've discovered why you can't use Postgres." Anything you introduce is another moving part you have to operate and maintain, and in the beginning, Postgres can probably handle it. Wait for load, see where its failing, and then you'll have a better idea if adding another tool is worth the cost.

The issue with this general rule of thumb is that we can swap Postgres for many others, including non-relational, and it works.

My rule is "use a single database for as many of you persistence needs as possible". Often Postgres is a good choice for that database, but many other general purpose DBMSs will work just as well.

In my previous company we used MongoDB, for almost everything, including text logs, request logs, job-queues and small template files. We added S3 since storing terabytes of files in a database is expensive. Now, I wouldn't choose MongoDB for a new app, since the data model and query language suck, but it performed reasonably across many use-cases.

For my next application I will use Postgres as primary database. Probably will integrate S3 before going live, to avoid the necessary data migration later, but haven't decided yet if that's a premature optimization.

Re: PostgreSQL for Everything

#262

Post by CodesInChaos »

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…

In our multi-tenant e-commerce application we handled it like this:

* Metadata for each file is stored in the database

* The application accesses files through an abstraction based on that metadata, and doesn't care if the actual file is stored in S3 or the database

* File types which are small and few, are stored in the database. For example letterheads, logos, terms-and-conditions. (a few gigabytes total)

* File types which are big (e.g. CSV-reports) or many (e.g. invoice-PDFs) are stored in S3 (several terabytes total)

* Development and test systems often use the database for everything and don't have an associated S3 bucket.

* Most of the application remains usable without S3 (access to invoice PDFs and CSV-reports is not critical)

In our case the DB was Mongo, but I expect Postgres to work the same.

We actually stored all files in the database originally, and only migrated after it reached several terabytes. It worked perfectly fine, but was rather expensive. So next time I'd go for S3 for the start.

Re: PostgreSQL for Everything

#263

Post by jroseattle »

sanderjd wrote: post #258
jroseattle wrote: post #257

> Events, queues and persistent logs are getting more and more important in today’s software systems. Systems like Kafka, RabbitMQ, SQS and others provide that functionality. But maintaining them is annoying, custom and you need the skillset. In tech stack choices, I prefer staying simple as long as feasible. That said, you also need to know and understand concepts at a thorough level. The above comment from the arti…

To me, your concluding sentence cuts the opposite direction. The server is not the most important thing, and each new kind of server that exists in the system is an appreciable increase in maintenance burden. To me, taken together, this is an argument for waiting until you have a very concrete forcing function to introduce that new kind of server for this purpose. I think a good way to think of this is: What empirica…

Heartily agree with the metrics for analysis, but this seems anchored around a focus on introduction of "something else" without regard firstly to correctness.

> The server is not the most important thing, and each new kind of server that exists in the system is an appreciable increase in maintenance burden.

Sure, but compared to what? It's right to consider complexity, but understanding tradeoffs requires depth. The OP's original premise was that learning all the things about specific queuing services was unnecessary chafe; that INSERTs, SELECTs and UPDATEs are all anyone needs.

The maintenance burden sits with your producers & consumers (or publishers/subscribers, whatever your nomenclature...), whether you want it there or not.

My learned experience is that as soon as you start moving messages that are beyond trivial and carry different operational characteristics, you're going to have to understand those deeper concepts anyway.

Re: PostgreSQL for Everything

#264

Post by CodesInChaos »

jtwaleson wrote: post #178

At Comper we have a very hot key-value store for annotating git data. We maintain a parallel git-blame data structure so we can do incremental "git blame -w -M -C -C". Typically a very expensive operation, but if you make it incremental, you can make it very cheap when new commits need to be analyzed. However, building the git blame tree is still pretty intensive for large repos. We currently use rocksdb with storage…

Unlogged tables still support MVCC and keep old versions of tuples around until they're garbage collected.

Re: PostgreSQL for Everything

#265

Post by dorfsmay »

KronisLV wrote: post #246
dorfsmay wrote: post #203

Earlier quoted context omitted.

I tried very hard to use postgres as a queue, it was robust but slow once I started to push from more than a few processes/servers. Moving to zeromq initially and sqs after solved all my perf issues, and was still solid.

What were your experiences with ZeroMQ?

It worked fine, we had no issue with it. We moved to SQS because it was one less thing we needed to spend time on.

Re: PostgreSQL for Everything

#267

Post by sanderjd »

jroseattle wrote: post #263
sanderjd wrote: post #258

Earlier quoted context omitted.

To me, your concluding sentence cuts the opposite direction. The server is not the most important thing, and each new kind of server that exists in the system is an appreciable increase in maintenance burden. To me, taken together, this is an argument for waiting until you have a very concrete forcing function to introduce that new kind of server for this purpose. I think a good way to think of this is: What empirica…

Heartily agree with the metrics for analysis, but this seems anchored around a focus on introduction of "something else" without regard firstly to correctness. > The server is not the most important thing, and each new kind of server that exists in the system is an appreciable increase in maintenance burden. Sure, but compared to what? It's right to consider complexity, but understanding tradeoffs requires depth. The…

I agree with you about being willing and unafraid of getting the depth in the concepts that matter. But that's also why I agree with you that the question of the server technology is the less important thing. I just think that leads to the conclusion "maybe stick with whatever you're already using for oltp for longer?" more so than to "you may as well introduce kafka".

I think both your questions, about correctness and about the comparison point, are answered by thinking through the empirical metrics you're trying to improve.

I often feel a bit out of step with other engineers on this point, but to me, "correctness" is not a binary yes/no, it is also a continuum. It's one of the metrics you probably want to be pushing to a large number of 9s, but trying to get to actually 100% has low ROI for what most people work on. (Not for everything! But for most things.) If you can already get to five 9s with postgres, but more 9s with kafka, is that worth it? Maybe, but maybe not. If you're missing way more messages than that and corrupting data because of it or some other bad thing, and this is the culprit, then that's certainly a good time to compare the trade offs of fixing your implementation or switching to a "proper" system for this.

And to "compared to what?", the answer is, compared to the current values of the metrics you've decided you care about. One of those might be the complexity of the implementation and the necessity to maintain specific expertise in a bespoke implementation of non-trivial complexity. This, to me, is usually the compelling reason to "buy" rather than build. But I think it is too often left too implicit and vibes-y instead of making it explicit and seeking to quantify the impact.

Re: PostgreSQL for Everything

#268

Post by farlight »

williamdclt wrote: post #31
Ozzie_osman wrote: post #9

I love postgres and use it heavily, but I still don't fully understand how it overlook MySQL. Maybe because of Heroku adopting it. MySQL was generally faster, and while MyISAM was a bit limited Innodb was pretty powerful, and you had the choice. It was also simpler (imo) and avoided a lot of the xid/vacuum issues. That said, still love Postgres. But at the time it started eclipsing MySQL, MySQL felt better positioned…

I've not interacted with mysql a whole lot, but when i did I was regularly surprised that it didn't have stuff I was missing from Postgres. Off the top of my mind: - Query planner is much worse (just yesterday I had to USE INDEX to sped up a query by 300x, I'm near-certain postgres would just have gotten it right) - Indexes are much more limited: no GIST, no GIN - No transactional lock (`pg_advisory_xact_lock` in pos…

Recent MySQL comes with a rewritten optimizer (which they call "hypergraph") that will soon become the default, but you can already use if you want to.

One application I'm working on (CRUD, but with fairly complicated business logic) is seeing large performance improvements, especially on reports which join many rows from 10+ tables.

I regularly test the same dataset on both PostgreSQL and MySQL -- we support both, and this new optimizer puts them pretty close.

The current default optimizer has some pathological cases where reports run 8-10 times slower on MySQL compared to PostgreSQL. Not with this one.

https://blogs.oracle.com/mysql/the-hypergraph-optimizer-is-n...

Re: PostgreSQL for Everything

#269

Post by pojzon »

Its not a meme. Postgres does all of those things good enough to be always considered the first pick.

I love Postgres. Im using it for majority of my career in IT. It never failed me, even on very big scale.

Amazing piece of software.

Re: PostgreSQL for Everything

#270

Post by pojzon »

Lio wrote: post #169
SoftTalker wrote: post #147

Earlier quoted context omitted.

Yes, for very small or embedded, single-purpose systems sqlite is usually a good choice. It's very well tested, and there's nothing extra to run or manage. But be careful if the system starts growing beyond that, you'll want a real RDBMS before you abuse sqlite too much.

Depends on what you mean by "small" and "growing". If you mean database size, SQLite can handle massive amounts of data. I've seen 281 TB quoted as theoretical max size.

How does it handle 100000 write transactions per second ?

From multi-client architecture and with regional HA?

Post reply on HN