The day I reached the 1600 columns limit in PostgreSQL | Hacker News Reader

The day I reached the 1600 columns limit in PostgreSQL

rosenfeld.herokuapp.com

Why Is there a limit on the number of columns I can have, and if something bad happens from having too many, why isn't the limit much lower than 1600, which hopefully most of us can agree is already way beyond absurd?

It has to do with how the tuple header that stores the metadata is designed. See comments here: https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f...

Maybe Im missing the point, but After reading that I still don't understand why you couldn't just add a permanent column and continually overwrite it.

You don't understand it because he doesn't understand the problem well enough to explain it, which is why he ever had this problem in the first place.

Maybe I failed to explain it well enough, but I certainly do understand the problem pretty well. If you have any questions just ask and I can tell you the reasons to do what I did.

The reason why I had this problem in the first place is that I wasn't aware that dropped columns would still count towards the columns limit in PostgreSQL. It's not a complaint, it's just ignorance. There's a difference between being ignorant and not understanding the problem after it happens. I never claimed to be an expert on PostgreSQL.

> that I wasn't aware that dropped columns would still count towards the columns limit in PostgreSQL.

FWIW, I hope we (the PG devs) will fix that some day.

Awesome, good to hear that :-)

Fixed or not, PG is awesome anyway. Thanks for helping to make it the best database I've worked with so far :-)

Any chance we can get a quick post mortem?

As pstuart mentioned, one of the post mortem was to fix the script, which I did by using a temporary table.

The other part was to enable new columns to be added to that table in case we need in the future. For that part, I locked a few tables for writing, recreated them with a separate name and replaced the old with the new one in order to avoid any downtime or dealing with master-slave set-ups to be able to dump and restore without downtimes. Full vacuum freeze wouldn't reclaim that space back.

He used a temp table to stash IDs, like he should have from the start.

My thoughts exactly.

Hi, I'm the author of that article. Yes, that was the first thing that came to my mind when I noticed the error messages but it wasn't really an option by that point because I could no longer add new columns to that table. I wanted to enable the deals porting as soon as possible and I didn't know yet by that time how to rewrite the table without asking for some maintenance window, so I decided to go with another solution.

If you're curious why I didn't simply create that column permanently in the first place, the reason is that this was supposed to be a one-off script (it would run multiple times but just during the transition to the new template until all deals would have been ported) and I didn't want to pollute the table or have to remember to drop that column in a future time. There are also other reasons why I don't think it would be a good idea. The script was greatly simplified with the assumption that all rows having a value in the previous_id column would be related to that deal being ported. If I want to keep the same simple logic I'd have to make sure the script would delete any values from that column in the beginning of the transaction and I figured that could increase the chance of conflicts in case of concurrent attempts of porting deals and I didn't want to have to bother about concurrency issues so I didn't want to even think about that. With a temporary column I knew I wouldn't have to worry about that.

Yeah, reading the old id and then using the new id seems like the textbook example of db triggers.

Yes, that could be an option if this was supposed to be a permanent script. However, I was trying very hard to finish the migration of the deals to the new template and the script was quite big and complex. I didn't talk about the script so that I could focus on this specific part that affected it, but the way some people are judging it's like I had put a lot of thoughts in this part of script. It wasn't the case. This was a minor part of the script. I hadn't thought about using nextval() to create the mapping table when I first wrote the script, so it looked like adding a temporary column to store the old id would be the simplest solution for the mapping problem. I had that gut feeling telling me it wasn't the right thing but as long as it worked, for a one-off script, I didn't really care if it allowed me to finish the porting earlier. Writing a trigger would take more time and code than adding a temporary column, just like using nextval() to create the temp table is less work than writing the trigger. Most people seem to ignore that this was a one-off script that won't ever be used again.

I don't actually regret my approach. It allowed me to deliver the first version for testing earlier and I was able to fix the script later in less than an hour once the problem happened. Maybe other parts of the script were not ideal either, but the migration was successful and this is what really matter to me. It would be a completely different situation if I was writing a permanent code. In those cases I write the code way more carefully and give it quite a lot of thoughts on the future implications.

I had this problem for real and there was no solution: parsing Edgar sec quarterly reports and trying to stick every field into its column. there was no solution so we just through out some of the fields.

Reply on news.ycombinator.com