Features I wish PostgreSQL had as a developer
bytebase.com
bytebase.com
You can get the same result by changing your is_deleted boolean field to a date_deleted date field. Then use a cron to purge all the records with dates older than the configured retention period.
This is a terrible idea. The behaviour of engines that do this is unpredictable and suddenly you lose data because the engine deleted a column.
Thousands of companies successfully use declarative schema management. Google and Facebook are two examples at a large scale, but it's equally beneficial at smaller scales too. As long as the workflow has sufficient guardrails, it's safe and it speeds up development time.
Some companies use it to auto-generate migrations (which are then reviewed/edited), while others use a fully declarative flow (no "migrations", but automated guardrails and human review).
I'm the author of Skeema (https://github.com/skeema/skeema) which has provided declarative flow for MySQL and MariaDB since 2016. Hundreds of companies use it, including GitHub, SendGrid, Cash App, Wix, Etsy, and many others you have likely heard of. Safety is the primary consideration throughout all of Skeema's design: https://www.skeema.io/docs/features/safety/
Meanwhile a few declarative solutions that support Postgres include sqldef, Migra, Tusker (which builds on Migra), and Atlas.
You input your create table statement and it issues you back the migration statements? Then you can check it against your development database or whatever and if you feel fine use it?
This way you could check and modify the migration path without writing the alter statements.
This is one of the most frustrating thinks with sqlite for me. Changing a table doesn't always work with an alter statement but sometimes you need to drop and recreate it with the new columns. Why can't they do the magic for me. It's really frustrating and was often enough the sole reason I used postgres for private projects.
For sqlite in particular, check out https://david.rothlis.net/declarative-schema-migration-for-s... and https://sqlite-utils.datasette.io/en/stable/python-api.html#...
We then have a program which compares the latest schema from XML to a given database, and performs a series of CREATE, ALTER and so on to update the database so it conforms.
Since we've written it ourselves we have full control over what it does and how it does it, for example it never issues DROP on non-empty tables/columns or similar destructive actions.
We've had it for a long time now and it's worked very well for us, allowing for painless autonomous upgrades of our customers on-prem databases.
It just requires that there are some system views or similar that you can use to extract the current database schema, so you have something to compare against.
Our tool goes through the XML file and for each table runs a query to find the current columns, and for each column find the current configuration. Then compare with the columns in the XML file and decide what to do for each, ALTER, DROP or ignore (because possible data loss) etc. Datatype changed from "int" to "varchar(50)"? Not a problem since 50 chars are enough to store the largest possible int, so issue ALTER TABLE. Column no longer present? Check if existing column has any data, if not we can safely DROP the column, otherwise keep it and issue warning.
Views, triggers and stored procs are replaced if different. We minimize logic in the database, so our triggers and stored procs are few and minimal.
Materialized views require a bit of extra handling with the database we use, in that we can't alter but have to drop and recreate. So we need to keep track of this.
As you say it's very nice to use as a developer, as you only have to care about what the database should look like at the end of the day, not how it got there. Especially since almost all of our customers skip some versions (we release monthly).
I was looking at a query yesterday that joined 3 tables and we were unable to convince Postgres to use the optimal plan, which was to scan an index on Table1 that matched its ORDER BY order, and filter by joining against the other tables.
With a CTE for Table1 that had a limit on it, Postgres would pick the optimal plan, but then may underflow the desired limit after filtering. However the query plan is optimal and it will finish in 3ms.
Without the limited CTE, Postgres would run the Table2 and Table3 join in parallel with Table1, and then try to hash join like 3,0000,000 rows to produce the final tuple set. This query plan takes 40 seconds.
It’s just so frustrating knowing the system has the capability to serve a query in fractions of a second but you can’t extract that performance directly. Instead you need to write some truly bizarre code you hope the query planner will like, and then live the rest of your life in fear the query planner changes its mind.
The difference between using a LIMIT and not hints this is happening because of a low correlation statistic between that index and the order on disk. Postgres is avoiding random-access lookups in what looks to you like the optimal plan, because this blows away the disk cache (paging cache) and would result in a much slower overall time than you think it will, given other configuration values.
The CLUSTER command will reorder the table data to match a chosen index, after which postgres will use that index with an index scan the way you want it to, even if you retrieved the whole table and have a slow disk, because now with a high correlation statistic the query planner knows it'll be working with the disk cache instead of against it.
If you're on an SSD or are otherwise sure it can all be held in memory, check out the "random_page_cost" setting on https://www.postgresql.org/docs/current/runtime-config-query... which may allow the index scan without having to run CLUSTER.
There are a lot of arguments about query hints etc becoming stale and the performance changing as the table grows but I'm less worried about that - it would be a gradual degradation of performance. What worries me is that the table stats cross a threshold at 3am and suddenly the query planner chooses something crazy.
I've also wondered if an approach of trying a bunch of query plans could be fun. Get it to pick the top 10 possibilities and just run them all and record stats on which was fastest. Doubly so if it ever decides to run a seq scan where there is an index. Please. Just try the index! That said yesterday I was definitely at the point where I just wanted to express the exact query plan myself.
> Bluesky scaling lesson: Postgres is not a great choice if your data can be irregular.
> The query planner will switch over to a new plan that consumes 100% CPU in the middle of the night whenever its table stats flip the heuristics the wrong way.
> Postgres badly needs query hints like MySQL has!
A plan to phase out some of these legacy features would be even better. Don't know if they do this in general.
Perhaps a background process could re-run the query planner for frequent queries and add an appropriate index if there's a big speedup.
In some applications you might value the performance of insert a lot more than select, and not want to pay for extra storage of the index.
People write terrible schemas. ORMs hide complexities and make terrible queries.
Some people refuse to use sophisticated features like DataTime types for storing DateTypes (using String instead. I'm not talking about formatting!).
Also, neither use JOINS or VIEWS or FUNCTIONS, or even adding indexes.
They build MonDB schemas on top of PostgreSQL. And then build their own query engine, that is not like the one an RDBMS is happy to deal it.
In short: "Trash-in Trash-out" but for queries, and that is something will be triggered by a system like this.
Plus:
Query engine optimizations IS the HARDEST aspect of build a DB: Example:
https://db.in.tum.de/~radke/papers/hugejoins.pdf
> The largest real-world query that we are aware of accesses more than 4,000 relations.
And you have a lot of constraints:
- More indexes more data, less RAM, less SPACE.
- More indexes, more query optimization paths, more complex solving of query optimization
- Adding more indexes could create suboptimal access patterns.
- And that changes with time
- And that changes could be on a second - And then you can create an unintentional delay that WILL impact the pocket of somebody
Doing this stuff "silently" is a sure way to add problems that will be very hard to solve or debug. Sure, without this some of the problems still remain, but at least will be possible to see why and to know when it get solved.
If anyone manages to create a solid implementation of this, it will deserve a noble prize, an oscar, a place in the half of fame, and will be rich.
Partitioned tables works pretty well for this. https://www.postgresql.org/docs/current/ddl-partitioning.htm...
Pretty interesting idea. Do you have any stories or experience to share when going with this approach?
I like several of the other ideas, though. Branching is at least partially implemented in other RDBMSes. MS SQL Server has been able to do instant read-only database snapshots (which capture a point in time and can be queried just like the original DB) for decades.
Wonderful intention, not always easy/possible to pull off because you're not just managing the stateless structure, but the state which is stored by the structure. Migrations that drop columns by merging their data into another may well lose information and may do so in a way that makes the original data unrecoverable... not mentioning how to decide the information for any records possibly added after the dropping migration, but prior to running the rollback.
Sure, there are ways to handle these kind of scenarios, and sometimes in reversible ways... but there comes a point were a rollback looks more like moving forward adding a feature/capability than it does a return to a previous state.
Defining the "one true way" for migrations may cause more harm than good despite how convenient it may be. How migrators help you manage scenarios like rollback can vary from tool to tool and is part of the selection process when finding the right tool for your project. I've built a database migrator for my project which implements a non-traditional approach to migrations which works very well for my project, but would cause others to cringe with all sorts of objections. Sometimes an external tool is a better answer than an internal one.
I like to extend the idea and would ask for a more ingraned concept of versions and version migration.
Basically what flyway does but on DB level.
You could ask your DB from any backend if its in a migration status or not, it would be first class citizen were its absolutly clear that a DB migration should NOT disrupt production, it could also make it much more longrunning.
Like let me schedule a migration over 1 or 2 days through the DB and thanks to statistics and knowledge it knows to do the big chunks at night for example.
https://www.postgresql.org/docs/current/explicit-locking.htm...
The solution to both cases is to define them as either static constants or use an Enum. Then you would not care if the end result is a string or a number.
At my work place we simply have a static class with lock names that we use.
I assume that if you do it this way, then you see a string key in logs, views of current/locked queries, etc. Which should immensely help when debugging any kind of problems.
On mysql you can just declare on the row: `on update CURRENT TIMESTAMP`
On postgresql you have to set triggers on each table individually, and trying to automate with ddl commands require admin privileges which you not always have when applying migrations.
For example, why not address JSON paths as WHERE a.b.name = "king"
I've got a table that contains the columns length, width, height and cube which is length x width x height) but cube is like 5 columns down next to the 'short description' field (which would ideally be next to the description field!)
The order can also impact postgres query performance, but for me it's mostly a look & feel thing.
“Show HN: Multiplayer Towers of Hanoi in Postgres”
This is it for me. Does anyone know a good way of version controlling views in Postgres?