SELECT * Anonymous: top posts

Posts

1. Experience

   Teaching SQL joins to Class 12 with the attendance register and the fee register

   Every year my Class 12 children memorise "INNER JOIN returns only matching rows" for the exam, write it perfectly, and understand nothing. This year I tried something different and it worked better than I expected, so I am sharing. I borrowed two real registers from the office: the attendance register (roll number, name) and the fee register (roll number, term, amount). I asked the children: "Who has paid fees, with…

   Anitha JosephOct 5, 202637likes10comments

2. Experience

   Blameless review: the 'quick' migration that blocked every write to our orders table

   Short incident review, because the lesson is more useful than the embarrassment. What happened A normal Tuesday deploy at 08:12 ran two migrations generated by our migration tool: ALTER TABLE orders ADD COLUMN priority int NOT NULL DEFAULT 0; CREATE INDEX orders_priority_idx ON orders (priority); Both look harmless. Since Postgres 11, adding a column with a constant default does not rewrite the table, so the ALTER…

   Bram VerhoevenOct 18, 202619likes9comments

3. Article

   Two cashiers, one last bottle of kecap: fixing overselling in a Laravel POS with row locks

   One of my clients is a small minimarket chain, 4 stores, 6 cashiers per store at peak. Last Lebaran, the day before the holiday, the stock report showed -14 bottles of kecap manis . Negative stock. The owner asked me, very politely, how we sold 14 bottles that do not exist. Good question, mas. The bug $product = Product::find($id); if ($product->stock < $qty) { throw new OutOfStock($product); }…

   Bayu SetiawanAug 22, 202614likes9comments

4. Question

   latest row per customer without a correlated subquery that takes 40 minutes?

   doing a take-home for a data engineering role (job hunt day 187, grand, all grand). table is basically: customer_events ( id bigint primary key, customer_id bigint, event_type text, payload jsonb, created_at timestamptz ) about 120M rows in their description, i've a 5M sample locally. they want the latest event per customer. my first go: SELECT e.* FROM customer_events e WHERE e.created_at = ( SELECT MAX(created_at)…

   Aoife BrennanSep 15, 202613likes10comments

5. Poll

   What is your main production database? No 'it depends', pick one.

   Bram VerhoevenAug 21, 20266likes8comments

6. Question

   Django REST Framework: still N+1 queries after select_related, what am I missing?

   One of my mentees brought this to me and now I am also stuck, which is humbling. We have a simple endpoint listing courses. Each course has a teacher (ForeignKey) and many tags (ManyToMany), and the serializer also shows how many students are enrolled. class CourseSerializer(serializers.ModelSerializer): teacher = TeacherSerializer() tags = TagSerializer(many=True) student_count = serializers.SerializerMethodField()…

   Grace WanjiruOct 3, 20264likes3comments

7. Question

   Daily sales report across timezones: which day does an order at 11:30pm Bangkok belong to?

   We store created_at as timestamptz. Until now all markets were UTC+8 (Singapore, Malaysia) so we just did: select (created_at at time zone 'Asia/Singapore')::date as day, sum(amount) from orders group by 1; Now we add Thailand (UTC+7). Order at 23:30 Bangkok time is 00:30 next day in Singapore. Thai team want it in their day, regional report want one number per day. Options: convert each order using its market…

   Lin WeiOct 14, 20263likes3comments

8. Question

   Payout worker queue in Postgres: SKIP LOCKED is holding transactions open for 30s, better way?

   We have a payouts table and 4 worker processes that pick pending payouts and call the bank API. Right now each worker does this: begin; select * from payouts where status = 'pending' order by created_at limit 10 for update skip locked; -- call bank API for each row (can take up to 30s) update payouts set status = 'sent' where id = ...; commit; It works, no double picks. But the transaction stays open during the HTTP…

   Arjun MehtaSep 25, 20263likes3comments

9. Question

   Storing high-frequency meter data: plain Postgres, TimescaleDB, or something else?

   We are getting more charger and meter data than our current setup was designed for. Right now it is about 40,000 devices sending a reading every 15 seconds, so roughly 230 million rows per day, into a single Postgres table with a B-tree index on (device_id, ts) . Inserts are still fine, but queries over a week for one site are getting slow and the table is huge. Typical queries: last 24 hours for one device, at full…

   Emma LindqvistSep 19, 20262likes3comments

10. Question

    Postgres count(distinct) on 80M rows very slow, any trick?

    Daily active customers query take ~40s, dashboard timeout: select date_trunc('day', created_at) as day, count(distinct customer_id) from orders where created_at >= now() - interval '90 days' group by 1 order by 1; Table ~80M rows, index on created_at. Postgres 15. EXPLAIN show big Sort spilling to disk. Tried increase work_mem to 256MB, bit faster but still 25s. Is count(distinct) just slow in Postgres? Thinking to…

    Lin WeiSep 13, 20262likes3comments