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

Migrating to Postgres

engineering.usemotion.com

Post reply on HN
Print view View original

221–230 of 278 posts

Re: Migrating to Postgres

#221

Post by hombre_fatal »

evantbyrne wrote: post #205
hombre_fatal wrote: post #177

Earlier quoted context omitted.

On the other hand, ORMs insulate you from database integrity since ORMs have limited access to underlying database features. In Postgres that usually means you're not locking rows, you're not using upsert, you might not be writing table DDL yourself. It often means you aren't even using database transactions. While these things might be extraneous fluff for an all-nighter hackathon, you really have to figure out a sw…

These are important features for a database toolkit to consider. I don't think that it is fair to dismiss an entire category of libraries on the grounds of some implementations being less complete than desired though. If we applied that same standard more generally, then we wouldn't use anything at all, because most software libraries kind of stink.

Fair enough. Do you have a favorite ORM that makes what you feel is a decent set of trade-offs, all things considered?

Admittedly, most of my experience with ORMs was with Ruby on Rails' Active Record + Rails' generated SQL tables + the culture that ensued from it, like large production Rails applications that didn't use a single db transaction (and often no indexes). Though I reckon things could have changed in 15 years.

I can imagine that an ORM might be the best option for most people. It wasn't until I worked at one specific company that I learned how to really use Postgres. Before that, an ORM and its abstractions probably made more sense than expecting me to figure out how to use a database directly on my own.

Re: Migrating to Postgres

#222

Post by wvh »

thehappyfellow wrote: post #162
luhn wrote: post #55

> By Jan 2024, our largest table had roughly 100 million rows. I did a double take at this. At the onset of the article, the fact they're using a distributed database and the mention of a "mid 6 figure" DB bill made me assume they have some obscenely large database that's far beyond what a single node could do. They don't detail the Postgres setup that replaced it, so I assume it's a pretty standard single primary an…

It’s incredible how much Postgres can handle. At $WORK, we write ~100M rows per day and keep years of history, all in a single database. Sure, the box is big, but I have beautiful transactional workloads and no distributed systems to worry about!

Two days ago, I'd have said the same. Yesterday, big box went down, and because it was so stable, it was a joint less oiled and the spare chickened out at the wrong time and apparently even managed to mess up the database timeline. Today was the post-mortem, and it was rough.

I'm just saying, simple is nice and fast when it works, until it doesn't. I'm not saying to make everything complex, just to remember life is a survivor's game.

Re: Migrating to Postgres

#223

Post by mplanchard »

bigfatkitten wrote: post #134
gbear605 wrote: post #79

Earlier quoted context omitted.

I’ve seen startups with a thousand active users paying $50k/month (though that’s overall costs, not just db). It’s really easy to waste a lot of money doing nothing.

It’s especially easy to waste money on databases. People just throw more compute power (ie money) at performance problems, rather than fixing their queries or making better use of indices.

You can see this in the article here, where they are just using whatever garbage queries Prisma spits out.

I’ve contended for a long time that ORMs and their ilk automatically building queries is an antipattern for anything but small data scale. At any reasonable size of db, you’re going to need to know sql well enough to write optimized queries anyway. There’s essential complexity in the DB queries, which ORMs can only hide for so long.

Re: Migrating to Postgres

#224

Post by abraxas »

freilanzer wrote: post #164
pojzon wrote: post #83

Earlier quoted context omitted.

I really enjoy this comment. > Postgres is the best answer if you have a solid team and you know what you’re doing. Not every type of data simply fits into relational model. Example: time series data. So depending on your model - pick your poison. But for relational models, there is hardly anything better than postgres now. It makes me happy coz I always rooted for the project from earily 2000s.

Even for timeseries there is https://github.com/timescale/timescaledb . Haven't used it, just knew it existed.

It's very good. Postgres by itself can handle a very high volume of inserts (I did over 100,000 rows/s on very modest hardware). But timescale makes it easier to deal with that data. It's not strictly necessary but it's very time series friendly (good compression, good indexing and partitioning etc). Nothing a pg expert can't accomplish with a vanilla postgres but very, very handy.

Re: Migrating to Postgres

#225

Post by mplanchard »

yakkomajuri wrote: post #181
etler wrote: post #40

I've lost count of how many "Migrating from X to Postgres" articles I've seen. I don't think I've once seen a migrating away from Postgres article.

I think your point still stands, and I'm a big Postgres advocate/user myself btw. But yeah we did migrate our _analytics_ data to ClickHouse (while still keeping Postgres for more transactional stuff) back when I was at PostHog. Writeup: https://posthog.com/blog/how-we-turned-clickhouse-into-our-e...

We also did this, using change data capture and kafka to stream data to clickhouse as it gets written to postgres.

Clickhouse is incredible tech. We’ve been very pleased with it for OLAP queries, and it’s taken a lot of load off the postgres instance, so it can more easily handle the very high write load it gets subjected to.

Re: Migrating to Postgres

#226

Post by 999900000999 »

pojzon wrote: post #83
999900000999 wrote: post #43

Earlier quoted context omitted.

Depends. If you want to fully embrace the vibe tables are difficult. Even before LLMs, I was at a certain company that preferred MongoDB so we didn’t need migrations. Sometimes you don’t care about data structure and you just want to toss something up there and worry about it later. Postgres is the best answer if you have a solid team and you know what you’re doing. If you want to ride solo and get something done fas…

I really enjoy this comment. > Postgres is the best answer if you have a solid team and you know what you’re doing. Not every type of data simply fits into relational model. Example: time series data. So depending on your model - pick your poison. But for relational models, there is hardly anything better than postgres now. It makes me happy coz I always rooted for the project from earily 2000s.

Don't get me wrong, Postgres is awesome when things work.

But, for example I was working on a .net project and entity framework decided it couldn't migrate Postgres tables correctly.

I'm not a SQL person, at this point my options are to drop tables, and let .net recreate them or try and write my own migrations.

This just isn't an issue with Firebase. I can add all the fields I want directly on the client.

Re: Migrating to Postgres

#227

Post by sgarland »

abraxas wrote: post #218
sgarland wrote: post #204

Earlier quoted context omitted.

Because MySQL got a (rightfully so) bad rap before it adopted InnoDB as the default storage engine, and then tech influencers happened. I love Postgres, but I also love MySQL, and 99% of the time I see people gushing about Postgres, they aren’t using any features that MySQL doesn’t have. The single biggest thing for MySQL that should be a huge attraction for devs without RDBMS administration experience is that MySQL,…

To the contrary when the PK has to be a BTree it already ties my hands because I can't have good disk layout for say, time series data where I might use a ligher index like BRIN at a cost of somewhat slower queries but much better index update rates.

I would not personally build a TSDB atop MySQL, though I worked at a place that did, and it worked OK. I don't remember their schema, though.

If I had to, I'd probably do something like this (haven't tested it beyond validating that it creates):

    mysql> SHOW CREATE TABLE ts\G
    *************************** 1. row ***************************
           Table: ts
    Create Table: CREATE TABLE `ts` (
      `metric_id` smallint unsigned NOT NULL,
      `ts` datetime NOT NULL,
      PRIMARY KEY (`metric_id`,`ts`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
    /*!50100 PARTITION BY RANGE (dayofmonth(`ts`))
    SUBPARTITION BY HASH (`metric_id`)
    SUBPARTITIONS 3
    (PARTITION p1 VALUES LESS THAN (2) ENGINE = InnoDB,
     PARTITION p2 VALUES LESS THAN (3) ENGINE = InnoDB,
     PARTITION p3 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
    1 row in set (0.012 sec)
Obviously there would be more partitions for different days (or whatever other date chunk you wanted – months, weeks, etc.), and the sub partitions number would depend on how many metrics you were tracking. You could also simplify this at the expense of more tables by having a table per metric.

Re: Migrating to Postgres

#228

Post by mplanchard »

abraxas wrote: post #224
freilanzer wrote: post #164

Earlier quoted context omitted.

Even for timeseries there is https://github.com/timescale/timescaledb . Haven't used it, just knew it existed.

It's very good. Postgres by itself can handle a very high volume of inserts (I did over 100,000 rows/s on very modest hardware). But timescale makes it easier to deal with that data. It's not strictly necessary but it's very time series friendly (good compression, good indexing and partitioning etc). Nothing a pg expert can't accomplish with a vanilla postgres but very, very handy.

I haven’t tried timescale, but I have found postgres with time-based partitions works very well for timeseries data. Unless you’ve got really heavy indexes, the insert speed is phenomenal, like you said, and you’ve got the freedom to split your partitions up into whatever size buckets makes the most sense for your ingestion and query patterns.

A really nice pattern has been to use change data capture and kafka to ship data off to clickhouse for long-term storage and analytics, which allows us to simply drop old partitions in postgres after some time.

Re: Migrating to Postgres

#229

Post by crowcroft »

abraxas wrote: post #218
sgarland wrote: post #204

Earlier quoted context omitted.

Because MySQL got a (rightfully so) bad rap before it adopted InnoDB as the default storage engine, and then tech influencers happened. I love Postgres, but I also love MySQL, and 99% of the time I see people gushing about Postgres, they aren’t using any features that MySQL doesn’t have. The single biggest thing for MySQL that should be a huge attraction for devs without RDBMS administration experience is that MySQL,…

To the contrary when the PK has to be a BTree it already ties my hands because I can't have good disk layout for say, time series data where I might use a ligher index like BRIN at a cost of somewhat slower queries but much better index update rates.

Postgres is a lot more flexible so if you're making a TSDB, handling geospatial data etc. etc. it is usually better (not to say MySQL can't be used effectively for a lot of these use cases still).

I just see lots of people making CRUD web apps and choosing these new Postgres solutions, and that seems like the one thing that MySQL is almost always better at.

Re: Migrating to Postgres

#230

Post by mplanchard »

mmiao wrote: post #74

a 100 million rows table is fairly small and you just don't need a distributed database. but you will need one if you hit 10 billion rows

Depends on what you’re doing with them. We’ve currently got a postgres DB with >100b rows in some tables. Partitioning has been totally adequate so far, but we’re also always able to query with the partition keys as part of the filters, so it is easy for the query planner to do the right thing.
Post reply on HN