Will that also apply to plans? I'd love to see ML models come up with better plans, if possible.
1,699 karma · joined February 27, 2018
Get my name on github, email is last.first @ gmail
In particular when it comes to the database side you're going to want to limit individual realtime event writes. I suggest write to an append only log table and process the data in batches using a background worker.
If you intend to insert every event one by one and index them various ways and have foreign keys to and from, inserting is going to incur a high cost. This can be mitigated for the most part with temporal partitioning, but even then it might be fine for you use case or analytic case to do background updates over an append only log and keep your appends quite fast.
As for the network traffic and loading of the HTTP front end someone else on the team can maybe chime in on that.
EDIT: Note this is very broad advice for any realtime app and not specific to this product.
To me it reads as being colored by a very specific tool bias. For example:
>> The key difference between Jd and most other database systems is that Jd comes with a fully integrated and mature programming language.
Most major database systems come with a fully integrated and mature programming language.
>> Row orientation works for small amounts of data.
It works for a different data access pattern. Row vs column is a tradeoff spectrum. Data size is just one dimension of the analysis.
>> Row oriented is slavishly tied to the design ideas of filing cabinets and manila folders. Column oriented embraces computers.
Pretty hyperbolic.
"Just culture is a concept related to systems thinking which emphasizes that mistakes are generally a product of faulty organizational cultures, rather than solely brought about by the person or persons directly involved."
[pgsodium](https://github.com/michelp/pgsodium) 2.0.0 is a postgres extension that uses the [libsodium](https://doc.libsodium.org/) library to provide high-performance, modern cryptography support for PostgreSQL 10+.
2.0.0 includes a ton of new feature and a few bug-fixes:
* Support for [XChaCha20-SIV](https://github.com/jedisct1/libsodium-xchacha20-siv) deterministic nonce-free encryption, this is useful for one-time workflows sacrificing a bit of speed and larger key size without worrying about nonce-handling issues.
* Support for [SignCryption](https://github.com/jedisct1/libsodium-signcryption) Sign & Encrypt identity verification. Signcryption goes beyond public key verification to provide identity verification, and negotiating a shared-secret key between two parties to use fast streaming encryption of the payload.
* Key id support for HMACSHA 512/256, generichash, and shorthash. This extends support for pgsodium's key management feature to these useful hash functions.
* Support for low level [XChaCha20 streaming](https://libsodium.gitbook.io/doc/advanced/stream_ciphers/xch...). This is if you want to support your own low level algorithms on top of XChaCha20 for example, PASETO.
* More tests, docs, and small bug fixes in argument parsing
* In-memory key now protected with [sodium\_malloc](https://libsodium.gitbook.io/doc/memory_management), now the key has additional protection from over/underflow memory attack.
Check it out at:
[https://github.com/michelp/pgsodium](https://github.com/mich...
Please feel free to submit any issues you may have and as usual pull requests are greatly appreciated!
Is that the bug, or the feature?
No.
https://www.youtube.com/watch?v=CaCcOwJPytQ
He walks you though all the way from the rationale, to a simple algebraic example, to the full blown Linear Algebraic Matrix solution. It's really worth watching even just the first couple videos to get a deeper understanding of this very useful technique.
1. Faster user experience, the user isn't waiting for the business logic to complete, inserting in the queue should only take a couple of milliseconds.
2. It's more secure, the web worker can have minimal privileges (INSERT only on the queue table) but the backend workers can have much more privilege because they are not user facing.
3. You can scale the web workers orthogonal to the queue workers, as they'll likely have very different scaling properties.
Supabase also has a WAL decoding tool called WALRUS that I have not tried yet, and that could be the most efficient approach going forward. The tradeoff with queue tables is that the tables are persistent and a bit more resilient to failure/retry.
> What does devops look like?
I usually spin PostgREST workers up in some kind of managed container service, like Google Compute Engine. PostgREST is stateless, so other than upgrades, you never really need to cycle the services. As for resources PostgREST is extremely lean, I usually try to run 4 to 8 workers per gigabyte of RAM.
> Do you have sophisticated db migrations with every deploy?
You can use whatever migration tool your want. Sqitch is quite popular. I've even worked on projects that were migrated by Django but PostgREST did the API service.
> Is all the SQL in version control?
Yes this is a good approach, but it means needing a migration tool to apply the migrations in the right order, this is what Sqitch does and many ORMy libraries have migration sort of half-baked in.
It's worth noting that because many of the objects that PostgREST deals with are views, which have no persistent state, the migration of the views can be decoupled from the migration of the persistent objects like tables. Replacing a view (with CREATE OR REPLACE VIEW) can be done very quickly without locking tables as long as you don't change the view's schema.
Agreed.
> * For more complex cases (e.g. external dependencies, complex business logic), they seem so much more difficult to make work that I can't imagine anyone _not_ setting up a separate service to handle those scenarios. Maybe you can do everything with stored procedures, but Hasura, Postgraphile, et al. all seem to suggest that you should stand up a separate service for this.
You should stand up a separate service for this regardless of your choice of web server.
A web server should not be handling complex business logic cases, for many reasons but one obvious one is synchronous wait times. PostgREST or not, bigger job should go into a work queue, which should take just a few milliseconds.
The second reason a work queue is better is that it is more secure. Your web process can be given the absolute minimal privileges, say INSERT only on the queue table. The queue worker can have more privileges. The important upside is that user facing systems cannot take "complex business logic" actions by way of security enforcement.
> * Once you've got a separate service where your complexity lives, that's going to be the meat of your project.
I agree, but that will happen regardless of your web server.
> * Is it really worth it to introduce a separate dependency & pattern just to scaffold the easy parts more quickly?
Quickness is only one tiny advantage of PostgREST. Native security on database objects, streaming responses without buffering, using every available postgres feature that no other database supports without third-party plugins to your web framework, that's just the beginning, but yeah it's a hell of a lot quick and easier to use too.
I agree!
Let's put aside that being the OWNER of a database object means that by default is granted all privileges to it, and that owners aren't (typically) filtered by Row Level Security.
Google "Django Postgres Tutorial" and the first link creates a superuser:
https://www.enterprisedb.com/postgres-tutorials/how-use-post...
The second link does not, but then it goes on to `GRANT ALL PRIVILEGES ON DATABASE` to that user:
https://www.section.io/engineering-education/django-app-usin...
The documentation has a handy guide on creating superusers through the web interface, and touts what a great feature it is!
https://djangocentral.com/creating-super-user-in-django/
To a security conscious DBA, this is insanity. Yes, you can lock down a Django app just like PostgREST in fact, and add some code to do SET ROLE and all that just like PostgREST, but no one does because the documentation on database setup in the Django does doesn't even mention superusers:
https://docs.djangoproject.com/en/3.2/ref/settings/#std:sett...
1. Your web framework logging into the database as a superuser.
This is huge: "kitchen-sink" frameworks tend to log into the database as a superuser, typically so they can "do migrations" from some SQL-degenerate ORM.
PostgREST (as documented) logs into the database as a user with NO privileges at all, and switches roles (with SET ROLE) to the role defined in the signed JWT token. If you never sign a token with a superuser role, users can never run queries as a superuser. You can also enforce host based security so that your web servers can't even SET ROLE even if they had a bogus signed token.
2. Buffering full database query responses as garbage collected "objects" and then serialize them to JSON.
Another problem proxy frameworks have is that they compose a SQL query, run it (typically as a superuser, sigh), then buffer then entire response in memory, typically as "objects" that need to be immediately garbage collected, and then rendered into JSON in another buffer! Finally the response is then streamed to the browser.
Postgres speaks JSON, and can produce rows of data as JSON natively. This data can be streamed directly to the browser before the query completes. This is a huge cost. Django workers typically need several gigabytes of RAM, proportional to the largest possible query response your database can produce. PostgREST does no buffering, parsing, or rendering, it just streams the json response directly from the database. Processes typically need only one or two hundred megabytes of RAM at most.
3. Tables and Views are elements of an object oriented type system. Not just a storage mechanisms.
Postgres is an Object-Oriented Relational Database Management System (OORDBMS). Tables are not just places you stick stuff, they are types and rows of data are instances of those types. Views are types composed of sub-types, which can in turn be views or tables. Postgres rewrites a query until all the views are expanded, like macros, until it gets one big query you never have to ever see.
PostgREST leverages all of this power of the database, you can model in the language that is the best for modeling data, SQL.
Good question. Ok let's do a thought experiment between PostgREST and Django. Every single Django production system I've seen logs into Postgres as a superuser (so it can do migrations).
PostgREST logs into the database as a user with NO privileges (because migration is a problem for a migration tool, not a web framework!) and then switches roles based on the (signed) JWT token. The PostgREST user is NEVER a superuser.
So you flub a Django permission and users can now run queries as a superuser and potentially leak everythinv. If you flub a PostgREST configuration, you leak data maybe in one or two tables.
> 2) the size of the attack surface (if we expose DB directly, as opposed to having an additional layer)
This is going to differ depending on who you ask. People talk about attack "surfaces" but I personally think of them as "attack paths". Does more code mean more paths? I think yes. Others think no.
> 3) how many developers are proficient in Postgres enough to avoid #1 and #2 as opposed to traditional code-based security policies
Postgres' security mechanism comes down to "who can see this" it's the same question those developers would be asking in Django. If they're not proficient enough to understand now to GRANT or REVOKE are they going to be proficient enough to understand the implications of their application logging into the database as a superuser?
Yes you are correct my statement was too vague, I was referring to Postgres' role based security in general at that point. RLS specifically was added in 9.5. Before that permission checking was table/column based, so you could still protect data on a per-role basis, just not as fine a grain.
Another subtle difference is that RLS hides rows you can't see, where as a REVOKE on a table or column would cause an error if you tried to access it. In either case unauthorized users cannot see protected data.
There are definitely fewer buffered copies of your responses, yes.
This is one advantage few people know about PostgREST, once the database produces even a single row of data, it starts to stream JSON to the frontend. Users don't have to wait for the response to fully buffer in memory in some Python or Go process before being relayed on.
As for security, Postgres supports Row Level Security. You can literally protect every single row of your data with a solid security policy using a security mechanism older and tested longer than the average age of your typical HN reader.
This security mechanism doesn't just apply to your web frontend either, but to all SQL clients regardless of the frontend language. No other framework has such a deep integration with Postres' native security mechanism.
One approach is to use Point In Time Recovery. When you run your migration, you take note of the LSN (the point in the WAL stream) just before you make your changes. Then you can roll back to the point in time right before you applied the migration.
Note that you will need some other mechanism for restoring any data you added after the migration point.
FORTH was invented in 1968, and was first used outside of Moore's lab by 1970. I could only wish one of my works was still being trash-talked on HN 53 years from now! You can't pay for that kind of "hype".
https://youtu.be/JJzU9hDjiRk?t=819
In summary, there is a strong selection bias in cosmology toward the standard model which induces "predictions" that confirm themselves. One bit of evidence he presents, dozens of studies were found to be within one sigma of the wmap measurement and not naturally distributed as one would expect.
https://en.wikipedia.org/wiki/Log_semiring
which can be used to multiply two sparse matrices with the GraphBLAS. here is an example in Python:
https://github.com/Graphegon/pygraphblas/blob/main/demo/Log-...
Dense numeric processing for image recognition is a key foundation for what Tesla is trying to do, but that tagging of the object is just the beginning of the process, what is the object going to do? What are its trajectories, what is the degree of belief that a unleashed dog vs a stationary baby carriage is going to jump out?
We are just beginning to scratch the surface of counterfactual and other belief propagation models which are hypersparse graph problems at their core. This kind of chip, and what Cerebras are working on, are the future platforms for the possibility of true machine reasoning.
This paper by Guy Steele and Danny Hillis is also a good introduction to the Lisp language they were developing (but from what I can tell never got off the ground) for the CM.
http://diyhpl.us/~bryan/papers2/paperbot/25aa007a093cd69bbf0...
In particular the language has two new operators, alpha and beta, that today we might consider flexible analogs to map (compute) and reduce (communicate). It's really fascinating how they made an algebraically closed language around this concept.
This lecture by Hillis is also fascinating:
https://www.youtube.com/watch?v=Ua-swPZTeX4
Near the end he talks about how this room-sized machine had a syncronous clock, and how there were bits stored in the wires in transit because all the wire lengths were carefully cut multiples of the clock period.
https://www.amazon.com/Misbehavior-Markets-Fractal-Financial...