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

231–240 of 278 posts

Re: Migrating to Postgres

#231

Post by evanelias »

graealex wrote: post #141
coolcase wrote: post #71

Earlier quoted context omitted.

I hear MySQL can be better for some workloads?

Till about yesterday, MySQL was little more than a CSV file on wheels, and even current popularity is mostly driven by two decades of LAMP. You can be sure that PostgreSQL will be applicable for any work load that MySQL can handle, but not necessarily the other way round, so if you actually have the freedom to make a choice, go with PostgreSQL. In particular, because PostgreSQL has a lot more features, people imply t…

> Till about yesterday, MySQL was little more than a CSV file on wheels

That's utter nonsense. What are you even referring to here?

InnoDB is a rock-solid OLTP storage engine, and it's been MySQL's default since 2010. A very large percentage of the global economy relies on InnoDB, and has for quite some time.

Re: Migrating to Postgres

#232

Post by 999900000999 »

graealex wrote: post #136
999900000999 wrote: post #112

Earlier quoted context omitted.

You define the data schema client side. That's the entire idea behind Firebase. It makes prototyping much faster. I don't know how well it scales, but it works for most smaller projects.

Wait until you hear about ORMs.

I still have to create tables. I still have to migrate tables, these are all things I don't need to worry about with firebase.

It all depends on what you need to actually do. The only real weakness of Firebase is the Google lock in.

Re: Migrating to Postgres

#233

Post by luhn »

nasretdinov wrote: post #151
luhn wrote: post #72

Earlier quoted context omitted.

The improvements to migrations have been the biggest boon for running even modestly-sized Postgres DBs. It wasn't that long ago that you couldn't add a column with a default value without rewriting the whole table, or adding NOT NULL without an exclusive lock while the whole table was scanned. That becomes unfeasible pretty quickly.

Does adding a default value into a column finally work without locking up an entire table now at least?

Like most ALTER TABLE subcommands, you need an exclusive lock on the table while the catalog is updated. But no table scan or rewrite is required, so that lock is sub-second and can usually be done without disrupting a live application.

Re: Migrating to Postgres

#234

Post by evanelias »

venantius wrote: post #196
crowcroft wrote: post #193

Why does Postgres get so much love, and MySQL/MariaDB get nothing? I'm assuming it's largely because Postgres has more momentum, and is much more extensible, but if you're just trying to do 'boring DB stuff' I find it's faster for most use cases. Development has slowed, but it would be hard to argue that it's not battle tested and robust.

I am not an expert, but I have worked somewhere MariaDB/MySQL was being used at scale. My preference today for Postgres comes down to the fact that its query planner is much easier to understand and interface with, whereas MySQL/Maria would be fine 29/30 times but would then absolutely fuck you with an awful query plan that you needed a lot of experience with to anticipate.

On the other hand, at least MySQL/MariaDB has built-in support for index hints. Postgres does not, and you can absolutely still get bitten by unexpected query plan changes. It's rarer than bad plans in MySQL, but it's worse when it happens in pg -- without index hint support, there's no simple built-in solution to avoid this.

Re: Migrating to Postgres

#235

Post by abraxas »

mplanchard wrote: post #228
abraxas wrote: post #224

Earlier quoted context omitted.

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 shi…

I think timescale will compress them heavily on your schedule so if that's acceptable to your use case you might be able to do away with clickhouse. Hard to say of course, without knowing details around your insertion and query patterns, retention requirements and aggregations you need. But timescale can do a lot of that with pretty straightforward syntax.

Re: Migrating to Postgres

#236

Post by evantbyrne »

hombre_fatal wrote: post #221
evantbyrne wrote: post #205

Earlier quoted context omitted.

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 y…

I've been building one for Go in my free time, but it's not ready for general use. Historically, I've used Django despite being imperfect, because I can just install Wagtail and have a nice admin interface for free. It does have some nice convenience features though and transactions are easy enough. At my day job we use a Java framework with a terrible codegen-based ORM. Laravel has a decent database toolkit if you are into PHP. Unfortunately, excellent database toolkits are rare, and I have historically found myself dipping into SQL frequently. All decent ones will at least allow you to do so though.

Re: Migrating to Postgres

#237

Post by KronisLV »

cies wrote: post #155
monero-xmr wrote: post #140

Earlier quoted context omitted.

It is truly amazing how mature developers always wind up at the same result - old tech that has stood the test of time. Betting the company on alpha solutions of dubious quality keeps the devs employed at least.

> It is truly amazing how mature developers ... ...use ORMs! They are always bad, but especially bad when the code base and/or query complexity grows. https://dev.to/cies/the-case-against-orms-5bh4

> They make the simple queries slightly simpler, but they do not help you for the harder queries. — me

I dont think it’s possible to say they’re always bad when offering an example of when they make things better. In my eyes, it’s pretty simple: use the ORM for the simple CRUD and simple data querying, because you’ll have to do a lot of it in most codebases.

For everything else, there is in-database processing or dropping down to raw SQL. Even when using an ORM, nobody is preventing you from making an entity mapping against a DB view or calling stored procedures so you can keep the complex querying logic in pure SQL but get an object that’s easy to work with out of it. Plus, you know, any ORM worth their salt will let you point it at a live DB that you’ve run your (hopefully versioned, for example, with dbmate) migrations against and will let you output the backend code for the entity mapping for everything in a single command.

It’s kind of unfortunate that a lot of ORMs do kinda suck and have jagged edges, as well that most attempts at dynamic SQL also remain pretty niche, like: https://mybatis.org/mybatis-3/dynamic-sql.html

Re: Migrating to Postgres

#238

Post by nasretdinov »

I_am_tiberius wrote: post #101
jacobsenscott wrote: post #100

Earlier quoted context omitted.

CH excels at extremely high volume writes. You probably can't throw enough data at it.

Sorry, meant writes in terms of update/delete.

Many use cases can be addressed by using ReplacingMergeTree and such, so even when you need updates you can get them relatively cheaply

Re: Migrating to Postgres

#239

Post by javier2 »

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…

We have a couple of tables with about a billion rows now on single nodes in mysql. 256GB RAM and a number of 2TB nvme drives. It works completely fine, but you can forget about timely restore if something goes completely fucked. And we cant do any operation that isnt directly using the index or the whole performance suffers immediately. Which means we basically have to use those tables like they are a distributed database, but at least we have transactionality.

Re: Migrating to Postgres

#240

Post by sreekanth850 »

simianwords wrote: post #173
sreekanth850 wrote: post #77

It's wild and hilarious, how often startups and companies go for distributed databases like CockroachDB/TiDB/Yugabyte before they actually need distribution, this trends sucks. 100 million rows is nothing that a well-tuned Postgres or MySQL instance (or even read-replicated setup) can't handle comfortably. Scale when you hit the wall.

I don't buy this! Startups do need high availability. If you start having replicas you are already in distributed territory!

>Startups do need high availability.

HA is important. But Postgres and MySQL both support HA and replication setups without needing to jump straight into a distributed SQL (In this context of using cockroach). We use MySQL Innodb cluster + MySQL router with auto failover on single primary mode.

> If you start having replicas you are already in distributed territory.

But it’s not the same as a distributed database with quorum writes, global consensus, and cross-region latencies. Those systems are built for horizontal write scaling, that come with added complexity and cost, which most apps don’t need.

Post reply on HN