4 ms·
> PostgreSQL is limited to 1600 columns in a table, and the column limit for a select clause isn't much bigger. We have several times this number of columns in
by YCode 9y ago
> PostgreSQL is limited to 1600 columns in a table, and the column limit for a select clause isn't much bigger. We have several times this number of columns in our largest analytic tables.
Is that typical for this kind of work?
- swasheck 9y agoI thought that too. 1600 columns seems like quite a few columns.
- barrkel 9y agoWhat's "this kind of work"? :) The company I work for does reconciliation as a service, something that's pervasive in finance. We support customer-defined schemas; in fact that's one of our selling-points. I could start talking about the kinds of things we've done with MySQL to make this perform well - it's not difficult, just a bit unorthodox. Anyway, the diversity in customer schema leaks out into the Hadoop schema, where we'd much prefer to give customers data using column names they're familiar with, and we also want to give them rows from all their different schemas in a single table (because many schemas have overlap by design). The superset of all schema columns is large, however. The problem can be overcome with more tooling - defining friendly views with explicit column choice - but having the option to implement that (and go to market sooner), vs a requirement to implement that, adds up to a distinct advantage for tech that can support the extra columns.
- jamespo 9y agoEdgar F. Codd just started spinning
- barrkel 9y agoSomething you need to bear in mind is that distributed joins are very expensive; you have a better time designing your schema such that related data can be placed logically close together, whether it's arrays / maps inside rows (for one to many), or very wide rows (denormalizing what might be a star schema). (I know, in a column store having related data in another column isn't actually close together; but it can be stepped through at the same time, it doesn't need a join to be correlated, it's correlated naturally.)
- YCode 9y agoTo be honest, "this kind of work" was a euphemism for "Just what the hell are you doing that requires over a thousand columns?" I understand though that sometimes customers give you a rotting dumpster of data and ask for critical insight into their operations.
- threeseed 9y agoYes. Customer analytical record tables are extremely wide. I have a number of enterprise customers who have use cases with tens of thousands of columns. And trust me that every enterprise is moving to having one since they are needed for fast supply of data to decisioning systems like PEGA. That's why I always find it hilarious when HN goes on about just using PostgreSQL or some other SQL database for everything when they don't understand the use case. They simply doesn't work in these scenarios.
- YCode 9y agoI'm ignorant on this topic... Why so many columns? I would expect there would be some way to break the data down or transpose it somehow to make it more manageable. At that point are you even really dealing with a database as most people use the term?
- threeseed 9y agoBecause they are attributes of a customer or a product. And many companies these days know a LOT about you as a customer e.g. everything from your age to how likely are you to purchase product X. It has to be one table because you need to get attributes of a customer very quickly (single digit milliseconds) in order to respond with the next best action e.g. show this advertisement or route them to this call centre person. And of course this is a database. It's all of the information about a customer in one place.