Primary
SELECT * Anonymous
Databases and SQL: queries, indexes and query plans
Databases, query plans and the index you forgot. Postgres mostly, everything else welcome.
30 members
10 posts
Created Aug 2026
+2
Community rules
Bring the EXPLAIN ANALYZE
The plan answers most questions before anyone has to guess.
Bring the detail
Versions, error text, what you already tried. "It does not work" is not a question anyone can answer.
No drive-by self promotion
Share your own work when it answers the thread. A link with no context is a advert, and it gets removed.
Top contributors
Discussions
Showing 10 posts.
Posts in t/select-star-anonymous
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…
- I don't use Postgres at work yet but I read it to learn how a postmortem looks. 'The engineer did what the tool generated' is very kind, my old team always search the person to blame 😅
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…
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…
- Diolch Anitha, this is tidy. I do something similar with the register and the dinner money list. The transferred student is a brilliant way to get to data quality. Small task for anyone reading: write the query that finds fee rows with no matching student. Answers below 😄
- Respected Anitha madam, this is wonderful. I am a maths teacher just starting Python and SQL with Class 9 and 10. May I use the fee register example in my class? Farhan appearing two times is a very good way to explain one-to-many. Thanking you.
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()…
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…
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…
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)…
Accepted answer
ok this one I do every week lah. three ways: 1. postgres only, shortest: SELECT DISTINCT ON (customer_id) id, customer_id, event_type, payload, created_at FROM customer_events ORDER BY customer_id, created_at DESC, id DESC; DISTINCT ON keeps the first row per customer_id according to the ORDER BY. id DESC breaks the tie when created_at is same. 2. portable, works in SQL Server, Oracle, MySQL 8, Snowflake etc: SELECT id, customer_id, event_type, payload, created_at FROM ( SELECT e.*, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC, id DESC) AS rn FROM customer_events e ) t WHERE rn = 1; ROW_NUMBER not RANK, because RANK gives 2 rows when tie. confirm they will ask this in the follow-up. 3. when there are few customers and very many events each, LATERAL with LIMIT 1 can be fastest, it jumps into the index once per customer: SELECT c.id, last.* FROM customers c CROSS JOIN LATERAL ( SELECT e.id, e.event_type, e.payload, e.created_at FROM customer_events e WHERE e.customer_id = c.id ORDER BY e.created_at DESC, e.id DESC LIMIT 1 ) last; for all three the index is: CREATE INDEX ON customer_events (customer_id, created_at DESC, id DESC); and since we are in select-star-anonymous: list the columns in your submission, the reviewer will notice 😄- not my area but sent you another lead aoife, data eng contract in Leeds, remote mostly. they're on Postgres so you're sorted after this
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…
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); }…
- confirm saving. our bank had the same race in a voucher redemption table, someone fixed it with an app-level mutex on one server. then they scaled to 3 servers lah. alamak
- MySQL for the agency, because every client's hosting has it. Postgres for anything we host ourselves. there, I said it in public