18 ms·
“Fashion Is Hard. PostgreSQL Is Easy” [video]
- tempodox 11y agoThat title is really great, and also rings true. And of all open-source SQL DBs, PostgreSQL is superbly executed and a breeze to work with. I can only recommend it.
- lmm 11y agoAs someone who's used to MySQL, I keep trying to use PostgreSQL (better standards compliance, better behaviour in the face of bad data) - and I keep going back. The psql interface is awkward (too many magic backslash commands to memorize instead of SHOW CREATE TABLE) and always feels slightly laggy somehow.
- allendoerfer 11y agoI have the same problem. Because of this I use PostgreSQL only if I need it feature-wise and use MySQL as the default. This of course prevents me from getting used to PostgreSQL.
- keithy 11y agoThe backslash commands are way better than having to type out SHOW TABLES; or SHOW DATABASES;, or CONNECT DATABASE X. All I have to do is \dt or \c x. It's so much better. Can't remember them? Just do \?.
- realusername 11y agoThat's also the problem I have, \dt,\c just don't make sense at all and I never remember them whereas 'SHOW TABLES' does not even need explanations. It's also the same thing with the unix users, I never fully understood how postgres handles user permissions. I would say that Postgres itself is really really good but the tooling around is just not there yet.
- noir_lord 11y agoI just remembered it as \d as display and \dt as display tables. I often invent mnemonics that only mean something to me until muscle memory kicks in.
- bshimmin 11y agoHere's a nice one-page cheat sheet, of which about 75% you will probably never need to use: http://www.postgresonline.com/downloads/special_feature/postgresql83_psql_cheatsheet.pdf http://www.postgresonline.com/downloads/special_feature/post...
- noir_lord 11y agoThats excellent, thanks :).
- collyw 11y agoThats like trying to say emacs is easier than eclipse. Sure it may be more efficient once you are used to it, but its not more intuitive.
- baq 11y agoeclipse and intuitive shouldn't ever be used in the same sentence. emacs ditto, but calling one more intuitive from another is missing the point by about 1 AU.
- collyw 11y agoEclipse isn't great in that respect, but choosing something from a menu is definitely more intuitive than remembering the cryptic commands in emacs.
- semi-extrinsic 11y ago"The only intuitive interface is the nipple, after that it's all learned." - Bruce Ediger
- neokya 11y ago\? is killer. Thanks a ton, I didn't know that yet. I also find MySQL cli very easy to work. But that's going to change now, it seems.
- ibotty 11y agoYou are the first person I read saying they like the mysql cli. Ctrl-c kills the whole shell. great, but not what I expect. Want to fix a small typo in your big query? In psql you can just edit your last command in your $EDITOR. Useful (but not perfect) autocomplete is also not in mysql.
- mdemare 11y ago> In psql you can just edit your last command in your $EDITOR. Cool! How?
- xd 11y agoAutocomplete is in MySQL you might just need to enable it with -A or in the config comment out no-auto-rehash.
- fweespeech 11y ago> Ctrl-c kills the whole shell. great, but not what I expect. That is actually exactly what you should expect. SIGINT cancels most things you run in the CLI. Interrupt a ping because you forgot a count parameter? Ctrl+C Honestly, the reason I don't use Postgres has nothing to do with the CLI and everything to do with the ecosystem for Multi-Master being inferior to Galera. But yeah, I'm happy with mysql-cli as well and don't really see the Postgres equivalent as a noteworthy improvement.
- jsight 11y ago
- brightball 11y agoAdmittedly, I haven't used the PG or MySQL cli in years. Coughed up money for a Navicat Premium license about 5 years ago and never looked back.
- neverminder 11y agoYou should also check out https://www.jetbrains.com/dbe/ https://www.jetbrains.com/dbe/
- jsmeaton 11y agoI'm fairly sure I signed up for the early access program months ago and still haven't heard back. It's possible that I meant to and didn't though.
- payamb 11y agohttp://go.jetbrains.com/HQ0110R0QkKgjD01O6V003M http://go.jetbrains.com/HQ0110R0QkKgjD01O6V003M
- cwyers 11y ago> It is important to distinguish EAP from traditional pre-release software. Please note that the quality of EAP versions may at times be way below even usual beta standards. That... scares me, more than a little. I'm okay with it crashing, or losing queries I wrote. I really want failure cases to be restricted to NOT affecting the database, though. I'll probably stay away or try it out on my personal machine on a toy database for now.
- jkestner 11y agoSame here. I muddled along with pgAdmin for a while, but now use Valentina Studio. Excellent free app (has an in-app purchase for some advanced features but doesn't feel crippled at all).
- dolel13 11y ago
- noir_lord 11y agoIf you haven't already checkout pgadmin-3, I mostly live in the cli but if I need to do something complex or write a large query I often jump into pgadmin as it's display and proper editor are excellent!. Prefer it to MySQL Workbench even which I liked.
- knodi123 11y agoI've recently made the switch to postgres, and then I found pgadmin3, and I'm really feeling some pain. For instance, the "explain query" visualization is great, and very helpful, but there's no way I can find to zoom out, at all. And that window doesn't support mouse scrolling. And there's no way to export the visualization somewhere else. So when I'm trying to figure out why a very, very large query is running slowly, it's agony to use pgadmin3. Do you know any good postgres query analysis tools?
- pmontra 11y agoShow create table was so awkward to me after years of \d table. I remember I had to google even desc table and show databases :-) Anyway, you might want to check http://pgcli.com/ http://pgcli.com/ for a free alternative cli with completion. I started using it over the standard psql and I recommend it to everybody.
- marcosdumay 11y agoCount another vote for the pgadmin. I hate the backslash commands of postgres, about as much as I hate the show commands of mysql (what's not nearly as much as I hate the oracle introspection tools). Why couldn't SQL standardize database introspection?
- baq 11y agoit did. https://en.wikipedia.org/wiki/Information_schema https://en.wikipedia.org/wiki/Information_schema
- marcosdumay 11y agoI didn't know it was a standard, nor that it was widely supported. Thanks.
- WorldWideWayne 11y agoThis whole thread makes me happy to be a SQL Server user, where the UI tools are standard and robust. 90% of the time, I don't need to remember any special commands for introspecting and since it's on Windows - I can use the keyboard to navigate the entire UI with ease. When I write SQL, I get glorious Intellisense (that's autocomplete to you) for every single object (edit: and command) in the db.
- jqm 11y agoI don't know why you are getting downvoted. I'm not personally crazy about SQL Server, but a lot of big companies are. In this situation some of us find ourselves in from time to time, SQL Management Studio is pretty handy.
- kaolinite 11y agoI have no idea how they compare but the open-source databases has proprietary database tools too, such as Navicat, which have auto-complete, a good UI (Navicat is hardly beautiful but it works well), etc. There are a few other options too.
- kriro 11y agoComing up with your own ideas is hard. Cloning other businesses is easy.
- perlgeek 11y agoComing up with ideas is easy. Executing them well is hard.
- kriro 11y agoZalando is hardly executed well. The work conditions in their logistics centers are (infamously) atrocious. The strategy was distinctly described by one of the Samwer brothers in email conversation as "Blitzkrieg" and "the most aggressive plan in history". Work climate (not only for manual labor) reflects this attitude pretty well.
- MrBuddyCasino 11y agoYes, their website still sucks in the same ways it did 3 years ago. It boggles the mind. They have a huge selection, expensive advertising campaigns and lenient shipping & return policies, fueled by huge sums of money, so they have a huge market share. Its nice, but execution is merely decent imho.
- crdb 11y agoTry a rewrite at a company with more than 100 employees... cf http://www.joelonsoftware.com/articles/fog0000000069.html http://www.joelonsoftware.com/articles/fog0000000069.html
- MrBuddyCasino 11y agoNo need to rewrite anything - all they need are a few UI improvements. Its not like their backend is MongoDB or something. ^_^
- crdb 11y ago
- kaufland 11y agoHmm, looking a bit at their site[1]: Addidas jogging pants -- 59,95 € Volcom T-shirt print -- 17,45 € Under Armour T-shirt -- 39.95 € Like really now -- who needs this stuff? Especially when you can just go to Aldi or Tchibo (or any of a number of other discount retailers readily visible at nearly every shopping mall in Germany) and buy essentially same stuff (minus the logos, of course) for 1/10th of the price. So perhaps a better title might be: “Fashion generates revenue, and handsome pay-outs. But is basically pointless. And a soul-sucking waste of time.” [1] https://www.zalando.de/herren-home/ https://www.zalando.de/herren-home/
- paublyrne 11y agoPointlessness is rather subjective.
- webjunkie 11y agoYou always get what you pay for. I'd rather not buy too cheap and wear toxic stuff: http://www.welt.de/wirtschaft/article133578674/Greenpeace-warnt-vor-billiger-Kinderkleidung.html http://www.welt.de/wirtschaft/article133578674/Greenpeace-wa...
- kaufland 11y agoValid point, but apparently the Greenpeace study only targeted Germany's largest (brick-and-mortar) discounted retailers; there's no particular reason to believe that the products offered by premium retailers would score any better. Or that the 10x factor in price overhead is in any substantive way directly invested in safer product standards. No matter how you slice it, to a large extent all we're really getting when we shop at specifically fashion-outlets (as their marketing gurus know all to well) is... the brand.
- deleted 11y ago[deleted]
- pavlov 11y agoA t-shirt for 1.75 € is so cheap that it's practically guaranteed to have been manufactured in dangerous conditions in a place like Bangladesh or Ethiopia. Big brands like Adidas or Under Armour don't have a clean track record either, but at least they are somewhat accountable to consumers because their #1 asset is their reputation. To fix global trade, it's better to do a bit of research into what kind of company you want to support, rather than blindly buying the cheapest imported thing "because they all suck anyway".
- deleted 11y ago[deleted]
- StavrosK 11y agoPosgreSQL is pretty much my default. Any other database has to have compelling arguments for how it fits my use case if I'm going to use it over Postgres, and even then, it usually gets used for a subset of the data, while Postgres gets everything else. I think it's better to default to the tried-and-true piece of software than the new and shiny, rather than the other way around and have to argue why Postgres is a good fit for your data.
- brightball 11y agoThat's probably the best way of looking at it. PostgreSQL is generally going to be the best solution for 90% of your data, short of having a subset that's write heavy to the point of streaming.
- escherize 11y agoWhat is a good space to look at when you get write heavy data to the point of streaming?
- fennecfoxen 11y agoDepending on your exact needs, Cassandra's not a bad idea for this space. O(n) scalablity for write capacity (+), and even on an individual node it's pretty write-friendly: its sstable data structures stream to disk well, keeping spinning disks happy while avoiding write amplification issues on SSDs. It does help if the data is nicely shardable, of course. That's the one I'm familiar with, anyway. (+) Write capacity is O(n) as you add machines but an individual write's time is pretty constant and cluster-wide maintenance operations do start taking longer as you add machines and they gossip to each other. It's not magic, obviously :)
- brightball 11y agoDepends on your application. In most cases your "write heavy" is going to be isolated to 1 or 2 tables worth of data (or equivalent) and for those cases some type of NoSQL solution can be a really good option. Especially since you can use a PG foreign data wrapper to allow PG to run queries against that information too. If it's system wide and you need PostgreSQL itself able to handle it, using PG's async features is one potential option but setting up and managing a Postgres-XC cluster would be the next best. XC allows scale-out for writes. If you're at a company with a budget for that type of thing I think I remember reading that EnterpriseDB (the PG company) is offering first class support for PG XC. In most cases though, I find that the write heavy parts of a system are so isolated that diverting them to a simple NoSQL solution tends to be easiest (Mongo, Couchbase, DynamoDB from AWS, etc).
- vassy 11y agoTwo years ago I switched from PHP and MySQL to Ruby and PostgreSQL. The only thing I'm missing is Sequel Pro. PG Admin is ok, does everything I need, but that interface is ugly and unintuitive. [1] http://www.sequelpro.com/ http://www.sequelpro.com/
- lxfontes 11y agoHave been using postico https://eggerapps.at/postico/ https://eggerapps.at/postico/ for day to day queries and pgadmin for more hardcore operations
- vassy 11y agoIt's still beta it seems. I used PG Commander, but it was too basic.
- juliangregorian 11y agoYou might try Valentina Studio.
- jkahn 11y agoWhen I tried that it crashed all the time. Huge pain to work with.
- krrrh 11y agoThis has some promise. http://www.psequel.com/ http://www.psequel.com/
- angrybits 11y agoAs of late, I've been doing php+pgsql and it's like easy+easy. My latest app was an absolute pleasure to code, and I haven't said that in a a long time. I agree that pgAdmin leaves a lot to be desired. It needs something on par with SSMS.
- rattray 11y agoRelevant Github Issue: https://github.com/sequelpro/sequelpro/issues/362 https://github.com/sequelpro/sequelpro/issues/362
- fideloper 11y agoPostgreSQL is great, but I've gone back to MySQL because of the ease of which I can setup replication (which I like to use when possible in addition to automated/periodic backups).
- rockdoe 11y agoI'd love a honest, MODERN comparison of both systems. Sometimes I feel like the cargoculting with PostgreSQL is as bad now as it was with MySQL 10 years ago and MySQL is being badmouthed for mistakes from the MyISAM age. MySQL has some features that I'm really missing in PostgreSQL, like more flexible compression, and batched index writeout.
- jordanthoms 11y agoPostgres replication is probably not as easy to setup, but can be extremely powerful especially when use tools like WAL-E as well. We have a setup where our WAL log is rotated every minute and backed up to S3. Replica databases can either load from S3 or connect to the master for streaming - so they will catch up using S3 and then connect, reducing load on the master.
- angrybits 11y agoRDS makes it trivial to have high availability if you can't afford a DBA worth their salt. You don't need 10x replication to beat the uptime of your shitty web app.
- takeda 11y agoI thought so too, until I decided to use RDS for storing zabbix. It looks great on paper, but unless you're doing development I would discourage its use. That said your original point about the replication is true, but there are shortcomings that come together with it. Some things that you will learn if you use RDS: - if you decide to increase volume size, change type to SSD or use provisioned IOPS, you might have database down for an hour or more. Regardless whether you use single instance or HA. - want to upgrade 9.3.x to 9.4.x? Tough luck you have stop dump the data and provision a new instance. You can't use postgres' inplace upgrade method - you have limited control regarding tuning, many settings require rebooting the whole thing, when normally you would just restart the process (with HA there's still several seconds of interruption) - you can only use extensions that they provide, there's almost no extension to monitor performance - you can't login to use shell to monitor the process (obviously, but it's a still shortcoming) - can't replicate data across regions or outside of RDS (it could resolve some of the issues above) - it can fail, we had two times a failure caused by AWS, it happened during backup period. Normally AWS does backup on the secondary database, but we learned that in those instances it got confused and was attempting to do it on primary, rebooted it and for about 15 min the database was unavailable.
- comrade1 11y agoAre they using Postgres for content, or for data? (at a place where I can't really watch a movie) There are some great content management tools out there, my favorite being apache jackrabbit, and on top of that, apache sling for displaying/accessing the content. It requires a different way of thinking than the standard mvc process - it is a more content focused display process. I don't know... if you're using Postgres to store non-relational data you may want to reassess your strategy. You may find you're doing the right thing still, but I think most people would be better served with a system like jackrabbit/sling, which is is used by publishers, fashion houses, etc (via Adobe's product that is built on sling) to store and display their content. (that said, you can use postgres on the back-end with sling)
- sancha_ 11y agoApache Jackrabbit is not a content management tool. It is an implementation of JCR, a content repository. But yes I agree that it might suit some usecases better than a relational db. That being said, you still need something like postgres for data storage, even if you use JCR.
- feedjoelpie 11y agoPostgres provides really nice features for non-relational data these days, though. JSON columns are nice. Hstore is fantastic. And you can even do pubsub. All without introducing another database system into your stack. Is it perfect for all software? I'm sure not. But 99% of everything I ever need can be had with Postgres + Elasticsearch.
- cwyers 11y ago> I don't know... if you're using Postgres to store non-relational data you may want to reassess your strategy. What kind of data lacks any relations whatsoever? Yes, there are cases where fitting data to the relational model is more awkward than others. But I would be really, really cautious about thinking that I could design on my own a data model that's better than the relational model. Relational databases are incredibly battle-tested across every type of data storage problem out there.
- 11y ago
- mhd 11y agoI had a job interview at Zalando a while ago, and apart from the huge bunch of bananas at the entrance to the developer's den, the one thing I remember most about was the fact that apparently they're using stored procedures for basically any database transaction. Which is probably a more unorthodox use of databases these days, at least for Postgres (I've heard it was more common for SQL Server and I once had the questionable joy of debugging a petri net solver in Oracle).
- kaufland 11y agoIt's quite common for startups to have 1 or 2 sexy (and well-enough engineered) projects involving your favorite technology X, to run up the flagpole at conferences or in job ads, etc. But like as not, 80% of what they do (and of what you'll be doing as a developer there) is the same half-baked, post-"blitzkrieg" mop-up work you'll be doing pretty much anywhere else.
- mhd 11y agoThey were pretty straight about that: Java, ExtJS, stored procedures. Not exactly candidates for the sexiest development tools alive. They were pretty honest about that, no "this will look awesome on your resume" tomfoolery. It was a pretty great interview, and I almost took them up on their offer. So don't get me wrong, I neither want to praise their stack nor put it down, I just thought that their ubiquitous use of stored procedures was an interesting fact (and Postgres with its pluggable languages supports this pretty well, these days you can even put your js/v8 code in it, if you really want to JavaScript all the thigns). They've got some code about it on github: https://github.com/zalando/java-sproc-wrapper https://github.com/zalando/java-sproc-wrapper
- kaufland 11y agoAlles klar. Thanks for clarifying.
- ma2rten 11y agoEven during the talk it was even mentioned. "Stored procedures are the core our success" He also mentioned that Zelando does 100 data model (schema?) changes per week, and that senior dev make code changes without code review.
- emilburzo 11y agoI love PostgreSQL. But for a personal project I went with MongoDB, because my data set was a perfect match for mongo's design. Now I love mongo too, I'm amazed with how easy it has been to maintain 100% uptime on comodity hardware (one server is literally in a room in my apartment) through all the random server downtimes, upgrades, migrations, etc. And now I have more ideas for some personal projects, and they would go very well with postgres, but I'm so missing replica sets from mongo. If postgres would have something similar to replica sets in mongodb, that would be amazing.
- simi_ 11y agoIf mongo impresses you, you should check out rethinkdb: https://github.com/rethinkdb/rethinkdb https://github.com/rethinkdb/rethinkdb
- emilburzo 11y agono official java driver :-/
- sandGorgon 11y agodo take a look if you can leverage jsonb datastructure of postgres - you get 80% of the power of mongo and all the advantages of postgres. https://www.compose.io/articles/is-postgresql-your-next-json-database/ https://www.compose.io/articles/is-postgresql-your-next-json...
- ma2rten 11y agoActually the article you posted makes the case that postgres is not a good json database, because it can not modify json documents in place.
- neilc 11y agoPG 9.5 will improve this situation somewhat, e.g., builtin functions like jsonb_set(), and overloading the '-' operator for jsonb values: http://www.postgresql.org/docs/devel/static/functions-json.html http://www.postgresql.org/docs/devel/static/functions-json.h... That said, if you're doing a lot of mutation of large JSON documents stored in a single Postgres row value, the storage/concurrency control behavior still won't be ideal.
- UserRights 11y agoI found this video to be much more interesting https://youtu.be/zsF1vfHBMBI https://youtu.be/zsF1vfHBMBI BTW I would like to send out a BIG THANK YOU to the guys that did the videos - this is such an important and great service for everybody who could not attend the conference, so: THANK YOU VERY MUCH!
- valgog 11y agoThere are definitely a lot of much more interesting videos from PGConf US 2015, especially the one from Robert. My talk was a 'keynote' and not really a conference talk :)
- geedy 11y agoI recently used Postgres for a serious project for the first time and I am really impressed. Certainly much more impressed with it than MySQL or SQL Server. plpgsql is far better than what MySQL offers out of the box, plus you have access to nicer languages out of the box with plv8, plpythonu, plr, etc. Language extensions really are a killer feature and make moving application logic into the database a cinch.
- ksec 11y agoIs there anything on Postgre Roadmap to allow, simple, easy, replication out of the box?