sa46

Is the idea that if you build the framework you can build the ideal platform for the framework with a nice warm customer base and collect that sweet recurring revenue. Way more upside than selling licenses.

Sounds like timescale, metabase, and posthog.

You might like pggen (I’m the author) which only supports Postgres and pgx. https://github.com/jschaf/pggen

pggen occupies the same design space as sqlc but the implementations are quite different. Sqlc figures out the query types using type inference in Go which is nice because you don’t need Postgres at build time. Pggen asks Postgres what the query types are which is nice because it works with any extensions and arbitrarily complex queries.

SimpleCircle | Senior Backend Engineer | San Francisco | Full-time

Join us as an engineer at our five-person, fully funded startup. We’re looking for a senior engineer well-versed with the intricacies of building rich, data-backed applications. You will develop and architect a greenfield ERP for breweries from the ground floor and help shape a world-class engineering team.

SimpleCircle helps craft brewers distribute their products to local points of sale, with an operations management platform that automates clerical chores, while supercharging sales with superior mobile tools and product analytics. Our customers look to us to take care of a large chunk of running the business side of their breweries so they can focus on the beer.

Tech stack:

  - React & Typescript

  - Go backend using gRPC (actually Twirp)

  - Postgres

We’ve got the right foundation in place: our CTO is former Google, Heap, and Army, and our CEO was product at four early startups and former McKinsey. We’re backed by top-flight VCs with deep connections to the alcohol industry. Drop us an email!

We’re based in SF in the Mission (neighbors with Smitten Ice Cream).

careers@simplecircle.io

Good recommendation; I also got a lot of mileage out of:

Managing Time in Relational Databases: How to Design, Update and Query Temporal Data How to Design, Update and Query Temporal Data

> be careful if you tend to over-engineer or are perfectionist

One of the downsides of these books is that I see bitemporal data everywhere now but its quite difficult to write bitemporal queries in Postgres.

If you're tempted to use audit tables, you might also consider making the jump to temporal tables. I rolled my own light-weight version using inheritance-based partitioning in Postgres. The basic idea is:

- Create a parent table like items with a valid time range column as a tstzrange type. This table won't store data.

- Create two child tables using Postgres inheritance, item_past and item_current that will store data.

- Use check constraints to enforce that all active rows are in the current table (by checking that the upper bound of the tstzrange is infinite). Postgres can use check constraints as part of query planning to prune to either the past or current table.

- Use triggers to copy from the current table into the past table on change and set the time range appropriately.

The benefits of this kind of uni-temporal table over audit tables are:

- The schema for the current and past is the same and will remain the same since DDL updates on the parent table propagate to children. I view this as the most substantial benefit since it avoids information loss with hstore or jsonb.

- You can query across all versions of data by querying the parent item table instead of item_current or item_past.

The downsides of temporal tables:

- Foreign keys are much harder on the past table since a row might overlap with multiple rows on the foreign table with different times. I limit my use of foreign keys to only the current table.

I use plpgsql for constraints but try to avoid any form of mutation mostly based on other folks horror stories.

Not a rust aficionado: If you’re using error.As, wouldn’t you need essentially the same logic in Rust to match the error type and get access to the right fields?

Seems like the big difference is the compiler knows the variants of the error. The trade off is that Rust modules are tightly coupled by the error type since you have wrap each one into the module’s own error type.

Could Postgres convert the function into a DFA using the JIT optimizer (based on LLVM)? That might delve into sufficiently smart compiler territory but recognizing a bunch of OR'ed string matches seems on the easier end of optimization passes.

You'll hit all sorts of other fun problems by vertically scaling.

- Many concurrent connections to a single database which Postgres has traditionally not handled well (though improved in recent versions).

- You're now on the hook for writing a database control plane.

- Backup and restore is much flakier since the data volume is so much larger. Lots of weird shit starts happening when you download tens of TB.

- In general, everything is a harder once you start nearing machine limits.

It's not an uncommon pattern: https://github.com/postgres/postgres/blob/master/src/backend...

They're a really bad idea, so much so that's it's the first item in Effective Java:

> Consider static factory methods instead of constructors

The reasons outlined in Effective Java include:

- Unlike constructors, static factory methods can have names.

- Unlike constructors, static factory methods are not required to create a new object each time they're invoked (immutable objects, flyweight pattern).

- Unlike constructors, they can return an object of any subtype of their return type.

- Unlike constructors, the class of the returned object can vary from call to call as a function of the input parameters.

Effective Java even has advice for overloading: Item 52, "use overloading judiciously"

> A safe, conservative policy is never to export two overloadings with the same number of parameters.

I prefer stronger guidance for overloading that I'll paraphrase poorly from a forgotten source (Guava maybe): each overload should return pretty much the same thing and it should be obvious from the call site. The OP's article violates this principle since warnings should be distinct from errors.

I'm pretty comfortable declaring that creating a mutable-by-default date, string, or number library is a mistake. I'm not on the make-all-the-things-immutable train but making dates mutable is a painful mistake that Java painfully worked through with JSR-310, the new date and time library.

Part of the problem is that moment would both mutate the underlying object and return the object.

    const a = moment()
    const b = a.add(1, 'second')

Both b and a are now the same object. I've lost a few hours to the design choice.

One thing I’m pursuing with pggen is serializing DB query results directly to a proto. It’d be super cool to write a sql query and have it spit out a proto and avoid all the boilerplate struct copying.

I uploaded most of our Workspace setup here: https://github.com/jschaf/bazel-postgres-sketch. The tooling is a bunch of Go to manage the Postgres process. Basically, give it schema files and receive a running database with a tear down command.

We make temp instances of Postgres quickly by:

- avoiding Docker, especially on Mac

- keeping the data dir on tmpfs

- Disable initdb cleanup

- Disable fsync and other data integrity flags

- Use unlogged tables.

- Use sockets instead of TCP localhost.

For a test suite, it was 12x faster to call createdb with the same Postgres cluster for each test than than to create a whole new db cluster. The trick was to create a template database after loading the schema and use that for each createdb call.

Oh hey! Apologies, not trying to throw shade. Last time I used sqlc was a while ago and I ran it in docker.

Yep, that’s the main downside. pggen works out of the box with Docker under the hood if you give it some schema files. Notably, the recommended way to run sqlc also requires Docker.

I check in the generated code so I only run pggen at dev time, not build time. I do intend to move to build time codegen with Bazel but I built out tooling to launch new instances of Bazel managed Postgres in 200 ms so not that painful.

More advanced database setups can point pggen at a running instance of Postgres meaning you can bring your own database which is important to support custom extensions and advanced database hackery.

I agree whole-heartedly that writing SQL feels right. Broadly speaking, you can take the following approaches to mapping database queries to Go code:

- Write SQL queries, parse the SQL, generate Go from the queries (sqlc, pggen).

- Write SQL schema files, parse the SQL schema, generate active records based on the tables (gorm)

- Write Go structs, generate SQL schema from the structs, and use a custom query DSL (proteus).

- Write custom query language (YAML or other), generate SQL schema, queries, and Go query interface (xo).

- Skip generated code and use a non-type-safe query builder (squirrel, goqu).

I prefer writing SQL queries so that app logic doesn't depend on the the database table structure.

I started off with sqlc but ran into limitations with more complex queries. It's quite difficult to infer what a SQL query will output even with a proper parse tree. sqlc also didn't work with generated code.

I wrote pggen with the idea that you can just execute the query and have Postgres tell you what the output types and names will be. Here's the original design doc [1] that outlines the motivations. By comparison, sqlc starts from the parse tree, and has the complex task of computing the control flow graph for nullability and type outputs.

[1]: https://docs.google.com/document/d/1NvVKD6cyXvJLWUfqFYad76CW...

Disclaimer: author of pggen (https://github.com/jschaf/pggen), inspired by sqlc

I was curious about the specific type of munition. It was for smokeless gunpowder:

> unquenchable thirst for the industrial alcohol needed to produce cordite—a smokeless gunpowder used in ammunition and artillery shells

https://en.wikipedia.org/wiki/Cordite

SimpleCircle | Founding Engineer | San Francisco | Full-time

Join us as a founding engineer at our five-person, fully funded startup. We’re looking for a senior engineer well-versed with the intricacies of building rich, data-backed applications. You will develop and architect a greenfield ERP for breweries from the ground floor and help shape a world-class engineering team.

SimpleCircle helps craft brewers distribute their products to local points of sale, with an operations management platform that automates clerical chores, while supercharging sales with superior mobile tools and product analytics. Our customers look to us to take care of a large chunk of running the business side of their breweries so they can focus on the beer.

Tech stack:

- React & Typescript

- Go backend using gRPC (actually Twirp)

- Postgres

We’ve got the right foundation in place: our CTO is former Google, Heap, and Army, and our CEO ran product at four early startups and former McKinsey. We’re backed by top-flight VCs with deep connections to the alcohol industry. Drop us an email!

We’re based in SF in the Mission (neighbors with Smitten Ice Cream).

hn.careers@simplecircle.io

I ended up skipping static compilation for the reasons you outlined (not necessary). I packaged everything up in a directory and used LD_LIBRARY_PATH. I linked to a sketch on GitHub in a sibling comment.

Sure, here's sketch of how it works: https://github.com/jschaf/bazel-postgres-sketch

It's possible but tediuos to run Postgres standalone. I packaged Postgres up for our Bazel build which requires a hermetic environment. The hard parts were:

1. Postgres doesn't really didn't want to be statically compiled, according to [1].

2. Messing with LD_LIBRARY_PATH to point to an embedded glibc and openssl when running the heremetic postgres binaries.

3. Postgres will absolutely refuse to run the server process as root. That's sensible for a production environment but a pain in CI because it would require setting up another user on machines. I patched away the problem to allow Postgres to run as root.

On the bright side, with some minor tweaking, Postgres can start a fresh database in 300 ms which is plenty fast for tests and you avoid spinning up Docker containers. I ended up using template databases to only spin up the database once per test suite. Each tests then copies the database template for each test in the suite which reduced the setup time per test to ~20 ms.

Instead of using rules_foreign_cc to build Postgres from source with Bazel, I ended up building Postgres outside of Bazel with Docker and zipping it up by target platform (linux|darwin)_(arm64|amd64).

https://www.postgresql.org/message-id/4E0DE1B6.6070600@postn...

There's a lot wrong with SQL. Chris Date [1] wrote several books comparing SQL to a more pure implementation relational algebra, Tutorial D. In Date's words:

> SQL is incapable of providing the kind of firm foundation we need for future growth and development. Instead, it’s the relational model that has to provide that foundation. [...] We see SQL as a kind of database COBOL, and we would like to see some other language become available as a better alternative to it.

Summarizing Date's points:

- Real relations don't contain duplicate tuples but SQL tables allow duplicate rows.

- Tuples and therefore relations don't ever contain nulls. Personally, I have trouble understanding how to support this limitation given LEFT JOIN.

- Domain types only provide type aliases not a new type. For example, you can compare or join on different domain types if the base type is the same. Date uses the example of `p.weight = sp.qty` to show a comparison on domain types that shouldn't be allowed.

- SQL has many ways to express the same query. His example shows at least 12 ways to answer "Get part numbers for parts that either are screws or are supplied by supplier S1, or both."

- SELECT DISTINCT should have been the default instead of SELECT ALL.

My personal gripes:

- SQL is quite chatty, so much so that you can omit words in some incantations.

- It's hard to dynamically build queries. It composes poorly for anything dynamic, even simple things like ordering by a column name.

[1]: https://en.wikipedia.org/wiki/Christopher_J._Date

It's really not. Coffeescript might be nice with fresh development and a small codebase but it's really bad when you're trying to figure out someone else's code.

This is great until you have a tenant too large for a single shard. It’s pretty difficult to perfectly shard relational data. At some point you’ll run into a need for cross shards queries.

I think for most workloads, this approach should work for a good long while.

The really fun part of having docs in confluence is that if Confluence goes down, you lose access to ops playbooks.

I’ve had similar fun experience with Slack-ops when slack goes down.

I really like the sound of this. That’s how Google’s internal technical docs worked. Write markdown in src repo, get nice, searchable doc site in return.

Anyone know if a tool like that already exists to turn README.md files into a website?

I wrote a similar tool for Go and Postgres but it also generates the serialization code. [1]

- I prefer generating code from queries instead of generating active record style models based on tables.

- I think it’s worth it to spin up Postgres to get type info instead of trying to parse queries. Reimplementing Postgres’ type inference is a Herculean task even with an AST. Sqlc, which directly inspired pggen does this.

- It’s hard to support customization of generated code in a structured way. The way I’m leaning towards is if you need customization, you need to use the SDK which provides plugin hooks. Using flags for customization is the road to madness.

- What I would like to do is serialize directly to protobuf to avoid the serialization dance of Go structs to Proto structs.

[1]: https://github.com/jschaf/pggen

An earlier submission has a lively debate between these two approaches. https://news.ycombinator.com/item?id=12120752

Another common slow down is bind mounts. Each file system operation is an RPC request between Docker for Mac and the Linux VM.

I’ve never been super happy with Docker for Mac. I did a pretty deep dive into figuring out why it’s so slow with no satisfying conclusion: https://stackoverflow.com/q/58277794/30900