“Fashion Is Hard. PostgreSQL Is Easy” [video]

https://tech.zalando.com/blog/watch-fashion-is-hard-postgresql-is-easy/

by nickdotmulder • 11 years ago

154 134 11 years ago

tech.zalando.com

tempodox• 11 years ago

That 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.

lmm2• 11 years ago

As 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• 11 years ago

I 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• 11 years ago

The 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 \?.

ibotty• 11 years ago

You 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.

brightball3• 11 years ago

Admittedly, 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.

noir_lord3• 11 years ago

If 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.

pmontra• 11 years ago

Show 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/ for a free alternative cli with completion. I started using it over the standard psql and I recommend it to everybody.

marcosdumay3• 11 years ago

Count 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?

kriro2• 11 years ago

Coming up with your own ideas is hard. Cloning other businesses is easy.

perlgeek• 11 years ago

Coming up with ideas is easy. Executing them well is hard.

kriro2• 11 years ago

Zalando 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.

toyg• 11 years ago

Commenting on HN is easy. Delivering a presentation that gets on HN frontpage is hard.

janfoeh• 11 years ago

While I agree in general, "sell shoes in a customer-friendly way" isn't exactly a revelatory business model.

ExpiredLink• 11 years ago

"startup ideas are worthless"

http://paulgraham.com/ideas.html

kaufland5• 11 years ago

Hmm, 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/

paublyrne• 11 years ago

Pointlessness is rather subjective.

webjunkie• 11 years ago

You always get what you pay for. I'd rather not buy too cheap and wear toxic stuff: http://www.welt.de/wirtschaft/article133578674/Greenpeace-wa...

kaufland5• 11 years ago

Valid 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.

pavlov2• 11 years ago

A 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".

kaufland5• 11 years ago

I was extremifying a bit my comparison; but the basic point is that the 10x price difference is in no way proportionate to any real infrastructure costs that would be necessary to guarantee safer (or non-exploitive) product standards. I can see the same no-name, barely-fitting (but otherwise generally quite adequate) apparel products from the major discounters costing 2x or 3x if produced at gold standards (environmentally or labor-wise); but not 10x.

That is, at the end of the day, 80% of the 10x overhead you pay at the premium retailers is just for the brand, not for safer products or better labor conditions (which are at best a secondary matter of concern to these outlets).

collyw4• 11 years ago

Sure, but is there any evidence that paying more for a brand name is going to avoid that? Nike doesn't exactly have the best reputation with this sort of thing.

icebraining2• 11 years ago

You mean the money is actually going to poor people? Sounds like a plus to me.

spython• 11 years ago

Please don't buy from discount retailers - support your local tailors and fashion designers. For a bit more than what it offered at zalando, you can get higher quality unique products from fashion design students.

whisdol• 11 years ago

Honest question: How do I find those? Online, local brick-and-mortar stores?

StavrosK• 11 years ago

PosgreSQL 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.

brightball3• 11 years ago

That'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• 11 years ago

What is a good space to look at when you get write heavy data to the point of streaming?

dk8996• 11 years ago

We used PostgreSQL at my old startup and switch away from MYSQL. Some of the things I liked about PostgreSQL; that you can do schema updates without rewriting and locking the table. The one thing we ran into, because of the way MVCC works, when you update a column the whole row gets rewritten. For example; if you have large rows and you update a Boolean column in a bunch of them - you will get a large write load. We ran on AWS and basically maxed out the PIOPs when updating a Boolean column on ~2m record table. Moreover, this would effect our read speeds causing our web site to be super slow. There was no easy way to throttle the MVCC writes.

vassy2• 11 years ago

Two 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/

lxfontes• 11 years ago

Have been using postico https://eggerapps.at/postico/ for day to day queries and pgadmin for more hardcore operations

vassy2• 11 years ago

It's still beta it seems. I used PG Commander, but it was too basic.

juliangregorian2• 11 years ago

You might try Valentina Studio.

jkahn• 11 years ago

When I tried that it crashed all the time. Huge pain to work with.

krrrh• 11 years ago

This has some promise. http://www.psequel.com/

angrybits6• 11 years ago

As 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• 11 years ago

Relevant Github Issue: https://github.com/sequelpro/sequelpro/issues/362

fideloper• 11 years ago

PostgreSQL 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• 11 years ago

I'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• 11 years ago

Postgres 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.

angrybits6• 11 years ago

RDS 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.

takeda2• 11 years ago

I 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• 11 years ago

Are 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_• 11 years ago

Apache 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• 11 years ago

Postgres 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.

cwyers3• 11 years 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.

JohnBooty• 11 years ago

  > What kind of data lacks any relations whatsoever?

You're right: all data has some kind of relation - when we say "non-relational data" we almost always really mean "loosely related." But some data is much more strongly related than others.

Think of a traditional RDBMS application such as an ordering system where there are strong relational constraints that need to be enforced before committing your data: ie, your order items had damn well better correlate with an order, which damn well better correlate with a user, or else nothing makes sense -- you had better raise an exception and should definitely not store those things in your database if the relational integrity is not there.

Now constrast that with other types of data collection where relational integrity is much more relaxed.

Generally, these examples would be (near) real time data collection - times when you're collecting data with a "collect first, analyze/correlate later" mentality. Specific examples would be a service that aggregates logs from a server farm in real time, or things like tracking user behavior statistics on the web. Or even an onboard computer that collects data from a car's engine - you want to read that data every 50ms no matter what, and maaaaaybe you will correlate it with events (accidents? combustion issues? whatever) later on oooooorrrr maybe not.

mhd2• 11 years ago

I 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).

kaufland5• 11 years ago

It'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.

mhd2• 11 years ago

They 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

ma2rten2• 11 years ago

Even 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.

valgog4• 11 years ago

Yes, 100 data model changes are schema changes (that can be one or more table structure change)

dragonwriter• 11 years ago

"Stored procedures for everything" is a bit unorthodox, but its probably a better practice that "base tables for everything" (which seems to be the common trend these days), and is pretty much the closest real practice to the "views for everything" that was long the ideal for decoupling decoupling the interface that each component using the DB saw from the underlying data model (an ideal that was, when it was made, often unachievable because of each RDBMS had different limitations on what you could do with views, particularly on the update side.)

Though, with modern PostgreSQL, views-for-everything would probably work at least as well as stored-procs-for-everything.

ams61102• 11 years ago

I don't think it's unorthodox. Perhaps uncommon these days. The application logic needs to live somewhere. I like doing in stored procedures because you are close to the data and that leaves your client code free to just implement the user interface. It's much easier to support different client platforms without having to rewrite all your core transactions. It's actually my favorite way to architect an application. I like views for the same reason, especially for use with 3rd party reporting/BI tools.

spacecowboy_lon3• 11 years ago

Isn't That how your supposed to do it using Sprocs is more time consuming but it does have several advantages from security and performance.

gdulli• 11 years ago

There's no way you're "supposed to" do it. There are tradeoffs and you pick the approach you like most given those tradeoffs.

mgkimsal• 11 years ago

I've seen this pattern used a few places (sprocs for every single interaction). Seems like a lot of development overhead, but at at least one place I saw them in use at, the db admin and one of his appointees were the only people allowed to even write these things. Devs were just given connection privileges to run those sprocs, nothing else. Things worked, but the process didn't seem terribly ... productive. Apparently it was partially in reaction to some previous developer who'd done all the SQL by hand, and no one else could understand it. They didn't seem to realize they'd just shifted the problem from a developer to a dba.

angrybits6• 11 years ago

I use the holy hell out of stored procs and UDFs, and they have saved my bacon plenty of times.

Once you get past building todo lists, you'll end up with complicated transactions that do many different data mutations, usually along with some simple logic. You can either maintain your virtue and pay the latency price for each of those, or just code a proc that implements the whole thing. It might offend your software dogma, but it'll get shit done so you can get home to the wife in time for dinner. These days, that is the only deadline I care about.

emilburzo6• 11 years ago

I 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_• 11 years ago

If mongo impresses you, you should check out rethinkdb: https://github.com/rethinkdb/rethinkdb

emilburzo6• 11 years ago

no official java driver :-/

sandGorgon• 11 years ago

do 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...

ma2rten2• 11 years ago

Actually the article you posted makes the case that postgres is not a good json database, because it can not modify json documents in place.

emilburzo6• 11 years ago

Maybe I wasn't clear, I love everything about PostgreSQL.

But I would love it even more if it were easier to distribute (see replica sets in mongodb, with auto-failover and other goodies).

petepete• 11 years ago

I'm genuinely interested in which dataset/use case is serviced better by MongoDB than PostgreSQL

emilburzo6• 11 years ago

I didn't mean that it was serviced better by MongoDB rather than PostgreSQL.

I meant that it was a valid/recommended use case for MongoDB since I didn't really have any relational data (every document inserted was pretty much standalone).

I've mentioned it because I've seen plenty of posts around HN where "mongo sucks" because they tried to fit a round shape through a square.

The extra goodies from MongoDB helped too.

Like automatic failover, I can literally go and unplug a node and everything will still be fine.

Having tail -f functionality in the db was also pretty handy (for my project).

The sysadmin in me was happy too, it's not everyday you see software that allows you to upgrade between major versions / storage engines without downtime (when using replica sets, not standalone, of course).

angrybits6• 11 years ago

If it's running on a desktop in your apartment, you don't need replica sets.

emilburzo6• 11 years ago

Why not?

It is a desktop (as-in, it has normal desktop components, although used headless like a server), but it's got plenty of RAM, good CPU, RAID1 on WD Re hard drives, 100mbit connection and hooked up to a UPS.

In the last year it has been more stable than some of the cheaper hosting I was using.

Besides, it's not running the whole replica set, just one member (out of 3).

UserRights• 11 years ago

I found this video to be much more interesting 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!

valgog4• 11 years ago

There 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• 11 years ago

I 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• 11 years ago

Is there anything on Postgre Roadmap to allow, simple, easy, replication out of the box?

“Fashion Is Hard. PostgreSQL Is Easy” [video]

Related Stories