Simon Willison's Weblog: d-richard-hipp

Production query plans without production data

2026-03-09T15:05:15+00:00

Production query plans without production data

Radim Marek describes the new pg_restore_relation_stats() and pg_restore_attribute_stats() functions that were introduced in PostgreSQL 18 in September 2025.

The PostgreSQL query planner makes use of internal statistics to help it decide how to best execute a query. These statistics often differ between production data and development environments, which means the query plans used in production may not be replicable in development.

PostgreSQL's new features now let you copy those statistics down to your development environment, allowing you to simulate the plans for production workloads without needing to copy in all of that data first.

I found this illustrative example useful:

SELECT pg_restore_attribute_stats(
    'schemaname', 'public',
    'relname', 'test_orders',
    'attname', 'status',
    'inherited', false::boolean,
    'null_frac', 0.0::real,
    'avg_width', 9::integer,
    'n_distinct', 5::real,
    'most_common_vals', '{delivered,shipped,cancelled,pending,returned}'::text,
    'most_common_freqs', '{0.95,0.015,0.015,0.015,0.005}'::real[]
);

This simulates statistics for a status column that is 95% delivered. Based on these statistics PostgreSQL can decide to use an index for status = 'shipped' but to instead perform a full table scan for status = 'delivered'.

These statistics are pretty small. Radim says:

Statistics dumps are tiny. A database with hundreds of tables and thousands of columns produces a statistics dump under 1MB. The production data might be hundreds of GB. The statistics that describe it fit in a text file.

I posted on the SQLite user forum asking if SQLite could offer a similar feature and D. Richard Hipp promptly replied that it has one already:

All of the data statistics used by the query planner in SQLite are available in the sqlite_stat1 table (or also in the sqlite_stat4 table if you happen to have compiled with SQLITE_ENABLE_STAT4). That table is writable. You can inject whatever alternative statistics you like.

This approach to controlling the query planner is mentioned in the documentation: https://sqlite.org/optoverview.html#manual_control_of_query_plans_using_sqlite_stat_tables.

See also https://sqlite.org/lang_analyze.html#fixed_results_of_analyze.

The ".fullschema" command in the CLI outputs both the schema and the content of the sqlite_statN tables, exactly for the reasons outlined above - so that we can reproduce query problems for testing without have to load multi-terabyte database files.

Via Lobste.rs

Tags: databases, postgresql, sql, sqlite, d-richard-hipp

Quoting D. Richard Hipp

2025-12-29T21:51:49+00:00

But once we got that and got this aviation grade testing in place, the number of bugs just dropped to a trickle. Now we still do have bugs but the aviation grade testing allows us to move fast, which is important because in this business you either move fast or you're disrupted. So, we're able to make major changes to the structure of the code that we deliver and be confident that we're not breaking things because we had these intense tests. Probably half the time we spend is actually writing new tests, we're constantly writing new tests. And over the 17-year history, we have amassed a huge suite of tests which we run constantly.

Other database engines don't do this; don't have this level of testing. But they're still high quality, I mean, I noticed in particular, PostgreSQL is a very high-quality database engine, they don't have many bugs. I went to the PostgreSQL and ask them “how do you prevent the bugs”? We talked about this for a while. What I came away with was they've got a very elaborate peer review process, and if they've got code that has worked for 10 years they just don't mess with it, leave it alone, it works. Whereas we change our code fearlessly, and we have a much smaller team and we don't have the peer review process.

— D. Richard Hipp, ACM SIGMOD Record, June 2019 (PDF)

Tags: testing, d-richard-hipp, postgresql, sqlite

Copyright Release for Contributions To SQLite

2025-12-29T19:58:45+00:00

Copyright Release for Contributions To SQLite

D. Richard Hipp called me out for spreading misinformation on Hacker News that SQLite refuses outside contributions:

No, Simon, we don't "refuse". We are just very selective and there is a lot of paperwork involved to confirm the contribution is in the public domain and does not contaminate the SQLite core with licensed code.

I deeply regret this error! I'm linking to the copyright release document here - it looks like SQLite's public domain nature makes this kind of clause extremely important:

[...] To the best of my knowledge and belief, the changes and enhancements that I have contributed to SQLite are either originally written by me or are derived from prior works which I have verified are also in the public domain and are not subject to claims of copyright by other parties.

Out of curiosity I decided to see how many people have contributed to SQLite outside of the core team of Richard, Dan and Joe. I ran that query using Fossil, SQLite's own SQLite-based version control system, like this:

brew install fossil
fossil clone https://www.sqlite.org/src sqlite.fossil
fossil sql -R sqlite.fossil "
  SELECT user, COUNT(*) as commits
  FROM event WHERE type='ci'
  GROUP BY user ORDER BY commits DESC
"

I got back 38 rows, though I think danielk1977 and dan may be duplicates.

Update: The SQLite team have clarified this on their SQLite is Public Domain page. It used to read "In order to keep SQLite completely free and unencumbered by copyright, the project does not accept patches." - it now reads:

In order to keep SQLite completely free and unencumbered by copyright, the project does not accept patches from random people on the internet. There is a process to get a patch accepted, but that process is involved and for smaller changes is not normally worth the effort.

Tags: open-source, sqlite, d-richard-hipp

Quoting D. Richard Hipp

2024-10-18T01:21:44+00:00

I'm of the opinion that you should never use mmap, because if you get an I/O error of some kind, the OS raises a signal, which SQLite is unable to catch, and so the process dies. When you are not using mmap, SQLite gets back an error code from an I/O error and is able to take remedial action, or at least compose an error message.

— D. Richard Hipp

Tags: d-richard-hipp, sqlite

Quoting D. Richard Hipp

2024-08-28T22:30:28+00:00

My goal is to keep SQLite relevant and viable through the year 2050. That's a long time from now. If I knew that standard SQL was not going to change any between now and then, I'd go ahead and make non-standard extensions that allowed for FROM-clause-first queries, as that seems like a useful extension. The problem is that standard SQL will not remain static. Probably some future version of "standard SQL" will support some kind of FROM-clause-first query format. I need to ensure that whatever SQLite supports will be compatible with the standard, whenever it drops. And the only way to do that is to support nothing until after the standard appears.

When will that happen? A month? A year? Ten years? Who knows.

I'll probably take my cue from PostgreSQL. If PostgreSQL adds support for FROM-clause-first queries, then I'll do the same with SQLite, copying the PostgreSQL syntax. Until then, I'm afraid you are stuck with only traditional SELECT-first queries in SQLite.

— D. Richard Hipp

Tags: d-richard-hipp, sql, postgresql, sqlite

Datasette 0.64.7

2024-06-12T22:55:00+00:00

Datasette 0.64.7

A very minor dot-fix release for Datasette stable, addressing this bug where Datasette running against the latest version of SQLite - 3.46.0 - threw an error on canned queries that included :named parameters in their SQL.

The root cause was Datasette using a now invalid clever trick I came up with against the undocumented and unstable opcodes returned by a SQLite EXPLAIN query.

I asked on the SQLite forum and learned that the feature I was using was removed in this commit to SQLite. D. Richard Hipp explains:

The P4 parameter to OP_Variable was not being used for anything. By omitting it, we make the prepared statement slightly smaller, reduce the size of the SQLite library by a few bytes, and help sqlite3_prepare() and similar run slightly faster.

Tags: projects, sqlite, datasette, annotated-release-notes, d-richard-hipp

Quoting D. Richard Hipp

2024-05-22T04:21:12+00:00

The default prefix used to be "sqlite_". But then Mcafee started using SQLite in their anti-virus product and it started putting files with the "sqlite" name in the c:/temp folder. This annoyed many windows users. Those users would then do a Google search for "sqlite", find the telephone numbers of the developers and call to wake them up at night and complain. For this reason, the default name prefix is changed to be "sqlite" spelled backwards.

— D. Richard Hipp, 18 years ago

Tags: d-richard-hipp, sqlite

Quoting D. Richard Hipp

2024-04-30T13:59:50+00:00

Performance analysis indicates that SQLite spends very little time doing bytecode decoding and dispatch. Most CPU cycles are consumed in walking B-Trees, doing value comparisons, and decoding records - all of which happens in compiled C code. Bytecode dispatch is using less than 3% of the total CPU time, according to my measurements.

So at least in the case of SQLite, compiling all the way down to machine code might provide a performance boost 3% or less. That's not very much, considering the size, complexity, and portability costs involved.

— D. Richard Hipp

Tags: d-richard-hipp, performance, sqlite

Why SQLite Uses Bytecode

2024-04-30T05:32:54+00:00

Why SQLite Uses Bytecode

Brand new SQLite architecture documentation by D. Richard Hipp explaining the trade-offs between a bytecode based query plan and a tree of objects.

SQLite uses the bytecode approach, which provides an important characteristic that SQLite can very easily execute queries incrementally—stopping after each row, for example. This is more useful for a local library database than for a network server where the assumption is that the entire query will be executed before results are returned over the wire.

Via @DRichardHipp

Tags: databases, sql, sqlite, d-richard-hipp

Quoting D. Richard Hipp

2023-09-18T18:02:37+00:00

Note that there have been no breaking changes since the [SQLite] file format was designed in 2004. The changes shows in the version history above have all be one of (1) typo fixes, (2) clarifications, or (3) filling in the "reserved for future extensions" bits with descriptions of those extensions as they occurred.

— D. Richard Hipp

Tags: d-richard-hipp, sqlite

Quoting D. Richard Hipp

2023-02-08T18:07:06+00:00

Many people, and even a few companies, have contributed code to SQLite over the years. I have legal documentation for all such contributions in the firesafe in my office. We are able to track every byte of the SQLite source code back to its original creator. The project has been and continues to be open to outside contributions, as long as those contributions meet high standards of provenance and maintainability.

— D. Richard Hipp

Tags: d-richard-hipp, sqlite, open-source

Notes on the SQLite DuckDB paper

2022-09-01T17:33:14+00:00

SQLite: Past, Present, and Future is a newly published paper authored by Kevin P. Gaffney, Martin Prammer and Jignesh M. Patel from the University of Wisconsin-Madison and D. Richard Hipp, Larry Brasfield and Dan Kennedy from the core SQLite engineering team.

The paper compares SQLite and DuckDB, and describes some optimization work to make SQLite perform better for analytical queries.

DuckDB is a relatively new project which is frequently nicknamed (including by this paper) "SQLite for analytics". It shares many of the characteristics of SQLite: an embedded database implemented as a header file and implementation file (using C++ as opposed to SQLite's C) with databases that are theselves single binary files.

The primary difference is that DuckDB is optimized for analytical queries: queries that apply aggregate calculations across large numbers of rows, rather than being optimized for fast scanning and lookup of individual rows of data.

I've been tracking DuckDB for a while - it's a very exciting project. If I ever introduce alternative database backends to Datasette it's top of my list of things to try.

The paper spends quite a bit of time on benchmark comparisons. To very loosely summarize those:

SQLite out-performs DuckDB on a write transactions benchmark by 10x-500x on a powerful cloud server and 2x-60x on a Raspberry Pi, for small to large databases.
For analytical benchmarks using the SSB (Star Schema Benchmark) DuckDB out-performs SQLite by 30-50x at the highest margin and 3-8x at the lowest.

Neither of these are particularly surprising: DuckDB isn't optimized for write transactions and SQLite isn't optimized for analytical queries.

Next, the paper explores optimizations that can be applied to SQLite to improve its analytical query performance.

One of these has already shipped! In February 2022 the SQLite 3.38.0 release notes included this:

Use a Bloom filter to speed up large analytic queries.

I had wondered at the time what the deal with this was - the paper explains it in some detail:

A key change is made to the join processing, which is to probe the Bloom filters before carrying out the rest of the join. Applying the Bloom filters early in the join pipeline dramatically reduces the number of tuples that flow through the join pipeline, and thus improves performance.

This had a pretty big impact on the benchmark:

The performance impact of our optimizations is shown in Figure 6. On the Raspberry Pi, SQLite is now 4.2X faster on SSB. Our optimizations are particularly effective for query flight 2, resulting in 10X speedup. On the cloud server, we observed an overall speedup of 2.7X and individual query speedups up to 7X

I decided to quote in full the first part of section 5, "future development", because it does a great job of describing some of the key features of the culture of SQLite itself.

5 FUTURE DEVELOPMENT

The developers intend to provide support for SQLite through the year 2050, and design decisions are made accordingly. SQLite’s code and database file format are fully cross-platform, ensuring that SQLite can run on any current or future platform with an 8-bit byte, two’s complement 32-bit and 64-bit integers, and a C compiler. Every machine-code branch in the SQLite library is tested with multiple platforms and compilers, which makes the code robust for future migrations. SQLite is also extensively documented and commented, which helps new developers quickly understand SQLite’s architecture. Finally, the developers work hard to evaluate new programming trends based on merit rather than popularity.

While the performance gap has narrowed as a result of this work, DuckDB is still considerably faster than SQLite on SSB. This is somewhat expected; SQLite is a general-purpose database engine, whereas DuckDB is designed from the ground up for efficient OLAP. Although SQLite’s OLAP performance could be further improved in future work, there are several constraints that potential modifications to SQLite must satisfy.

First, modifications should cause no significant performance regression across the broad range of workloads served by SQLite. Second, the benefit of an optimization must be weighed against its impact on the size of the source code and the compiled library. Finally, modifications should not break SQLite’s backwards compatibility with previous versions and cross-compatibility with different machine architectures

Although SQLite’s performance is a key priority, it must be balanced with these (sometimes competing) goals. We considered several means of improving value extraction in SQLite, but no single solution satisfied all the constraints above. For example, changing the data format from row-oriented to column-oriented would streamline value extraction, but it would also likely increase overhead for OLTP workloads. Moreover, drastic changes to the data format are at odds with SQLite’s goal of stability for the database file format

That section continues with this intriguing detail:

An alternative approach to improving SQLite’s OLAP performance is a separate, yet tightly connected query engine that evaluates analytical queries on its own copy of the data, while SQLite continues to serve transactional requests, ensuring that the analytical engine stays up to date with the freshest data. If the extra space overhead is acceptable, the specialized analytical engine can provide substantial OLAP performance gains.

This design has been successfully implemented in SQLite3/HE [46], a query acceleration path for analytics in SQLite. SQLite3/HE achieves speedups of over 100X on SSB with no degradation in OLTP performance. However, the current implementation of SQLite3/HE does not persist columnar data to storage and is designed to be used in a single process. Future work may explore similar approaches without these limitations.

This is the first I had heard of SQLite3/HE. It's described in this paper: Introducing a Query Acceleration Path for Analytics in SQLite3 - by Martin Prammer, Suryadev Sahadevan Rajesh, Junda Chen, Jignesh M. Patel from the University of Wisconsin-Madison. Martin and Jignesh are both credited as authors on the SQLite/DuckDB paper.

It's also the subject of this 12 minute video from the CIDR DB conference.

It's not clear to me if the code for this has been made available yet. I'd be fascinated to learn more about this project.

Tags: databases, sqlite, duckdb, d-richard-hipp, paper-review

The Untold Story of SQLite With Richard Hipp

2021-07-16T20:12:29+00:00

The Untold Story of SQLite With Richard Hipp

This is a really interesting interview with SQLite creator D. Richard Hipp—it covers all sorts of aspects of the SQLite story I hadn’t heard before, from its inspiration by a software challenge on a battleship to the first income from clients such as AOL and Symbian to the formation of the SQLite Consortium (based on advice from Mozilla’s Mitchell Baker) and more.

Tags: podcasts, sqlite, d-richard-hipp

Datasette 0.58: The annotated release notes

2021-07-16T02:21:11+00:00

I released Datasette 0.58 last night, with new plugin hooks, Unix domain socket support, a major faceting performance fix and a few other improvements. Here are the annotated release notes.

Faceting performance improvement

Facets remains my favourite feature in Datasette: it turns out a simple group by / count against a column is one of the most productive ways I know of to start understanding new data.

Yesterday I stumbled across a tiny tweak (details in this issue) that gave me a 10x performance boost on facet queries! Short version: given the following example query:

select
  country_long,
  count(*)
from (
  select * from [global-power-plants]
  order by rowid
)
where
  country_long is not null
group by
  country_long
order by
  count(*) desc

Removing the unnecessary order by rowid from that inner query knocked the time down from 53ms to 7.2ms (and makes even more of a difference on larger tables).

I was surprised SQLite didn't perform that optimization automatically - so I started a thread on the SQLite forum and SQLite author D. Richard Hipp figured out a patch! It's not yet certain that it will land in a SQLite release but I'm excited to have found an issue interesting enough to be worth looking into. (UPDATE: it landed on trunk).

The get_metadata() plugin hook

New plugin hook: get_metadata(datasette, key, database, table), for returning custom metadata for an instance, database or table. Thanks, Brandon Roberts! (#1384)

Brandon Roberts contributed this hook as part of work he's been doing with Newsday nextLI - always exciting to see Datasette used by another news organization. Brandon has a live demo of the plugins he has been building: datasette-live-config, datasette-live-permissions, datasette-csv-importer and datasette-surveys. He also has a 6 minute demo video explaining the project so far.

The new hook allows plugins to customize the metadata displayed for different databases and tables within the Datasette interface.

There is one catch at the moment: the plugin doesn't yet allow for async calls (including await db.execute(sql)) because Datasette's own internals currently treat access to metadata as a sync rather than async feature.

There are workarounds for this. Brandon's datasette-live-config plugin opens an additional, synchronous connection to the DB which is completely fine for fast queries. Another option would be to keep metadata in an in-memory Python dictionary which is updated by SQL queries that run in an async background task.

In the longer run though I'd like to redesign Datasette's internals to support asynchronous metadata access - ideally before Datasette 1.0.

The skip_csrf() plugin hook

New plugin hook: skip_csrf(datasette, scope), for opting out of CSRF protection based on the incoming request. (#1377)

I wanted to write a plugin that supported an HTTP POST to a Datasette form that wasn't protected by Datasette's CSRF protection. This proved surprisingly difficult! I ended up shipping asgi-csrf 0.9 with a new mechanism for custom opting-out of CSRF protection based on the ASGI scope, then exposing that mechanism in a new plugin hook in Datasette.

CSRF is such a frustrating security issue to write code against, because in modern browsers the SameSite cookie attribute more-or-less solves the problem for you... but that attribute only has 90% global usage according to caniuse.com - not quite enough for me to forget about it entirely.

There also remains one obscure edge-case in which SameSite won't help you: the definition of "same site" includes other subdomains of your domain (provided it's not on the Public Suffix List). This means that for SameSite CSRF protection to work you need to be confident that no subdomains of your domain will suffer an XSS - and in my experience its common for subdomains to be pointed at third-party applications that may not have the same stringent XSS protection as your main code.

So I continue to care about CSRF protection in Datasette.

Unix domain socket support

New datasette --uds /tmp/datasette.sock option for binding Datasette to a Unix domain socket, see proxy documentation. (#1388)

I wrote about this in my weeknotes - this is a great way to run Datasette if you have it behind a proxy such as Apache or nginx and don't want to have the Datasette server listening on a high port.

"searchmode": "raw" in table metadata

"searchmode": "raw" table metadata option for defaulting a table to executing SQLite full-text search syntax without first escaping it, see Advanced SQLite search queries. (#1389)

SQLite's built in full-text search feature includes support for advanced operators: you can use operators like AND, OR and NEAR and you can add column specifiers like name:Simon to restrict searches to individual columns.

This is something of a two-edged sword: I've found innocent looking queries that raise errors due to unexpected interactions with the query language.

In issue 651 I switched to escaping all queries by default to prevent these errors from happening, with a ?_searchmode=raw query string option for opting back into the default functionality.

I've since had a few requests for a mechanism to enable this by default - hence the new "searchmode": "raw" option in table metadata.

Link plugin hooks now take a request

The menu_links(), table_actions() and database_actions() plugin hooks all gained a new optional request argument providing access to the current request. (#1371)

I have a plugin which needs to add links to different places depending on the subdomain that the Datasette instance is running on. Adding request to these plugin hooks proved to be the easiest way to achieve this.

This is a really nice thing about how Pluggy (the plugin library used by Datasette) works: adding new named parameters to hooks can be done without breaking backwards compatibility with existing plugins.

And the rest

Improved documentation for Running Datasette behind a proxy to recommend using ProxyPreservehost On with Apache. (#1387)
POST requests to endpoints that do not support that HTTP verb now return a 405 error.
db.path can now be provided as a pathlib.Path object, useful when writing unit tests for plugins. Thanks, Chris Amico. (#1365)

Tags: csrf, releasenotes, sqlite, datasette, annotated-release-notes, d-richard-hipp, samesite

Pikchr

2020-10-21T16:02:48+00:00

Pikchr

Interesting new project from SQLite creator D. Richard Hipp. Pikchr is a new mini language for describing visual diagrams, designed to be embedded in Markdown documentation. It’s already enabled for the SQLite forum. Implementation is a no-dependencies C library and output is SVG.

Tags: c, sqlite, svg, markdown, d-richard-hipp

Quoting D. Richard Hipp

2018-05-10T05:15:49+00:00

The latest SQLite 3.8.7 alpha version is 50% faster than the 3.7.17 release from 16 months ago. That is to say, it does 50% more work using the same number of CPU cycles. [...] The 50% faster number above is not about better query plans. This is 50% faster at the low-level grunt work of moving bits on and off disk and search b-trees. We have achieved this by incorporating hundreds of micro-optimizations. Each micro-optimization might improve the performance by as little as 0.05%. If we get one that improves performance by 0.25%, that is considered a huge win. Each of these optimizations is unmeasurable on a real-world system (we have to use cachegrind to get repeatable run-times) but if you do enough of them, they add up.

— D. Richard Hipp

Tags: sqlite, performance, d-richard-hipp

SQLite Keynote

2006-10-21T23:44:28+00:00

SQLite Keynote

SQLite 3.3.8 has full text indexing!

Tags: full-text-search, sqlite, d-richard-hipp

More lightweight software: SQLite

2003-03-12T23:47:16+00:00

The other toy I've been playing with recently is SQLite. SQLite is an embeddable SQL database engine written in just under 25,000 lines of (heavily commented) C. Don't let the size fool you - it's phenomenally powerful and is released under a no-holds-barred public domain license that practically begs you to include it in your applications, commercial or not.

The feature list is pretty awesome - it's two times faster than both mySQL and Postgres on simple operations, implements an impressive amount of the SQL92 standard (including transaction support) and has bindings for Tcl, PHP, Perl, Python and Java.

That said, the lack of a security model (databases are stored in a single file with no user authentication / permissions system) and the fact that it's very much meant for embedding rather than featuring a client-server model mean it isn't really suitable for web applications. Where it would shine (and where the author D. Richard Hipp intended it to be used) is in applications that would benefit from an ultra fast relational database but don't need the overhead of embedding a large system such as mySQL. The no-strings license makes it a tempting prospect for that kind of work as well.

Definitely one for the tool box.

Tags: c, software, sqlite, d-richard-hipp