HNHacker News
TopNewBestAskShowJobs

AlisdairO

2,006 karma · joined March 19, 2009

email: alisdair@zaltys.net web: www.zaltys.net, pgexercises.com
submissionscomments
AlisdairO··on PostgreSQL Exercises
Thank you!
AlisdairO··on PostgreSQL Exercises
thanks :)
AlisdairO··on PostgreSQL Exercises
Glad you've found it useful! FWIW window functions are already covered in the Aggregation section.

The rest of it, yeah, as you say it's not so great a fit.

AlisdairO··on PostgreSQL Exercises
Ah, I should have been clearer in my initial response. I actually have no opinion on whether zip codes are a number, as I'm from the UK and don't really know how they work :-). I was more responding to the idea that any numeric value you don't do calculations on should be stored as a bare string. I would be happy enough with a string that had a constraint limiting the string to characters [0-9], though (again, assuming the data in question is limited to those chars).

On the whole, pgexercises punts on the data modelling aspect, as it's focused on teaching SQL instead - in some cases choosing deliberately bad schema design to make it easier to ask interesting questions. On reflection I'm not sure this was a good approach, but I'm far past the point where I have time/inclination to revisit it.

AlisdairO··on PostgreSQL Exercises
That wouldn't generally be my approach. In my view if something is a number it should be typed as such.

That said, I recognise that there are pros and cons to this approach similar to those in strongly vs weakly typed PLs. My personal preference is for a strong schema.

AlisdairO··on PostgreSQL Exercises
grins for that I'll have to say that I'm from the UK and that situating the exercises in the US was probably an error :-)
AlisdairO··on PostgreSQL Exercises
Author here: out of curiosity, what would be your priorities for future exercises? Right now my list looks roughly like:

    - DDL
    - Advanced datatypes (JSON, arrays, etc)
    - GIS
This order is partly informed by the current support of the site: adding DDL exercises is a relatively minor bit of technical work, and then it's just writing. Everything else involves schema changes (or, possibly, a second database).
AlisdairO··on PostgreSQL Exercises
Yeah, I have zero design sense and just went with the defaults of a bootswatch theme that I liked :-)
AlisdairO··on PostgreSQL Exercises
Author here - thanks a lot for the feedback, it's appreciated.

You're right on the naming convention, it's very haphazard. I'll raise an issue on Github to revisit that - although it may take me quite a while to get to it as I have a two week old to look after right now :-).

AlisdairO··on PostgreSQL Exercises adds DML exercises
Author here: hope people find this useful. I've been very quiet on the site lately due to a serious of (mostly good) life upheavals. This update lays the technical groundwork for a bunch of future exercises, so I hope to have a little bit more steady progress in future.
AlisdairO··on Memoir of Growing Up Fat Forces France to Look in the Mirror
> with just a little bit of will

As a once-fat guy, it's truly frustrating to read this. If there's one thing I could drill into people observing this problem, it's that different things are hard for different people.

For myself, losing weight (and keeping it off long term) is by a distance the hardest thing I have ever done. I've done a reasonable range of challenging, willpower-requiring things, and I've been fairly successful academically, professionally, and as a father. The difficulty of any of the things I've done in my life pales in comparison to the challenge of stopping myself overeating (particularly sugary foods).

I make no claims that I gain weight more easily than others, but I am quite positive that I want food a lot more than most - similarly, I feel no need to drink to excess, while for others it's a challenge that ends their lives. I don't advocate normalisation of fatness, but I do wish people could manage to avoid being so unpleasant to people who are struggling.

AlisdairO··on Being Thankful for Free Software Developers
I really liked this post. Obviously everyone has different motivations, but at its heart FOSS is an enormous collection of cooperative and/or charitable work, and the industry as a whole should be really, really proud of it.

One other option for giving back - just send an email saying thanks, and that you love the project. I get such an email once or twice each week for my open source project, and it really brightens up my day. I'm lucky enough to not really need any donations, but everybody needs their spirits lifted from time to time.

Knowing that your contribution has made a positive impact on someone else's life is a powerful thing, and from the user's perspective costs very little to do.

AlisdairO··on Literate SQL
[self plug] Take a look at https://pgexercises.com/ . It's a learn-by-doing set of SQL exercises. Focused on Postgres, but the large majority is standard SQL.
AlisdairO··on DynamoDB to Postgres: Why and How
"These, of course, are pitiful numbers for a database. However, it’s important to note that these numbers are not representative of what real-world applications can expect to see from CockroachDB.

First, this is a test in which the Jepsen nemesis is continuously tweaking the network configuration to cause (and heal) partitions between different nodes. You don’t commonly see behavior like this in a real world deployment.

Second, tests like these deliberately try to reach high levels of contention, in which many transactions are trying to operate on the same keys. In real-world situations contention tends to be much lower as each user is operating mainly on their own data. The performance of all transactional systems suffer under high contention (although CockroachDB’s optimistic concurrency model fares worse than most)"

AlisdairO··on Running 10M PostgreSQL Indexes in Production
(self plug) for learning Postgres-flavoured SQL, take a look at https://pgexercises.com.

Books-wise, Learning SQL is a great start. http://shop.oreilly.com/product/9780596007270.do

The postgres docs themselves are also pretty fantastic.

AlisdairO··on Why Use Postgres?
Disclaimer: huge postgresql fanboy, use it whenever I get the chance

A few things I've found great in Oracle that aren't (AFAIK) available in PG:

    - Straight better/more reliable performance on average
    - More advanced parallel queries (obviously this is changing in PG right now)
    - Flashback queries
    - Better materialized views
    - Plan stability (maintains predictable query performance, rather than the nasty jumps you sometimes see when plans change)
    - Better clustering story (RAC is super expensive but pretty good)
AlisdairO··on YC AI
> In the past we've replaced unskilled labor with new unskilled roles, and I don't see those roles hanging around anymore.

The sad reality is that there's a nontrivial chunk of the populace that isn't able to pick up highly skilled roles. It also ignores the role of unskilled jobs in providing space for people whose job class has been destroyed and need to retrain (or mark time until retirement).

I'm not advocating slowing innovation to prevent job loss. I am advocating avoiding magic thinking ('there's always new jobs to go to'): we need to start a serious conversation about what we do with our society when we have the levels of unemployment we can expect in an AI-shifted world. Right now we're trending much more towards dystopia than utopia.

AlisdairO··on Google Cloud SQL for Postgres
> Aurora is similar idea.

Aurora isn't substantially more expensive than RDS Postgres. Caveat is that you can't run it on the really tiny instance sizes right now. On an r3.large with no reserve pricing you're looking at ~$200pm, or ~115 with reserve.

AlisdairO··on Japan's Universities Are Failing
> What if those in Culture B face problems? Should they not ask for change? What if those in the culture are citizens? The idea that people can't ask or demand for things just because they are of a different culture is ridiculous.

Sure, they should absolutely ask for change. I think the point is that if Culture B wants to change the course of the nation to something that the currently dominant Culture A really doesn't like, it's not crazy for people of Culture A to not want to import a whole bunch more people who subscribe to Culture B and in the process give said culture way more power.

AlisdairO··on Is PostgreSQL good enough?
Postgres uses process-per-connection. Usual model is to put it behind a connection pool like pgbouncer.
AlisdairO··on Postmortem of database outage of January 31
Not sure how it works on cloudformation, but in the console and API you have to explicitly skip the final snapshot.
AlisdairO··on Data Loss at GitLab
You gave one example of each. That's a pretty painful definition of the word 'every'.

On performance: Every database will have some areas where it performs better than others - Uber just happened to hit a case that MySQL is well optimised for.

On data loss: MySQL's binlog replication is hardly a 'just works' solution. It's definitely got a lot better over time (particularly in 5.7), but it's not like a replica falling behind is some incredibly unusual event. Go back a fair while and it was really broken - much more so than Postgres' solution has ever been. It's fair to say it's more mature right now, and one reason to prefer MySQL for replication is that there's certainly more help out there for it.

AlisdairO··on Ask HN: What is the best online resource to learn advanced SQL?
Thanks :-)
AlisdairO··on Ask HN: What is the best online resource to learn advanced SQL?
I'm fortunate in my career - while more money is always nice, I'm at the point now where it would have to be an awful lot to persuade me to take more time away from my (very young) family. I suspect all a patreon would accomplish would be my still producing slowly on the site but now feeling guilty about it :-).

Maintaining the site is in many ways its own reward - it gives me a great deal of joy to see how much it's used now. Improvements are actually a reasonably high priority for me, it's just that toddlers have a way of creating their own special priority levels :-).

All that said, I very much appreciate the sentiment!

AlisdairO··on Ask HN: What is the best online resource to learn advanced SQL?
No worries - hope you find it useful.
AlisdairO··on Ask HN: What is the best online resource to learn advanced SQL?
Thanks, appreciate it :-)
AlisdairO··on Ask HN: What is the best online resource to learn advanced SQL?
Thanks - I'll try to get hold of an ios device to test it on. Sadly my webdev skills are fairly lol-worthy ;-)
AlisdairO··on Ask HN: What is the best online resource to learn advanced SQL?
No problem - it's really gratifying to know that it helped you. I'm not so interested in monetizing - I've received an awful lot from the wider OSS community, and I did the project as a way to give something back.

That is a really good idea on the app. I do have some ideas for making DDL and DML available via the web interface, but sadly I'm extremely short on free time in the last couple of years.

AlisdairO··on Ask HN: What is the best online resource to learn advanced SQL?
[self plug]

On https://pgexercises.com I focus quite a bit on developing a mental model for SQL. I would suggest not skipping the easier exercises - even if you can do the SQL, you might find the explanations useful.

AlisdairO··on Yubikey with USB-C
The Yubikey Nano is pretty great in this regard - doesn't stick out enough to really worry about breaking.
← PreviousPage 4 of 26Next →