Learn how to really use a relational database, relational data modeling, and SQL. Not knowing of their capabilities may lead you to unnecessarily complicating your tech stack. You can go a really long way with just this domain of expertise. From there, do the same with whatever key-value store interests you (for me, it's Redis). Python isn't known for high performance but when a django web app uses a cache and relational database effectively, it can achieve a very acceptable peformance. Case in point: the Zulip chat platform: zulipchat.com.
Aside from the database domain, I really enjoy using Rust and recommend it as the next language for anyone to learn, but only after taking time for in-depth relational database training. :).
> Learn how to really use a relational database, relational data modeling, and SQL.
I have to second this. There is so much power in relational databases that is untapped by most developers. The best part about this is that, for the most part, this type of knowledge can apply to multiple databases.
Some specific things that I want to gain a deeper understanding of are window functions[0] and recursive CTEs[1]. In particular, I've used window functions to identify peaks in sensor data (e.g., finding spikes in temperature, water level, etc), which would otherwise require iterating through rows and maintaining a bunch of state. I've never actually written a recursive CTE, but I'm pretty sure it would simplify virtually anything dealing with a hierarchy.
In the first 15+ years of my career I never used or understood recursive ctes in sql. Then finally learned it and used them multiple times in last year or so.
They can be incredibly helpful once you grok them! And recognise when they can be used.
I'm not sure recursive CTEs simplify things. Yes, they allow you to express Turing-complete computations in SQL, including indefinite and parallel iteration. But if you weren't going to express those computations in SQL, you'd probably express them in Python or Lua or JS. Is doing them in SQL really going to be an improvement? So far I have not impressed with the results.
My experience has been, especially if your application layer's in a scripting language, pushing as much data-fiddling to the DB as possible will save you serious performance headaches down the road, even if the application layer implementation looks OK at first. They're all really slow and memory-inefficient, and often moving that stuff to the application layer also means more queries (else, typically, why not do it in the DB?) which means more network latency, which an be a real killer. In a lot of cases fixing the performance means, at the very lease, re-implementing a bunch of what your DB already does to support fast & efficient data manipulation.
I've also seen the application change on top of the DB way more than I've seen the reverse, so I'm inclined to avoid putting data manipulation in the application when possible. That way it's there, for free, when we need a second application to access the same thing, or when we break off some chunk of a program into its a separate service and re-write it in Go because it turns out to be a performance bottleneck, or WTF ever.
Third- for an analysis project, I had to look ahead to the next transaction. Tried a bunch of implementations, ranging from offset self joins to iterators to recursion.
Turns out the fastest and simplest solution, by far, was a mix of window functions and CTEs directly in the database.
I inherited a code base that has major issues stemming from the developers not realizing what postgres can do.
It's reliant on Kafka with a bizarrely complex queue system for answering questions from users automatically in a distributed, scalable fashion. It has schema-less messages shooting around referencing database rows and tables with zero certainty that anything will exist when the message arrives. It works, but it's a real mess and it breaks remarkably easily.
There's nothing about it that couldn't have been done more easily and perfectly scalably enough with a single database. The product will probably never be large enough to need something like Kafka.
I really, really agree with you. Better database knowledge would have put us weeks ahead on this project already, and it's still very early.
My thinking here is that had the original devs had a better grasp on redis and postgres, they never would have tried using Kafka in the first place. I can't imagine the problem ever requiring the throughput of Kafka, and there would likely be several other scaling issues in the way of utilizing Kafka to its full potential anyway.
I'm pretty sure a redis-based queue like Bull (https://github.com/OptimalBits/bull) would have sufficed for queuing message responses directly on the server (or multiple instances of the server), and while Kafka works fine for long term storage of logs, our use case for the data makes it so it would be far better stored directly in postgres.
Postgres is apparently also a decent pub/sub solution, though I'm not sure if it's superior to Kafka in this case.
The worst part is that the alternative architecture using a redis queue and postgres for message history is very simple, easy to maintain, benefits from the ability to normalize data, and is comfortably boring. Kafka is not that. It's a miserable beast sometimes, and it presents hurdles all the time for many of us. It's good at what it does and people should consider it (or Pulsar) if their problem requires a high throughput message broker. For everyone else, it's a really risky investment for small or no returns over alternatives. It's the worst decision the developers made in this application by a wide margin.
Since Zulip was mentioned, I like to point folks who are interested more to the Architecture overview docs of Zulip.
The docs has details on how Zulip make use of Django, PostgreSQL, Redis, Tornado, RabbitMQ etc for building a scalable chat application.
Zulip is Open Source, so do take a look at our GitHub page if you folks want to dive deeper or want to get some hands on experience. We are a welcoming community to new contributors :)
What do you recommend for aggregating and scraping the data? I’ve been working with PyCharm and BeautifulSoup4.
Also, any suggestions for the best ways to apply the data to a website if the data is being refreshed daily? I’ve been using csv files to pass the variables into a Wordpress theme / post but it seems like building something from scratch would be more efficient in the long term.
As was said by one of the original Twitter architects, defending the choice of Ruby against people who were saying that it was at the root of all of their performance problems, for any well capitalized company, the language rarely makes a difference.
A stateless web server is a “embarrassingly parallelizable”. The speed or lack there of your runtime is usually not a make or break business decision.
That's all fine if you stay stateless. Once a well-meaning developer introduces local application state into your web app or adds a feature that locks your database, your web server is no longer "embarrassingly parallelizable". This doesn't even start to handle issues you get when you use a single-threaded langugage that cannot handle multitasking well. Sidekiq makes money purely because Ruby is single threaded, and its thread will lock if you give it a task that takes too long.
The microservices movement seems to be a misguided reaction to these self-imposed issues where instead of handling proper task management on a process level or with async/concurrency, functionality is split between servers, codebases and infrastructure. This problem was solved with Erlang decades ago with the actor model and supervision, and newer BEAM languages like Elixir and LFE are a pleasure to work in.
You even have this model and concurrency ported to JVM with Akka, C++ with CAF. Granted, the Actor model and the field of concurrency as a whole is solving the problem of enforcing statelessness in a way such that tasks can be efficiently distributed multiple cores, and that no single task locks up your machine for too long.
Once a well-meaning developer introduces local application state
And this would break the minute you have more than one web server. How many websites of any consequence are running on only one web server?
Having server side session state that can be shared across servers is a solved problem as is having a load balancer that handles “sticky sessions”. I’m not saying either is a good idea.
Also if you are “locking your database” even if you use a faster runtime, you’re just delaying the inevitable of your scaling limits.
Maybe you don't understand persistent background jobs or maybe I don't understand Erlang.
What happens if you have a bug in a task and it takes a week for your development team to develop a fix? Does that Erlang task live in memory all that time? That's the point of Sidekiq's retry subsystem and persistence in Redis.
Ruby is multi-threaded. My customers buy my commercial versions because they want the more complex features and support.
So your proposed solution is to throw more hardware at the problem? It surely can work at the small scale, but why do it when you're talking about hundreds of thousands of dollars / mo in servers? Why not choose a proper high-performance language, at least for the parts that are slow?
> So your proposed solution is to throw more hardware at the problem?
Yes. It's usually cheaper and better for the business, as has been proven time and time again. There's a reason the phrase "cheaper to throw hardware at it" is kinda a thing in our industry. It took Facebook a LONG time and many hundreds of developers before they needed to create HipHip/HHVM.
Even at a small startup, my team of 6 costs over $1MM/yr while our two dozen or so EC2 instances and other AWS resources cost less than $25k/yr. Hell yes I'm going to throw hardware at it.
Do you know how much hardware you can buy for the fully allocated cost of one developer?
In reality how many companies in the world have hundreds of thousands of dollars a month in servers?
Why not choose a high performance language? Maybe it’s easier to find developers in a certain language, maybe the developers are cheaper for a certain language or it may have a better ecosystem.
If I just needed a simple CRUD app and thought I could get a lot of cheap developers I might choose PHP (hypothetically) because I know PHP developers are cheap.
Do you have any examples of a good book that will take me from intermediate to advanced? Most of the guides I’ve found online either assume you’re an absolute beginner or already quite advanced. I’m quite competent with SQL and relational databases, but nearly all of my experience is on Microsoft SQL server. I’ve heard postgresql has a lot of really cool functionality but I would really love a nice, professional, in-depth book that will help me get fully up to speed.
I'll highly recommend "A Curious Moon" by Rob Conery [0].
In this book you'll load Cassini space mission data from NASA into Postgres and analyze one of the Saturn's moon. I learnt a lot about Postgres and also about satellite data.
My first day of Postgres experience is that the first query to fetch 1 record takes 5 seconds and subsequent queries take 50ms. There are a 1000 explanations as to why, but I have no idea which is correct. I hate Postgres. I heard it does JSON or something well.
+1 for relational models. All data access is relational in some way, and knowing how to efficiently access/index the data is an important step for building efficient applications.
I've come to a realization. Domain data is basically always relational. Configuration data may or may not be. Where it's not, NoSQL is good for configuration data.
What helped me a lot getting away from being limited by ORM capabilites while on the job:
- Get the raw SQL of some slow or memory intensive queries the ORM produces and try to optimize them by hand. Try different approaches to get the same result, measure and understand them by using 'explain' and visualizers like http://tatiyants.com/pev/
This works great together with libraries like sqlalchemy where the ORM is optional and build upon a abstraction of SQL that you can use directly. This way you can use the ORM for the 80% where it works just fine and hand write the rest in Python without having to deal with raw, fairly inflexible SQL in application code.
- Try moving workload from the application to the database. In the past I often ended up doing ORM queries to get a large numbers of objects and then further process and even join them in Python. In Most of these cases doing it in the database is way more efficient and lets you get away with a slow language, synchronous requests running on small servers for a surprisingly long time.
- Do business intelligence type queries for reports and monitoring. Through doing this I discovered a lot of database features that I didn't encountered commonly in web application development but that nonetheless came in handy several times for it. Also since you often need to combine data in ways that it wasn't necessarily originally designed for, you really need to start thinking about how your data is structured and how to get it in and out efficiently.
- Don't immediately dismiss relational databases for tasks where they might not be the infamous "best tool for the job". Chances are, that the relational database you already have in place is good enough for your use case and that it will safe you headaches of setting up, understanding, synchronizing and maintaining a entirely different db system. E.g. Vanilla Postgres for timeseries data worked just fine for us for years before moving to a more specialized solution with TimescaleDB. Also used it with success for non-relational data, simple graphs, key/value stores and for queues.
ORMs are great for fast prototyping in my opinion. With Django and its ORM I can build a web app in a couple of days. Scaling is of course a totally different challenge.
I don't have any good resources on the data modeling side, but on the SQL side the PostgreSQL manual[0] is really good. Even when I'm working with an Oracle database, I often find myself looking at the PostgreSQL documentation.
Using Python for high-performance anything is a bad choice. You will quickly bottleneck at the code execution speed, even if it's just to query some cache.
If you don't agree, make a simple "hello world" endpoint and see how many req/sec you get. Then compare to Rust / Java / C++ / Go. It will be radically different.
Aside from the database domain, I really enjoy using Rust and recommend it as the next language for anyone to learn, but only after taking time for in-depth relational database training. :).