21 ms·
Migrating to Postgres
- hobs 1y agoIt 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.
- NegativeLatency 1y agoHoping 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.
- hobs 1y agoHow 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.
- NegativeLatency 1y agojson isn't necessarily columnar, but it is a natrual fit for stuff that is columnar that's otherwise harder to model in a traditional relational db here's my usecase: - we have a bunch of attributes (all different names by customer, and many different values for each record that a customer stores) - it's a fairly natural fit for a json value with only one level of key: value mapping - we use mysql on GCP (no columnar plugins, too hard to switch to postgres) Someone could go back in time and correctly model it as columns and not json but that ship has sorta sailed. While it's not impossible to change, it would be pretty hard, time will tell if that ends up happening. I would love to be able to tell mysql "put this column in a collumnar engine and use that when I query on it" (AlloyDB is this for postgres on GCP)
- hobs 1y agoToo bad mysql doesn't even have materialized views - its not a good combination with a json only situation, but you clearly are suffering through that, good luck :) probably etl it to clickhouse and move on. Could always try and dump to GCP/Parquet/json and use duckdb/bq to just query it all.
- moonikakiss 1y agogreat 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.
- NegativeLatency 1y agoI wish, someone unfortunately picked mysql
- bastawhiz 1y agoIf the queries are sensible, you can always create indexes that index on the queried expressions. https://www.postgresql.org/docs/current/indexes-expressional.html https://www.postgresql.org/docs/current/indexes-expressional...
- panzi 1y agoNot sure why those are json_agg() instead of array_agg() in that example. Why would you use a JSON array instead of a native properly typed array? Yes, if you have some complex objects for some reason you can use JSON objects. But those where all just arrays of IDs. Also why was it json_agg() and not jsonb_agg()? Is there any reason on why to use JSON over JSONB in PostgreSQL?
- renhanxue 1y agoIf 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.
- paulryanrogers 1y agoThe non-B JSON can take up less space on disk and less write time complexity.
- panzi 1y agoI wonder if its just used in a query like that if the generated data structure is only in memory anyway and then serialized to JSON to send to the client. I.e. JSON VS JSONB would not make any difference?
- niwtsol 1y agoThat was an interesting read, seemed like an overwhelming amount of data for why they should move off cockroach. All of my db work has been read heavy and I’ve never had a need for super fast multi-region writes. Is a multi-region write architecture possible in Postgres? I’m trying to understand if GDPR was the requirement that resulted in cockroach or if the lackluster multi region write was the bigger driver.
- sgarland 1y agoThere are multi-master Postgres options like BDR (I think it’s since renamed; whatever EnterpriseDB calls it now), yes. Most people don’t need it, even if they think they do, and they also usually are in no way capable of dealing with the operational complexity it involves. If you’ve ever administered Postgres at scale, multiply it by 10. That’s what dealing with multi-master is like. It’s a nightmare.
- sroussey 1y agoMost people don’t need multi-region read architecture for that matter. SaaS app devs at 5 person companies really want to do “Facebook” scale problems.
- ketzo 1y agoI think this is kinda reductive. If you’ve got users all over the world, then global DB replicas are a sizable performance improvement, whether you’ve got 1 engineer or 1,000.
- sgarland 1y agoYes, global read replicas can be helpful, though the replica lag needs to be thoughtfully considered. IMO, one of the worst offenders is places with microservices, who absolutely do not need microservices, and are doing reads-after-writes. For every RDBMS I know of other than MySQL (even MariaDB has this), you can tack a RETURNING clause onto the write, and get the entire row back. This shouldn’t be necessary for most cases anyway, because by definition, you have (almost) all of the row data already. The only times you wouldn’t is DB-generated values like an auto-incrementing ID, UUID from the DB, or a timestamp from the DB. MySQL can actually handle the first case; the cursor holds LAST_INSERT_ID. So if you had a monolith, it’s trivial to pass what you wrote to the next function that needs it – boom, no concern about stale data, and one fewer DB round trip. Even with separate services, this is still possible, though somewhat slower, but I’ve not seen anyone do it.
- throwaway984393 1y ago[dead]
- sgarland 1y agoIt is forever enraging to me that ORMs turn SELECT * into each individual column, mostly because people then post the whole thing and it’s obnoxiously large. Similarly maddening, the appalling lack of normalization that is simply taken for granted. “It’s faster, bro.” No, no, it is not. Especially not at the hundreds of millions or billions of rows scale. If you store something low-cardinality like a status column, with an average length of perhaps 7 characters, that’s 8 bytes (1 byte overhead assumed, but it could be 2). Multiply that by 2 billion rows, and you’re wasting 16 GB. Disk is cheap, but a. Memory isn’t b. Don’t be lazy. There’s a right way to use an RDBMS, and a wrong way. If you want a KV store, use a damn KV store. Finally, I’d be remiss if I failed to point out that Prisma is an unbelievably immature organization who launched without the ability to do JOINS [0]. They are forever dead to me for that. This isn’t “move fast and break things,” it’s “move fast despite having zero clue what we’re doing but convince JS devs that we do.” [0]: https://github.com/prisma/prisma/discussions/19748 https://github.com/prisma/prisma/discussions/19748
- HappyJoy 1y agoSpelling out columns can help the query optimizer too
- amazingamazing 1y agoI don't disagree with your point, but over normalization and joining everywhere also isn't necessarily the answer, even with an index. there's no easy answer to this, really depends on the performance characteristics the critical user journeys need. with a little pain, if I had to pick an extreme, I'd pick extreme normalization with materialized views that are queried (e.g. no joins), rather than joining all of the time.
- compton93 1y agoI'm curious about Motion's experience with "Unused Indices". They suggest Cockroach's dashboard listed used indexes in the "Unused Indices" list. I think the indexes they suspect were used are unused but Motion didn't realize CockroachDB was doing zigzag joins on other indexes to accomplish the same thing, leaving the indexes that would be obviously used as genuinely not used. It's a great feature but CRDB's optimizer would prefer a zig zag join over a covering index, getting around this required indexes be written in a way to persuade the optimizer to not plan for a zig zag join.
- esafak 1y agoI read it as: Why You Shouldn't Use Prisma and How Cockroach Hung Us Out To Dry I already knew about prisma from the infamous https://github.com/prisma/prisma/discussions/19748 https://github.com/prisma/prisma/discussions/19748
- etblg 1y ago> It's true that Prisma currently doesn't do JOINs for relational queries. Instead, it sends individual queries and joins the data on the application level. ..........I'm sorry, what? That seems........absurd. edit: Might as well throw in: I can't stand ORMs, I don't get why people use it, please just write the SQL.
- compton93 1y agoIt 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.
- Tadpole9181 1y agoIt's pretty obvious when somebody has only heard of Prisma, but never used it. - Using `JOIN`s (with correlated subqueries and JSON) has been around for a while now via a `relationLoadStrategy` setting. - Prisma has a Rust service that does query execution & result aggregation, but this is automatically managed behind the scenes. All you do is run `npx prisma generate` and then run your application. - They are in the process of removing the Rust layer. The JOIN setting and the removing of the middleware service are going to be defaults soon, they're just in preview.
- compton93 1y agoThey've been saying that for 3 years. We actually had a discount for being an early adopter. But hey its obvious Ive never used it and only heard of it.
- frollogaston 1y agoDid I miss something, or does the article not mention anything about sharding in Postgres? Was that just not needed? Also, query planner maturity is a big deal. It's hard to get Spanner to use the indexes you want.
- monkeyelite 1y agoThere are probably fewer than 100 websites that couldn’t be a single Postgres instance on nice server hardware, with good caching.
- ScalaHanSolo 1y agoYeah, 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.
- kevml 1y agoNot everything on the internet is a “website” and then there are several website hosting platforms that aggregate the individual concerns.
- monkeyelite 1y agoAll true points. I guess I just want to hear more about why they think sharding is important to them.
- maz1b 1y agoWhat makes you say that? AFAIK, the largest single dedicated servers you can buy on the market go up to around hundreds of cores and terabytes of ram, and NVME up to a PB~ish if you stack NVME/SSD/HDD as well. this is when i last checked.
- monkeyelite 1y agoYes. What do you have in mind?
- moonikakiss 1y agogreat 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 this workload.
- compton93 1y agoWhat are your thoughts on Fujitsu's VCI? I typically work for ERP's but im always advocating to offload the right queries to columnar DB's (not for DB performance but for end user experience).
- I_am_tiberius 1y agoThat 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_am_tiberius 1y agoA follow up question: You can't join columnar tables with OLTP tables, right?
- moonikakiss 1y agoyes you can. Even if the columnar tables are in the read replica. you'll be able to do joins with your OLTP tables
- I_am_tiberius 1y agoThat's great, thanks.
- 1y ago
- Inviz 1y agoWHERE CONDITION AND 1=1 results in scanning whole table? I dont think so...
- from-nibly 1y agoFeels 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.
- 999900000999 1y agoDepends. 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 fast, Firebase and its NoSQL cousins might be easier .
- pojzon 1y agoI 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.
- freilanzer 1y agoEven for timeseries there is https://github.com/timescale/timescaledb https://github.com/timescale/timescaledb. Haven't used it, just knew it existed.
- abraxas 1y agoIt'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.
- etler 1y agoI'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.
- betaby 1y agoHere you go https://www.uber.com/en-CA/blog/postgres-to-mysql-migration/ https://www.uber.com/en-CA/blog/postgres-to-mysql-migration/
- psionides 1y agoYeah so there's basically just that one ;)
- hu3 1y agohttps://engineeringblog.yelp.com/2024/10/migrating-from-postgres-to-mysql.html https://engineeringblog.yelp.com/2024/10/migrating-from-post...
- wredcoll 1y agoYou're not wrong, but this is the tldr: > The DB used is PostgreSQL which is not used anywhere else at Yelp, which meant that only a small rotation of long-tenured employees knew Postgres well enough to do outage response. This caused issues in maintenance, visibility, and outage response times. The teams working on the Restaurants products are not infra teams, and the Yelp-wide infra teams (understandably) focus on Yelp-standard infrastructure. As a result, when we did see issues with Postgres it was often a scramble to find people with relevant expertise. > So, we switched out this DB in-place with a Yelp-standard MySQL DB.
- speed_spread 1y agoIt's a very Uber thing to do to enter a one way from the wrong end.
- 1y ago
- coolcase 1y agoWhy not optimise the bad queries first? Aside. Job section says not 9-5. What does that mean? Long hours? Or not 9-5 attitude?
- ScalaHanSolo 1y agoAuthor here. Optimizing bad queries was absolutely part of the issues with the performance. The issue with cockroach was that the visibility into those bad queries was not great. It wasn't until we had the superior tooling from the Postgres ecosystem that we were able to track them down more efficiently.
- compton93 1y agoWhen you get a chance can you take a look my reply here: https://news.ycombinator.com/item?id=43990502 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.
- luhn 1y ago> 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 and a 100 million row table is well within the abilities of that—I have a 150 million row table happily plugging along on a 2vCPU+16GB instance. Apples and oranges, perhaps, but people shouldn't underestimate what a single modern server can do.
- casper14 1y agoNice! What optimizations have you put in llace yo support 150 mil? Just some indexing or other fancy stuff?
- luhn 1y agoYou don't need to optimize anything beyond appropriate indices, Postgres can handle tables of that size out of the box without breaking a sweat.
- gopalv 1y ago> Postgres can handle tables of that size out of the box 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.
- mmiao 1y agoa 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
- jacobsenscott 1y agoYou can partition that over 20 or 30 or more tables on one PG instance and have good performance - assuming a good partitioning key exists. If you need to query all 10B rows you'll have a bad day though.
- mplanchard 1y agoDepends 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.
- sreekanth850 1y agoIt'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.
- Spivak 1y ago100M isn't much even for not super well tuned postgres.
- sreekanth850 1y agoYes, 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.
- simianwords 1y agoI don't buy this! Startups do need high availability. If you start having replicas you are already in distributed territory!
- GaryNumanVevo 1y agoIt's much more simple to have a single master multi replica setup than a multi master one
- sreekanth850 1y ago>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.
- vivzkestrel 1y agodid you try using the native pg library or postgres or pg-promise library and scrap the ORM completely to see what effect it has? If you are looking explicitly for migrations, you can simply use node-pg-migrate https://www.npmjs.com/package/node-pg-migrate https://www.npmjs.com/package/node-pg-migrate and scrap the rest of all the FLUFF that ORMs come with. ORMs in general are horribly bloated and their performance for anything more than select from table where name = $1 is very questionable
- badmonster 1y agoindeed interesting
- liampulles 1y agoI wonder increasingly with tools like ChatGPT whether ORMs make sense anymore? The argument I've always heard for ORMs is that they make it quick and easy to make the initial repository method, and that they make migrating to a new DB easier (hypothetically). It seems to me LLMs now help fill both those roles, but with the benefit that you can now tune the SQL as needed. I also always prefer actually knowing exactly what queries are going to my DB, but YMMV.
- eftpotrm 1y agoI can't help thinking more startups need greybeards around. (Of which, realistically, I'm now one.) Largest table 100 million rows and they were paying 6 figures for database services annually? I have one now that sits happily enough on an 8yo laptop. I've worked on systems that had similar scale tables chugging along on very average for 20 years ago MSSQL 2000 boxes. There just isn't a need for cloud scale systems and cloud scale bills for that data volume. The problems they're describing should never have got that far without an experienced hand pointing out they didn't make sense, and if they'd hired that greybeard they'd have spotted it long before.
- abraxas 1y ago> and they were paying 6 figures for database services annually? Might have been monthly. 100,000,000 rows is what I handled on a single Sun server in 2001 with Sybase, no problemo.
- CoolCold 1y ago> First, the support portal is a totally different website that doesn’t share auth with the main portal. Second, you have to re-input a lot of data they already know about you (cluster ID, etc). And by the time they respond it’s typically been a week. I was about to ask what was main constraint for CockroachDB like iostats and atop info for CPU/disk drives, but realized that is probably something offloaded to some SaaS - so still curious
- optician_owl 1y agoIt's an awkward article. To answer why a query is slow you need a bit more details than just the query. Also, I reread about timeouts and didn't get it, what was the database, whether it was a database issue, how it was related to migration. The only information I could extract was that the company made bad architectural decisions, believes in ORM (looking at the queries, there are many doubts that the data layouts in DB are adequate) and cannot clearly explain situations. But this is only interesting to their candidates or investors. It may sound rude, so I apologise.
- robertlagrant 1y agoFor all the Prisma-haters: I salute you. But I want to reply to numerous comments with the following: ORMs come in two main types, that I'm aware of: Active Record (named after the original Ruby one, I think) and Data Mapper (think Hibernate; SQLAlchemy). Active Record ORMs are slightly more ergonomic at the cost of doing loads of work in application memory. Data Mapper looks slightly more like SQL in your code but are much more direct wrappers over things you can do in SQL. Data Mapper also lets you keep various niceties such as generating migration code, that stem from having your table definition as objects. Use Data Mapper ORMs if you want to use an ORM.
- felipemesquita 1y agoRails’ Active Record was named after the pattern as described by Martin Fowler: https://www.martinfowler.com/eaaCatalog/activeRecord.html https://www.martinfowler.com/eaaCatalog/activeRecord.html
- robertlagrant 1y agoAh - the reverse! Thanks.
- arunix 1y agoAlso, the Query Object style, e.g. JOOQ and SQLAlchemy Core https://martinfowler.com/eaaCatalog/queryObject.html https://martinfowler.com/eaaCatalog/queryObject.html
- gilbetron 1y agoThe answer to the question, "what database should I use?" is "postgres". If you are in a situation where postgres actually won't work, then you already would know exactly why postgres won't work. In other words: [Postgres -> exotic solution] is the path everyone should take (and 99% will just stay in postgres), and not [exotic solution -> postgres].
- abraxas 1y agoYes, the nosql fad that swept the industry was nearly as insufferable as the SPA craze that followed alongside. Now everyone's back to tried and true. Most data once more sits in RDBMS and most html gets render on the server. Us grizzled genX devs saw this coming a decade ago.
- gilbetron 1y agoAs a grizzled genX dev myself, we are in a different situation now - "nosql" (hate the term) has tremendous use cases, it's just that most people aren't creating something that requires it. It was a natural exploration of the tools, something that should be encouraged. "I knew it all along" isn't an attitude I find helpful or effective. My "grizzled genX dev" attitude is that nearly all people think they know what is going to happen or what is the best route, and they are almost always entirely wrong. We only find out by trying a bunch of things. In other words, there are many companies currently worth $Billion+ that wouldn't have succeeded had they followed your advice. Today, with incredibly powerful infra of all types available, starting with Postgres is almost always the right step unless you know, know, better. That wasn't the case 10+ years ago.
- crowcroft 1y agoWhy 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.
- ahartmetz 1y agoFunny that it was the other way around 20 years ago. Everybody was using MySQL, but there were many blog posts and such about the looseness of MySQL with the SQL standard and other issues. And that people should use Postgres unless they need the replication features of MySQL. AFAIU, replication is still the main (good) reason to use MySQL, though there are some semi-proprietary(?) solutions for Postgres.
- crowcroft 1y agoYea, Postgres really came up when Node and Mongo did. During the PHP/RoR era MySQL was a very clear winner. I still think MySQL is a better choice for most web apps due to performance, but more general use cases I can understand the debate.
- venantius 1y agoI 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.
- evanelias 1y agoOn 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.
- JackSlateur 1y agothere were concerns about the eventuality of a multi-region setup (mandated by GDPR) Can anyone share more details about this ? The GDPR is mandating a multi-region setup ? This sounds very wild