Saturday, August 31, 2019

Postgres does not use index on gigantic numeric value

Nothing beats a Saturday morning when you wake up fresh and excited, ready for the second sleep, and realize that your Postgres database was harassed over the night and accumulated a number of downtime minutes that is too embarrassing to state here.

My database load looked like this during the outage.

buffer_mapping looks new. Postgres official documentation on this matter seems to be written by Captain Obvious: Waiting to associate a data block with a buffer in the buffer pool. Thanks for nothing. Basically buffer_mapping is a lightweight lock in read operations, my processes were fighting to reserve buffers in which to read data pages.

I have a read problem.

This query accommodates the highest number of locks:

pp_pqsql_prod=> explain select * FROM "big_table" WHERE "big_table"."id" = 9200190224054041915721;
                                           QUERY PLAN
------------------------------------------------------------------------------------------------
 Gather  (cost=1000.00..4568847.85 rows=535675 width=1673)
   Workers Planned: 2
   ->  Parallel Seq Scan on big_table  (cost=0.00..4514280.35 rows=223198 width=1673)
         Filter: ((id)::numeric = '9200190224054041915721'::numeric)
(4 rows)

It is a sequence scan, on a 100M-row table, so it is obvious why it caused all the ruckus. What's less obviously is why Postgres performed a sequence scan on a primary key column.

With the help of a colleague, it appeared that given a smaller numeric value, the index kicks in just fine.

pp_pqsql_prod=> explain select * FROM "big_table" WHERE "big_table"."id" = 9200190;
                                              QUERY PLAN
-------------------------------------------------------------------------------------------------------
 Index Scan using big_table_pkey on big_table  (cost=0.57..8.59 rows=1 width=1673)
   Index Cond: (id = 9200190)
(2 rows)

The problem is the size of the queried value. Eventually I stumbled upon this stackexchange Q&A. It can be seen in the first explain that because 9200190224054041915721 was too big, it had to be casted into numeric data type. My primary key was not that big, its data type was bigint. So it had to be casted too, because apple can't be compared to orange. What I have now is a numeric to numeric comparison, and a bigint index can't serve that.

Problem be gone and so was my morning.

Wednesday, August 21, 2019

On organizing Barcamp

If you haven't heard of Barcamp Saigon, then putting it shortly, Barcamp is an open space, also known as an unconference. As organizers, we prepare the facilities to make presentations happen, but both the content and agenda are determined by participants on the event day. Topics are submitted online a couple of months before the event to somewhat ease the hassle. But the voting is strictly limited to the event day, to ensure only the opinions of those who come matter. Topics are then allocated to rooms and time slots accordingly to their popularity. It's that simple.

I have been involving in the Barcamp scene of Vietnam for a few years. There was a 2-year hiatus when it was a make-it-or-break-it period for my employment. Yet eventually, when the dust settled, I found myself, once more, organizing one.

There are more or less a thousand participants in each Barcamp Saigon event. And though we typically only hold one a year, it is still a time sucker, with some hard work, and being a volunteer community project, one pretty much plays the hand that's dealt. Organizing Barcamp is one of the things in my life I consider unusual. Contacting seemingly random people asking for money, dealing with finance and paperwork, and coordinating other people, for an introvert, sound dreadful. Making and answering phone call push my wrong buttons.

Yet I hold on to organizing Barcamp Saigon like my dear life depends on it. I guess because as a twenty-something, I understand how hard it is to get heard in this exhilarating hyperactive city. You kinda have this neat little idea in your mind, but can't seem to find a thought partner to bounce it back and forth, nor an audience to whom the thought is relevant. Heck, even getting invited into a conference (as a speaker) is hard. You are nobody, and the conference you need doesn't exist yet. After a while, you just lose the excitement, the idea is now collecting dust in some closet, in a far corner of your clustered mind. An open space like Barcamp provides all people with an idea to share a platform to do exactly that. You don't need invitation, and the voting board of Barcamp merely shows the topic name, everyone is nobody, and content is king. In a way, an open space is the serverless of knowledge exchange. You get right into the exciting bits.

Barcamp audience is more diverse than it is specialized, more of a melting pot than a silo. If you want to argue the better approach to natural language processing, go to an AI conference; get started with conversational UI (aka chatbot), an UX conference. If you want to make the reservation experience at your restaurant more fluid, well, I guess you are out of luck, there isn't such a space in Saigon yet. But Barcamp is probably the only place you can find people with these three expertise mingling. It reminds me fondly of university time. There was these general elective courses where I got to pick non-IT ones. In those courses, everyone knew something I didn't, and the other way around. And the clash of knowledge, mindsets, and life styles was refreshing. As time goes by, my "tribe" gets smaller and more focused, usually on topics I am very much familiar with. The magic moment when something you have known since forever is basically witchcraft to me get fewer and farther in between. So I learned to appreciate Barcamp for pulling me out of my cave.

To be at Barcamp, in the middle of all communities is, for lack of a better word, a happening feeling. I get to see communities as living creatures, breathing, morphing into shape and form, and reincarnating. That doesn't necessarily mean a good thing. I have been to some Barcamps, at different places. Some were mind blowing. Some sucked though. One was particularly bad that I decided I wanted to do something about it. In a way, Barcamp is a health indicator of its local communities. But unlike GDP, which indicates wealth (1), AQI for pollution, or HPI for happiness (2), this indicator is one where individual actions can make huge difference. I am doing my part.

Open space concept doesn't have to be limited to Barcamp. In fact it shouldn't be. It should be another option in every community builder's toolkit for growing their group of like-minded people. It reduces the stress to find speakers for the next meetup, and give new participants chances to shine. And by the time open space is adopted as a second nature, perhaps Barcamp can retired for it has accomplished what it means to :)

---

(1) Whether GDP can indicate economy growth is a controversial topic that I am not jumping into.
(2) In Zen And The Art Of Motorcycle Maintenance, Pirsig argued that quality cannot be defined. When one defines characteristics of quality, those characteristics cease to be quality and can't describe the whole, which is greater than the sum of its parts. Is happiness quality?