8 ms·
Serious question: when you need a relational database, are there any technical reasons why you would use anything other than Postgres these days?
by enraged_camel 8y ago
Serious question: when you need a relational database, are there any technical reasons why you would use anything other than Postgres these days?
- jeltz 8y agoI can only think of three cases: 1) You need an embedded relational database with a small foot print. Here PostgreSQL cannot compete with SQLite. 2) You have an application which does not support PostgreSQL. 3) You have petabytes worth of data. Here you want to look into something like Greenplum, a PostgreSQL fork.
- truth_seeker 8y agoPG works okay with Peta Bytes of data. Various types of indexes, Table partitioning and parallel query computation can help. Citus and other community extensions can help if you want to go distributed and fault tolerant
- jacques_chester 8y agoWorth noting that the Greenplum team are working to converge back to mainline.
- majewsky 8y agoBecause the app you want to run does not support it. OpenStack used to support both MySQL and Postgres, but they removed the Postgres support, which is a decision that completely baffles me.
- drej 8y agoHorizontal scaling and/or alternative storage systems (I know, pg can theoretically do both, but...). That and perhaps global consistency aka "sort of but not really defeating the CAP theorem using atomic clocks and all that" in Spanner.
- pgaddict 8y agoYeah, we're not particularly good at those out of the box. 1) Horizontal scaling: In some cases it's doable using streaming replication, but it depends if you need to scale reads or writes. Or if you need distributed queries. There are quite a few forks and/or projects built on PostgreSQL that address different use cases (CitusDB, Greenplum, Postgres-XL, BDR, ...). And the features slowly trickle back. One reason why it's like this is extensibility/flexibility - the project is unlike to hard-code one particular approach to horizontal scaling, because that would not work for the other use cases. So we need something that does not have that effect, which takes longer. It's a bit annoying, of course. 2) Storage systems: We don't really have a way to do that now - there are extensions using FDW to do that, but I'd say that's really a misuse of the FDW interface, and it has plenty of annoying limitations (backups, MVCC, ...). But it's something we're currently working on so there's hope for PG12+: https://commitfest.postgresql.org/20/1283/ https://commitfest.postgresql.org/20/1283/
- truth_seeker 8y agoCitus extension can help you achieve horizontal scaling. Here are some of the success stories. https://www.citusdata.com/customers/ https://www.citusdata.com/customers/
- collyw 8y agoYou have a sever with MySQL already. We had Rackspace servers in the place I worked a couple of years ago and they didn't offer Postgress as far as I remember. (You could install and manage it on a virtual machine but then you had to do everything yourself, while they had managed MySQL servers available). Also setting up master / slave replication seems easier with MySQL as I understand - I never tried with Postgress but did with MySQL.
- mrcarruthers 8y agoOne thing that Postgres does not do well is row churn. If you have a table that have rows that are constantly added/deleted/updated, Postgres tends to bloat pretty quickly because of the way they handle updates. Say what you will about using a relational db for that kind of data, but if you need to, Postgres isn't a great choice.
- panarky 8y agoWhat databases do you recommend to handle frequent updates? How does their design differ? What are the tradeoffs to consider?
- zzzcpan 8y agoLSM-tree, fractal tree, B-epsilon tree or similar ideas based stuff for on disk storage is what solves the problem. Pretty much no tradeoffs compared to classic approaches, these things are just better.
- dhd415 8y agoLSM trees are good for frequent updates on recent data. Frequent updates of old data are much worse on LSM trees since they trigger merges on the oldest (and typically largest) levels of the tree. Some storage engines allow for in-place updates of records if the fields are fixed-length or if the updated variable-length value fits in the existing space. I've used some of those (e.g., MS SQL Server) to good effect in high-update scenarios.
- zzzcpan 8y agoDepends on your implementation of course, but updates shouldn't be triggering merges on the largest levels. They should be accumulated until there is enough of them to merge or there is some other reason to merge things, like scrabbing. Either way, it's still more efficient.
- jeltz 8y agoOnly in some cases of churn plus there are plenty of workarounds where you sacrifice some performance and/or usability to avoid the bloat so I would still recommend PostgreSQL since few databases consist only, or almost only, of the bad cases for bloat. I used to work in online gambling where we had plenty of row churn and not much bloat at all without having to use any of the workarounds.
- sroussey 8y agoYou might want clustered indexes.
- knocte 8y agoNo, there aren't. In other words, I hope MSSQL dies in a dumpster fire.
- paulie_a 8y agoI agree, it should first beaten with a baseball bat.
- timc3 8y agoMulti-region replication like Cassandra? Sort of a question because the last time I really looked around the release of PostgreSQL 9 there wasn't a solution.
- chucky_z 8y agoActually an interesting solution to this is using something like kafka-connect. Debezium specifically has libraries around pgsql -> pgsql replication using kafka + kafka-connect that work shockingly well. The lag is not much different than traditional replication but you can have a single primary that kafka-connect is pulling into kafka, and a gazillion replicas off that single stream.
- Tepix 8y agoSQLite fits the bill but of course fills a very different niche.
- benmmurphy 8y agosome people have problems with they way MVCC is implemented. like Mysql using rollback segments so it doesn't have issues with vacuuming [tho, there is tradeoffs with doing it that way as well]. the vacuuming issues are really scary and basically involve losing write access to the whole database. have a look at stuff like this: https://www.joyent.com/blog/manta-postmortem-7-27-2015 https://www.joyent.com/blog/manta-postmortem-7-27-2015 . i believe postgres have made a bunch of changes to reduce the risk of vacuums blowing things up. this MVCC implementation couple with the way indexing works also causes write amplification. the indices have pointers to the physical place where the row is stored (some other databases might instead record the primary key and do a lookup on the primary key to get the record). so if you update a row and it causes it to move to a different physical page (you need to keep the original row for MVCC so there needs to be space for the new row in the current page) then you need to update all the indices as well. PG has some optimisation around trying to write the update to the same physical page to reduce the number of writes.
- tfehring 8y agoOne reason is the lack of native support for columnar storage (though an open source third-party extension exists: https://github.com/citusdata/cstore_fdw https://github.com/citusdata/cstore_fdw) The need to manually update clustered indexes, and the fact that an exclusive lock is needed to do so, is also annoying.
- Thaxll 8y agoWhy would it be better than MySQL is you're already using MySQL for years and built knowledge with it and have no issues? Why would I change, why Postgres would be better?
- astrodust 8y agoThe JSON/JSONB columns and the JSON navigator options are amazing. MySQL isn't even close yet, they have a JSON column that's basically opaque. Other column types like the INET, CIDR and MACADDR are often invaluable. You can also index ARRAY columns, meaning if you need a "tags" feature it's ridiculously easy to implement, no need for additional tables. There's also PostGIS, which has no MySQL analog, and makes doing actual real-world GIS work possible.
- nobleach 8y agoHaving tried, and failed to use MySQL Spatial Extension (which some might argue is PostGIS' analog) and failing miserably at some pretty basic things, I agree totally. I'd take PostGIS over ESRI's SDE and Oracle Spatial as well.
- nathan_long 8y agoOne "quality of life" thing - you can wrap changes to the schema in a transaction, so that a given set of changes is atomic. (Elixir's Ecto and Ruby's ActiveRecord both have migrations that do this by default.) As far as I know, that doesn't work in MySQL. There's also neat stuff like exclusion constraints: http://nathanmlong.com/2016/01/protect-your-data-with-postgresql-constraints/#exclusion_constraints http://nathanmlong.com/2016/01/protect-your-data-with-postgr... And there's the ability to do all kinds of neat geospatial queries with PostGIS - eg "list all properties within X distance from the curvy coastline".
- barrkel 8y agoFeature for feature, PG blows MySQL out of the water. PG gives you way more tools to use in your queries, indexes etc. to solve app problems. The real knowledge gap would come into things like all the various tactics you need to learn to get MySQL to produce good plans for your queries. PG has a much smarter optimizer which is harder to control - if it does the wrong thing, it's harder to encourage it to do the right thing, and philosophically they don't support query hints. OTOH, worst case in PG is usually much better than worst case on MySQL.
- grouseway 8y agoI'm a fairly casual PG user but I have these issues: Lack of case and accent insensitive collations is inexcusable at this point. Is there anyone that enjoys sprinkling every bit of SQL with upper() comparisons? Non materialized CTEs. I like materialized CTEs sometimes and wish MSSQL had them as an option but I also need it to work the other way. Native point-in-time recovery out of the box. This looks like it might be possible but the process doesn't fill me with a lot of confidence. Better connection scaling. Ability to load custom TS dictionaries in user space (for hosted postgres on google/aws/etc..)
- Tostino 8y agoAgree on PITR being something that seems less approachable than it really is. Use a tool like pgbackrest, and everything just works really well.
- nathan_long 8y ago> Is there anyone that enjoys sprinkling every bit of SQL with upper() comparisons? Does using a citext column not solve this?
- twic 8y agoIf somebody else is paying for it, Oracle is still pretty good, and has some features that PostgreSQL doesn't.
- captain_perl 8y agoMySQL with Innodb is easier to manage in large replication clusters, doesn't require connection pooling, and doesn't have write amplification problems. MySQL out of the box is more secure and easier to configure than Postgresql because of pg's public and the legacy config files. It's also much easier to hire DBAs.
- GlennS 8y agoMS SQL Server still has a few things up its sleeve which can be valuable in specific cases: 1. Incrementally updated materialized views (in MS SQL Server, these are called 'indexed views'). 2. SQL Server Management Studio is better than anything I've seen elsewhere. 3. Reporting and analytics (SSRS/SSAS) built in. These are actually pretty good, but not the approach I would recommend. 4. Very solid clustering (I haven't used PostgreSQL's clustering, so this point might be out of date). The license fees are steep (not as steep as Oracle's...) though, so you've got to really want those features. I think report builders tools miss the sweet spot. Instead, build the specific reports that your business needs. Or, do a regular export to flat files (tab delimited causes fewest problems) and let people build what they need in Excel/R/whatever. Or both. In practice, I try SQLite first, and fall back to Postgres if I need concurrent writes.