81–90 of 278 posts
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.
Here you go https://www.uber.com/en-CA/blog/postgres-to-mysql-migration/
> 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…
Secondly, we did most of these things using SQL, Bash scripts, cron jobs and some I/O logic built directly into the application code. They were robust enough to handle some extremely mission critical systems (a failure could bring down a US primary market and if it's bad enough, you hear it on the news).
Feels like postgres is always the answer. I mean like there's gotta be some edge case somewhere where postgres just can't begin to compete with other more specialized database but I'd think that going from postgres to something else is much easier than the other way around.
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…
> 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.
Earlier quoted context omitted.
Nice! What optimizations have you put in llace yo support 150 mil? Just some indexing or other fancy stuff?
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.
This is definitely true, but I've seen migrations from other systems struggle to scale on Postgres because of decisions which worked better in a scale-out system, which doesn't do so well in PG.
A number of well meaning indexes, a very wide row to avoid joins and a large number of state update queries on a single column can murder postgres performance (update set last_visited_time= sort of madness - mutable/immutable column family classifications etc.)
There were scenarios where I'd have liked something like zHeap or Citus, to be part of the default system.
If something was originally conceived in postgres and the usage pattern matches how it does its internal IO, everything you said is absolutely true.
But a migration could hit snags in the system, which is what this post celebrates.
The "order by" query is a good example, where a bunch of other systems do a shared boundary variable from the TopK to the scanner to skip rows faster. Snowflake had a recent paper describing how they do input pruning mid-query off a TopK.
great blog. It seems like you might benefit from columnar storage in Postgres for that slow query that took ~20seconds. It's interesting that people typically think of columnstores for strict BI / analytics. But there are so many App / user-facing workloads that actually need it. ps: we're working on pg_mooncake v0.2. create a columnstore in Postgres that's always consistent with your OLTP tables. It might help for t…
That sounds awesome. Are you saying you still use your normal OLTP table for writing data and the columnstore table is always in sync with that OLTP table (that's fantastic)? I ready it works with duckdb - how does it work? I guess there's no chance this is going to be available on Azure Flexible Server anytime soon.
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.
great blog. It seems like you might benefit from columnar storage in Postgres for that slow query that took ~20seconds. It's interesting that people typically think of columnstores for strict BI / analytics. But there are so many App / user-facing workloads that actually need it. ps: we're working on pg_mooncake v0.2. create a columnstore in Postgres that's always consistent with your OLTP tables. It might help for t…
That sounds awesome. Are you saying you still use your normal OLTP table for writing data and the columnstore table is always in sync with that OLTP table (that's fantastic)? I ready it works with duckdb - how does it work? I guess there's no chance this is going to be available on Azure Flexible Server anytime soon.
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!
Earlier quoted context omitted.
That sounds awesome. Are you saying you still use your normal OLTP table for writing data and the columnstore table is always in sync with that OLTP table (that's fantastic)? I ready it works with duckdb - how does it work? I guess there's no chance this is going to be available on Azure Flexible Server anytime soon.
A follow up question: You can't join columnar tables with OLTP tables, right?
> 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 still makes me sad when half the queries I see are json_* - I know its far too late, but a big sad trombone in query performance is constantly left joining to planner queries that are going to give you 100 rows as an estimate forever.
Hoping for more easy columnar support in databases, which is one of the things that can lead you to storing json in database columns (if your data is truly columnar). Currently the vendor lock-in or requirements for installing plugins make it hard to do with cloud sql providers. Especially hard since by the time it's a problem you're probably at enough scale to make switching db/vendors hard or impossible.
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 (Keep your write path unchanged, and keep your Postgres where it is. Deploy Mooncake as a replica for the columnar queries.