Migrating to Postgres

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.

You don't need to optimize anything beyond appropriate indices, Postgres can handle tables of that size out of the box without breaking a sweat.

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.

$500k for only 100 millions rows db also sounds crazy

Vitess and planetscale seem to have quite a number of high profile users who have lauded its capabilities. A search through hn history pops up a few.

As someone who has primarily worked with Postgres for relational concerns, I’ve envied the apparent robustness of the MySQL scaling solutions.

I'd argue that horizontally sharded databases can work well, but they do tend to have significant non obvious tradeoffs that can be pretty painful.

There's a handful of companies that have scaled Citus past 1PB for production usage, but the examples I'm aware of all had more engineering to avoid capability or architecture limitations than one might like. I'd love to see someone come back with a fresh approach that covered more use cases effectively.

Disclaimer: former Citus employee

I don’t understand the hate, the only truly limiting factor for Prisma right now is its poor support for polymorphism, apart from that it has quite good support for complicated index setups, and if you need anything more performant, just drop to typed raw sql queries, it also supports views (materialized or otherwise) out of the box.

I recently wanted to check it out and wrote a small app that had good use of pgvector for embeddings, custom queries with ctes for a few complex edge cases, and it was all quite smooth.

Now it might not be at the level of active record, ecto or sqlalchemy but it was quite decent.

If you know your sql at any point it gave me options to drop down a level of abstraction, but still keep the types so as not to break the abstraction too much for the rest of the code.

Every ORM is bad. Especially the "any DB" ORMs. Because they trick you into thinking about your data patterns in terms of writing application code, instead of writing code for the database. And most of the time their features and APIs are abstracted in a way that basically means you can only use the least-common-denominator of all the database backends that they can support.

I've sworn off ORMs entirely. My application is a Postgres application first and foremost. I use PG-specific features extensively. Why would I sacrifice all the power that Postgres offers me just for some conveniences in Python, or Ruby, or whatever?

Nah. Just write the good code for your database.

I believe it’s either released now or at least a feature flag (maybe only some systems). It’s absolutely absurd it took so long. I can’t believe it wasn’t the initial implementation.

Funny relevant story: we got an OOM from a query that we used Prisma for. I looked into it - it’s was a simple select distinct. Turns out (I believe it was changed like a year ago, but I’m not positive), event distincts were done in memory! I can’t fathom the decision making there…

Not 100% parallel, but I was debugging a slow endpoint earlier today in our app which uses Mongo/mongoose.

I removed a $lookup (the mongodb JOIN equivalent) and replaced it with, as Prisma does, two table lookups and an in-memory join

p90 response times dropped from 35 seconds to 1.2 seconds

> I can't stand ORMs, I don't get why people use it, please just write the SQL.

I used to agree until I started using a good ORM. Entity Framework on .NET is amazing.

It is. But wait... it doesn't join the data on the application level of your application. You have to deploy their proxy service which joins the data on the application level.

Yes, there are multiple steps to consider before jumping to a distributed database and only when you actually hit bottlenecks, like read replication, CQRS, etc. But I guess it's often just about chasing fancy stuff.

It's a very Uber thing to do to enter a one way from the wrong end.

Yeah so there's basically just that one ;)

The entire database? Isn't that very limiting due to slow write speeds in Clickhouse? I saw ch more as a db for mainly read activities.

Yeah, this is our read with Postgres here at Motion. I believe that Motion will easily be able to 10x on modern hardware along with various optimizations along the way.

Not everything on the internet is a “website” and then there are several website hosting platforms that aggregate the individual concerns.

exactly. we take the CDC output / logical decoding from your OLTP tables and write into a columnar format with <s freshness.

We had to design this columnstore to be 'operational' so it can keep up with changing oltp tables (updates/deletes).

You'll be able to deploy Mooncake as a read-replica regardless of where your Postgres is. Keep the write path unchanged, and query columnar tables from us.

--- v0.2 will be released in preview in ~a couple weeks. stay tuned!

A follow up question: You can't join columnar tables with OLTP tables, right?

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.

When you get a chance can you take a look my reply here: https://news.ycombinator.com/item?id=43990502

When I first stepped into a DBA role with CockroachDB I was confused why indexes we obviously need were in unused indexes. It wasn't until I did an explain on the queries I learned the planner was doing zig-zag joins instead.

If you, for whatever obscure reason, need to preserve whitespace and key ordering, that is you want something that is effectively just a text column, then you should use JSON over JSONB.

I can't think of any case at all, no matter how contrived, where you'd want to use the non-B versions of the JSON aggregate functions though.

great point.

with pg_mooncake v0.2 (launching in ~couple weeks), you'll be able to get a columnar copy of your Postgres that's always synced (<s freshness).

Keep your write path unchanged, and keep your Postgres where it is. Deploy Mooncake as a replica for the columnar queries.

How does columnar = json? json isn't colunar at all... If you just want to have a schema in json instead of sql, use a no-sql db, postgres nosql features are strong, but the db features are actually much stronger.