The Postgres development platform. Supabase gives you a dedicated Postgres database to build your web, mobile, and AI applications.
TypeScript aialternativeauthdatabasedenoembeddingsexamplefirebasenextjsoauth2pgvectorpostgispostgrespostgresqlpostgrestrealtimesupabasevectorswebsockets
import { literal, safeSql, type SafeSqlFragment } from '../../../pg-format'
import { Filter, Query } from '../../../query'
import { COUNT_ESTIMATE_SQL, THRESHOLD_COUNT, THRESHOLD_ESTIMATE_BYTES } from './get-count-estimate'
/**
* Row-count for a table. reltuples = -1 (never analyzed) covers both an
* empty/small new table and a freshly bulk-loaded huge one, so we gate on
* real heap size (pg_relation_size, + pg_partition_tree for partitioned
* parents whose own size is 0): large -> EXPLAIN estimate, small/empty ->
* exact count.
*/
export const getTableRowsCountSql = ({
table,
filters = [],
enforceExactCount = false,
isReadOnlyContext = false,
}: {
table: any
filters?: Filter[]
enforceExactCount?: boolean
/** Skip creating pg_temp.count_estimate; fall back to pg_class.reltuples. */
isReadOnlyContext?: boolean
}): SafeSqlFragment => {
if (!table) return safeSql``
if (enforceExactCount) {
const query = new Query()
let queryChains = query.from(table.name, table.schema ?? undefined).count()
filters
.filter((x) => x.value && x.value !== '')
.forEach((x) => {
queryChains = queryChains.filter(x.column, x.operator, x.value)
})
const queryChainsSql = queryChains.toSql()
const queryChainsSqlWithoutSemicolon = queryChainsSql.endsWith(';')
? (queryChainsSql.slice(0, -1) as SafeSqlFragment)
: queryChainsSql
return safeSql`select (${queryChainsSqlWithoutSemicolon}), false as is_estimate;`
} else {
const selectQuery = new Query()
let selectQueryChains = selectQuery.from(table.name, table.schema ?? undefined).select()
filters
.filter((x) => x.value && x.value != '')
.forEach((x) => {
selectQueryChains = selectQueryChains.filter(x.column, x.operator, x.value)
})
const selectBaseSql = selectQueryChains.toSql()
const selectBaseSqlWithoutSemicolon = selectBaseSql.endsWith(';')
? (selectBaseSql.slice(0, -1) as SafeSqlFragment)
: selectBaseSql
const countQuery = new Query()
let countQueryChains = countQuery.from(table.name, table.schema ?? undefined).count()
filters
.filter((x) => x.value && x.value != '')
.forEach((x) => {
countQueryChains = countQueryChains.filter(x.column, x.operator, x.value)
})
const countBaseSql = countQueryChains.toSql()
const countBaseSqlWithoutSemicolon = countBaseSql.endsWith(';')
? (countBaseSql.slice(0, -1) as SafeSqlFragment)
: countBaseSql
if (isReadOnlyContext) {
// Readonly can't create the pg_temp function: an over-threshold or
// physically-large never-analyzed table reports -1 (is_estimate=true);
// a small/empty one still gets an exact count.
const sql = safeSql`
with approximation as (
select
reltuples as estimate,
-- Whole-tree heap size. A partitioned PARENT (relkind 'p') has no storage
-- of its own, so its size is the sum over pg_partition_tree; every other
-- relkind uses its own heap directly (pg_partition_tree returns NO rows
-- for a plain non-partitioned table, so it cannot be used unconditionally).
-- Views/foreign tables yield 0 (-> exact count, unchanged behavior).
case when relkind = 'p'
then (select coalesce(sum(pg_relation_size(relid)), 0) from pg_partition_tree(oid))
else pg_relation_size(oid)
end as bytes
from pg_class
where oid = ${literal(table.id)}
)
select
case
when estimate > ${literal(THRESHOLD_COUNT)} or (estimate = -1 and bytes > ${literal(THRESHOLD_ESTIMATE_BYTES)}) then -1
else (${countBaseSqlWithoutSemicolon})
end as count,
(estimate > ${literal(THRESHOLD_COUNT)} or (estimate = -1 and bytes > ${literal(THRESHOLD_ESTIMATE_BYTES)})) as is_estimate
from approximation;
`
return sql
} else {
// estimate = -1 gated on heap size: large -> EXPLAIN estimate,
// small/empty -> exact count. literal() quotes the embedded select so
// backslash identifiers survive under any standard_conforming_strings.
const estimateExpr = safeSql`pg_temp.count_estimate(${literal(selectBaseSqlWithoutSemicolon)})`
const sql = safeSql`
${COUNT_ESTIMATE_SQL}
with approximation as (
select
reltuples as estimate,
-- Whole-tree heap size. A partitioned PARENT (relkind 'p') has no storage
-- of its own, so its size is the sum over pg_partition_tree; every other
-- relkind uses its own heap directly (pg_partition_tree returns NO rows
-- for a plain non-partitioned table, so it cannot be used unconditionally).
-- Views/foreign tables yield 0 (-> exact count, unchanged behavior).
case when relkind = 'p'
then (select coalesce(sum(pg_relation_size(relid)), 0) from pg_partition_tree(oid))
else pg_relation_size(oid)
end as bytes
from pg_class
where oid = ${literal(table.id)}
)
select
case
when estimate = -1 and bytes > ${literal(THRESHOLD_ESTIMATE_BYTES)} then ${estimateExpr}
when estimate > ${literal(THRESHOLD_COUNT)} then ${filters.length > 0 ? estimateExpr : safeSql`estimate`}
else (${countBaseSqlWithoutSemicolon})
end as count,
(estimate > ${literal(THRESHOLD_COUNT)} or (estimate = -1 and bytes > ${literal(THRESHOLD_ESTIMATE_BYTES)})) as is_estimate
from approximation;
`
return sql
}
}
}