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

How to use Postgres for everything

github.com

Post reply on HN
Print view View original

141–150 of 181 posts

Re: How to use Postgres for everything

#141

Post by whateveracct »

com wrote: post #90
xnx wrote: post #80

Earlier quoted context omitted.

Not a problem until it's a problem.

And the best thing is that until it’s a problem you can focus on product/market fit and delighting your customers. Overengineering is a plague amongst SWEs, and almost as dangerous as failing to sell the product in the market.

I think cargo cutting stuff like "Postgres for everything" is also a plague. I agree you don't want to overengineer, but if you're a leader and your policy is basically "engineers can't ever be trusted to do hard things" then you're a bad leader.

Sorry if this comes off as brusque, but I've seen good engineers want to use Elasticache for the right reasons and seen "leaders" tell them no because "caching is one of the hard problems in computer science."

Leadership-by-folksy-saying is sadly a real thing.

Re: How to use Postgres for everything

#142

Post by drtgh »

hamilyon2 wrote: post #100
tlarkworthy wrote: post #91

I was recently annoyed to find postgres indexes don't support skipping [1] you also can't have the nul character in a string (\u0000) [2]. Its great, but it has some strange WTF gaps in places. [1] https://wiki.postgresql.org/wiki/Loose_indexscan [2] https://stackoverflow.com/questions/28813409/are-null-bytes-...

Yes, skip-index scans require custom sql now. I am also a bit annoyed by cache-like uses not being first-class. Unlogged tables get you far, temporary tables are nice, but still all this feels like a hurdle, awkward and not what you actually need.

> I am also a bit annoyed by cache-like uses not being first-class.

Since what happened recently with Redis[1] the first thing I thought about was Postgre, but the performance[2] difference is too noticeable, so one have to look for other alternatives, and not very confident due thinking such alternatives may follow the same "Redi's attitude" ( ValKey, DragonflyDB, KeyDB, Kvrocks, MinIO, RabbitMQ, etc etc^2 ).

It would be nice if these cache-like uses within Postgre had a tinny push.

[1] https://news.ycombinator.com/item?id=42239607

[2] https://medium.com/redis-with-raphael-de-lio/can-postgres-re...

    XXXXX achieves a latency of 0.095 ms, which is approximately 85% faster than the 0.679 ms latency observed for Postgres’ unlogged table.
    
    It also handles a much higher request rate, with 892.857,12 requests per second compared to Postgres’ 15.946,02 transactions per second.

Re: How to use Postgres for everything

#143

Post by KronisLV »

rvba wrote: post #118
KronisLV wrote: post #110

Earlier quoted context omitted.

> Let's ship first and worry about this, much much much much later. Overarchitecting stuff makes life hard; coming into companies that have 40 servers running with everything architected for 1000000 engineers and billions of visitors while in reality there aren't even 2 users and there is 1 overworked engineer. Even if you try to draw boundaries between different bits of the system, you are unlikely to end up with 40…

User above was making a point. And you are lucky to not see an org where everything is a microservice that uses some unusual database, because the poeple responsible wanted to use some fancy new technology. Also seems you were lucky to not see messy development where some data is in a legacy system, some in new (which doesnt quite work), some in "cool" mongoDB that uses math.random to report just 10% of errors and re…

> And you are lucky to not see an org where everything is a microservice that uses some unusual database

I've been unlucky to see an org where everything is a monolith (not horizontally scalabe due to a plethora of design choices along the way) that uses Oracle, including plenty of stored procedures and DB links along the way.

Honestly, I'm starting to think that you can't win with these things and that there will be projects that suck to work with regardless of the tech stack or regardless of how much you try to make them not suck.

That said, in general, you could probably do worse than PostgreSQL or even MariaDB (because at least with that one you can still run it in a container locally, much like MySQL, instead of running into Oracle XE or Oracle free version, whatever they called the latest one, limitations where you can't even bring the shared environment schema over to local containers).

Re: How to use Postgres for everything

#144

Post by Kwpolska »

lelanthran wrote: post #123
Kwpolska wrote: post #119

Earlier quoted context omitted.

Stored procedures just add overhead and make everyone's lives harder. Forget about any ORMs, you're writing raw SQL with all the quirks of PL/pgSQL biting you all the time.

There's advantages too: - marking some columns as NOT NULL. - referential integrity means you can't accidentally have dangling pointers to non-existant dat. - mutually exclusive columns let's the database enforce things like "at least one of A and B needs to be Nonzero, and both cannot be Nonzero at the same time. - create a type that allows only values matching a specific regex. Seriously, if you want strong typing…

The things you listed aren’t stored procedures, they are all possible to implement as check constraints. They are great, and they are fully compatible with ORMs. A stored procedure is a bit of code, stored in and executed by the database, usually written in a 1960s-era language (like PL/SQL or PL/pgSQL).

Re: How to use Postgres for everything

#145

Post by osigurdson »

pdimitar wrote: post #134
osigurdson wrote: post #114

Postgres, ClickHouse and NATS for everything

You use NATS as opposed to Kafka, I presume? Also, what's Clickhouse for? Logs, observability data?

NATS is great because it has pub/sub, streaming (like Kafka), KVS (like Redis). It is possible to do some of these things in Postgres but really, why?

We use ClickHouse for time series data. Postgres was ok up to low billions of points. Despite trying to use timescale for this purpose it did not fit our use case.

Re: How to use Postgres for everything

#146

Post by pdimitar »

osigurdson wrote: post #145
pdimitar wrote: post #134

Earlier quoted context omitted.

You use NATS as opposed to Kafka, I presume? Also, what's Clickhouse for? Logs, observability data?

NATS is great because it has pub/sub, streaming (like Kafka), KVS (like Redis). It is possible to do some of these things in Postgres but really, why? We use ClickHouse for time series data. Postgres was ok up to low billions of points. Despite trying to use timescale for this purpose it did not fit our use case.

Very valuable, thank you. As time series data (and especially observability data) tends to very quickly explode in volume, I believe planning for tens of billions, if not trillions, of records is worth planning for from the start and it is not over-engineering.

If you don't mind one final question: can you ACK a message in NATS without it being bound to offset that makes it impossible to _not_ ACK a message without ruining the ACKs of the previous messages?

To clarify: I often found myself in situations when I was fetching batches of stuff from Kafka, say, 50 at a time, and then hand them off to 50 parallel agents to process. However, f.ex. messages 17, 31 and 47 failed processing and I could not not ACK them as that would not allow us to ACK those that succeeded before. So I ended up pushing them to another Kafka queue / topic that specifically deals with retries. That's IMO a hack, as most apps out there surely don't need the monstrous speed that Kafka can provide. I am OK with something (not much) slower where I have the freedom to ACK or not-ACK any particular event/message regardless of its position.

Does NATS allow for it?

Re: How to use Postgres for everything

#147

Post by sureglymop »

ksynwa wrote: post #103

Anyone has advice for backup up a postgres database that is run for personal use? I currently have one that is used by miniflux. I wrote a script for backing it up but it is gonna fail me some day. ``` #!/usr/bin/env sh set -e PGUSER=miniflux PGPASSWORD=... pg_dump -F t -h 127.0.0.1 miniflux | gzip > /backups/miniflux_db_temp.tar.gz mv /backups/miniflux_db{_temp,}.tar.gz ```

I do something similar but pipe a dump of the whole db with pg_dump into restic. Only works well for small to medium sized DBs.

Re: How to use Postgres for everything

#148

Post by sureglymop »

lokimedes wrote: post #13

Having just spent the better part of two weeks integrating Apache Age for Graph data, just to realize the project is stale and a mess, don’t take this list on face value. Now hoping for better results with DGraph, but it seems that graph databases are living a precarious existence.

If I may ask, what type of use cases are there for which a graph database is well suited?

I could imagine there to be a few (that I can't think of and haven't seen). I have seen graph databases used where they didn't make sense though.

Re: How to use Postgres for everything

#149

Post by osigurdson »

pdimitar wrote: post #146
osigurdson wrote: post #145

Earlier quoted context omitted.

NATS is great because it has pub/sub, streaming (like Kafka), KVS (like Redis). It is possible to do some of these things in Postgres but really, why? We use ClickHouse for time series data. Postgres was ok up to low billions of points. Despite trying to use timescale for this purpose it did not fit our use case.

Very valuable, thank you. As time series data (and especially observability data) tends to very quickly explode in volume, I believe planning for tens of billions, if not trillions, of records is worth planning for from the start and it is not over-engineering. If you don't mind one final question: can you ACK a message in NATS without it being bound to offset that makes it impossible to _not_ ACK a message without r…

Perhaps I misunderstand, but if you have 50 parallel agents, why not just have each pull messages, process them and ACK when complete? The part I don't understand is the pre-fetch. Note that NATS is much more flexible than Kafka however, so more likely to fit more uses cases (even just for streaming).

For the first question, I'd definitely recommend using ClickHouse for 10B - 1T points.

Re: How to use Postgres for everything

#150

Post by pdimitar »

osigurdson wrote: post #149
pdimitar wrote: post #146

Earlier quoted context omitted.

Very valuable, thank you. As time series data (and especially observability data) tends to very quickly explode in volume, I believe planning for tens of billions, if not trillions, of records is worth planning for from the start and it is not over-engineering. If you don't mind one final question: can you ACK a message in NATS without it being bound to offset that makes it impossible to _not_ ACK a message without r…

Perhaps I misunderstand, but if you have 50 parallel agents, why not just have each pull messages, process them and ACK when complete? The part I don't understand is the pre-fetch. Note that NATS is much more flexible than Kafka however, so more likely to fit more uses cases (even just for streaming). For the first question, I'd definitely recommend using ClickHouse for 10B - 1T points.

I mean the following (let me simplify it). You pull stuff from Kafka in batches of 4 and immediately send each message to a parallel worker, first parsing them in-thread and sequentially and these are the results of the parsing:

1. ok

2. error

3. ok

4. ok

I cannot not-ACK message#2 because that means message#1 is not ACK-ed as well.

Does NATS solve this? F.ex. can I get a reference to each message in my parallel workers for them to also say "I am not ACK-ing this because I failed processing it, let the next batch include it again"?

Post reply on HN