I do Django freelancing and have had a fair amount of small clients with relatively few users. Adding workers and a message broker adds a lot of complexity over just a single Django server. I even had the thought to build something like this but looks like someone already did! Thanks for sharing this, looks like a great tool to know about.
Why does the article talk about client complexity, then say Postgres can be used for everything, but then not address client complexity. Does Postgres have a nifty server-side rendering pipeline that could replace front-end complexity?
You joke, but I have literally never had as great of a success at work as that one time my team didn't end up building the software.
We had great ideas about scheduling and caching and task priorities...
...and then we asked the customer what they wanted, and they wanted none of it.
So we built none of it, and produced a solution that just did the stupidest thing, and did it without any edge cases, without ever crashing, reliably, as a cronjob, once on Sunday night.
Some people would be disappointed that they couldn't put this on their CV because it didn't involve FancyTech #413, but damn it, I am still proud of that stupid thing.
I interviewed a (junior) candidate once that had tried and failed at a hackathon to build an Rails system that connected restaurants and shops with excess food to charities that gave food away to those that needed it.
I asked him what he’d done to work around the technical difficulties, and it turned out that he’d set up a Wordpress site with a phone number of the guy running the scheme, and a Google sheet to manage contact details.
My take is that there's many unnecessary complexities (at least in many cases), where using an old, battle tested, "boring" technology suffice. The graph just seems to be an illustration of that, but it focus on storage (there could be a better graph that focus on the database only, but I guess the message is conveyed)
As long as you have good interfaces in your code to make it possible to swap out Postgres to more specialized components when needed, I think it’s a good idea.
Saving ops resources by managing fewer services when getting started is a good idea, until scale necessitates dedicated technologies for certain components.
I wouldn't even worry about coding to interfaces. When you need to replace Postgres because you have more than 1M users, you will probably have the revenue to refactor your code to swap out Postgres dependencies. Because you have good integration and E2E tests, right?
That will make you lose a lot of productivity boosts SQL can give you.
Obviously, if you anticipate to have very high traffic and applicable usage patterns, do use that advice, but if your apps anticipated usage pattern is in fact not "very high rps per buck made", then I recommend the opposite.
I've went the whole way from very-well-abstracted-away services/repositories to an almost complete lack of abstraction.
Direct SQL or ORM in your functions, operating on many models at once, with a real postgres database available to all your unit tests, treating the SQL code as part of your application logic. Transaction per test so that unit tests are fast to run.
I've been very happy since. SQL is very powerful if you use it as SQL and not as a glorified KV store.
To me, good modularity at the data layer doesn't mean abstracting away Postgres or even SQL. To me, it means having a separation of concerns between loading data, writing data, and _processing data as domain objects_. Don't have your core business logic operate over database rows, but in-memory objects. You can test the loaders/writers separately to your actual logic at that point.
> Don't have your core business logic operate over database rows
This is the part where I don't agree, as SQL is very expressive, and needlessly loading data to your app is also not performant (making the transition to something else required sooner).
I think writing parts of your business logic in SQL that make sense to be written in SQL is just fine.
If you only use it to load and write entities, then that is basically a glorified KV store.
Often writing abstract interfaces costs more then switching things out by changing your code. So it's often better to keep concerns separated and make it so that code is easy to change, but creating abstract interfaces is often not worth it for a lot of products.
C++ is heavily customized C. The heavy customization make Redshift a columnar database and more ideal for querying large amounts of data quickly. How does Timescale help Postgres in this area?
Timescale is built around a concept they call "hypertables", which automatically partition data into a set of smaller tables segmented by time range. Timescale exposes the time-series data as if it was a single table, but behind the scenes is managing queries against the individual table partitions and automatically creating new partitions as data is inserted.
By tuning the chunk sizes so their data fits in memory, many common queries gain a lot of efficiency. It's built around some assumptions of time-series data: Most inserts and queries are for recent data and are generally ordered.
I've had great experience with TimescaleDB for small-medium time-series loads such as sensor or analytics data; I've found it's pretty plug-and-play and have used it to store tables with ~1B time-series rows of geospatial data, sensor values, etc.
I think you should keep complexity down by only using postgres, but just use it as a sequel database until you scale enough that it's a problem.
90% of the time it's never going to scale that far.
When it does, use the tools people have made to do those jobs correctly. Do not hack you way into half-made tools put into a database that isn't specialized in that job.
It seems like you're making your life more simple by having only one system, but you're having that one system to so many things at the same time that it's going to be an absolute kludge in the long term.
Does anyone have experience trying something like this? If you did, is this how it turned out or did it actually work all right?
Its somewhat situation dependent, but I've developed healthy systems that scale to surprisingly high throughputs with just a couple well designed sql databases, read replicas and memcached.
> A complex system that works is invariably found to have evolved from a simple system that worked. A complex system designed from scratch never works and cannot be patched up to make it work. You have to start over with a working simple system.
A SQL database, maybe supplemented by a cache, can carry most projects as far as they'll ever go. And if you outgrow it, replace it with something that meets your new needs.
Any given project's "right tool for the job" can change over the project's lifetime, and optimizing for problems you don't have is quite harmful.
Yep, let’s not forget that your “simple” Postgres cluster gets complicated really fast when you start having to graft layers of tools on top of it. There’s a reason why Spanner and DynamoDB exist. God forbid someone try to use Postgres to solve the same problems and actually get the configs wrong leading to years of inconsistent data. Definitely never seen that happen…
I second that. SPs/funcs have this weird tendency to always stay hidden in the fringes and out of sight, easily forgotten when adding new functionality, easily overlooked when making changes elsewhere.
From my experience only if there are dedicated DBAs and you have too many systems running - then you forget one. If you only have server code and the stored procedures in the same repository, with migrations, this problem goes away.
> If you don't have the discipline to do these things then they are likely best avoided.
I'd go further and say you should avoid databases and maybe even persistence entirely if you don't have the discipline to do the above. Sprocs will be the least of your problems otherwise.
Yeah, I think so. But my hunch is that the majority of people who tell you never to use stored procedures have been burned by these techniques not being used for them.
Before the development of decent migration systems it was incredibly common for database structure - including stored procedures - to be treated independently of source code in a repository.
True, of course. There were also undoubtedly a lot of production systems that didn’t even use version control for non-database code. Industry practices certainly evolve over time. But it’s difficult to imagine a scenario where a team is aware of version control, uses it for the things they realize are code, but somehow doesn’t realize that stored procedures are code.
These sorts of places also tend to have database admins in one team and programmers in another team. All database changes go through the database team with tickets or whatever. It's a huge pain in the ass to navigate and enable quick changes.
It's a common thing to miss. There's a reason SQL injections are (unless things have changed recently) among the most prevalent classes of web exploits.
> SPs/funcs have this weird tendency to always stay hidden in the fringes and out of sight, easily forgotten when adding new functionality, easily overlooked when making changes elsewhere.
This is the classic "carpenter blames his tools for crappy results" argument. Implementation isn't easy.
If the developer doesn't know / doesn't document the project has code embedded in the database, that's on the developer, not the tools. Because the use of any developer tools requires a certain level of competence in order to use them successfully.
I thought so for 30 years but changed my opinion recently. I even argued with the author of Redis for some time to add some functionality so we didn't have to write Lua and have another deployment target.
Now I do think there is a benefit in stored procedures and triggers (E.g. for audits) if they don't contain too much logic or complexity.
> if they don't contain too much logic or complexity.
I think this is the catch. Most folks who are arguing against SP have been burned by huge complex stored procedures with nested dependencies with deeply intertwined business logic and rules. I completely agree that you shouldn't use a SP in that way. But to help perform maintenance, or to audit, or perform data correction all make sense when kept small and simple.
I've only given up trying to understand a system once. It was when I was handed over an application that used stored procedures for everything. Including recursive stored procedures... The rest could be figured out, but they were just too much.
I feel this. Once had something locking up a production SQL Server instance, and it turned out to be a dreadful partially-recursive web of sprocs, views, and TVFs that worked fine until apparently one day the query optimiser decided otherwise. Spent hours tracing what the heck was going on.
Why? What is your specific reasoning? Using EXPLAIN and SP's to help fix cache misses, slow queries, poor index performance, etc. is generally considered a good thing.
As a side note, I did not realize $diety was concerned about DDL/DML, so thanks for pointing it out. I never really thought about it.
Hard disagree with this attitude but I see it all the time.
Stored procedures are much faster than writing logic in some remote server (just by virtue of getting rid of all the round trips), require far less code (no DAOs, entities and all that crap which simply serves to duplicate existing definitions), and have built-in strong consistency checking primitives - which can even be safely delayed until the end of the transaction.
And what people do is, they throw all these advantages away because they can’t be bothered working out how to integrate the stored procedure code ergonomically into their workflow.
I mean - I even use an IDE (JetBrains) to write pl/pgsql. It’s just another file in my repo. Get to this point and stored procedures are a game changer.
Stored procedures has some advantages (fast to debug/try out a query from your service without copy+paste all the time, etc), but also disadvantages (unreadable git diffs, big bang rollouts on changes)
I have done this, and the one thing I would note is that if you are requesting a maximum number of rows per call, and if something in the queue breaks for all returned rows (like because it crashed with an exception or something and its status field isnt updated), then you can run a situation where every call to get more rows goes to the same broken rows and the queue becomes blocked. I hadn't figured out a great solution for this but otherwise my SKIP LOCKED queue worked great!
It's fine for streaming, i.e Spark and friends.
I generally define streaming as the success of each individual message is entirely uniform, i.e the system can either process messages or it cannot. If this holds none of Kafkas drawbacks should significantly affect you.
When people talk messaging they normally mean something lower latency, potentially out of order and where the success of each message is likely independent of other messages. For these use-cases Kafka is not well suited. For these cases you will want something like Pulsar, SQS, PubSub, etc.
Because it's really a log and it's partitioned it has a whole host of issues that make it a very poor messaging system like hot partions, head of line blocking etc.
If what you want is a proper messaging system where the underlying model is still a distributed log then you should look at Apache Pulsar. It can provide the same semantics as Kafka but can properly implement job queues and messaging semantics ala SQS, Google PubSub, et al.
I spent a decade using SQL Server for everything (back when Postgres wasn't close) and it is an exceptionally effective strategy. Deployments are simple, debugging is simple, operations are simple.
Do that until scale forces you to start specialisation.
It's surprising how far you can go with database-as-MQ. Even database-as-IPC can work for smaller systems.
How do you handle high availability? Ideally I would want to not lose many transactions and have automatic failover. SQL Server has that, but it not the default config, and it becomes expensive af.
A lot of startups are convinced they'll need Google scale from day one. Then, of course, the overwhelming majority fail in the first year.
Get a big, reliable, and cheap vhost/server somewhere, use as many "can't really go all that wrong" components like postgres, minio, etc and dockerize everything. If you want to get "fancy" use ZFS and setup some snapshots and backup. Most solutions don't even need 100% uptime. Communicate maintenance windows to customers and you'll be fine with a total of an hour of downtime (or whatever) in the first year. Most big, really complex, early over-engineered and unnecessarily "optimized" solutions have enough footguns you'll probably end up with more unscheduled downtime in the first year anyway.
In the rare event the startup really succeeds and customer demand, load, uptime requirements, etc demand it you can throw revenue/funding/etc at a K8s control plane on your favorite hosting provider, use a managed postgres/db/whatever, and S3 compatible object store, etc. Or, if things get really big skip all of that and hire in house talent to manage a couple of racks of leased hardware (same opex as cloud but almost always SUBSTANTIALLY cheaper) in geo redundant/distributed colocation facilities.
I've launched multiple startups with this strategy and it's gone very well. My current startups all run from the same big (but 10yr old) hardware that has loooooong since paid for itself even with lots of GPU, storage, etc upgrades over the years. People can be kind of scared of hardware but I've never had downtime or data loss caused by a hardware failure in almost 20 years of this approach.
People are always amazed when I do things with ML, TBs of data, lots of bandwidth, etc and I tell them my total hosting costs are $150/mo.
> My current startups all run from the same big (but 10yr old) hardware
I'd love to hear more about the setup. I suspect something as simple as disk failure would cause outage, although I suppose you can detect a soon to fail disk via SMART and resolve that with scheduled maintenance/downtime. But what about power supply failure? Do you keep redundant backup parts on hand?
There's definitely something nice about having a hardware error on a cloud VM result in that VM cycling out to new hardware automatically. In contrast, something as simple as buying a new off the shelf PSU feels like a ~1 hour downtime event (longer of you don't have purchase card authority, it's night time, you need to order online, etc.).
I like to use ZFS raidz2 at a minimum. More or less bulletproof from a storage/hardware standpoint.
The system I referenced currently has x8 4TB NVMe drives on ZFS raidz2 and x8 16TB rust drives also on raidz2. I use sanoid to snapshot like crazy (down to 15 minutes) and then syncoid to push snapshots and then some to the spinners ZFS array. Plus zfs send remote to offsite.
Modern switching power supplies are incredibly reliable. Then do proper “half load” dual power supplies from dual conditioned power feeds with UPS and generator. Losing power to a machine almost implies an extinction level event or complete incompetence on the part of a data center operator.
But I'm on a single medium sized Linode and doing full-text search over 120M small (< 1KB) to medium (< 10KB) text documents and it's still so fast I'm not sure that I will ever need to consider an alternative.
I use Patroni behind HAProxy setup to automatically point to the new active host in the case of a failover. It's not a huge scale setup though so I'm sure there will be better ways or other input on this.
1) Autoscaling. Adding a new reader takes 15 minutes. Instead of provisioning for peak traffic and paying for it 24/7, run an extra reader for a few hours each day.
2) Metered IO. Aurora IO can handle 400k+ iops when needed. Or it can hum along at 100 iops. You pay only for what you use, you don't have to provision for peak load 24/7.
Switching to Aurora saved us money over vanilla RDS. Self hosting postgres may be cheaper, but not by as much as would first appear.
It's been a while now but we saved about 10-100x moving from aurora to dynamo for a pure/simplish OLTP use case. Lost a lot of flexibility but the aurora costs were going to send us under.
We also had a few issues where aurora was giving us *incorrect query results* and aws support were not helpful. Most of the issues were denied, even with simple reproductions, and then got fixed years later (~2-3) after we had given up. We still use it today but stay away from cutting edge features, unusual query patterns, and high volume use cases.
Having said all that, SQL still seems like the correct choice for getting an MVP out quickly.
The main thing I like about MySQL-based solutions is the availability of Galera multi-master software, which for simple configurations is very easy to get going. Drop a fairly cookie-cutter config on each host, and you're done:
Combine with keepalived on each node which does a health check, and if one goes down/bad, the vIP is moved over to another host in the cluster with minimal fuss.
I've gone this route before and debugging your system can become problematic, especially when using adapters to format tables as JSON. But it works and the dev speed is unparalleled.
Just use the right tool for the job at hand. If it’s Postgres for now, sure. There’s really nothing more to this. Period.
But saying this would not make for a blog post. I can’t help but categorize this post as juvenile at best. Generalizations like “use XXX for everything’ should be avoided at all costs. No software product serves well to such sweeping generalizations. I’m surprised that it’s coming from a CTO. They should know better if they’re worth their salt .
It's often better to use the tool that is capable but isn't the absolute best possible option to solve multiple problems, rather than taking on the burden of running five different "best" tools for five different problems.
If you are at a startup, sure. But many of us work at big companies that have the scale that requires specialized solutions. I just frustrated that everyone always seems to assume everyone is working on POCs at startups.
Somebody just jumping on a new tool thinking it's the right tool for the job might still end up with worse performance than someone proficient with another tool (like Postgres) might get in less time.
No matter the scale or how big it is.
If you are talking of specialized solutions, that means you know exactly where an existing, well known solution is failing you and why.
Eg. if you drop all foreign key references and constraints in Postgres, you might get similar write performance to other databases which can't make those guarantees when you do need them.
I've worked at big companies, where I have championed introducing "the right" technology for scale reasons... and have in some cases later regretted it because with hindsight we would have been fine sticking with what we had already, at a greatly reduced cost in terms of time and complexity.
It comes from a CTO (me ;-) coach who has seen dozens of startups entangle themselves with systems until they work more on tech than on delivering features - or who came to a standstill after VC driven layoffs b/c of complexity of their systems.
"Just use the right tool for the job at hand."
Yes, but I have heard exact that phrase for decades to rationalize tech decisions for tools that weren't needed.
I know you're different, but think of all the people who have the same problems.
If you need complexity b/c you're Netflix or Uber, go ahead. If you're the 95% others who don't need that complexity, then don't do it.
"I can’t help but categorize this post as juvenile at best."
Thanks, I guess this is the nicest thing to say to someone 50+!
> Thanks, I guess this is the nicest thing to say to someone 50+!
Your username is also juvenile, well done you!
Fwiw, my experience has also led me to be on the side of "just use Postgres" unless required otherwise. I've seen enough people glue whatever crazy technologies together where a relational database would've been enough.
It was Codemonkeyism before (𝅘𝅥𝅮 "Code Monkey think maybe manager want to write god d** login page himself") but I thought I had grown to be king of the asylum.
"Right tool for the job" is as foolish as any other approach: this discounts the experience someone or even a team might have with another tool that is not a perfect fit, but might still do the job just as well. If you ignore this and instead go with a different tool for every little thing, your project will be a dependency nightmare no developer can master easily.
Postgres is an all-purpose database that has it all: relational databases were created to model real world problems, and while they might not perform best for all the usecases, they can usually model them just fine while protecting from many programming errors.
And then Postgres has features on top: partial indexes, object DB features, replication etc.
> Use Postgres to generate JSON in the database, write no server side code and directly give it to the API.
Who does this? "give it to the API"... ? Still sounds like there's "server side code" there if there's an "API" involved. Surely they're not meaning let front-end code hit the database directly?
It's possible to have Postgres serve HTTP, actually. I've seen many experiments like this. I don't remember the name, but... actually... Yeah, thanks ChatGPT - It's called PostgREST: https://postgrest.org/en/stable/
I've obviously not used it and to be honest, I probably wouldn't. But IMO Postgres is a radically different type of framework. People just think of it as a SQL database, it can be much more though. Tooling is just a bit lacking.
Edit: Heh, Retool is sponsoring them. "PostgREST for the backend, Retool for the frontend". Cool idea. I might reconsider...
PostgREST is an external web server; postgres isn’t serving HTTP, though its effectively doing everything else that the backend requires besides serving HTTP when you use PostgREST.
Yes, postgrest isn't the one I was actually remembering that DID serve HTTP itself. I know there was an extension that did this though.
It doesn't matter anyway. Serving HTTP is not a problem particularly well solved by Postgres, and needing an extension instead of whatever hyper-optimized http server you usually use is increasing complexity, not decreasing it.
You can go even farther and generate an entire GraphQL api with row level security, custom functions, etc. I've had a great experience developing with Postgraphile - https://www.graphile.org/postgraphile/
In this particular case, it would actually have been slower. I can't phrase things on Google like I do on the chat; I first have to distill my query down. And when I actually am trying to remember something, it's easier for me to be descriptive, because the keywords Google needs can be the important ones I forgot.
PostgREST is page two of "postgres http server" for me.
> Surely they're not meaning let front-end code hit the database directly?
In this case they mean "let Postgres generate the JSON for API responses" instead of having the API interface do it. The "Generating JSON in Postgres" section in the linked article shows how this is done.
"let Postgres generate the JSON for API responses" would have been a far less confusing way of expressing that idea. I had read the linked stuff and none of it looked like front-end code hitting a PostgreSQL db directly.
yes, if you can create some JSON without having to transform in an intermediate code layer, that's handy/useful. I often don't run in to too many scenarios where things are trivial enough to allow for that - there's usually some app-level logic that comes in to play with respect to visibility/permissions/etc.
Oracle database can do all of these things as well, and they even have a complete low code development and hosting environment running in the database, Oracle Apex. I know there are a couple of companies that actually serve their home page straight from their oracle database.
but it's also oracle db i.e. expensive, especially expensive support and tones of hidden annoying complexity not publicly well documented. For most companies doing the same in Postgres is just cheaper even if there is a bit less low-code provided out of the box.
Sure, my go to database is also Postgres rather than oracle. Good enough for most things, without the downsides of the company oracle. Just wanted to highlight that Postgres isn’t unique here, and that serving http from a database is actually not that special.
> Surely they're not meaning let front-end code hit the database directly?
Why not? You can make Postgres speak SQL or GraphQl it removes an additional network indirection and there are very clear ways about how Posgres does scale well and where it doesn't scale well. (EDIT: Or put a dump translation layer/service in-between they exist to and are cheap to run and manage.)
You can write your core logic in JS, Rust etc. and plug it into your database as virtual tables or shared procedures (it's surprisingly simple).
Any DDOS protection and similar is anyway in front of whatever you write as some form of proxyish thing.
And just needing to scale Postgres instead of a bunch of different systems is so much more simple. Depending on how you run it and what input characteristics you have it might even be more efficient and cheaper to scale that way (or it can be noticeable more expensive) and it's most times cheaper to manage.
If the suggestion truly is to try squeeze as much utility as possible out of a single RDMBS setup, and if you already need and use an RDBMS, then I would consider it as an option. If not, it's an absolutely idiotic choice. And that's the problem with this article - it really comes of as a suggestion to supplant your Redis setup, a dedicated key-value store, with Postgres, a full-fledged and highly complex relational database.
Because it is not a cache. High entropy tables require a lot of vacuuming, which adds overhead to actual database operations. While you can set autovacuum parameters per table, they still are not free .
If you need something like Redis or memcache, use that for anything that is heavy on writes and deletes.
I love postgres for most things, but these days (Especially while my product is in early development, embedded, or just not internet-facing) sqlite is amazingly workable.
Killer postgres features however: Row-level security (Fantastic when you're using something like postgrest for rapid backend development [1]), and its built in fulltext search engine is 'good enough' for use cases like when you have an enormous users table and need to index something simple enough, like email addresses for quick login.
It is not very useful to add full text search to an email field used for login. A regular unique index, perhaps case insensitive, is what you should be using.
Today I could be a lucky 10,000 - are emails case sensitive? I had always assumed you could pre-process (ie lowercase) them before insertion so that database case sensitivity was not an issue.
Technically speaking the portion of the email address before the @ is case-sensitive. However in practice it is ubiquitous that they’re treated as case-insensitive across all mail platforms.
This means however that you should store the original email and not just lower case it on insert. Imagine if you could reset password for Jane.Doe@example.com by registering the jane.doe@example.com address (assuming example.com does differentiate between the two) and requesting password reset for that.
It goes deeper than that. If emails are case sensitive, everything changes in the context of unique accounts. If you have jane.doe@ and Jane.doe@ attempts to login - what do you do?
You create a contact address from a normalized version of the entered address (after address verification) and an independent account ID. You can also generate an account ID derived from that normalized address.
The positive response of the address verification will tell you the address is deliverable and the user has access to it. Later if someone tries to register a capitalized form of the address it'll get rejected because of that account ID collision. Then the user can be pushed to a password recovery path where they'll need access to the e-mail/MFA to get control of the account.
My point was that I think it is bad user experience if my email is "jane.doe@", but autocorrect has me input "Jane.doe@" (something I have experienced before). As a user, I "entered the same thing". On a technical level, they are different, but a decision must be made as to what is the true representation.
Amusingly, the context of this thread was in using case-insensitive search for email fields, but if emails are truly case sensitive, this is all moot, because you can only do direct comparisons.
In practical terms e-mail addresses are case insensitive. So if on account creation your normalize the address (lower case, trim white space) and send a verification e-mail and they successfully verify you can safely derive an ID from that normalized address. It won't matter later if autocorrect tries a mixed case address since you normalize and compare it on the back end.
If you run into a case where their e-mail server enforces case sensitivity they have bigger problems to deal with. E-mail has long been a system that requires loose adherence to the specs.
Surely this is why standards are important. An email server could use whatever logic it wants to determine which account to deliver an email to. But if email is to be used by other services as an authentication mechanism there certainly better be a widely adopting standard for how emails get delivered.
Does anyone know a single example of a case sensitive email provider or email server implementation? I believe I saw a positive answer to this 10 years back (an old university mail server?) but these must be quite rare.
Just use the citext extension and move on with your life. This is a solved problem: citext recalls the casing but querying and indexing against it is case-insensitive.
IIRC, as per spec/rfc, e-mail addresses ARE case sensitive.
However the de-facto standard is to ignore such thing and deliver emails for Bob@example.com, bob@example.com and BoB@example.com all to the same mailbox.
I would agree, I'm currently also playing with Postgres that triggers some data to sqllite data wrapper to be distributed by Litestream to servers so they can locally read data.
> need to index something simple enough, like email addresses for quick login
It sounds like you might be unfamiliar with the common trick to index long text fields in Postgres: you just make an index of hashed values, and use that for lookup as well to ensure index gets hit.
In case you are familiar with it, maybe it helps someone else who stumbles upon this. :)
I've seen this kind of thing a lot before and I'm saying that it's almost never needed. All you're doing is building your own bespoke indexing system on top of a database that is doing it (much better) already.
You're augmenting the indexing system. By using hashes you get a column with fixed predictable size. If the average size of your large strings is larger than 16 bytes (or 32 bytes if you store the hex string) you'll get more rows per memory page. If you've got many millions of rows the savings adds up. A little bit of savings let's a smaller DB instance go farther.
Oh wow that makes perfect sense, I see exactly why this solution would work better now, very good point.
The other thing is that if you're inserting your emails in without running some ToLower() function on them first in the validation, you're probably making a bit of a mistake. There's some other discussion in the thread about this.
You should definitely normalize e-mails before hashing or doing any comparison on the back end. Especially if you've had a stage during account creation where you verified delivery of that normalized address. When you take in an address later you normalize the input and compare it to your stored value (that itself was normalized before storage).
I'm confused, how is matching against a hashed index faster than just matching against a string field? Surely postgres' indexing engine should treat these two things more or less the same, perhaps quietly performing the text -> hash conversion on the email field for quick lookups when it's used directly in an index (and perhaps performing even more optimizations than this basic transform)?
I was worried they were talking about doing it manually, and was thinking "surely there's a built-in way to create a hashed index" -- Glad to learn there is!
I can confirm that indexing on a hashed value is definitely faster on SQLite at least.
The reason is that indexing on a hash produces a much smaller index so more of it can fit in memory. By minimizing disk seeks, you can speed up query time even if the Big O is the same. In both cases it should be O(log n) since SQLite uses btree indices.
Last time I needed this, LIKE queries still did sequential scans anyway, so the presence of an index or not did not matter.
Also note that you can have multiple indexes in Postgres (well, any RDBMS).
For emails in particular, you can even do partial indexes (eg. imagine filtering emails on @gmail.com to hit a separate index that's only half of your full table index). This requires some care with queries, but a wonderful feature regardless.
This is purely up to the schema designer or dbadmin. You can create a Postgres index specifying “using hash” to specify that the index will not contain the contents of the field, just its hash.
I’m pretty sure that still necessitates a hit to the db to prevent false hashes, but that’s going to be the case with your approach, too.
You're probably right. I'm not really a DB admin I just found the builtin fulltext search when I was tasked with reworking a million + users table to go fast. Boss thought we needed sphinx or elastic or something hahaha
Do you need (or at least benefit from) your database to run in-process? Because that is the only advantage I can see to SQLite over Postgres. Which makes it the better candidate in many places, but not for anything like a server.
Sure, but an embedded database is still simpler than client/server. For many tasks, postgres does not offer any meaningful benefit over sqlite so why add the complexity?
I've never worked on such a project that didn't benefit from postgres, but if I did, I would still use postgres because upgrading from sqlite to postgres, and learning a second SQL dialect, and converting a SQL between dialects sounds like a useless nightmare that costs me nothing to avoid.
I feel it's modern hype on lonely/indie/solo developer creating new thing which changes the world. Not caring on "complex" things till the project reaches 1 request per minute and finally have some data to be lost, is totally fine with this goal/scale.
exactly. single-node availability is often "good enough." when using sqlite for a simple service, I've cranked things out from first-line-of-code to production in 30 minutes. When the app only gets a hit every minute or so and only from internal traffic, no need for the overhead of a highly available service.
I think this is true only if you're willing to give up resiliency.
You'll have to shut the app down to back it up. That problem gets worse if you want some kind of a cold standby, since backups should be frequent.
You could replicate it, but that requires running a separate daemon and starts to beg the "why not just have Postgres be the daemon?" question.
Sysadmin tasks start to get very difficult when someone starts with SQLite and expects to get Postgres-like features out of it. I'd rather run Postgres than try to replicate or continually back up SQLite.
Oh yeah, if the app is big enough to have multiple replicas, I'll use postgres over sqlite. Mainly sqlite for me is for little apps/services. I do know people that have a setup for distributed sqlite, but IMHO when you want more than machine talking to the db, postgres is a much better fit.
One thing I miss in sqlite is numeric types that store binary-coded decimal. (Is there a plugin for that?) This is very useful for currency calculations.
If only there was a infrastructure in a box that gave you all the features he mentioned in a well maintained distribution.
Then rather than saying just use Postgres you say just use X.
I like postgres but I've only used it in production once. My experiences were fine. We used alembic for database migrations. It was a Python Flask app.
We also used it as a message queue and stored JSON form data.
There's still hand crafted code for using Postgres as a message queue.
A bit like Linux distributions which are aggregations of desktop or server software collections of well integrated software.
I tried to build a stack that could be spun up with all goodies included.
But I would not want to inherit something hand stuck together.
But at the same time, the thought of setting up Kubernetes for everything from scratch is also a lot of work.
If you need to use cloud, that's also a lot of work.
If we are talking about really small business, yeah fine but I really don’t see the point of such articles without any numbers. Do some benchmarks and compare postgres with kafka, postgres with elasticsearch, etc. Define the use cases or minimum number of table rows or number of requests that performance deteriorates.
2. Have some standard written down rules about what SQL type to use in which situation and what approaches to use for table structure.
3. don't overuse triggers or stored plSQL procedures or similar they are hard to test and debug
The two main problems I have seen with SQL databases is:
1. People not understanding transaction isolation, most inconsistency bugs or strange behaviour no seem to be able to explain I have seen where due to this. As far as I can tell this is a HUGE problem, even through SQL databases are used much less and at least theoretically it's normally through in any bachelor level course about SQL.
2. people wasting time on discussion about types and table structure. For example weather this or that int type should be used,for a lot of use-cases it's just fine to use bigint (64bit) initially for everything. With 32bit there is often some edge case in which it isn't enough. For example if you do 5_000_000 increments per second on a 31bit (i32>=0) counter it overflows in ~7 minutes but if you the same with a 63bit counter it takes ~58494 years. Sure you sometimes might have to go back and optimize storage and there are cases with clearly constrained numbers, but it's the best default for not clearly bound integer numbers. Similar "initial start with" default approaches can be written down for most situations in just a single DIN-A4 page of paper or so.
I think (1.) is especially tricky b/c it works if you're small (experienced this myself with Hibernate, not understanding transaction levels) and then breaks with the number of concurrent transactions.
When the company rapidly grows you get mysterious bugs - in the worst case customers seing other customers data because of the wrong transaction levels.
Yes, thats why I think it's a huge problem for the industry. How the heck is it possible that so many developers with bachelor and master degrees which otherwise do a reasonable job don't even know that they have a dangerous knowledge gap there???
through that:
> in the worst case customers seing other customers data because of the wrong transaction levels.
should not happen even with wrong transaction levels, that indicates some additional serious design problems IMHO, likely related to premature optimizations
I feel fairly competent but would definitely struggle to debug a transaction isolation problem. I think part of the issue is that I have always operated at an ORM level, on personal projects and at large (>400 dev) companies.
From my understanding getting this kind of error would involve something going wrong at a pretty low level? I am not sure how a developer could cause this at the ORM level.
It's very easy to cause in the ORM because by default a chain of SELECTs may ready different committed data unless you add row level locks like FOR SHARE . In a horizontally scaled backend you need these locks usually, but it takes scanning the DB log to figure out how to get them in the right place.
You can't effectively use an ORM without detailed knowledge of the generated output unfortunately. It does not add locks for you, so it's probably just wrong in prod when horizontally scaled and requests span multiple statements.
This is one of the many problems I have with ORMs, you end up needing to know more about the ORM and the database than you would need to know if you just used stored procedures.
I don’t recall ever having a transaction isolation issue with my busy stored procedures - though I often use SELECT .. FOR UPDATE which is maybe cheating because an ORM can’t do that efficiently - but I’ve certainly seen them in ORMs.
> don't overuse triggers or stored plSQL procedures or similar they are hard to test and debug
I just don’t find this to be true at all, quite the opposite in fact.
It’s true that (AFAIK) there isn’t a stepping debugger for pl/pgsql, although I would love to hear that I’m wrong.
But it’s trivial to debug procedures using RAISE NOTICE commands, and you can run your tests non destructively (in a transaction that you abort) which makes setting up the state for debugging much easier.
I also personally find that I write fewer bugs in plpgsql simply because there is less abstraction (no DAOs, ORMs, caches etc) and I’m working much more closely with the actual data structures I care about. And the referential integrity checks tend to catch those I do write, early.
> People not understanding transaction isolation, most inconsistency bugs or strange behaviour no seem to be able to explain I have seen where due to this. As far as I can tell this is a HUGE problem, even through SQL databases are used much less and at least theoretically it's normally through in any bachelor level course about SQL.
Well, same people won't have much success with NoSQL either, as they inevitably will skip reading on how this particular NoSQL DB handles it.
I prefer the model of event triggers writing to a DDL audit table and calling NOTIFY. Then your script can just LISTEN for changes after you run your idempotent scripts. Then you're capturing all changes to the DB, not just the ones you're expecting.
Because it's always that guy making manual DDL updates that screws everything up, isn't it Mr. Hosick. ;)
I wonder if the author is trying out GPT as a kind of auto-complete for ideas. I have not had a lot exposure to GPT, but it does seem to have something like an "accent". I was listening to This American Life, and they had a mini game show about accents and ages. So, maybe that's just on my mind.
Can someone explain why I shouldn't just use something like cockroachdb, yugabyte or spanner for my primary database nowadays? I've seen the pain and resources wasted from teams needing to find ways to shard mysql and postgres and make them scale. Sure, you might say I might never need that scale but why not just avoid this problem entirely? If I'm building something I intend to support hundreds of millions of users why would I choose a technology I know will fail to scale to that?
Because you are working for a company that like 99% of companies have less than a million users?
You want data analytics, financial reports, the ability to query your data in new ways you didn't think of when you first built the product? NoSQL comes at a great cost.
When you hit a million users, the entirely of your codebase will be on-fire with scalability issues anyway so it won't make any difference?
441 comments
[ 2.2 ms ] story [ 189 ms ] threadCurrently integrating Procrastinate (https://procrastinate.readthedocs.io/en/stable/) to use Postgres as a job queue in a Django API backend.
Dropping dependencies is very nice especially when dealing with an MVP / small apps.
But also, Celery has an awful DX. I think the question is more, why do I need celery when Postgres can do the job itself?
We had great ideas about scheduling and caching and task priorities...
...and then we asked the customer what they wanted, and they wanted none of it.
So we built none of it, and produced a solution that just did the stupidest thing, and did it without any edge cases, without ever crashing, reliably, as a cronjob, once on Sunday night.
Some people would be disappointed that they couldn't put this on their CV because it didn't involve FancyTech #413, but damn it, I am still proud of that stupid thing.
I asked him what he’d done to work around the technical difficulties, and it turned out that he’d set up a Wordpress site with a phone number of the guy running the scheme, and a Google sheet to manage contact details.
A better definition of MVP I’m yet to see.
I still have nightmares of post-redirect-get complexity and giant balls of mud in session scope to support the back button.
Saving ops resources by managing fewer services when getting started is a good idea, until scale necessitates dedicated technologies for certain components.
I agree that hedging against having to move from a FOSS database is pure waste.
Obviously, if you anticipate to have very high traffic and applicable usage patterns, do use that advice, but if your apps anticipated usage pattern is in fact not "very high rps per buck made", then I recommend the opposite.
I've went the whole way from very-well-abstracted-away services/repositories to an almost complete lack of abstraction.
Direct SQL or ORM in your functions, operating on many models at once, with a real postgres database available to all your unit tests, treating the SQL code as part of your application logic. Transaction per test so that unit tests are fast to run.
I've been very happy since. SQL is very powerful if you use it as SQL and not as a glorified KV store.
This is the part where I don't agree, as SQL is very expressive, and needlessly loading data to your app is also not performant (making the transition to something else required sooner).
I think writing parts of your business logic in SQL that make sense to be written in SQL is just fine.
If you only use it to load and write entities, then that is basically a glorified KV store.
How does this stack compare to Snowflake or Redshift?
By tuning the chunk sizes so their data fits in memory, many common queries gain a lot of efficiency. It's built around some assumptions of time-series data: Most inserts and queries are for recent data and are generally ordered.
I've had great experience with TimescaleDB for small-medium time-series loads such as sensor or analytics data; I've found it's pretty plug-and-play and have used it to store tables with ~1B time-series rows of geospatial data, sensor values, etc.
I think you should keep complexity down by only using postgres, but just use it as a sequel database until you scale enough that it's a problem.
90% of the time it's never going to scale that far.
When it does, use the tools people have made to do those jobs correctly. Do not hack you way into half-made tools put into a database that isn't specialized in that job.
It seems like you're making your life more simple by having only one system, but you're having that one system to so many things at the same time that it's going to be an absolute kludge in the long term.
Does anyone have experience trying something like this? If you did, is this how it turned out or did it actually work all right?
> A complex system that works is invariably found to have evolved from a simple system that worked. A complex system designed from scratch never works and cannot be patched up to make it work. You have to start over with a working simple system.
A SQL database, maybe supplemented by a cache, can carry most projects as far as they'll ever go. And if you outgrow it, replace it with something that meets your new needs.
Any given project's "right tool for the job" can change over the project's lifetime, and optimizing for problems you don't have is quite harmful.
For God's sake, please don't
- they live in source control
- they are covered by automated tests
- they are applied using some form of automatic database migration system (not by someone manually executing SQL against a database somewhere)
If you don't have the discipline to do these things then they are likely best avoided.
I'd go further and say you should avoid databases and maybe even persistence entirely if you don't have the discipline to do the above. Sprocs will be the least of your problems otherwise.
so that probably excludes 95% of legacy codebases out there from the 90s,00s
For "you" in my comment, please read "one" instead:
> I think stored procedures can be perfectly safe provides one follows these rules:
> ...
> If one doesn't have the discipline to do these things then they are likely best avoided.
This is the classic "carpenter blames his tools for crappy results" argument. Implementation isn't easy.
Now I do think there is a benefit in stored procedures and triggers (E.g. for audits) if they don't contain too much logic or complexity.
I think this is the catch. Most folks who are arguing against SP have been burned by huge complex stored procedures with nested dependencies with deeply intertwined business logic and rules. I completely agree that you shouldn't use a SP in that way. But to help perform maintenance, or to audit, or perform data correction all make sense when kept small and simple.
As a side note, I did not realize $diety was concerned about DDL/DML, so thanks for pointing it out. I never really thought about it.
Stored procedures are much faster than writing logic in some remote server (just by virtue of getting rid of all the round trips), require far less code (no DAOs, entities and all that crap which simply serves to duplicate existing definitions), and have built-in strong consistency checking primitives - which can even be safely delayed until the end of the transaction.
And what people do is, they throw all these advantages away because they can’t be bothered working out how to integrate the stored procedure code ergonomically into their workflow.
I mean - I even use an IDE (JetBrains) to write pl/pgsql. It’s just another file in my repo. Get to this point and stored procedures are a game changer.
We made this tool to get the best of both worlds:
https://github.com/vippsas/sqlcode
So… why exactly would you exclude compute running close to the storage?
You can use that minimal latency.
Of course, people can create an uncontrolled mess of spaghetti code out of sps/funcs… like they can with any kind of code.
When people talk messaging they normally mean something lower latency, potentially out of order and where the success of each message is likely independent of other messages. For these use-cases Kafka is not well suited. For these cases you will want something like Pulsar, SQS, PubSub, etc.
1. https://kafka.apache.org/documentation/#uses_messaging
2. https://kafka.apache.org/
I use (and think of) Kafka as a message bus, with distributed commit logging being the mechanism for how it accomplishes that task.
Because it's really a log and it's partitioned it has a whole host of issues that make it a very poor messaging system like hot partions, head of line blocking etc.
If what you want is a proper messaging system where the underlying model is still a distributed log then you should look at Apache Pulsar. It can provide the same semantics as Kafka but can properly implement job queues and messaging semantics ala SQS, Google PubSub, et al.
Do that until scale forces you to start specialisation.
It's surprising how far you can go with database-as-MQ. Even database-as-IPC can work for smaller systems.
A lot of startups are convinced they'll need Google scale from day one. Then, of course, the overwhelming majority fail in the first year.
Get a big, reliable, and cheap vhost/server somewhere, use as many "can't really go all that wrong" components like postgres, minio, etc and dockerize everything. If you want to get "fancy" use ZFS and setup some snapshots and backup. Most solutions don't even need 100% uptime. Communicate maintenance windows to customers and you'll be fine with a total of an hour of downtime (or whatever) in the first year. Most big, really complex, early over-engineered and unnecessarily "optimized" solutions have enough footguns you'll probably end up with more unscheduled downtime in the first year anyway.
In the rare event the startup really succeeds and customer demand, load, uptime requirements, etc demand it you can throw revenue/funding/etc at a K8s control plane on your favorite hosting provider, use a managed postgres/db/whatever, and S3 compatible object store, etc. Or, if things get really big skip all of that and hire in house talent to manage a couple of racks of leased hardware (same opex as cloud but almost always SUBSTANTIALLY cheaper) in geo redundant/distributed colocation facilities.
I've launched multiple startups with this strategy and it's gone very well. My current startups all run from the same big (but 10yr old) hardware that has loooooong since paid for itself even with lots of GPU, storage, etc upgrades over the years. People can be kind of scared of hardware but I've never had downtime or data loss caused by a hardware failure in almost 20 years of this approach.
People are always amazed when I do things with ML, TBs of data, lots of bandwidth, etc and I tell them my total hosting costs are $150/mo.
I'd love to hear more about the setup. I suspect something as simple as disk failure would cause outage, although I suppose you can detect a soon to fail disk via SMART and resolve that with scheduled maintenance/downtime. But what about power supply failure? Do you keep redundant backup parts on hand?
There's definitely something nice about having a hardware error on a cloud VM result in that VM cycling out to new hardware automatically. In contrast, something as simple as buying a new off the shelf PSU feels like a ~1 hour downtime event (longer of you don't have purchase card authority, it's night time, you need to order online, etc.).
The system I referenced currently has x8 4TB NVMe drives on ZFS raidz2 and x8 16TB rust drives also on raidz2. I use sanoid to snapshot like crazy (down to 15 minutes) and then syncoid to push snapshots and then some to the spinners ZFS array. Plus zfs send remote to offsite.
Modern switching power supplies are incredibly reliable. Then do proper “half load” dual power supplies from dual conditioned power feeds with UPS and generator. Losing power to a machine almost implies an extinction level event or complete incompetence on the part of a data center operator.
But I'm on a single medium sized Linode and doing full-text search over 120M small (< 1KB) to medium (< 10KB) text documents and it's still so fast I'm not sure that I will ever need to consider an alternative.
Not that bad actually, but it varies by use case.
Reasons aurora can be cheaper than you think:
1) Autoscaling. Adding a new reader takes 15 minutes. Instead of provisioning for peak traffic and paying for it 24/7, run an extra reader for a few hours each day. 2) Metered IO. Aurora IO can handle 400k+ iops when needed. Or it can hum along at 100 iops. You pay only for what you use, you don't have to provision for peak load 24/7.
Switching to Aurora saved us money over vanilla RDS. Self hosting postgres may be cheaper, but not by as much as would first appear.
My rule of thumb is about $1,000 per month for a reasonably large DB in RDS.
We also had a few issues where aurora was giving us *incorrect query results* and aws support were not helpful. Most of the issues were denied, even with simple reproductions, and then got fixed years later (~2-3) after we had given up. We still use it today but stay away from cutting edge features, unusual query patterns, and high volume use cases.
Having said all that, SQL still seems like the correct choice for getting an MVP out quickly.
The main thing I like about MySQL-based solutions is the availability of Galera multi-master software, which for simple configurations is very easy to get going. Drop a fairly cookie-cutter config on each host, and you're done:
Combine with keepalived on each node which does a health check, and if one goes down/bad, the vIP is moved over to another host in the cluster with minimal fuss.* https://github.com/codership/galera
Zalando's operator use Patroni under the hood, to create a cluster over streaming replication. It also has Spilo, which orchestrates pg_basebackup or WAL-E for point-in-time backup. https://github.com/zalando/postgres-operator#postgresql-feat...
CrunchyData operator seems to have built their own streaming replication system coordinated by Raft. https://access.crunchydata.com/documentation/postgres-operat...
Both are fantastically featureful well-integrated operators that are super well maintained. Both are very recommendable.
But saying this would not make for a blog post. I can’t help but categorize this post as juvenile at best. Generalizations like “use XXX for everything’ should be avoided at all costs. No software product serves well to such sweeping generalizations. I’m surprised that it’s coming from a CTO. They should know better if they’re worth their salt .
No matter the scale or how big it is.
If you are talking of specialized solutions, that means you know exactly where an existing, well known solution is failing you and why.
Eg. if you drop all foreign key references and constraints in Postgres, you might get similar write performance to other databases which can't make those guarantees when you do need them.
That's Postgres.
"Just use the right tool for the job at hand."
Yes, but I have heard exact that phrase for decades to rationalize tech decisions for tools that weren't needed.
I know you're different, but think of all the people who have the same problems.
If you need complexity b/c you're Netflix or Uber, go ahead. If you're the 95% others who don't need that complexity, then don't do it.
"I can’t help but categorize this post as juvenile at best."
Thanks, I guess this is the nicest thing to say to someone 50+!
Your username is also juvenile, well done you!
Fwiw, my experience has also led me to be on the side of "just use Postgres" unless required otherwise. I've seen enough people glue whatever crazy technologies together where a relational database would've been enough.
It was Codemonkeyism before (𝅘𝅥𝅮 "Code Monkey think maybe manager want to write god d** login page himself") but I thought I had grown to be king of the asylum.
Postgres is an all-purpose database that has it all: relational databases were created to model real world problems, and while they might not perform best for all the usecases, they can usually model them just fine while protecting from many programming errors.
And then Postgres has features on top: partial indexes, object DB features, replication etc.
Who does this? "give it to the API"... ? Still sounds like there's "server side code" there if there's an "API" involved. Surely they're not meaning let front-end code hit the database directly?
I've obviously not used it and to be honest, I probably wouldn't. But IMO Postgres is a radically different type of framework. People just think of it as a SQL database, it can be much more though. Tooling is just a bit lacking.
Edit: Heh, Retool is sponsoring them. "PostgREST for the backend, Retool for the frontend". Cool idea. I might reconsider...
It doesn't matter anyway. Serving HTTP is not a problem particularly well solved by Postgres, and needing an extension instead of whatever hyper-optimized http server you usually use is increasing complexity, not decreasing it.
I find the thing quicker than asking google, pretty often.
I really want a CLI interface to have an "ask the oracle" kind of feature.
PostgREST is page two of "postgres http server" for me.
In this case they mean "let Postgres generate the JSON for API responses" instead of having the API interface do it. The "Generating JSON in Postgres" section in the linked article shows how this is done.
yes, if you can create some JSON without having to transform in an intermediate code layer, that's handy/useful. I often don't run in to too many scenarios where things are trivial enough to allow for that - there's usually some app-level logic that comes in to play with respect to visibility/permissions/etc.
Why not? You can make Postgres speak SQL or GraphQl it removes an additional network indirection and there are very clear ways about how Posgres does scale well and where it doesn't scale well. (EDIT: Or put a dump translation layer/service in-between they exist to and are cheap to run and manage.)
You can write your core logic in JS, Rust etc. and plug it into your database as virtual tables or shared procedures (it's surprisingly simple).
Any DDOS protection and similar is anyway in front of whatever you write as some form of proxyish thing.
And just needing to scale Postgres instead of a bunch of different systems is so much more simple. Depending on how you run it and what input characteristics you have it might even be more efficient and cheaper to scale that way (or it can be noticeable more expensive) and it's most times cheaper to manage.
What the heck?! No!
If you need something like Redis or memcache, use that for anything that is heavy on writes and deletes.
Killer postgres features however: Row-level security (Fantastic when you're using something like postgrest for rapid backend development [1]), and its built in fulltext search engine is 'good enough' for use cases like when you have an enormous users table and need to index something simple enough, like email addresses for quick login.
[1] https://postgrest.org/
https://www.postgresql.org/docs/current/pgtrgm.html#id-1.11....
The positive response of the address verification will tell you the address is deliverable and the user has access to it. Later if someone tries to register a capitalized form of the address it'll get rejected because of that account ID collision. Then the user can be pushed to a password recovery path where they'll need access to the e-mail/MFA to get control of the account.
Amusingly, the context of this thread was in using case-insensitive search for email fields, but if emails are truly case sensitive, this is all moot, because you can only do direct comparisons.
If you run into a case where their e-mail server enforces case sensitivity they have bigger problems to deal with. E-mail has long been a system that requires loose adherence to the specs.
IIRC, as per spec/rfc, e-mail addresses ARE case sensitive.
However the de-facto standard is to ignore such thing and deliver emails for Bob@example.com, bob@example.com and BoB@example.com all to the same mailbox.
It sounds like you might be unfamiliar with the common trick to index long text fields in Postgres: you just make an index of hashed values, and use that for lookup as well to ensure index gets hit.
In case you are familiar with it, maybe it helps someone else who stumbles upon this. :)
the database should be able handle case insensitive indexed lookups directly.
The other thing is that if you're inserting your emails in without running some ToLower() function on them first in the validation, you're probably making a bit of a mistake. There's some other discussion in the thread about this.
When you get over a million users...
I've yet to try the index of hashed values trick though, when I revisit this problem in the future I'll make sure to take note of this!
The reason is that indexing on a hash produces a much smaller index so more of it can fit in memory. By minimizing disk seeks, you can speed up query time even if the Big O is the same. In both cases it should be O(log n) since SQLite uses btree indices.
Also note that you can have multiple indexes in Postgres (well, any RDBMS).
For emails in particular, you can even do partial indexes (eg. imagine filtering emails on @gmail.com to hit a separate index that's only half of your full table index). This requires some care with queries, but a wonderful feature regardless.
I’m pretty sure that still necessitates a hit to the db to prevent false hashes, but that’s going to be the case with your approach, too.
If you need any normalization like lowercasing, you need to do that yourself.
Next, you can't do collation/ordering using the index (and greater/less than comparisons), unless the hash function maintains ordering properties.
By having an option of an expression index customised to your data, you can use it to get the fastest query performance.
Modern, production-grade, web-scale machines are able to run more than one process, these days.
Plus, not everything is a public-facing website. It's amazing how little overhead it takes.
exactly. single-node availability is often "good enough." when using sqlite for a simple service, I've cranked things out from first-line-of-code to production in 30 minutes. When the app only gets a hit every minute or so and only from internal traffic, no need for the overhead of a highly available service.
You'll have to shut the app down to back it up. That problem gets worse if you want some kind of a cold standby, since backups should be frequent.
You could replicate it, but that requires running a separate daemon and starts to beg the "why not just have Postgres be the daemon?" question.
Sysadmin tasks start to get very difficult when someone starts with SQLite and expects to get Postgres-like features out of it. I'd rather run Postgres than try to replicate or continually back up SQLite.
This is not the case:
1) SQLite recommends using the official backup API, rather than copying files on disk. The backup API can be used while the app is running.
2) Litestream is the hot new tool on the block. It streams incremental DB changes to a backup stored on S3, for up-to-date point-in-time recovery.
Then rather than saying just use Postgres you say just use X.
I like postgres but I've only used it in production once. My experiences were fine. We used alembic for database migrations. It was a Python Flask app.
We also used it as a message queue and stored JSON form data.
There's still hand crafted code for using Postgres as a message queue.
A bit like Linux distributions which are aggregations of desktop or server software collections of well integrated software.
I tried to build a stack that could be spun up with all goodies included.
But I would not want to inherit something hand stuck together.
But at the same time, the thought of setting up Kubernetes for everything from scratch is also a lot of work.
If you need to use cloud, that's also a lot of work.
https://devops-pipeline.com
1. Teach people transaction isolation levels.
2. Have some standard written down rules about what SQL type to use in which situation and what approaches to use for table structure.
3. don't overuse triggers or stored plSQL procedures or similar they are hard to test and debug
The two main problems I have seen with SQL databases is:
1. People not understanding transaction isolation, most inconsistency bugs or strange behaviour no seem to be able to explain I have seen where due to this. As far as I can tell this is a HUGE problem, even through SQL databases are used much less and at least theoretically it's normally through in any bachelor level course about SQL.
2. people wasting time on discussion about types and table structure. For example weather this or that int type should be used,for a lot of use-cases it's just fine to use bigint (64bit) initially for everything. With 32bit there is often some edge case in which it isn't enough. For example if you do 5_000_000 increments per second on a 31bit (i32>=0) counter it overflows in ~7 minutes but if you the same with a 63bit counter it takes ~58494 years. Sure you sometimes might have to go back and optimize storage and there are cases with clearly constrained numbers, but it's the best default for not clearly bound integer numbers. Similar "initial start with" default approaches can be written down for most situations in just a single DIN-A4 page of paper or so.
When the company rapidly grows you get mysterious bugs - in the worst case customers seing other customers data because of the wrong transaction levels.
through that:
> in the worst case customers seing other customers data because of the wrong transaction levels.
should not happen even with wrong transaction levels, that indicates some additional serious design problems IMHO, likely related to premature optimizations
From my understanding getting this kind of error would involve something going wrong at a pretty low level? I am not sure how a developer could cause this at the ORM level.
You can't effectively use an ORM without detailed knowledge of the generated output unfortunately. It does not add locks for you, so it's probably just wrong in prod when horizontally scaled and requests span multiple statements.
I don’t recall ever having a transaction isolation issue with my busy stored procedures - though I often use SELECT .. FOR UPDATE which is maybe cheating because an ORM can’t do that efficiently - but I’ve certainly seen them in ORMs.
Really!!!
Mandate automated DB unit testing [0] from day 1, just like you would for the rest of your code base.
[0] - https://pgtap.org/
I just don’t find this to be true at all, quite the opposite in fact.
It’s true that (AFAIK) there isn’t a stepping debugger for pl/pgsql, although I would love to hear that I’m wrong.
But it’s trivial to debug procedures using RAISE NOTICE commands, and you can run your tests non destructively (in a transaction that you abort) which makes setting up the state for debugging much easier.
I also personally find that I write fewer bugs in plpgsql simply because there is less abstraction (no DAOs, ORMs, caches etc) and I’m working much more closely with the actual data structures I care about. And the referential integrity checks tend to catch those I do write, early.
You'd be wrong there: https://www.pgadmin.org/docs/pgadmin4/development/debugger.h...
Well, same people won't have much success with NoSQL either, as they inevitably will skip reading on how this particular NoSQL DB handles it.
PostgreSQL Extensions: http, pg_cron, timescaledb
To help with development, I am using https://www.npmjs.com/package/sql-watch (written by myself) to do continuous development and testing (TDD/BDD).
The stack "doesn't scale" but the turn around time for development is crazy.
Because it's always that guy making manual DDL updates that screws everything up, isn't it Mr. Hosick. ;)
> Use Postgres for caching instead of Redis with UNLOGGED tables and TEXT as a JSON data type.
> Use Postgres as a message queue with SKIP LOCKED instead of Kafka (if you only need a message queue).
I'm sure if we try hard enough we could find some sort of meaning in these points, but then the content is coming from the reader not the author.
Also from the author's mastodon:
> Using ChatGPT As a Co-Founder
Serious question, though: when the data span several instances, how well does PostGreSQL do compared with Elasticsearch?
You want data analytics, financial reports, the ability to query your data in new ways you didn't think of when you first built the product? NoSQL comes at a great cost.
When you hit a million users, the entirely of your codebase will be on-fire with scalability issues anyway so it won't make any difference?
Isolation?
Please do elaborate.