[HN Gopher] Just use Postgres for everything
___________________________________________________________________
Just use Postgres for everything
Author : KingOfCoders
Score : 450 points
Date : 2022-12-10 16:52 UTC (6 hours ago)
(HTM) web link (www.amazingcto.com)
(TXT) w3m dump (www.amazingcto.com)
| haolez wrote:
| 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.
| simonebrunozzi wrote:
| Dear Stephan, in my experience (6 years as tech evangelist for
| AWS, met with thousands of startups and large companies in 6
| continents - not to brag, just to give you context, AWS' success
| and relevance is only very slightly attributable to my tiny
| contribution), what you suggest might work if and only if the
| company is not really successful, and its IT needs do not change
| quickly over time.
|
| If instead we're talking about a fast growing startup, a single
| solution like Postgres (btw, I adore Postgres, to be clear), it's
| going to become an issue.
|
| You can dramatically improve your performance if you allow the
| use of Redis, as a start. And your title could have been "Just
| use Postgres + Redis for everything", and it would have been half
| as bad, or quite good.
|
| I think it's ok to try to keep things simple, but limiting
| yourself to JUST Postgres is not going to work.
| christophilus wrote:
| > might work if and only if the company is not really
| successful
|
| What is your definition of success? I've seen Postgres scale to
| pretty large loads. I've also seen plenty of complex stacks
| where the scale never warranted the complexity.
|
| That said, Redis + Postgres is a pretty simple stack, so I'm
| not complaining about your suggestion. I'm more curious about
| specifics.
| guidoism wrote:
| Ouch. That first image has really bad kerning. It hurts my eyes.
| posharma wrote:
| 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 .
| necovek wrote:
| "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.
| simonw wrote:
| 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.
| cortesoft wrote:
| 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.
| simonw wrote:
| 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.
| necovek wrote:
| 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.
| fbdab103 wrote:
| Engineering is trade-offs. Complexity is an enormous one best
| avoided unless you absolutely cannot work with the simpler
| system.
| New_California wrote:
| > Just use the right tool for the job at hand. Period.
|
| That's Postgres.
| [deleted]
| KingOfCoders wrote:
| 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+!
| tasuki wrote:
| > 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.
| [deleted]
| sammy2255 wrote:
| A blog called "amazingcto" that isn't loaded over HTTPS..
| jmartrican wrote:
| Oh wow. It shouldn't even work on http... it should redirect to
| https. If you calling yourself amazing CTO.
| xrayarx wrote:
| It loads via https for me
| mgkimsal wrote:
| > 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?
| scrollaway wrote:
| 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...
| dragonwriter wrote:
| 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.
| scrollaway wrote:
| 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.
| fnimick wrote:
| 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/
| xrayarx wrote:
| Looks and sounds great, but nodejs
| qup wrote:
| > thanks ChatGPT
|
| 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.
| CharlesW wrote:
| > _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.
| mgkimsal wrote:
| "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.
| ako wrote:
| I do, works really well if you need json with nested objects,
| you can get an entire tree of objects with a single call and
| query to the database.
| ako wrote:
| 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.
| dathinab wrote:
| 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.
| ako wrote:
| 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.
| xrayarx wrote:
| Sad to say, but the number of companies leaving oracle is
| increasing in my local bubbles
| dathinab wrote:
| > 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.
| daneel_w wrote:
| _" Use Postgres for caching instead of Redis with UNLOGGED tables
| and TEXT as a JSON data type."_
|
| What the heck?! No!
| robin_reala wrote:
| More info on why not?
| qatanah wrote:
| You offload some load like pg connections from postgres to
| redis. Your pg shared buffers would also be available for
| other stuff.
| huntertwo wrote:
| Hot take but I prefer dynamo for storing persistent state. Much
| easier to scale.
| nix23 wrote:
| I correct it....just use the filesystem for everything.
| jamal-kumar wrote:
| 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.
|
| [1] https://postgrest.org/
| giovannibonetti wrote:
| 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.
| VWWHFSfQ wrote:
| is case insensitive fulltext faster or slower than case
| insensitive index search on a varchar
| microsoftdoes wrote:
| The index should not be slower unless something is
| seriously wrong.
| lolinder wrote:
| There's an extension for trigram similarity operators which
| is useful for a quick and easy fuzzy search for small things
| like email and name:
|
| https://www.postgresql.org/docs/current/pgtrgm.html#id-1.11..
| ..
| fbdab103 wrote:
| 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.
| datalopers wrote:
| 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.
| notpushkin wrote:
| 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.
| fbdab103 wrote:
| 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?
| giantrobot wrote:
| 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.
| fbdab103 wrote:
| 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.
| tshaddox wrote:
| 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.
| [deleted]
| mickeyp wrote:
| 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.
| znpy wrote:
| > are emails case sensitive?
|
| 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.
| [deleted]
| KingOfCoders wrote:
| 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.
| asenchi wrote:
| Why would you add sqlite here? Postgres can do all of that
| without the extra tech of litestream.
| VWWHFSfQ wrote:
| Yeah I'm super confused about what is going on in this
| architecture
| fbdab103 wrote:
| I think the design is for local (edge node) read
| replicas?
| necovek wrote:
| > 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. :)
| VWWHFSfQ wrote:
| it's a huge waste of space and now you have to have triggers
| somewhere to keep the index field consistent.
|
| the database should be able handle case insensitive indexed
| lookups directly.
| ddorian43 wrote:
| Just use an expression index.
| efficax wrote:
| you can use postgres generated columns to keep the index
| field automatically consistent.
| https://www.postgresql.org/docs/current/ddl-generated-
| column...
| VWWHFSfQ wrote:
| 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.
| giantrobot wrote:
| 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.
| jamal-kumar wrote:
| 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.
| indymike wrote:
| This is using the DB as designed. Not a bespoke solution.
| tshaddox wrote:
| Couldn't you use an expression index, or even a stored
| generated column with a normal index on it?
| nicoburns wrote:
| Can't you use CITEXT for this?
| trifurcate wrote:
| 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)?
| [deleted]
| CodeWriter23 wrote:
| I think GP may be referring to the use of GIN indexes
| https://www.postgresql.org/docs/current/textsearch-
| tables.ht...
| jamal-kumar wrote:
| Yes that.
|
| 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!
| jefftk wrote:
| 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!
| sieabahlpark wrote:
| zffr wrote:
| 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.
| quickthrower2 wrote:
| Surely you give up LIKE queries benefiting from the index
| this way?
| ComputerGuru wrote:
| 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.
| zffr wrote:
| Alternatively you might be able to just use a hash index if
| you don't care about range query performance.
| jamal-kumar wrote:
| 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
| kristiandupont wrote:
| 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.
| freedomben wrote:
| For me it's maintenance. Sysadmin level of effort on a SQLite
| file is near 0
| jamal-kumar wrote:
| Yeah it's nice not to have to think too hard about that
| when you're trying to focus on a product getting shipped.
|
| Plus, not everything is a public-facing website. It's
| amazing how little overhead it takes.
| kristiandupont wrote:
| That's fair, though I've never done any sort of sysadmin on
| a Postgres server either..
| everforward wrote:
| 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.
| spiffytech wrote:
| > You'll have to shut the app down to back it up.
|
| 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.
| luto wrote:
| It makes the dev setup trivial, since there is no database
| server around.
| moron4hire wrote:
| Setting up postgres on the same machine is also trivial.
|
| Modern, production-grade, web-scale machines are able to
| run more than one process, these days.
| aiwv wrote:
| 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?
| madjam002 wrote:
| I need to store ~50 million records with around 40 columns of
| strings, integers, decimals, various data. Only needs to be
| indexed by two string columns, but every day I want to "upsert"
| ~10 million records with data that has potentially changed, plus
| update a column that represents the date that the row was last
| updated on every row.
|
| With Postgres it seems to be inefficient at storing rows with
| large amounts of columns, and "upserting" data (using UPDATEs or
| INSERT ON CONFLICT) results in huge amounts of disk writes, I
| think because Postgres writes the entire row even if a single
| column has been updated.
|
| I could store some of the regularly updated columns in a separate
| table, but I'd thought I'd ask, is there a database out there
| that is more suited for this type of workload? Because I feel
| like "just use postgres for everything" is not the answer here.
| Izkata wrote:
| > I think because Postgres writes the entire row even if a
| single column has been updated.
|
| This is because of the consistency model postgres uses:
| Roughly, the old row remains in existence as long as
| transactions started before the UPDATE are still running, and
| the new row exists alongside it for that duration. Transactions
| started before the UPDATE can't see the new row, and
| transactions started after the update can't see the old row,
| because the transaction id (txid) is added to the query and is
| compared to hidden columns on each table/row (xmin and xmax).
|
| This is one of the things VACUUM cleans up, actually deleting
| those old rows once all the old transactions have ended.
| crazygringo wrote:
| I mean, you could certainly try MySQL to see how it compares.
| It's hard to guess in advance because it seems like this is a
| very specific scenario.
|
| But 40 columns should be a cakewalk for any RDBMS to handle.
| And I assume you're not storing KB's of data in the different
| columns? Because if your strings are large enough to be
| allocated as references to separate blob storage, rather than
| in the row, that could be a performance problem.
|
| Bulk operations with millions of rows can also sometimes take
| way longer if you're doing them as a transaction that can be
| rolled back. If it's acceptable to disable that, that could be
| a huge improvement.
|
| You can also sometimes find massive speedups in using bulk SQL
| statements (upserting 1000 rows per query, rather than 1 row
| per query) or CSV file import rather than SQL.
|
| And obviously make sure you're using indexes wherever
| appropriate.
|
| Because generally speaking, upserting ~10 million reasonably-
| sized records is the kind of thing that should only take a few
| minutes on an SSD. You're not going to do it in seconds, but it
| shouldn't be taking an hour or anything either.
| madjam002 wrote:
| The issue isn't so much the speed as yes I use bulk SQL
| statements and can upsert like 4-5k rows per second, it's
| more the disk writes which I find unsettling as after a few
| months I've noticed several 10s of TBs of disk writes from
| Postgres onto the SSDs which seems like unnecessary wear.
| crazygringo wrote:
| Then it seems your setup is totally fine. If you're
| upserting 10 million rows a day with lots of columns, then
| of course you're going to be seeing TB's of disk writes.
| And remember that SSD's can't even write individual bytes
| the way HDD's can, they necessarily write a whole page at a
| time, which might be 4K or 16K, even if you're just
| updating a single integer.
|
| Your database and SSD are functioning totally normally, as
| designed.
| qeternity wrote:
| Several 10s of TBs of writes over a few months is literally
| nothing to be concerned about for any recently modern
| enterprise ssd in a production setting.
|
| This seems like a bizarre rationale to make a database
| choice.
| civilian wrote:
| I mean, isn't the 40 columns the problem?
|
| You might benchmark it against a couple other database flavors,
| I suspect most dbs would have issues, although maybe not the
| same issues as postgres
| hgamaral wrote:
| > I think because Postgres writes the entire row even if a
| single column has been updated.
|
| You might want to have a look at HOT [0] tuples if you haven't
| already.
|
| [0] - https://www.cybertec-postgresql.com/en/hot-updates-in-
| postgr...
| madjam002 wrote:
| Thanks for the pointer, that looks very interesting!
| blast wrote:
| Thanks. I also found https://medium.com/adyen/fighting-
| postgresql-write-amplifica....
| unhammer wrote:
| Perhaps a https://en.wikipedia.org/wiki/Column-oriented_DBMS
| madjam002 wrote:
| Yes I've been considering evaluating ScyllaDB, I think it
| could be a good fit for my use case.
| tremon wrote:
| This sounds like a data lake or delta lake type of situation.
| If all you need is daily bulk updates but don't care about
| individual row updates nor concurrent access, I would probably
| not use a full-fledged database in the first place.
|
| e.g. https://docs.delta.io/latest/delta-standalone.html
| klysm wrote:
| Fully agree with this philosophy. With json support and timescale
| you can get really far. If you run into performance problems
| you've already won.
| JacksonGariety wrote:
| The graphic is misleading:
|
| - You can easily do prerendering and asset packing in the
| frontend build step
|
| - You don't need MVC on the backend
|
| - You can use a third party for authorization
|
| - Frontend can just be a React app
|
| - VirtualDOM doesn't add any complexity in addition to the
| frontend View
|
| - You don't need MVC on the frontend
|
| - Frontend can fetch data from the API and cache it with cache-
| control header
|
| The simplest setup is actually to host a React app from an S3
| bucket and use Lambda / Cloud Functions to respond to network
| requests.
| adamckay wrote:
| > The simplest setup is actually to host a React app from an S3
| bucket and use Lambda / Cloud Functions to respond to network
| requests.
|
| You'd have to pry my monolith-on-a-vps out of my cold dead
| hands before you can call that the simplest setup.
| bitwize wrote:
| Be like a former boss of mine. Replace your entire backend
| strategy with stored procedures. Fire your backend developers.
| Save cash. Get a promotion for saving the company millions a
| year.
| yehia2amer wrote:
| We actually use dapr which provides abstraction layer over all of
| those things including State management, Pub/sub, etc.. So it's
| really easy to switch between them without changing the code at
| all.
|
| https://docs.dapr.io/developing-applications/building-blocks...
| rubenfiszel wrote:
| Our open-source project [1] was built with nothing but Rust +
| Postgres on the backend side of things and it allows us to
| iterate quickly and have very simple deployments for self-
| hosting. The queue which is the core bottleneck of our stack will
| eventually get replaced by redis but for a scale of less than
| 10k/s jobs, it scale incredibly well.
|
| [1]: https://github.com/windmill-labs/windmill
| k__ wrote:
| I don't know if I wanted to iterate with a datastore that
| required me to define a schema.
| samsquire wrote:
| 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.
|
| https://devops-pipeline.com
| scrollaway wrote:
| I'm just about there and agree in general.
|
| Currently 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.
| whatever1 wrote:
| Why not the battle tested celery?
| simonw wrote:
| Here's a really good article about why you might want to
| involve your database in queues (short version:
| transactions): https://brandur.org/job-drain
| llIIllIIllIIl wrote:
| Battle tested Celery has dropped the ball so many times
| during operations and updates, that I would be very
| uncomfortable giving that recommendation.
| scrollaway wrote:
| See article?
|
| 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?
| phoebefactorial wrote:
| 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.
| nikeee wrote:
| You can so use pgnotify for a simple pub/sub.
| johne20 wrote:
| NOTIFY / LISTEN is handy too. (pubsub)
| higeorge13 wrote:
| 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.
| themenomen wrote:
| And not just performance, also costs related to scalability
| fidrelity wrote:
| I love postgres, keen to recommend it and did use a couple of the
| suggested tips in my last startup. However:
|
| - After just the first couple of months we had to replace full
| text search with Elastic because it just wasn't up to the task
| fully. - We did introduce redis for cashing which was maybe 2
| hours of setup overhead (using Rails, deploying on Render for
| $5/month).
|
| YMMV.
| tuukkah wrote:
| In my next backend, I want to use serverless functions
| (Cloudflare), serverless Postgres (Neon), and nothing more.
| jb1991 wrote:
| 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?
| Finnucane wrote:
| "You probably don't need as many back-end services as you think
| you do" isn't quite as good a title.
| glutamate wrote:
| Yes: HTML. Seriously, just generate HTML on the backend and
| serve web pages like it's 1999. Easiest way to get 100% on all
| Lighthouse scores.
| jshen wrote:
| Don't write any code, easiest way to not have bugs.
| ThePadawan wrote:
| 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.
| robin_reala wrote:
| 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.
|
| A better definition of MVP I'm yet to see.
| ebiester wrote:
| So long as you never have multi-page forms and don't worry
| about error handling on bad submissions and the back button,
| you're golden!
|
| I still have nightmares of post-redirect-get complexity and
| giant balls of mud in session scope to support the back
| button.
| alexcosan wrote:
| 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)
| dathinab wrote:
| Just one thing:
|
| 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.
| asguy wrote:
| > 3. don't overuse triggers or stored plSQL procedures or
| similar they are hard to test and debug
|
| Mandate automated DB unit testing [0] from day 1, just like you
| would for the rest of your code base.
|
| [0] - https://pgtap.org/
| doctor_eval wrote:
| > 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.
| victor106 wrote:
| > even through SQL databases are used much less
|
| Really!!!
| ilyt wrote:
| > 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.
| KingOfCoders wrote:
| 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.
| dathinab wrote:
| 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
| ericmcer wrote:
| 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.
| tlarkworthy wrote:
| 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.
| doctor_eval wrote:
| 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.
| ericHosick wrote:
| My current stack is PostgreSQL (on Supabase) + retool (or similar
| front end tools).
|
| 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.
| lysecret wrote:
| Big fan of retool too. I also like to throw FastApi in front of
| Postgres on lambdas or cloud runs.
| sk55 wrote:
| What are some other frontend tools you've found to be
| effective?
| CodeWriter23 wrote:
| I'll tell you what Postgres, Kafka, Cockroach and Mongo DON'T do
| for your startup. Make sales.
| jimbokun wrote:
| 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.
| cube2222 wrote:
| 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_.
| lukeramsden wrote:
| 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.
| cube2222 wrote:
| > 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.
| alch- wrote:
| YAGNI!
| dathinab wrote:
| 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.
| glutamate wrote:
| 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?
| marcosdumay wrote:
| At such time you will also have plenty of interesting new
| problems that require changing most of your code anyway.
|
| I agree that hedging against having to move from a FOSS
| database is pure waste.
| thomaslangston wrote:
| Good tests are a lot easier to write with good interfaces.
| PaulsWallet wrote:
| > Use Postgres with TimescaleDB as a data warehouse.
|
| How does this stack compare to Snowflake or Redshift?
| tootie wrote:
| Redshift is just a heavily customized Postgres
| PaulsWallet wrote:
| 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?
| chrisdalke wrote:
| 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.
| darkr wrote:
| It uses Postgres wire protocol sure, and may even contain
| some Postgres code; otherwise this is like saying "a tank is
| just a heavily customised car"
| endisneigh wrote:
| Though Postgres is great, I personally disagree. If you're
| starting a brand new project now I think you should use something
| redundant and distributed like CockroachDB or Vitess.
| theptip wrote:
| This seems like overkill to me; a single Postgres master with
| replication/standby gets you very far. Why pay the ops burden
| of Cockroach when it buys you nothing in your first N years of
| business pre-PMF?
|
| Concretely speaking most startups should be using RDS or
| whatever hosted Postgres their cloud provider offers, not
| running their own.
|
| I think it's sensible to use what one knows best, but for
| default advice to others I think "simplest viable option" is
| generally better.
| heywherelogingo wrote:
| "Why pay the ops burden of Cockroach" - sounds like you
| haven't used it. Considerably easier than postgres.
| jpdb wrote:
| > a single Postgres master with replication/standby gets you
| very far
|
| So does a distributed database. A primary/replica has an ops
| burden too, and measuring that complexity is subjective.
|
| With primary/replica you still take downtime during failover,
| you need to make sure you're testing the failover, upgrades
| aren't zero downtime in a lot of cases, etc.
|
| You're essentially treating your database as a pet and a
| clustered option is much more cattle vs pet. This saves you
| time to develop other features (including working on
| reliability -- the most important feature)
| ebfe1 wrote:
| Add ClickHouse for analytic, columnar use case and you have
| everything.... Oh wait, Cloudflare already pointed that out XD:
|
| https://about.gitlab.com/blog/2022/04/29/two-sizes-fit-most-...
| sjducb wrote:
| I used to think this, but now that I know a bunch of these cloud
| tools; I think these frameworks standardise things between
| companies.
|
| Kafka is well documented and understood. Your custom postgres
| solution is not.
|
| Best case you write high quality maintainable code and have a
| single dependency. Worst case is you create a complete mess that
| takes months to onboard people.
|
| Right now I'm contracting on a project that uses AWS BATCH,
| docker, kubernetes, terraform, Nextflow, Django and postgres. It
| took me 2 days to on-board myself and start delivering features
| because everything was standard.
| chucke wrote:
| I've worked for a while now in the field and I never worked
| with kafka. Nor most of the peers I've worked with. And I
| wouldn't even call it the most used message queue or data
| stream solution, so I don't understand why people treat it as
| ubiquitous software. SQL, on the other hand, is. And while it's
| certainly not built for message queues, that's not the point of
| the article.
| jerry1979 wrote:
| I really like postgres, but I'm starting to wonder if some of
| these articles are GPT or not.
| donatzsky wrote:
| The "Use stored procedures" link points to ChatGPT, so I'd say
| that's a distinct possibility here.
| ls-lah_33 wrote:
| Good to see I'm not the first one to notice. Some of the
| author's points are super vague:
|
| > 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
| smitty1e wrote:
| Then there is http://postgis.net/ for Geospatial requirements.
|
| Serious question, though: when the data span several instances,
| how well does PostGreSQL do compared with Elasticsearch?
| projectileboy wrote:
| Context matters. If you're going to be scaling huge, this advice
| is silly. If you're just starting, or planning to rewrite later,
| or for good reasons never expect huge scale, staying as simple as
| possible makes sense. Also, tons of devs build to scale huge for
| systems that will never, ever scale huge.
|
| I have two competing values in this space: someone else has
| solved problem x better than you will, and you will live to
| regret every dependency you introduce.
| friedman23 wrote:
| 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?
| Joel_Mckay wrote:
| The best part of Postgres was it wasn't MySQL.
|
| ...geospatial types were just a nice addition later. ;)
| wistlo wrote:
| Agree. In my case, I went from Excel stare'n'compare to MS
| Access and finally mySQL for several years.
|
| mySQL worked great for my lil piece of company business
| involving a few million records. But then I volunteered to do
| some analysis with files an order or two of magnitude larger
| (10 mlllion-100 million). mySQL started coughing up with memory
| errors. Uh oh. Embarrassing.
|
| I should mention that I'm doing all this on a Windows desktop,
| as that's what is installed and locked down on the machines
| they give me.
|
| Enter postGRES. No matter what I throw at it, it gets to end of
| the queries without errors--in Windows, no less. With an nVME
| and 32 GB of ram, I can run a complex report on 70M records in
| seconds. Loading and indexing takes about 15 min.
|
| mySQL was very very good to me for a long time, but for the
| really big stuff I'm sticking with PostGRES.
| whoopdeepoo wrote:
| We tried using postgres for a rewrite of an existing system but
| the lack of support for columnar data made it not possible.
| DrBazza wrote:
| I've just started at a company using MSSQL and it feels like a
| massive backwards step. This article indirectly shows what's
| missing with MSSQL - an 'ecosystem' of people developing products
| on top of it, and a much larger number of people working on what
| is, ironically, a free product.
| albertopv wrote:
| Having experience with both, with small-medium size DB, they
| have pros and cons. MSSQL wins on ootb tooling, easier
| monitoring, collations, better query planner, ootb encryption,
| ootb data compression etc. I have seen running more than 100
| databases of hundreds of GBs on a single MSSQL instance without
| a problem. I heard stories about data corruption, never
| happened to me.
|
| Postgres wins on (lots of) datatypes, indexes types and plugin
| ecosystem, eg Postgis is great. Per process connection is meh,
| but can be mitigated. It loses a lot on all features based on
| operating system libraries, different OSes give different
| results, not great, not at all. Also if you are using postgres
| at enterprise level, better have some kind of support, which
| may cost a lot.
|
| So far I had less issues with MSSQL than with Postgres. YMMV,
| ofc.
| baq wrote:
| MSSQL is a beast of a RDBMS. There's little it can't do better
| than Postgres, so you either have to read up on it or the niche
| you're in doesn't fit.
| tormeh wrote:
| The db that makes you use bit instead of boolean is a beast?
| I disagree. IMO it's one of those technologies waiting to
| die.
| didip wrote:
| Yup, I like the general idea of using PG for everything.
|
| The author forgot to mention LISTEN/NOTIFY feature in PG. Perfect
| for low volume pubsub.
| bioemerl wrote:
| Man, I agree but I also really disagree.
|
| 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?
| sk55 wrote:
| Nailed it.
| jb3689 wrote:
| 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...
| N_A_T_E wrote:
| 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.
| spiffytech wrote:
| Gall's Law:
|
| > 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.
| viach wrote:
| > Use stored procedures
|
| For God's sake, please don't
| daneel_w wrote:
| 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.
| KingOfCoders wrote:
| 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.
| maerF0x0 wrote:
| I believe that stems from people frequently not including
| them in version control, or not doing tests.
| simonw wrote:
| I think stored procedures can be perfectly safe provides you
| follow these rules:
|
| - 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.
| lukeramsden wrote:
| > 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.
| abledon wrote:
| > they live in source control
|
| so that probably excludes 95% of legacy codebases out there
| from the 90s,00s
| tshaddox wrote:
| Aren't those also the absolute bare minimum bar for _any_
| code in a production system?
| simonw wrote:
| 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.
| ThePadawan wrote:
| The realization that database procedures are code, not
| data, even though they reside on the database (where the
| data lives) is the difficult part.
| tshaddox wrote:
| That's baffling to me. Who doesn't realize that that
| thing which looks and behaves exactly like all other code
| isn't code?
| simonw wrote:
| 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.
| tshaddox wrote:
| 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.
| tormeh wrote:
| I know a place that operated like this for years, so I
| don't have to imagine.
| CodeWriter23 wrote:
| > 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.
| jb3689 wrote:
| Such is the life of picking complex tools
| dagss wrote:
| 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)
|
| We made this tool to get the best of both worlds:
|
| https://github.com/vippsas/sqlcode
| KingOfCoders wrote:
| 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.
| flowerbreeze wrote:
| 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.
| jwmcq wrote:
| 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.
| officialchicken wrote:
| 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.
| doctor_eval wrote:
| 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.
| Lio wrote:
| He forgot one, you can also use Postgres for ML tasks too:
|
| https://postgresml.org/
| reese_john wrote:
| > Use Postgres as a message queue with SKIP LOCKED instead of
| Kafka (if you only need a message queue).
|
| I wouldn't describe Kafka as a message queue. It's more of a
| distributed commit log.
| ARandomerDude wrote:
| Messaging is the first use-case given by the Kafka docs [1] and
| is the first core capability they mention on the Kafka landing
| page [2].
|
| 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.
| pdntspa wrote:
| 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!
| KingOfCoders wrote:
| Yes I do agree. But looking into many startups, many of them
| use it as a message queue with the ability for transformations
| (E.g. Spark).
| manicennui wrote:
| Unfortunately many (most?) people use Kafka as a message queue
| with larger message size.
| cortesoft wrote:
| So I should replace our 200 node Kafka cluster with Postgres?
|
| Ok, I'll get started on Monday.
| freedomben wrote:
| > _Use Postgres as a message queue with SKIP LOCKED instead of
| Kafka (if you only need a message queue)._
|
| you have a 200 node Kafka cluster just as a message queue?
| KingOfCoders wrote:
| Tell me how it went ;-)
| radicalbyte wrote:
| 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.
| coredog64 wrote:
| The original implementation of MSMQ was built on top of MSSQL.
| I don't remember when it switched to a bespoke storage
| technology though.
| DenisM wrote:
| 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.
| popotamonga wrote:
| Do you really need it early? Got like 2 failures in 20+
| years, i just let it fail.
| kkielhofner wrote:
| Exactly.
|
| 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.
| 29athrowaway wrote:
| You can use a spoon as a knife, it will just take you more
| effort.
|
| Probably you can use Magic the gathering cards as a computer
| since it is turing complete, but it will just be inconvenient as
| hell.
| kolar wrote:
| Be careful when using Postgres as queue with priorites,
| especially if your messages are large. Removing rows doesn't free
| up the memory in Postgres and vacuum could remove rows only from
| the end of a 'page'. As a result of that queue will require
| enormous amount of space to work. The only way to free up space
| is to use VACUUM FULL which rewrites whole database and will lock
| queue for long time. I've had a lot of headaches when we've tried
| 'use Postgres for everything' on production :)
| kolar wrote:
| Also Postgres is too slow for large analytical databases. You
| need columnar database to make fast queries on >1Tb of data.
| ithkuil wrote:
| Does anyone have experience with some postgres columnar store
| extension like https://github.com/citusdata/cstore_fdw ?
| whoopdeepoo wrote:
| My experience was not enough support for common postgres
| features
| martintietz wrote:
| Agree. Here is a list of the limitations: https://github.
| com/citusdata/citus/tree/main/src/backend/col...
| martintietz wrote:
| As always: it depends. For some workloads something like
| Citus [1] might allow you stay within the PostgreSQL
| ecosystem even when you are trying to do OLAP.
|
| [1] https://github.com/citusdata/citus
| zkirill wrote:
| I think that the job of a CTO is to minimize the surface area of
| "built in-house" so that your team can focus on things for which
| your customers actually pay you.
|
| Sure, PostgreSQL can be used for sessions, but Redis has solved
| this problem on virtually every single platform a long time ago.
| Your customer probably doesn't even know what a session is, but
| they will definitely learn about them when your in-house
| implementation inevitably encounters an edge case.
|
| Of course, PostreSQL has wonderful search capabilities but it was
| never intended to be used as a search engine. Solr was created in
| 2004 and is still being improved and used in production every
| day. Do you know what will happen to your full-text search when
| your customer types in a mix of English and Chinese characters?
|
| Yes, PostgreSQL addition of SKIP LOCKED was neat but AFAIK the
| author of that feature himself recommended using a traditional
| job queue unless you had a very good reason against it. RabbitMQ
| was designed as a message queue and when you read the
| documentation you realize that they had encountered virtually
| every single problem and figured out a way to deal with it so
| that you don't have to. The documentation pretty much tells you
| what problems you are going to have later so that you can plan
| for them today.
|
| Choose boring technology (tm), follow industry best practices,
| and enable your team to get their work done using the right tools
| for the job, so they can deliver the product to the customer, and
| leave the office on time.
| tormeh wrote:
| Now your engineers need to understand all these technologies
| instead of just postgres. As usual the correct answer to when
| to repurpose existing tech in your stack and when to add new
| tech depends on your specific needs, team size, etc.
| sonthonax wrote:
| The majority of engineers understand basic SELECT and INSERT
| semantics. Correctly building queuing and caching systems on
| top of database concurrency primitives is an order of
| magnitude harder than just using RabbitMQ and Redis.
| Justsignedup wrote:
| Counter argument: 1 postgres instance is far easier to manage
| than a slew of servers. I can spin up a heroku environment with
| a postgres very quickly, and with little effort.
|
| If you're starting out with a team of say 4, and that team will
| stay small, don't waste your time integrating complex tech.
|
| Solr is DEFINITELY better than postgres fulltext search.
| However pg_search and a clever index has let me solve 95% of
| all our search needs in almost no time with zero maintenance.
| However once you want to get into search complexities, Solr
| quickly outperforms postgres, but you have a lot of setup and
| env management to do now.
|
| So it is all about team size. My instinct is at around 10
| engineers you should start thinking about which complexity is
| worth pulling out of postgres and into its own service based on
| needs.
| cube2222 wrote:
| I get where you're coming from, but I think that folks often
| underestimate the maintenance burden of additional components
| in their system.
|
| Sure, if your use case requires it, use Redis for sessions or
| RabbitMQ for queues. But you can also use a library with
| postgres, or even write the 40 lines of code yourself.
|
| Each component has its own debugging requirements and tooling.
| Each component adds a bunch of complexity. Sometimes it's worth
| it, sometimes it's not. It's not as clear-cut as you're making
| it be. There are pros and cons to both alternatives.
| SpeedilyDamage wrote:
| It takes effectively zero effort to maintain my redis
| instance in AWS, so I'm not sure you've got a real argument
| here...
| omginternets wrote:
| Perhaps you're not using it in "clever" ways, as
| recommended in TFA?
| chucke wrote:
| Now you have to pay for 2 services instead of one
| (elasticache is not cheap), and you may need to account for
| two differently configured redis instances (setting it as
| cache store requires a different configuration than setting
| it as a job queue).
|
| You'll also need to code for two integrations (orm +
| whatever you're using redis with), which may be a solved
| problem or not, depending of your stack. And even then,
| still more complex than just postgres, and more error prone
| considering you'll either have to ignore enqueue
| reliability, or find a complex way around it.
| [deleted]
| julik wrote:
| This is all fine and dandy but the article has a great
| point here. Redis is absolutely amazing, but if you bring
| it in you have to care about _more stuff_. Mo ' stuff, mo'
| problems as they say. You now need to synchronize writes
| between your Redis and your DB (hello "after_commit" hooks
| and similar, how is your read-after-write doing?). You need
| to install metrics for Redis (lest you find that suddenly
| your application spends a huge amount of time in MGETs or
| blocks on set operations). You need to have good failover
| in place at AWS and make sure you do not save anything non-
| transient into it - yes, this is how Redis is supposed to
| be used for transient stuff, but are you positive your
| application can cold-start well enough with a blank Redis?
| Oh, and now everyone on the team needs to run a Redis
| locally, and a matching version at that - hello docker-
| compose...
|
| Brief: yes, a specific datastore is usually better fit for
| the job, except that until your app requires more
| performance than Postgres can deliver if you already _have_
| Postgres you might as well just stick to it.
|
| Same for SQS - SQS is incredibly performant, but there is a
| whole lot of features it does not have which a PG-based
| queue system like good_job will give you out of the box.
| Just off the top of my head - priorities, separate queues,
| scheduling - and, do not forget, atomicity with your main
| transactional workload.
|
| So while it is usually - when everything works great and
| the workload fits - not a big deal to run a specialized
| store, it can be more economical and simpler to just stuff
| everything into the DB until you outgrow that.
| serverholic wrote:
| I actually had this debate at work a few years ago. We needed
| a queue system and I wrote a postgres prototype in about 100
| lines of code that would have worked fine for our use-case
| and would have likely scaled with us for years of growth.
|
| My boss said he just wanted to install the best thing and be
| done with it forever. So we ended up spending 10x the time
| and wrote like 10x the code to integrate a third-party
| solution and we only ended up using the most basic features.
| Not to mention additional infrastructure.
| WJW wrote:
| So those 1000 lines of code (your prototype was 100 lines
| and this was 10x that) would still have been a week tops,
| right? That doesn't seem too bad a price to pay for scaling
| potential, even if in hindsight it wasn't needed. You can't
| always know in advance which features you are going to need
| and which ones are superfluous.
| grenoire wrote:
| I think there exists a midway, where you rely on the
| heavily-tested open source solution that implements what
| you want, and a sprinkle more. RabbitMQ for example is that
| layer that your 40 lines of code would do, but pays off
| dividends in good nights' sleep.
| [deleted]
| maayank wrote:
| I think this may underplay the additional operational cost and
| risk in deploying multiple classes of services.
|
| You added RabbitMQ for that one queue use case? You suddenly
| need to handle some health check edge case in prod since your
| programmers doesn't have experience with it. Just added redis?
| You now have an extra set of server and client sdks to
| regularly patch up.
|
| Etc. Sure, there's a point where it's logical to add a new
| class of services, but it's not remotely close to zero (which
| is how I read the comment).
| zkirill wrote:
| The enemy's gate is down. If a CTO brings the surface area of
| "built in-house" down to zero while accomplishing all of
| their objectives, ad infinitum, they win the game.
|
| Obviously, it would be impossible to maintain such an
| advantage in real life for any extended period of time.
| However, orienting your team towards that goal gives the CTO
| a way to quantify risks and costs associated with
| accomplishing their objectives. Health checks and SDKs are
| standardized commodities that can be implemented and
| maintained at a predefined market rate that's always
| approaching zero. Finding and fixing a bug in your
| proprietary code has a potentially infinite cost.
| WhiteOwlEd wrote:
| Building on this, for many companies, the leader of the IT org
| has the main responsibility of focusing on end (or outside)
| customer needs at the highest quality with a low "Total Cost of
| Ownership".
| spiffytech wrote:
| > Choose boring technology (tm)
|
| I'd call Postgres pretty solidly "boring technology", including
| for session storage and job queues. People were storing
| sessions in SQL databases when I got my start in 2005!
|
| It won't address every scale and every use case, but then,
| that's never your project's requirement anyway.
|
| I frequently see the term "boring technology" treated as a
| euphemism for "what I'm accustomed to".
| [deleted]
| hkon wrote:
| What is a session?
| serverholic wrote:
| I very much disagree with the idea that a CTOs job is to
| minimize code built in-house.
|
| Every app has different needs and there's always some code that
| would be a lot simpler if it were written in-house and
| specifically built for the needs of the application.
|
| Abstraction has a cost and if you take everything off the shelf
| you'll end up with a much higher overall level of abstraction
| in your codebase. Plus, your engineers aren't going to
| understand third-party code as well as in-house code.
|
| I've worked at companies that wrote almost everything in-house
| and I've worked at companies that had a phobia of in-house
| code. Both had problems and I think the real solution is
| somewhere in the middle.
|
| Edit: I also get a "nobody was fired for buying IBM" vibe from
| this.
| didibus wrote:
| Not only that, but in-house offerings can be a competitive
| advantage. Sometimes it's over-engineering, and sometimes
| it's the secret to what makes your business successful over
| competitiors.
|
| In house has the ability to build only what is really needed
| and nothing more, and can adapt to your specific needs, it
| can also identify unique to your domain challenges and tailor
| solutions specific to that.
|
| A good CTO has good intuition into when and what makes sense
| to invest in an in-house solution and what is best using a
| self-managed open source solution, and what is best using a
| paid managed offering, and all manners of hybrids.
| otabdeveloper4 wrote:
| > I think that the job of a CTO is to minimize the surface area
| of "built in-house" so that your team can focus on things for
| which your customers actually pay you.
|
| The "built in-house" things are the only things that actually
| make your company competitive and provide shareholder value.
| There is no value in downloading and installing commonly-
| available stuff from the internet.
| pushedx wrote:
| Which is exactly why if you aren't a database vendor, you
| shouldn't have your engineers spend their time maintaining a
| proprietary database technology.
| theptip wrote:
| I'd go with this approach for my next startup, unless I had
| specific performance requirements. (Having built a company to
| Series B on the standard Django/Celery/Redis stack).
|
| Very hard to overstate the benefit of having all data in one DB
| that your developers can trivially run, mutate, and step-debug in
| your application.
| edgyquant wrote:
| very hard to undersell just how bad this is. Now every action
| on your application it running database queries.
| jawns wrote:
| The link to full-text search uses a relatively small dataset. Can
| anyone provide advice about how large a dataset has to be before
| this breaks down?
| buro9 wrote:
| I don't know when it breaks.
|
| 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.
| iends wrote:
| My company has a similar philosophy. "Just use DynamoDB for
| everything." Seemingly they don't care about costs, but otherwise
| it is mostly working.
| pkstn wrote:
| I just use MongoDB for everything. It's easy to scale both
| vertically but also horizontally. Also enhances availability and
| avoids data loss to have replica set with at least three MongoDB
| instances geographically distributed. MongoDB also keeps most
| frequent queries in memory which makes it super fast. ObjectID
| UUIDs are generated client-side making it fast writing. GridFS
| makes storing files easy, fast and robust etc...
| dpedu wrote:
| What's the favored way to run highly available postgres these
| days?
| bsagdiyev wrote:
| 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.
| cube2222 wrote:
| AWS Aurora has been working well for me in general.
| baq wrote:
| How painful is the cost?
| phamilton wrote:
| Not OP, but a big Aurora fan.
|
| 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.
| benji-york wrote:
| Here's a cost calculator:
| https://calculator.aws/#/addService/RDSPostgreSQL
|
| My rule of thumb is about $1,000 per month for a reasonably
| large DB in RDS.
| throw0101c wrote:
| I'd like to know answers on this.
|
| 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:
| # Galera Provider Configuration wsrep_on=ON
| wsrep_provider=/usr/lib64/galera-4/libgalera_smm.so
| # Galera Cluster Configuration
| wsrep_cluster_name="test_cluster" wsrep_cluster_address
| ="gcomm://First_Node_IP,Second_Node_IP,Third_Node_IP"
| # Galera Synchronization Configuration
| wsrep_sst_method=rsync # Galera Node Configuration
| wsrep_node_address="This_Node_IP"
| wsrep_node_name="This_Node_Name"
|
| 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.
| jmartrican wrote:
| Is that part of Galera free or licensed?
| rektide wrote:
| My favorite route right now is running a postgres operator on
| Kubernetes & letting it do all the work for me.
|
| 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.
| whitepoplar wrote:
| pg_auto_failover is nice:
| https://github.com/citusdata/pg_auto_failover
| Ingaz wrote:
| Run Yugabyte maybe
| faebi wrote:
| Can I use a few postgres tables as redis style in memory
| database? Can I finally have more connections to postgres without
| using pgbouncer?
| Ingaz wrote:
| What's wrong using pgbouncer? Today it's not rare to run http
| proxy in sidecars for every service.
___________________________________________________________________
(page generated 2022-12-10 23:01 UTC)