Really curious to know your single, most important database tech? SQL or NoSQL? Postgre, MySQL, Mongo, Elastic, Redis, ...? Are you happy? Would you choose differently today? Thank you for sharing!
SQLite for little apps that are easily deployable, and PostgreSQL for more serious applications that would benefit from its extensions.
I haven't changed that setup for years, even when people tried to convince me that Mongo was the superior way to store JSON, which I do in Postgres very easily.
(I also don't need Redis because most of my applications are in Erlang/Elixir, so we already have a bunch of Key-Value solutions available)
Single most used is PostgreSQL with Redis as a cache store for most cases.
But the more nuanced answer is, it depends on the project and the goals. I have used MSSQL, MySQL, PostgreSQL, Mongo and Cassandra all as primary data stores. I have utilized ElasticSearch, Redis, couchbase and memcached for specific caching or search purposes but never as a primary data store as they aren't designed for that IMO.
Today, primarily I rely on PostgreSQL as a default, with other databases used for specific purposes. I follow a different convention than the one large database for everything, even if I have a monolith application. I like to use small independent databases that are designed for the specific type of workflow for the task. e.g. user auth has different needs than say logging data or any high write low read datasets. Also, I personally like this because it forces defined lines in the application space for where data resides and the intended uses.
I think the thing I have learned to be better at over the past say 10 years especially, is to use the right datastore for the right job, versus a one size fits all. At the same time, I still try to keep the number of different type of data stores to the shear minimum required. So sometimes it maybe multiple instances of say PostgreSQL but each setup differently based on workload and access needs, or maybe it is PostgreSQL and Mongo for the strengths of each. Redis is almost always my caching engine now for any data stores.
In the 80's and early 90's nearly everything was file based in small companies (e.g. we wrote our own data stores), then we moved to ISAM then SQL in the mid-90's. In the 80's and early 90's SQL was out of the reach for a lot of smaller companies, license cost + dba etc. In the mid to late 90's we got distributed object stores (the first real NoSQL databases IMO), like Versant (still around) which we used too.
Would be great for you to mention what kind of tweaks you do in postgres depending on the workload. Really curious about which settings to optimize for what.
To be fair there are people far more of an expert to provide guidance on postgres tuning, but here is a slide share (near top google result) I have used for some high write ideas: https://www.slideshare.net/GrantMcAlister/tuning-postgresql-... . Not advocating everything said there, but it has quality points to utilize.
The idea, is for example on high write loads, I don't really need to index for read, and indexes in general will cause slower writes in any SQL system. In addition I might choose different types of disk to store data on depending on durability and load. High write, SSD optimized (with high IOPS) for write and lower durability is probably ideal, yet for high reliability needs, high durability magnetic slower disks is likely a better choice. Stuff like that makes a huge difference, in fairness AWS (and other cloud providers) make it easy to make the choice today, but it makes a huge difference in performance capabilities and so many people don't understand the details of the selection they make.
This also leads us to adjusting the parameters for the Postgres install, which of course if you use RDS or similar you are more limited on, but it is still critical to weigh.
If you have questions in specific I am happy to try and help or I will ask one of my teammates to respond that has more specific knowledge. As an example, my consulting team a few years back scaled a Wordpress install using MySQL to millions of requests but did so using EC2 instead of RDS and used way less resources because we tuned the database and queries to be efficient for the hardware. It goes back to the old adage, for a 10x increase in hardware, there is likely a 100x increase in software if you just look. Our client at the time kept upgrading the hardware on AWS to the next larger instance for RDS, and we instead moved off RDS to EC2 and then tuned the instance and MySQL install to be more efficient for their specific workload. Obviously highly tailored to their specific use case but we saved them a HUGE amount of money and still provided the same scalability and reliability.
4 comments
[ 5.1 ms ] story [ 22.1 ms ] threadI haven't changed that setup for years, even when people tried to convince me that Mongo was the superior way to store JSON, which I do in Postgres very easily. (I also don't need Redis because most of my applications are in Erlang/Elixir, so we already have a bunch of Key-Value solutions available)
So yeah, pretty happy :)
But the more nuanced answer is, it depends on the project and the goals. I have used MSSQL, MySQL, PostgreSQL, Mongo and Cassandra all as primary data stores. I have utilized ElasticSearch, Redis, couchbase and memcached for specific caching or search purposes but never as a primary data store as they aren't designed for that IMO.
Today, primarily I rely on PostgreSQL as a default, with other databases used for specific purposes. I follow a different convention than the one large database for everything, even if I have a monolith application. I like to use small independent databases that are designed for the specific type of workflow for the task. e.g. user auth has different needs than say logging data or any high write low read datasets. Also, I personally like this because it forces defined lines in the application space for where data resides and the intended uses.
I think the thing I have learned to be better at over the past say 10 years especially, is to use the right datastore for the right job, versus a one size fits all. At the same time, I still try to keep the number of different type of data stores to the shear minimum required. So sometimes it maybe multiple instances of say PostgreSQL but each setup differently based on workload and access needs, or maybe it is PostgreSQL and Mongo for the strengths of each. Redis is almost always my caching engine now for any data stores.
In the 80's and early 90's nearly everything was file based in small companies (e.g. we wrote our own data stores), then we moved to ISAM then SQL in the mid-90's. In the 80's and early 90's SQL was out of the reach for a lot of smaller companies, license cost + dba etc. In the mid to late 90's we got distributed object stores (the first real NoSQL databases IMO), like Versant (still around) which we used too.
The idea, is for example on high write loads, I don't really need to index for read, and indexes in general will cause slower writes in any SQL system. In addition I might choose different types of disk to store data on depending on durability and load. High write, SSD optimized (with high IOPS) for write and lower durability is probably ideal, yet for high reliability needs, high durability magnetic slower disks is likely a better choice. Stuff like that makes a huge difference, in fairness AWS (and other cloud providers) make it easy to make the choice today, but it makes a huge difference in performance capabilities and so many people don't understand the details of the selection they make.
This also leads us to adjusting the parameters for the Postgres install, which of course if you use RDS or similar you are more limited on, but it is still critical to weigh.
If you have questions in specific I am happy to try and help or I will ask one of my teammates to respond that has more specific knowledge. As an example, my consulting team a few years back scaled a Wordpress install using MySQL to millions of requests but did so using EC2 instead of RDS and used way less resources because we tuned the database and queries to be efficient for the hardware. It goes back to the old adage, for a 10x increase in hardware, there is likely a 100x increase in software if you just look. Our client at the time kept upgrading the hardware on AWS to the next larger instance for RDS, and we instead moved off RDS to EC2 and then tuned the instance and MySQL install to be more efficient for their specific workload. Obviously highly tailored to their specific use case but we saved them a HUGE amount of money and still provided the same scalability and reliability.