Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

SQL Design Patterns (2010)

vadimtropashko.wordpress.com

Post reply on HN
Print view View original

11–20 of 35 posts

Re: SQL Design Patterns (2010)

#11

Post by dspillett »

morkalork wrote: post #6
potatoproduct wrote: post #2

Not ashamed to admit that I never really thought about the distinct operator 'being redundant' as its essentially just a group by.

distinct has always felt like a query smell to me. Too many junior analysts abusing it because they don't know the schema well and are over-joining entities

DISTINCT is often a smell at the head (or middle) of a complex query as you are throwing away processed information, sometimes a lot of it, late in the game. Much better to filter it out earlier and not process it further, where possible. Filtering earlier, as well as reducing waste processing time (and probably memory use), increases the chance of the query planner being able to use an index for the filter which could greatly decrease the IO cost of your query.

Re: SQL Design Patterns (2010)

#12

Post by yakshaving_jgt »

alphazard wrote: post #7

I always tell people to worry about the data structures that you want the database to maintain for you, and not worry about the SQL. You can always use Google to look up the SQL, or now ChatGPT to generate it for you. SQL is a not-that-great language and it intentionally hides what's going on. It is also different enough between databases that you need to pay attention. So learning to design/think in terms of SQL is…

For posterity, how would you recommend the average working programmer should go about doing that?

Re: SQL Design Patterns (2010)

#13

Post by ryanjshaw »

yakshaving_jgt wrote: post #12
alphazard wrote: post #7

I always tell people to worry about the data structures that you want the database to maintain for you, and not worry about the SQL. You can always use Google to look up the SQL, or now ChatGPT to generate it for you. SQL is a not-that-great language and it intentionally hides what's going on. It is also different enough between databases that you need to pay attention. So learning to design/think in terms of SQL is…

For posterity, how would you recommend the average working programmer should go about doing that?

Read code from other projects

Re: SQL Design Patterns (2010)

#14

Post by ryanjshaw »

morkalork wrote: post #6
potatoproduct wrote: post #2

Not ashamed to admit that I never really thought about the distinct operator 'being redundant' as its essentially just a group by.

distinct has always felt like a query smell to me. Too many junior analysts abusing it because they don't know the schema well and are over-joining entities

Sometimes the number of joins is fine but they don’t understand the data properly and should be spending more time understanding why multiple rows are being returned when they expect one (eg they need to filter on an additional field).

I wish SQL had a strict mode syntax that forces you to use something like `select one` (like LINQ’s Single()) or `select many` to catch these kinds of bugs.

Re: SQL Design Patterns (2010)

#15

Post by sgarland »

alphazard wrote: post #7

I always tell people to worry about the data structures that you want the database to maintain for you, and not worry about the SQL. You can always use Google to look up the SQL, or now ChatGPT to generate it for you. SQL is a not-that-great language and it intentionally hides what's going on. It is also different enough between databases that you need to pay attention. So learning to design/think in terms of SQL is…

Frankly, this is terrible advice. If you’re not designing your data model around the language it’s going to be queried in, how do you expect to get decent performance out of the database?

Also, in no way does SQL hide anything - it’s a declarative language, and will produce exactly what you tell it to, provided you understand what it is you asked it to do. The query engine is somewhat of a black box, but that is completely orthogonal.

Re: SQL Design Patterns (2010)

#16

Post by awesome_dude »

alphazard wrote: post #7

I always tell people to worry about the data structures that you want the database to maintain for you, and not worry about the SQL. You can always use Google to look up the SQL, or now ChatGPT to generate it for you. SQL is a not-that-great language and it intentionally hides what's going on. It is also different enough between databases that you need to pay attention. So learning to design/think in terms of SQL is…

I don't get this - my database is going to be normalised to whatever is optimal (3rd normal form generally, denormalised for higher load/sharded/caching)

The indexing is where the main optimisations take place - hashmap indexes, or clustering indexes for priority queues.

What am I missing?

Re: SQL Design Patterns (2010)

#17

Post by paulddraper »

sgarland wrote: post #15
alphazard wrote: post #7

I always tell people to worry about the data structures that you want the database to maintain for you, and not worry about the SQL. You can always use Google to look up the SQL, or now ChatGPT to generate it for you. SQL is a not-that-great language and it intentionally hides what's going on. It is also different enough between databases that you need to pay attention. So learning to design/think in terms of SQL is…

Frankly, this is terrible advice. If you’re not designing your data model around the language it’s going to be queried in, how do you expect to get decent performance out of the database? Also, in no way does SQL hide anything - it’s a declarative language, and will produce exactly what you tell it to, provided you understand what it is you asked it to do. The query engine is somewhat of a black box, but that is comp…

SQL is a declarative language so it —- by definition —- hides the execution.

Not really sure what you’re trying to argue here.

Re: SQL Design Patterns (2010)

#19

Post by sgarland »

paulddraper wrote: post #17
sgarland wrote: post #15

Earlier quoted context omitted.

Frankly, this is terrible advice. If you’re not designing your data model around the language it’s going to be queried in, how do you expect to get decent performance out of the database? Also, in no way does SQL hide anything - it’s a declarative language, and will produce exactly what you tell it to, provided you understand what it is you asked it to do. The query engine is somewhat of a black box, but that is comp…

SQL is a declarative language so it —- by definition —- hides the execution. Not really sure what you’re trying to argue here.

Parent made it sound - to me - that you put an input in and hope for the best. If you understand the operators, you can quite confidently predict an output given an input.

Re: SQL Design Patterns (2010)

#20

Post by alphazard »

yakshaving_jgt wrote: post #12
alphazard wrote: post #7

I always tell people to worry about the data structures that you want the database to maintain for you, and not worry about the SQL. You can always use Google to look up the SQL, or now ChatGPT to generate it for you. SQL is a not-that-great language and it intentionally hides what's going on. It is also different enough between databases that you need to pay attention. So learning to design/think in terms of SQL is…

For posterity, how would you recommend the average working programmer should go about doing that?

An intro data structures course is worth watching if you haven't taken one. There are plenty of them on YouTube. Try to follow along with a language that has an explicit pointer type. Go is a good choice. Java and Python are worse choices (for this particular thing) IMO.

Assuming you are familiar with trees and hashmaps, you have all the important building blocks. You can imagine a database as a bunch of trees, hashmaps and occasionally other stuff, protected by a lock. First you acquire the lock, then you update some of the data structures, and maybe that requires you to update some of the other data structures (like indexes) for consistency. Then you release the lock.

By default, most data will live in a BTree with an integer primary key, and that integer is taken from a counter that you increment for new inserts. Indexes will be BTrees where the key is stuff you want to query on, and the value is the primary key in the main table.

Using just those data structures you should be able to plan for any query or insert pattern. It helps to figure this out yourself in a programming language for a few practice cases, so you know you can do it. Eventually it will be easy to figure out what tables and indexes you need in your head. In the real world, this stuff is jotted down in design docs, often as SQL or even just bullets.

That's really all you need, and that's where I recommend getting out of the rabbit hole. Query planners are pretty good. You can usually just write SQL and if you did the work to understand what the tables and indexes should be, the planner will figure out how to use them to make the query fast.

Post reply on HN